[CESQL] Better string concatenation

[slinkydeveloper]

I think it would be nice to have better string concatenation, using the + operator:

id + "abc"

From my understanding, this is not really a standard operator, so only some databases support it:

• sql server supports it https://database.guide/6-ways-to-concatenate-string-and-number-sql-server/
• oracle sql and mysql doesn't support it, but they uses || https://www.sqlbook.com/sql-string-functions/sql-concatenate/

What are your opinions about it?

[slinkydeveloper]

cc @dug 😄

[duglin]

don't give too much weight to my opinion since I'm not an SQL expert, but if the goal is interop and adoption it seems we should focus on the more common pieces of SQL instead of extending it.

[slinkydeveloper]

I've found on an old ansi sql 99 doc that || is the standard string concatenation operator, but i would love to have somebody double check this.

[duglin]

General agreement on the 5/20 call... do not add it now. Let's see what the community thinks.

Hold off on || too. But general preference for || over +

[github-actions]

This issue is stale because it has been open for 30 days with no
activity. Mark as fresh by updating e.g., adding the comment /remove-lifecycle stale.

[duglin]

@Cali0707 any reason we should keep this one open? Any hint that Knative wants/needs this?

[Cali0707]

@duglin I can see how this would be nice, but I'm not totally sure if there would be a need for this in Knative. We only use CESQL in the subscriptions api context to evaluate to a boolean. I can't currently think of any obvious examples where these statements would need string concatenation. However, the string concatenation would definitely make these expressions easier. That being said, I'm not sure of a scenario where someone would want to make these types of queries.

For example, if I wanted to check if myextension = type || "abc", I could express this as myextension LIKE "%abd" AND LEFT(myextension, LENGTH(myextension) - 3) = type. This is definitely more verbose, and took me more time to come up with a "correct" solution. However, we are still able to express this idea with the current spec.

I'll try thinking of some more examples, but I'm curious what your thoughts are! WDYT about the example above, and of the scenario where we can still express these ideas, it's just more complicated. Do you think it may be worth expanding the spec to simplify these use cases, or is it better to keep the spec simpler?

[duglin]

@Cali0707 any update on this? Should we close and re-open if someone wants to drive it?

[Cali0707]

I don't have strong opinions on including vs. not including this one. With the CONCAT function I think we cover the basic use case, even though the operator could be nicer to use.

However, I do think that if we do go with the operator we should use || instead of + to avoid any confusion for CESQL users if their extension attributes may be different types.

As an example, if I wrote mystringextension + otherextension, if otherextension is a string then we would do string concatenation, but if otherextension was an integer, we would attempt to convert mystringextension to an integer, which may fail.

I feel that this could lead to unnecessary confusion for users, so my vote is that if we do include this we do so with the || operator.

[duglin]

I agree that || would be better than +.

I also agree that CONCAT fills the need.

What if we did this... could you ask in the Knative community (like @lionelvillard?) to see what they think? If they either don't like the idea or don't have any strong opinion then perhaps we leaving things as they are now until someone comes with a good reason to add it. Thoughts?

[Cali0707]

Sure, I can send an email to the knative mailing lists and ask in slack to see if there is any strong interest in this

[duglin]

thanks!!