Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

SQL: fix string functions handling of NULL parameter values #58907

Closed
bpintea opened this issue Jul 2, 2020 · 2 comments · Fixed by #68379
Closed

SQL: fix string functions handling of NULL parameter values #58907

bpintea opened this issue Jul 2, 2020 · 2 comments · Fixed by #68379
Labels
:Analytics/SQL SQL querying >bug Team:QL (Deprecated) Meta label for query languages team

Comments

@bpintea
Copy link
Contributor

bpintea commented Jul 2, 2020

Some string functions that take an integer parameter - like SUBSTRING or INSERT - exhibit a potentially unexpected behavior when any of the integer parameters is computed from a column containing NULL values: the function simply returns the original string [expression] value, applying no operation.
Example: SELECT SUBSTRING(str_column, int_column_with_nulls, some_lenght) = str_column will return true everyWHERE int_column_with_nulls IS NULL.

This is different from directly providing a NULL literal for any of the parameters, when the function returns NULL (as expected).

Note that other RDBMSes (ex: SQL Server, Postgres, MariaDB) return NULL when any parameter - either literal or attribute - is NULL; we should arguably follow lead or at least document the behavior.

@bpintea bpintea added >bug :Analytics/SQL SQL querying labels Jul 2, 2020
@elasticmachine
Copy link
Collaborator

Pinging @elastic/es-ql (:Query Languages/SQL)

@elasticmachine elasticmachine added the Team:QL (Deprecated) Meta label for query languages team label Jul 2, 2020
@bpintea
Copy link
Contributor Author

bpintea commented Jul 6, 2020

Example: SELECT str, int, substring(str, int, 3) AS sub FROM index:

      str      |      int      |      sub      
---------------+---------------+---------------
FURNITURE      |17             |               
OFFICE SUPPLIES|null           |OFFICE SUPPLIES
OFFICE SUPPLIES|9              |UPP            

or SELECT str, num, substring(str, 1, num) AS sub FROM index:

      str      |     num       |      sub      
---------------+---------------+---------------
FURNITURE      |17             |FURNITURE      
OFFICE SUPPLIES|null           |OFFICE SUPPLIES
OFFICE SUPPLIES|9              |OFFICE SU 

@bpintea bpintea changed the title SQL: fix or document string function handling of NULLs SQL: fix string functions handling of NULL parameter values Jul 6, 2020
palesz pushed a commit to palesz/elasticsearch that referenced this issue Feb 2, 2021
Fixed the inconsistencies regarding NULL argument handling.
NULL literal vs NULL field value as function arguments in some case
resulted in different function return values.

Functions should return with the same value no matter if the argument(s)
came from a field or from a literal.

The introduced integration test tests if function calls with same
argument values (regardless of literal/field) will return with the
same output (also checks if newly added functions are added to the
testcases).

Fixed the following functions:
* Insert: NULL start, length and replacement arguments (as fields) also
result in NULL return value instead of returning the input.
* Locate: NULL pattern results in NULL return value, NULL start
parameter results 0 return value (0 = no match) instead of NULL
* Replace: NULL pattern and replacement results in NULL instead of
returning the input
* Substring: NULL start or length results in NULL instead of returning
the input

Fixes elastic#58907
palesz pushed a commit that referenced this issue Feb 5, 2021
Fixed the inconsistencies regarding NULL argument handling.
NULL literal vs NULL field value as function arguments in some case
resulted in different function return values.

Functions should return with the same value no matter if the argument(s)
came from a field or from a literal.

The introduced integration test tests if function calls with same
argument values (regardless of literal/field) will return with the
same output (also checks if newly added functions are added to the
testcases).

Fixed the following functions:
* Insert: NULL start, length and replacement arguments (as fields) also
result in NULL return value instead of returning the input.
* Locate: NULL pattern results in NULL return value, NULL optional start
argument handled the same as missing start argument
* Replace: NULL pattern and replacement results in NULL instead of
returning the input
* Substring: NULL start or length results in NULL instead of returning
the input

Fixes #58907
palesz pushed a commit to palesz/elasticsearch that referenced this issue Feb 8, 2021
…68379)

Fixed the inconsistencies regarding NULL argument handling.
NULL literal vs NULL field value as function arguments in some case
resulted in different function return values.

Functions should return with the same value no matter if the argument(s)
came from a field or from a literal.

The introduced integration test tests if function calls with same
argument values (regardless of literal/field) will return with the
same output (also checks if newly added functions are added to the
testcases).

Fixed the following functions:
* Insert: NULL start, length and replacement arguments (as fields) also
result in NULL return value instead of returning the input.
* Locate: NULL pattern results in NULL return value, NULL optional start
argument handled the same as missing start argument
* Replace: NULL pattern and replacement results in NULL instead of
returning the input
* Substring: NULL start or length results in NULL instead of returning
the input

Fixes elastic#58907
palesz pushed a commit that referenced this issue Feb 8, 2021
…68684)

Fixed the inconsistencies regarding NULL argument handling.
NULL literal vs NULL field value as function arguments in some case
resulted in different function return values.

Functions should return with the same value no matter if the argument(s)
came from a field or from a literal.

The introduced integration test tests if function calls with same
argument values (regardless of literal/field) will return with the
same output (also checks if newly added functions are added to the
testcases).

Fixed the following functions:
* Insert: NULL start, length and replacement arguments (as fields) also
result in NULL return value instead of returning the input.
* Locate: NULL pattern results in NULL return value, NULL optional start
argument handled the same as missing start argument
* Replace: NULL pattern and replacement results in NULL instead of
returning the input
* Substring: NULL start or length results in NULL instead of returning
the input

Fixes #58907

(cherry-pick from a3dbdae)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
:Analytics/SQL SQL querying >bug Team:QL (Deprecated) Meta label for query languages team
Projects
None yet
Development

Successfully merging a pull request may close this issue.

2 participants