Skip to content

SQL: Interval arithmetics failure #41200

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.

Sign up for GitHub

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

Jump to bottom
Closed
astefan opened this issue Apr 15, 2019 · 2 comments
Closed

SQL: Interval arithmetics failure #41200

astefan opened this issue Apr 15, 2019 · 2 comments
Assignees
@matriv
Labels
:Analytics/SQL SQL querying >bug v6.8.3 v7.1.2 v7.2.2

Comments

@astefan
Copy link
Contributor

astefan commented Apr 15, 2019 

sql> SELECT CONVERT(TRUNCATE((-1 * (DAYOFYEAR(date2) - 1)),0), SQL_BIGINT) * INTERVAL '1' DAY from calcs;
Server error [Server encountered an error [A number is required; received +1 00:00:00.0]. [SqlIllegalArgumentException[A number is required; received +1 00:00:00.0]
        at org.elasticsearch.xpack.sql.expression.predicate.operator.arithmetic.BinaryArithmeticProcessor.doProcess(BinaryArithmeticProcessor.java:173)
        at org.elasticsearch.xpack.sql.expression.gen.processor.BinaryProcessor.process(BinaryProcessor.java:50)
        at org.elasticsearch.xpack.sql.execution.search.extractor.ComputingExtractor.extract(ComputingExtractor.java:78)
        at org.elasticsearch.xpack.sql.execution.search.SearchHitRowSet.extractValue(SearchHitRowSet.java:111)
        at org.elasticsearch.xpack.sql.execution.search.SearchHitRowSet.extractValue(SearchHitRowSet.java:23)
        at org.elasticsearch.xpack.sql.execution.search.ResultRowSet.getColumn(ResultRowSet.java:37)
        at org.elasticsearch.xpack.sql.session.AbstractRowSet.column(AbstractRowSet.java:18)
        at org.elasticsearch.xpack.sql.session.RowView.forEachColumn(RowView.java:38)

Same query, but with grouping works though:

sql> select CONVERT(TRUNCATE((-1 * (DAYOFYEAR(date2) - 1)),0), SQL_BIGINT) * INTERVAL '1' DAY from calcs GROUP BY 1;
CONVERT(TRUNCATE((-1 * (DAYOFYEAR(date2) - 1)),0), SQL_BIGINT) * INTERVAL '1' DAY                                   
---------------------------------------------------------------------------------                                   
-109 00:00:00.0                                                                                                     
-116 00:00:00.0                                                                                                     
-122 00:00:00.0

The test query can be simplified to something like select CONVERT(DAYOFYEAR(date2), SQL_BIGINT) * INTERVAL '1' DAY from calcs
@elasticmachine
Copy link
Collaborator

Pinging @elastic/es-search

@astefan astefan mentioned this issue Apr 16, 2019
Closed
@matriv matriv self-assigned this Apr 23, 2019
@matriv matriv mentioned this issue May 9, 2019
Merged
@matriv matriv closed this as completed in 91039ba May 15, 2019
matriv added a commit that referenced this issue May 15, 2019
@matriv
SQL: Fix issue regarding INTERVAL * number (#42014)
7473742 
Interval * integer number is a valid operation which previously was
only supported for foldables (literals) and not when a field was
involved. That was because:

1. There was no common type returned for that combination
2. The `BinaryArithmeticOperation` was permitting the multiplication
(called by fold()) but the BinaryArithmeticProcessor didn't allow it

Moreover the error message for invalid arithmetic operations was wrong
because of the issue with the overloading methods of
`LoggerMessageFormat.format`.

Fixes: #41239
Fixes: #41200
(cherry picked from commit 91039ba)
matriv added a commit that referenced this issue May 15, 2019
@matriv
SQL: Fix issue regarding INTERVAL * number (#42014)
08d38da 
Interval * integer number is a valid operation which previously was
only supported for foldables (literals) and not when a field was
involved. That was because:

1. There was no common type returned for that combination
2. The `BinaryArithmeticOperation` was permitting the multiplication
(called by fold()) but the BinaryArithmeticProcessor didn't allow it

Moreover the error message for invalid arithmetic operations was wrong
because of the issue with the overloading methods of
`LoggerMessageFormat.format`.

Fixes: #41239
Fixes: #41200
(cherry picked from commit 91039ba)
matriv added a commit that referenced this issue May 15, 2019
@matriv
SQL: Fix issue regarding INTERVAL * number (#42014)
3a20503 
Interval * integer number is a valid operation which previously was
only supported for foldables (literals) and not when a field was
involved. That was because:

1. There was no common type returned for that combination
2. The `BinaryArithmeticOperation` was permitting the multiplication
(called by fold()) but the BinaryArithmeticProcessor didn't allow it

Moreover the error message for invalid arithmetic operations was wrong
because of the issue with the overloading methods of
`LoggerMessageFormat.format`.

Fixes: #41239
Fixes: #41200
(cherry picked from commit 91039ba)
@matriv
Copy link
Contributor

matriv commented May 15, 2019

Backported to 7.x with 7473742
to 7.1 with 08d38da
to 6.8 with 3a20503

gurkankaymak pushed a commit to gurkankaymak/elasticsearch that referenced this issue May 27, 2019
@matriv
SQL: Fix issue regarding INTERVAL * number (elastic#42014)
4b8f32b 
Interval * integer number is a valid operation which previously was
only supported for foldables (literals) and not when a field was
involved. That was because:

1. There was no common type returned for that combination
2. The `BinaryArithmeticOperation` was permitting the multiplication
(called by fold()) but the BinaryArithmeticProcessor didn't allow it

Moreover the error message for invalid arithmetic operations was wrong
because of the issue with the overloading methods of
`LoggerMessageFormat.format`.

Fixes: elastic#41239
Fixes: elastic#41200
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees

@matriv matriv

Labels
:Analytics/SQL SQL querying >bug v6.8.3 v7.1.2 v7.2.2
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
4 participants
@costin @astefan @matriv @elasticmachine