sql: remake string value functions

[romanhabibov]

These are functions like TRIM(), QUOTE(), SUBSTR(), HEX(), LOWER(). If we want to follow ANSI SQL, we have to:

1. disallow using other types for args which supposed to be string. String and binary string must be allowed only.
2. modify the signatures of the functions according to the standard (if needed).
3. make each function with two or more args accept only args of the same type. For example, TRIM(string FROM string) - allowed, TRIM(binary_string FROM string) - disallowed.

From discussion #3879

[pgulutzan]

Also SELECT TYPEOF(TRIM(both X'00' FROM X'')); should return 'varbinary'. Currently it returns 'string'.

[pgulutzan]

SELECT TRIM(1 FROM '123'); currently does nothing, and will be illegal after this fix.
In other words, this will be another case where we do not automatically cast integer to string.

[ImeevMA]

@pgulutzan I plan to rework some SQL built-in functions. What I plan to do:

1. Rework substr() function. Now there will be substring() function:

<character substring function> ::=
SUBSTRING (<character value expression> FROM <start position> [ FOR <string length> ])
<binary substring function> ::=
SUBSTRING (<binary value expression> FROM <start position> [ FOR <string length> ])

I removed [ USING <char length units> ] in <character substring function> definition for now. I will add it later in all functions, that needs it.

How it will work:

SUBSTRING (<character value expression> FROM <start position>) = SUBSTR(<character value expression>, MAX(1, <start position>))
SUBSTRING (<character value expression> FROM <start position> FOR <string length>) = SUBSTR(<character value expression>, MAX(1, <start position>), <string length>)

If value of <string length> is less than 0, an error is thrown.
The same for <binary value expression>. Function substr() will be removed. What do you think?

[pgulutzan]

@ImeevMA:

I believe that when you write MAX(1, <start position>) you mean,
in Tarantool/SQL terms, GREATEST(1, <start position>). That is fine.

I have a simple C program that simulates the SQL-2016 standard rules
for feature E021-06 SUBSTRING function in
Subclause 6.32, "<string value function>":
This is what it tells me will be returned for various situations.
SUBSTRING('abc' FROM 1 FOR 1)        a
SUBSTRING('abc' FROM -1 FOR 1)       zero-length string
SUBSTRING('abc' FROM 1 FOR -1)        data exception -- substring error
SUBSTRING('abc' FROM 3 FOR 5)         c
SUBSTRING('abc' FROM 2)                    bc
SUBSTRING('' FROM -1)                        zero-length string
SUBSTRING('abc' FROM 0 FOR 1000000000)     abc
Do you think it is correct?
It seems to me that one of the answers is different from
what you propose.

I know that SUBSTR() currently accepts DECIMAL and DOUBLE
values for start position.
(The standard would say that DECIMAL with scale 0 is okay
but we don't define that.)
Do you intend to allow non-integer values for SUBSTRING?

It is an undocumented feature that, when start position is negative,
SUBSTR() counts backwards from the end of the string. For example
SELECT SUBSTR('abc',-1,4); is 'c'
SELECT substr('abc',-1,-3); is 'ab'
...
It is not quite like Lua's string.sub
string.sub('abc',-1,4)        is 'a'
string.sub('abc',-1,-3)       is ''
Similar behaviour appears in MySQL and Oracle and SQLite
(not SQL Server).
Do you think that anyone should care if we're compatible with them?

Since you will disallow USING OCTETS, I guess the only way to 
find a byte-substring in a STRING is to cast. For example
SELECT HEX(SUBSTRING(CAST('aГc' AS VARBINARY) FROM 3 FOR 2));
is 9363, and
SELECT TYPEOF(SUBSTRING(CAST('aГc' AS VARBINARY) FROM 3 FOR 2));
is varbinary.
Can you think of a better way?

Why not allow data type UUID as well as STRING and VARBINARY?

In what Tarantool version will you deprecate and/or disallow SUBSTR()?

[ImeevMA]

@pgulutzan thank you for the answer! My answers below.

I was planning to drop SUBSTR() in the next release. However, with a little thought, I realized that it might not be the best idea to just drop it without proper deprecation. @unera @kyukhin what do you think? We can just leave it, but I suggest making it a smaller version of ANSI's SUBSTRING() function. It will have the same rules as SUBSTRING(), but it will not have its own syntax and will not be USING OCTETS.

About your program, that simulates ANSI - I believe the SQL-2011 standard (6.30 , in General Rules, 3)-f)-ii)-1)) says:

1) Let S1 be the larger of S and 1 (one).

The S here is value of the <start position>. Should we do what ANSI says, or are you proposing to change this part? I do not have text of SQL-2016 standard.

I executed code below in MySQL, PostgreSQL, MS SQL Server:

CREATE TABLE t(i INT PRIMARY KEY, s TEXT);
INSERT INTO t VALUES(1, '1234567890');
SELECT SUBSTRING(s FROM 5 FOR 3) FROM t;
SELECT SUBSTRING(s FROM -5 FOR 3) FROM t;
SELECT SUBSTRING(s FROM 5 FOR -3) FROM t;
SELECT SUBSTRING(s FROM -5 FOR -3) FROM t;

The results are:

MySQL 5.6:

567
678
NULL
NULL

PostgreSQL 9.6:

567
NULL
error
error

MS SQL Server 2017:

567
NULL
error
error

In addition, MySQL and PostgreSQL can work with both SUBSTRING(s from 5 for 3) and SUBSTRING(s, 5, 3). MS SQL Server can only work with SUBSTRING(s, 5, 3)

For us the result will be:

567
123
error
error

What do you think about such difference?

Also, after some thought, I came to the conclusion that I would add USING CHARACTERS and USING OCTETS to the SUBSTRING() function.

[pgulutzan]

Re deprecation:
As you know, I deplore the 2.10-beta changes. But this one, SUBSTR, is relatively small.

Re "1) Let S1 be the larger of S and 1 (one)":
I agree with that,
I hope I didn't something so unclearly that you think I want to ignore the standard,
and the 2016 version has the same sub-clause as the 2011 version.

Re "I executed code below in MySQL, PostgreSQL, MS SQL Server":
The NULLs are surprising.
Is it possible that you used a client that doesn't distinguish NULL from zero-length string?
Can you confirm with "SELECT SUBSTRING(s FROM 5 FOR -3) is null FROM t;"?
As for the particular results:
MySQL of course is counting from the end, I asked what others think of such behaviour.
PostgreSQL doesn't appear to be acting like Oracle, I had expected that it would.
SQL Server's manual
https://docs.microsoft.com/en-us/sql/t-sql/functions/substring-transact-sql?view=sql-server-ver15
is what I prefer to believe.

Re "For us the result will be:"
By the time you reach f) what do you calculate are the values of the
relevant variables? My program is outputting:
./substring 1234567890 -5 3
character_value_expression: 1234567890.
start_position: -5
string_length: 3
LC: 10
S: -5
L: 3
E: -2
S > LC or E < 1. Return zero-length string
I have checked. I have checked again. I think it is right.
Therefore I think you are ignoring f)i).

Re "USING OCTETS":
Sure. Of course the result string might not be valid UTF-8 but we never promise that.

Re my other questions:
I guess you'll be able to answer after you start making the changes.

[ImeevMA]

@pgulutzan you are right: I wrongly interpreted '' as NULL and I missed f)i).

About UUID as an argument - I am not sure how to implement this. I mean, should it work as SUBSTR(CAST(uuid AS STRING), start, length) or SUBSTR(CAST(uuid AS VARBINARY), start, length)?

About non-integer arguments - if an argument can be implicitly cast to INTEGER, than there is no reason to not accept it.

In a few days I will submit a document in which I describe what I plan to do with the existing SQL built-in functions and what SQL built-in function I plan to add in the next release. There will be quite a few questions that I hope you can help us resolve.

[pgulutzan]

I have published a blog post about the SQL substring function:
http://ocelot.ca/blog/blog/2021/09/02/the-sql-substring-function/
Although it is not specifically about Tarantool, I believe it will clarify some things for any reader of this issue. And I included the program that I was talking about earlier.