[jira] [Commented] (CALCITE-2981) Syntax error with simple nested select query
[ https://issues.apache.org/jira/browse/CALCITE-2981?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16814648#comment-16814648 ] Vamshi commented on CALCITE-2981: - [~my7ym] I know that work around but this still needs fix in Calcite grammar. If SQL is generated from BI tool workflows, modifying SQL is not an option. A simple query like select * from ((select * from t1)dt) reports syntax error. Everything runs fine if the sub queries are under from clause, Grammar in TableRef2 handles this well. if the subqueries are joined within nested parenthesis then this path is not handled for joins but is handled for Set operators(union/intersect/minus) in QueryOrExpr grammar. Basically most of the logic in TableRef2 needs to be pulled up to QueryorExpr routine. > Syntax error with simple nested select query > > > Key: CALCITE-2981 > URL: https://issues.apache.org/jira/browse/CALCITE-2981 > Project: Calcite > Issue Type: Bug > Components: core >Affects Versions: 1.18.0 >Reporter: Vamshi >Assignee: Danny Chan >Priority: Critical > > Below standard sql-92 query reports syntax error in calcite: > create table test1(id integer, name integer); > create table test2(id integer, name integer); > select * from ( > ( select id,name from test1 ) test3 > inner join > ( select id,name from test2) test4 on test4.id = test3.id > ); > Caused by: org.apache.calcite.parser.ParseException: Encountered "test3" at > line 4, column 31. > Was expecting one of: > ")" ... > "ORDER" ... > "LIMIT" ... > "OFFSET" ... > "FETCH" ... > "UNION" ... > "INTERSECT" ... > "EXCEPT" ... > "MINUS" ... > "." ... > "NOT" ... > "IN" ... > "<" ... > "<=" ... > ">" ... > ">=" ... > "=" ... > "<>" ... > "!=" ... > "BETWEEN" ... > "LIKE" ... > "SIMILAR" ... > "+" ... > "-" ... > "*" ... > "/" ... > "%" ... > "||" ... > "AND" ... > "OR" ... > "IS" ... > "MEMBER" ... > "SUBMULTISET" ... > "CONTAINS" ... > "OVERLAPS" ... > "EQUALS" ... > "PRECEDES" ... > "SUCCEEDS" ... > "IMMEDIATELY" ... > "MULTISET" ... > "[" ... > "YEAR" ... > "MONTH" ... > "DAY" ... > "HOUR" ... > "MINUTE" ... > "SECOND" ... > > The above query works in standard databases like postgres/oracle/sql server. > This is a commonly generated query pattern by popular BI tools. > -- This message was sent by Atlassian JIRA (v7.6.3#76005)
[jira] [Updated] (CALCITE-2981) Syntax error with simple nested select query
[ https://issues.apache.org/jira/browse/CALCITE-2981?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Vamshi updated CALCITE-2981: Priority: Critical (was: Major) Description: Below standard sql-92 query reports syntax error in calcite: create table test1(id integer, name integer); create table test2(id integer, name integer); select * from ( ( select id,name from test1 ) test3 inner join ( select id,name from test2) test4 on test4.id = test3.id ); Caused by: org.apache.calcite.parser.ParseException: Encountered "test3" at line 4, column 31. Was expecting one of: ")" ... "ORDER" ... "LIMIT" ... "OFFSET" ... "FETCH" ... "UNION" ... "INTERSECT" ... "EXCEPT" ... "MINUS" ... "." ... "NOT" ... "IN" ... "<" ... "<=" ... ">" ... ">=" ... "=" ... "<>" ... "!=" ... "BETWEEN" ... "LIKE" ... "SIMILAR" ... "+" ... "-" ... "*" ... "/" ... "%" ... "||" ... "AND" ... "OR" ... "IS" ... "MEMBER" ... "SUBMULTISET" ... "CONTAINS" ... "OVERLAPS" ... "EQUALS" ... "PRECEDES" ... "SUCCEEDS" ... "IMMEDIATELY" ... "MULTISET" ... "[" ... "YEAR" ... "MONTH" ... "DAY" ... "HOUR" ... "MINUTE" ... "SECOND" ... The above query works in standard databases like postgres/oracle/sql server. This is a commonly generated query pattern by popular BI tools. was: Below standard sql query reports a syntax error: create table test1(id integer, name integer); create table test2(id integer, name integer); select * from ( ( select id,name from test1 ) test3 inner join ( select id,name from test2) test4 on test4.id = test3.id ); Caused by: org.apache.calcite.parser.ParseException: Encountered "test3" at line 4, column 31. Was expecting one of: ")" ... "ORDER" ... "LIMIT" ... "OFFSET" ... "FETCH" ... "UNION" ... "INTERSECT" ... "EXCEPT" ... "MINUS" ... "." ... "NOT" ... "IN" ... "<" ... "<=" ... ">" ... ">=" ... "=" ... "<>" ... "!=" ... "BETWEEN" ... "LIKE" ... "SIMILAR" ... "+" ... "-" ... "*" ... "/" ... "%" ... "||" ... "AND" ... "OR" ... "IS" ... "MEMBER" ... "SUBMULTISET" ... "CONTAINS" ... "OVERLAPS" ... "EQUALS" ... "PRECEDES" ... "SUCCEEDS" ... "IMMEDIATELY" ... "MULTISET" ... "[" ... "YEAR" ... "MONTH" ... "DAY" ... "HOUR" ... "MINUTE" ... "SECOND" ... The above query works in standard databases like postgres/oracle/sql server > Syntax error with simple nested select query > > > Key: CALCITE-2981 > URL: https://issues.apache.org/jira/browse/CALCITE-2981 > Project: Calcite > Issue Type: Bug > Components: core >Affects Versions: 1.18.0 >Reporter: Vamshi >Priority: Critical > > Below standard sql-92 query reports syntax error in calcite: > create table test1(id integer, name integer); > create table test2(id integer, name integer); > select * from ( > ( select id,name from test1 ) test3 > inner join > ( select id,name from test2) test4 on test4.id = test3.id > ); > Caused by: org.apache.calcite.parser.ParseException: Encountered "test3" at > line 4, column 31. > Was expecting one of: > ")" ... > "ORDER" ... > "LIMIT" ... > "OFFSET" ... > "FETCH" ... > "UNION" ... > "INTERSECT" ... > "EXCEPT" ... > "MINUS" ... > "." ... > "NOT" ... > "IN" ... > "<" ... > "<=" ... > ">" ... > ">=" ... > "=" ... > "<>" ... > "!=" ... > "BETWEEN" ... > "LIKE" ... > "SIMILAR" ... > "+" ... > "-" ... > "*" ... > "/" ... > "%" ... > "||" ... > "AND" ... > "OR" ... > "IS" ... > "MEMBER" ... > "SUBMULTISET" ... > "CONTAINS" ... > "OVERLAPS" ... > "EQUALS" ... > "PRECEDES" ... > "SUCCEEDS" ... > "IMMEDIATELY" ... > "MULTISET" ... > "[" ... > "YEAR" ... > "MONTH" ... > "DAY" ... > "HOUR" ... > "MINUTE" ... > "SECOND" ... > > The above query works in standard databases like postgres/oracle/sql server. > This is a commonly generated query pattern by popular BI tools. > -- This message was sent by Atlassian JIRA (v7.6.3#76005)
[jira] [Updated] (CALCITE-2981) Syntax error with simple nested select query
[ https://issues.apache.org/jira/browse/CALCITE-2981?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Vamshi updated CALCITE-2981: Description: Below standard sql query reports a syntax error: create table test1(id integer, name integer); create table test2(id integer, name integer); select * from ( ( select id,name from test1 ) test3 inner join ( select id,name from test2) test4 on test4.id = test3.id ); Caused by: org.apache.calcite.parser.ParseException: Encountered "test3" at line 4, column 31. Was expecting one of: ")" ... "ORDER" ... "LIMIT" ... "OFFSET" ... "FETCH" ... "UNION" ... "INTERSECT" ... "EXCEPT" ... "MINUS" ... "." ... "NOT" ... "IN" ... "<" ... "<=" ... ">" ... ">=" ... "=" ... "<>" ... "!=" ... "BETWEEN" ... "LIKE" ... "SIMILAR" ... "+" ... "-" ... "*" ... "/" ... "%" ... "||" ... "AND" ... "OR" ... "IS" ... "MEMBER" ... "SUBMULTISET" ... "CONTAINS" ... "OVERLAPS" ... "EQUALS" ... "PRECEDES" ... "SUCCEEDS" ... "IMMEDIATELY" ... "MULTISET" ... "[" ... "YEAR" ... "MONTH" ... "DAY" ... "HOUR" ... "MINUTE" ... "SECOND" ... The above query works in standard databases like postgres/oracle/sql server was: Below standard query reports a syntax error: select * from ( ( select id,name from test1 ) test3 inner join ( select id,name from test2) test4 on test4.id = test3.id )dt Caused by: org.apache.calcite.parser.ParseException: Encountered "test3" at line 4, column 31. Was expecting one of: ")" ... "ORDER" ... "LIMIT" ... "OFFSET" ... "FETCH" ... "UNION" ... "INTERSECT" ... "EXCEPT" ... "MINUS" ... "." ... "NOT" ... "IN" ... "<" ... "<=" ... ">" ... ">=" ... "=" ... "<>" ... "!=" ... "BETWEEN" ... "LIKE" ... "SIMILAR" ... "+" ... "-" ... "*" ... "/" ... "%" ... "||" ... "AND" ... "OR" ... "IS" ... "MEMBER" ... "SUBMULTISET" ... "CONTAINS" ... "OVERLAPS" ... "EQUALS" ... "PRECEDES" ... "SUCCEEDS" ... "IMMEDIATELY" ... "MULTISET" ... "[" ... "YEAR" ... "MONTH" ... "DAY" ... "HOUR" ... "MINUTE" ... "SECOND" ... The above query works in standard databases like postgres > Syntax error with simple nested select query > > > Key: CALCITE-2981 > URL: https://issues.apache.org/jira/browse/CALCITE-2981 > Project: Calcite > Issue Type: Bug > Components: core >Affects Versions: 1.18.0 >Reporter: Vamshi >Priority: Major > > Below standard sql query reports a syntax error: > create table test1(id integer, name integer); > create table test2(id integer, name integer); > select * from ( > ( select id,name from test1 ) test3 > inner join > ( select id,name from test2) test4 on test4.id = test3.id > ); > Caused by: org.apache.calcite.parser.ParseException: Encountered "test3" at > line 4, column 31. > Was expecting one of: > ")" ... > "ORDER" ... > "LIMIT" ... > "OFFSET" ... > "FETCH" ... > "UNION" ... > "INTERSECT" ... > "EXCEPT" ... > "MINUS" ... > "." ... > "NOT" ... > "IN" ... > "<" ... > "<=" ... > ">" ... > ">=" ... > "=" ... > "<>" ... > "!=" ... > "BETWEEN" ... > "LIKE" ... > "SIMILAR" ... > "+" ... > "-" ... > "*" ... > "/" ... > "%" ... > "||" ... > "AND" ... > "OR" ... > "IS" ... > "MEMBER" ... > "SUBMULTISET" ... > "CONTAINS" ... > "OVERLAPS" ... > "EQUALS" ... > "PRECEDES" ... > "SUCCEEDS" ... > "IMMEDIATELY" ... > "MULTISET" ... > "[" ... > "YEAR" ... > "MONTH" ... > "DAY" ... > "HOUR" ... > "MINUTE" ... > "SECOND" ... > > The above query works in standard databases like postgres/oracle/sql server > -- This message was sent by Atlassian JIRA (v7.6.3#76005)
[jira] [Updated] (CALCITE-2981) Syntax error with simple nested select query
[ https://issues.apache.org/jira/browse/CALCITE-2981?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Vamshi updated CALCITE-2981: Description: Below standard query reports a syntax error: select * from ( ( select id,name from test1 ) test3 inner join ( select id,name from test2) test4 on test4.id = test3.id )dt Caused by: org.apache.calcite.parser.ParseException: Encountered "test3" at line 4, column 31. Was expecting one of: ")" ... "ORDER" ... "LIMIT" ... "OFFSET" ... "FETCH" ... "UNION" ... "INTERSECT" ... "EXCEPT" ... "MINUS" ... "." ... "NOT" ... "IN" ... "<" ... "<=" ... ">" ... ">=" ... "=" ... "<>" ... "!=" ... "BETWEEN" ... "LIKE" ... "SIMILAR" ... "+" ... "-" ... "*" ... "/" ... "%" ... "||" ... "AND" ... "OR" ... "IS" ... "MEMBER" ... "SUBMULTISET" ... "CONTAINS" ... "OVERLAPS" ... "EQUALS" ... "PRECEDES" ... "SUCCEEDS" ... "IMMEDIATELY" ... "MULTISET" ... "[" ... "YEAR" ... "MONTH" ... "DAY" ... "HOUR" ... "MINUTE" ... "SECOND" ... The above query works in standard databases like postgres was: Below standard query reports a syntax error: select * from ( ( select id,name from test1 ) test3 inner join ( select id,name from test2) test4 on test4.id = test3.id )dt Caused by: org.apache.calcite.parser.ParseException: Encountered "test3" at line 4, column 31. Was expecting one of: ")" ... "ORDER" ... "LIMIT" ... "OFFSET" ... "FETCH" ... "UNION" ... "INTERSECT" ... "EXCEPT" ... "MINUS" ... "." ... "NOT" ... "IN" ... "<" ... "<=" ... ">" ... ">=" ... "=" ... "<>" ... "!=" ... "BETWEEN" ... "LIKE" ... "SIMILAR" ... "+" ... "-" ... "*" ... "/" ... "%" ... "||" ... "AND" ... "OR" ... "IS" ... "MEMBER" ... "SUBMULTISET" ... "CONTAINS" ... "OVERLAPS" ... "EQUALS" ... "PRECEDES" ... "SUCCEEDS" ... "IMMEDIATELY" ... "MULTISET" ... "[" ... "YEAR" ... "MONTH" ... "DAY" ... "HOUR" ... "MINUTE" ... "SECOND" ... The above query works in standard databases like oracle/postgres/sql server. > Syntax error with simple nested select query > > > Key: CALCITE-2981 > URL: https://issues.apache.org/jira/browse/CALCITE-2981 > Project: Calcite > Issue Type: Bug > Components: core >Affects Versions: 1.18.0 >Reporter: Vamshi >Priority: Major > > Below standard query reports a syntax error: > select * from ( > ( select id,name from test1 ) test3 > inner join > ( select id,name from test2) test4 on test4.id = test3.id > )dt > Caused by: org.apache.calcite.parser.ParseException: Encountered "test3" at > line 4, column 31. > Was expecting one of: > ")" ... > "ORDER" ... > "LIMIT" ... > "OFFSET" ... > "FETCH" ... > "UNION" ... > "INTERSECT" ... > "EXCEPT" ... > "MINUS" ... > "." ... > "NOT" ... > "IN" ... > "<" ... > "<=" ... > ">" ... > ">=" ... > "=" ... > "<>" ... > "!=" ... > "BETWEEN" ... > "LIKE" ... > "SIMILAR" ... > "+" ... > "-" ... > "*" ... > "/" ... > "%" ... > "||" ... > "AND" ... > "OR" ... > "IS" ... > "MEMBER" ... > "SUBMULTISET" ... > "CONTAINS" ... > "OVERLAPS" ... > "EQUALS" ... > "PRECEDES" ... > "SUCCEEDS" ... > "IMMEDIATELY" ... > "MULTISET" ... > "[" ... > "YEAR" ... > "MONTH" ... > "DAY" ... > "HOUR" ... > "MINUTE" ... > "SECOND" ... > > The above query works in standard databases like postgres > -- This message was sent by Atlassian JIRA (v7.6.3#76005)
[jira] [Created] (CALCITE-2981) Syntax error with simple nested select query
Vamshi created CALCITE-2981: --- Summary: Syntax error with simple nested select query Key: CALCITE-2981 URL: https://issues.apache.org/jira/browse/CALCITE-2981 Project: Calcite Issue Type: Bug Components: core Affects Versions: 1.18.0 Reporter: Vamshi Below standard query reports a syntax error: select * from ( ( select id,name from test1 ) test3 inner join ( select id,name from test2) test4 on test4.id = test3.id )dt Caused by: org.apache.calcite.parser.ParseException: Encountered "test3" at line 4, column 31. Was expecting one of: ")" ... "ORDER" ... "LIMIT" ... "OFFSET" ... "FETCH" ... "UNION" ... "INTERSECT" ... "EXCEPT" ... "MINUS" ... "." ... "NOT" ... "IN" ... "<" ... "<=" ... ">" ... ">=" ... "=" ... "<>" ... "!=" ... "BETWEEN" ... "LIKE" ... "SIMILAR" ... "+" ... "-" ... "*" ... "/" ... "%" ... "||" ... "AND" ... "OR" ... "IS" ... "MEMBER" ... "SUBMULTISET" ... "CONTAINS" ... "OVERLAPS" ... "EQUALS" ... "PRECEDES" ... "SUCCEEDS" ... "IMMEDIATELY" ... "MULTISET" ... "[" ... "YEAR" ... "MONTH" ... "DAY" ... "HOUR" ... "MINUTE" ... "SECOND" ... The above query works in standard databases like oracle/postgres/sql server. -- This message was sent by Atlassian JIRA (v7.6.3#76005)
[jira] [Created] (CALCITE-2147) GroupingSets involving rollup resulting into an incorrect plan
Vamshi created CALCITE-2147:
---
Summary: GroupingSets involving rollup resulting into an incorrect
plan
Key: CALCITE-2147
URL: https://issues.apache.org/jira/browse/CALCITE-2147
Project: Calcite
Issue Type: Bug
Reporter: Vamshi
Assignee: Julian Hyde
Problem description:
When rollup or cube expressions are specified as input to grouping_sets ,then
instead of handling the rollup or cube expression in LogicalAggregate operator
they are handled in LogicalProject operator which is incorrect.
Basically grouping_sets with rollup/cube expressions as input is not flattened
into logical aggregate operator.
Problem Test case:
create table temp11(a integer, b integer);
select a,b,count(*) from temp11 group by grouping sets(rollup(a,b),a,b,());
Test Case output:
{
"rels": [
{
"id": "0",
"relOp": "LogicalTableScan",
"table": [
"CATALOG",
"temp11"
],
"inputs": []
},
{
"id": "1",
"relOp": "LogicalProject",
"fields": [
"$f0",
"a",
"b"
],
"exprs": [
{
"op": "ROLLUP", <<--- Incorrect expressioin in
LogicalProject operator.
"operands": [
{
"input": 0,
"name": "$0"
},
{
"input": 1,
"name": "$1"
}
]
},
{
"input": 0,
"name": "$0"
},
{
"input": 1,
"name": "$1"
}
]
},
{
"id": "2",
"relOp": "LogicalAggregate",
"group": [
0,
1,
2
],
"groups": [
[
0
],
[
1
],
[
2
],
[]
],
"aggs": [
{
"agg": "COUNT",
"type": {
"type": "BIGINT",
"nullable": false
},
"distinct": false,
"operands": []
}
]
},
{
"id": "3",
"relOp": "LogicalProject",
"fields": [
"a",
"b",
"EXPR$2"
],
"exprs": [
{
"input": 1,
"name": "$1"
},
{
"input": 2,
"name": "$2"
},
{
"input": 3,
"name": "$3"
}
]
}
]
}
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
