[jira] [Commented] (CALCITE-2276) Calcite unable to parse ROW value constructor in certain scenario
[
https://issues.apache.org/jira/browse/CALCITE-2276?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16465843#comment-16465843
]
Yuzhao Chen commented on CALCITE-2276:
--
[~julianhyde], i have fixed testCollectionTableWithColumnListParam fails. Also
add the conformance switch tests in, really thx for your review.
> Calcite unable to parse ROW value constructor in certain scenario
> -
>
> Key: CALCITE-2276
> URL: https://issues.apache.org/jira/browse/CALCITE-2276
> Project: Calcite
> Issue Type: Bug
> Components: core
>Reporter: Shuyi Chen
>Assignee: Julian Hyde
>Priority: Major
>
> h2. Original dev mailing list question:
> Now for our production, we can parse a query successfully like this :
> {code}
> -- correlated IN subquery
> -- TC 01.01
> SELECT t1a,
>t1b,
>t1h
> FROM t1
> WHERE ( t1a, t1h ) NOT IN (SELECT t2a,
>t2h
> FROM t2
> WHERE t2a = t1a
> ORDER BY t2a)
> AND t1a = 'val1a'
> {code}
> but if we add in `Row`:
> {code}
> -- correlated IN subquery
> -- TC 01.01
> SELECT t1a,
>t1b,
>t1h
> FROM t1
> WHERE ROW( t1a, t1h ) NOT IN (SELECT t2a,
>t2h
> FROM t2
> WHERE t2a = t1a
> ORDER BY t2a)
> AND t1a = 'val1a'
> {code}
> it will throw exception:
> {noformat}
> Caused by: org.apache.calcite.sql.parser.SqlParseException: ROW expression
> encountered in illegal context
> at
> org.apache.calcite.sql.parser.impl.SqlParserImpl.convertException(SqlParserImpl.java:351)
> at
> org.apache.calcite.sql.parser.impl.SqlParserImpl.normalizeException(SqlParserImpl.java:133)
> at org.apache.calcite.sql.parser.SqlParser.parseQuery(SqlParser.java:138)
> at org.apache.calcite.sql.parser.SqlParser.parseStmt(SqlParser.java:163)
> at
> org.apache.flink.table.calcite.FlinkPlannerImpl.parse(FlinkPlannerImpl.scala:81)
> ... 8 more
> {noformat}
> For the success query, if we exec parsed AST tree rootNode.toString(), it
> will return a query like:
> {code}
> SELECT `t1a`,
>`t1b`,
>`t1h`
> FROM `t1`
> WHERE ROW(`t1a`, `t1h`) NOT IN (SELECT `t2a`, `t2h`
> FROM `t2`
> WHERE `t2a` = `t1a`
> ORDER BY `t2a`)
> AND `t1a` = 'val1a'
> {code}
> This is inconsistent by Calcite itself semantic.
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
[jira] [Commented] (CALCITE-2276) Calcite unable to parse ROW value constructor in certain scenario
[
https://issues.apache.org/jira/browse/CALCITE-2276?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16464116#comment-16464116
]
Julian Hyde commented on CALCITE-2276:
--
[~danny0405], If I disable {{allowExplicitRowValueConstructor}}, not only does
{{SqlParserTest.testRow}} fail (as it should) but also
{{testCollectionTableWithColumnListParam}} fails (which it should not). In
other words, you removed functionality. Can you fix that, and also add some
tests which try the behavior with the conformance enabled and disabled.
> Calcite unable to parse ROW value constructor in certain scenario
> -
>
> Key: CALCITE-2276
> URL: https://issues.apache.org/jira/browse/CALCITE-2276
> Project: Calcite
> Issue Type: Bug
> Components: core
>Reporter: Shuyi Chen
>Assignee: Julian Hyde
>Priority: Major
>
> h2. Original dev mailing list question:
> Now for our production, we can parse a query successfully like this :
> {code}
> -- correlated IN subquery
> -- TC 01.01
> SELECT t1a,
>t1b,
>t1h
> FROM t1
> WHERE ( t1a, t1h ) NOT IN (SELECT t2a,
>t2h
> FROM t2
> WHERE t2a = t1a
> ORDER BY t2a)
> AND t1a = 'val1a'
> {code}
> but if we add in `Row`:
> {code}
> -- correlated IN subquery
> -- TC 01.01
> SELECT t1a,
>t1b,
>t1h
> FROM t1
> WHERE ROW( t1a, t1h ) NOT IN (SELECT t2a,
>t2h
> FROM t2
> WHERE t2a = t1a
> ORDER BY t2a)
> AND t1a = 'val1a'
> {code}
> it will throw exception:
> {noformat}
> Caused by: org.apache.calcite.sql.parser.SqlParseException: ROW expression
> encountered in illegal context
> at
> org.apache.calcite.sql.parser.impl.SqlParserImpl.convertException(SqlParserImpl.java:351)
> at
> org.apache.calcite.sql.parser.impl.SqlParserImpl.normalizeException(SqlParserImpl.java:133)
> at org.apache.calcite.sql.parser.SqlParser.parseQuery(SqlParser.java:138)
> at org.apache.calcite.sql.parser.SqlParser.parseStmt(SqlParser.java:163)
> at
> org.apache.flink.table.calcite.FlinkPlannerImpl.parse(FlinkPlannerImpl.scala:81)
> ... 8 more
> {noformat}
> For the success query, if we exec parsed AST tree rootNode.toString(), it
> will return a query like:
> {code}
> SELECT `t1a`,
>`t1b`,
>`t1h`
> FROM `t1`
> WHERE ROW(`t1a`, `t1h`) NOT IN (SELECT `t2a`, `t2h`
> FROM `t2`
> WHERE `t2a` = `t1a`
> ORDER BY `t2a`)
> AND `t1a` = 'val1a'
> {code}
> This is inconsistent by Calcite itself semantic.
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
[jira] [Commented] (CALCITE-2276) Calcite unable to parse ROW value constructor in certain scenario
[
https://issues.apache.org/jira/browse/CALCITE-2276?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16461831#comment-16461831
]
Yuzhao Chen commented on CALCITE-2276:
--
[~julianhyde]
I have updated the conformance for row value constructor, please help me to see
if this is okey for the common case.
[PR-679| https://github.com/apache/calcite/pull/679]
> Calcite unable to parse ROW value constructor in certain scenario
> -
>
> Key: CALCITE-2276
> URL: https://issues.apache.org/jira/browse/CALCITE-2276
> Project: Calcite
> Issue Type: Bug
> Components: core
>Reporter: Shuyi Chen
>Assignee: Julian Hyde
>Priority: Major
>
> h2. Original dev mailing list question:
> Now for our production, we can parse a query successfully like this :
> {code}
> -- correlated IN subquery
> -- TC 01.01
> SELECT t1a,
>t1b,
>t1h
> FROM t1
> WHERE ( t1a, t1h ) NOT IN (SELECT t2a,
>t2h
> FROM t2
> WHERE t2a = t1a
> ORDER BY t2a)
> AND t1a = 'val1a'
> {code}
> but if we add in `Row`:
> {code}
> -- correlated IN subquery
> -- TC 01.01
> SELECT t1a,
>t1b,
>t1h
> FROM t1
> WHERE ROW( t1a, t1h ) NOT IN (SELECT t2a,
>t2h
> FROM t2
> WHERE t2a = t1a
> ORDER BY t2a)
> AND t1a = 'val1a'
> {code}
> it will throw exception:
> {noformat}
> Caused by: org.apache.calcite.sql.parser.SqlParseException: ROW expression
> encountered in illegal context
> at
> org.apache.calcite.sql.parser.impl.SqlParserImpl.convertException(SqlParserImpl.java:351)
> at
> org.apache.calcite.sql.parser.impl.SqlParserImpl.normalizeException(SqlParserImpl.java:133)
> at org.apache.calcite.sql.parser.SqlParser.parseQuery(SqlParser.java:138)
> at org.apache.calcite.sql.parser.SqlParser.parseStmt(SqlParser.java:163)
> at
> org.apache.flink.table.calcite.FlinkPlannerImpl.parse(FlinkPlannerImpl.scala:81)
> ... 8 more
> {noformat}
> For the success query, if we exec parsed AST tree rootNode.toString(), it
> will return a query like:
> {code}
> SELECT `t1a`,
>`t1b`,
>`t1h`
> FROM `t1`
> WHERE ROW(`t1a`, `t1h`) NOT IN (SELECT `t2a`, `t2h`
> FROM `t2`
> WHERE `t2a` = `t1a`
> ORDER BY `t2a`)
> AND `t1a` = 'val1a'
> {code}
> This is inconsistent by Calcite itself semantic.
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
[jira] [Commented] (CALCITE-2276) Calcite unable to parse ROW value constructor in certain scenario
[
https://issues.apache.org/jira/browse/CALCITE-2276?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16456991#comment-16456991
]
Julian Hyde commented on CALCITE-2276:
--
I meant to say conformance, not compliance. There are several places in the
parser that do conformance checks, e.g. {{if
(!this.conformance.isLimitStartCountAllowed())}}.
> Calcite unable to parse ROW value constructor in certain scenario
> -
>
> Key: CALCITE-2276
> URL: https://issues.apache.org/jira/browse/CALCITE-2276
> Project: Calcite
> Issue Type: Bug
> Components: core
>Reporter: Shuyi Chen
>Assignee: Julian Hyde
>Priority: Major
>
> h2. Original dev mailing list question:
> Now for our production, we can parse a query successfully like this :
> {code}
> -- correlated IN subquery
> -- TC 01.01
> SELECT t1a,
>t1b,
>t1h
> FROM t1
> WHERE ( t1a, t1h ) NOT IN (SELECT t2a,
>t2h
> FROM t2
> WHERE t2a = t1a
> ORDER BY t2a)
> AND t1a = 'val1a'
> {code}
> but if we add in `Row`:
> {code}
> -- correlated IN subquery
> -- TC 01.01
> SELECT t1a,
>t1b,
>t1h
> FROM t1
> WHERE ROW( t1a, t1h ) NOT IN (SELECT t2a,
>t2h
> FROM t2
> WHERE t2a = t1a
> ORDER BY t2a)
> AND t1a = 'val1a'
> {code}
> it will throw exception:
> {noformat}
> Caused by: org.apache.calcite.sql.parser.SqlParseException: ROW expression
> encountered in illegal context
> at
> org.apache.calcite.sql.parser.impl.SqlParserImpl.convertException(SqlParserImpl.java:351)
> at
> org.apache.calcite.sql.parser.impl.SqlParserImpl.normalizeException(SqlParserImpl.java:133)
> at org.apache.calcite.sql.parser.SqlParser.parseQuery(SqlParser.java:138)
> at org.apache.calcite.sql.parser.SqlParser.parseStmt(SqlParser.java:163)
> at
> org.apache.flink.table.calcite.FlinkPlannerImpl.parse(FlinkPlannerImpl.scala:81)
> ... 8 more
> {noformat}
> For the success query, if we exec parsed AST tree rootNode.toString(), it
> will return a query like:
> {code}
> SELECT `t1a`,
>`t1b`,
>`t1h`
> FROM `t1`
> WHERE ROW(`t1a`, `t1h`) NOT IN (SELECT `t2a`, `t2h`
> FROM `t2`
> WHERE `t2a` = `t1a`
> ORDER BY `t2a`)
> AND `t1a` = 'val1a'
> {code}
> This is inconsistent by Calcite itself semantic.
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
[jira] [Commented] (CALCITE-2276) Calcite unable to parse ROW value constructor in certain scenario
[
https://issues.apache.org/jira/browse/CALCITE-2276?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16455744#comment-16455744
]
Yuzhao Chen commented on CALCITE-2276:
--
In Calcite Parser.jj, the definition for Expression3：
{code:java}
//part-1
LOOKAHEAD(3)
{ s = span(); }
list = ParenthesizedSimpleIdentifierList() {
if (exprContext != ExprContext.ACCEPT_ALL
&& exprContext != ExprContext.ACCEPT_CURSOR
&& exprContext != ExprContext.ACCEPT_SUB_QUERY)
{ throw SqlUtil.newContextException(s.end(list),
RESOURCE.illegalRowExpression()); }
return SqlStdOperatorTable.ROW.createCall(list);
}
|
//part-2
[
{ rowSpan = span(); }
]
list1 = ParenthesizedQueryOrCommaList(exprContext) {
if (rowSpan != null)
{ // interpret as row constructor return
SqlStdOperatorTable.ROW.createCall(rowSpan.end(list1), list1.toArray()); }
}{code}
If we use explicit `ROW` tag, it will always match part-1,
So you mean we can change the part-1 if logic to deciding if strict compliance
is enabled for your statement 'remove the restriction' right?
Then how can i deicide if the compliance is strict?
> Calcite unable to parse ROW value constructor in certain scenario
> -
>
> Key: CALCITE-2276
> URL: https://issues.apache.org/jira/browse/CALCITE-2276
> Project: Calcite
> Issue Type: Bug
> Components: core
>Reporter: Shuyi Chen
>Assignee: Julian Hyde
>Priority: Major
>
> h2. Original dev mailing list question:
> Now for our production, we can parse a query successfully like this :
> {code}
> -- correlated IN subquery
> -- TC 01.01
> SELECT t1a,
>t1b,
>t1h
> FROM t1
> WHERE ( t1a, t1h ) NOT IN (SELECT t2a,
>t2h
> FROM t2
> WHERE t2a = t1a
> ORDER BY t2a)
> AND t1a = 'val1a'
> {code}
> but if we add in `Row`:
> {code}
> -- correlated IN subquery
> -- TC 01.01
> SELECT t1a,
>t1b,
>t1h
> FROM t1
> WHERE ROW( t1a, t1h ) NOT IN (SELECT t2a,
>t2h
> FROM t2
> WHERE t2a = t1a
> ORDER BY t2a)
> AND t1a = 'val1a'
> {code}
> it will throw exception:
> {noformat}
> Caused by: org.apache.calcite.sql.parser.SqlParseException: ROW expression
> encountered in illegal context
> at
> org.apache.calcite.sql.parser.impl.SqlParserImpl.convertException(SqlParserImpl.java:351)
> at
> org.apache.calcite.sql.parser.impl.SqlParserImpl.normalizeException(SqlParserImpl.java:133)
> at org.apache.calcite.sql.parser.SqlParser.parseQuery(SqlParser.java:138)
> at org.apache.calcite.sql.parser.SqlParser.parseStmt(SqlParser.java:163)
> at
> org.apache.flink.table.calcite.FlinkPlannerImpl.parse(FlinkPlannerImpl.scala:81)
> ... 8 more
> {noformat}
> For the success query, if we exec parsed AST tree rootNode.toString(), it
> will return a query like:
> {code}
> SELECT `t1a`,
>`t1b`,
>`t1h`
> FROM `t1`
> WHERE ROW(`t1a`, `t1h`) NOT IN (SELECT `t2a`, `t2h`
> FROM `t2`
> WHERE `t2a` = `t1a`
> ORDER BY `t2a`)
> AND `t1a` = 'val1a'
> {code}
> This is inconsistent by Calcite itself semantic.
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
[jira] [Commented] (CALCITE-2276) Calcite unable to parse ROW value constructor in certain scenario
[
https://issues.apache.org/jira/browse/CALCITE-2276?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16454357#comment-16454357
]
Julian Hyde commented on CALCITE-2276:
--
Thanks, that helps clarify.
It seems to me that the standard still prevents {{ROW (...)}} or {{(...)}} in
places where its type becomes visible, because it never wants anonymous record
types to appear. This is very similar to how NULL is used - you can write {code}
INSERT INTO t (x, y) VALUES (1, NULL){code}but you cannot write{code}SELECT 1,
NULL FROM t{code}because the latter would produce a column whose type cannot be
deduced. You have to write {code}SELECT 1, CAST(NULL AS INTEGER) FROM
t{code}instead.
I think we should remove the restriction from the parser. The parser should be
able to parse {code}SELECT ROW(x, y) FROM t{code} or equivalently {code}SELECT
(x, y) FROM t{code}. But if you have strict compliance enabled those queries
would fail validation.
> Calcite unable to parse ROW value constructor in certain scenario
> -
>
> Key: CALCITE-2276
> URL: https://issues.apache.org/jira/browse/CALCITE-2276
> Project: Calcite
> Issue Type: Bug
> Components: core
>Reporter: Shuyi Chen
>Assignee: Julian Hyde
>Priority: Major
>
> h2. Original dev mailing list question:
> Now for our production, we can parse a query successfully like this :
> {code}
> -- correlated IN subquery
> -- TC 01.01
> SELECT t1a,
>t1b,
>t1h
> FROM t1
> WHERE ( t1a, t1h ) NOT IN (SELECT t2a,
>t2h
> FROM t2
> WHERE t2a = t1a
> ORDER BY t2a)
> AND t1a = 'val1a'
> {code}
> but if we add in `Row`:
> {code}
> -- correlated IN subquery
> -- TC 01.01
> SELECT t1a,
>t1b,
>t1h
> FROM t1
> WHERE ROW( t1a, t1h ) NOT IN (SELECT t2a,
>t2h
> FROM t2
> WHERE t2a = t1a
> ORDER BY t2a)
> AND t1a = 'val1a'
> {code}
> it will throw exception:
> {noformat}
> Caused by: org.apache.calcite.sql.parser.SqlParseException: ROW expression
> encountered in illegal context
> at
> org.apache.calcite.sql.parser.impl.SqlParserImpl.convertException(SqlParserImpl.java:351)
> at
> org.apache.calcite.sql.parser.impl.SqlParserImpl.normalizeException(SqlParserImpl.java:133)
> at org.apache.calcite.sql.parser.SqlParser.parseQuery(SqlParser.java:138)
> at org.apache.calcite.sql.parser.SqlParser.parseStmt(SqlParser.java:163)
> at
> org.apache.flink.table.calcite.FlinkPlannerImpl.parse(FlinkPlannerImpl.scala:81)
> ... 8 more
> {noformat}
> For the success query, if we exec parsed AST tree rootNode.toString(), it
> will return a query like:
> {code}
> SELECT `t1a`,
>`t1b`,
>`t1h`
> FROM `t1`
> WHERE ROW(`t1a`, `t1h`) NOT IN (SELECT `t2a`, `t2h`
> FROM `t2`
> WHERE `t2a` = `t1a`
> ORDER BY `t2a`)
> AND `t1a` = 'val1a'
> {code}
> This is inconsistent by Calcite itself semantic.
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
[jira] [Commented] (CALCITE-2276) Calcite unable to parse ROW value constructor in certain scenario
[
https://issues.apache.org/jira/browse/CALCITE-2276?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16453670#comment-16453670
]
Yuzhao Chen commented on CALCITE-2276:
--
In sql-2003, the syntax elements for my case are:
{code:java}
+
+
+
+
+
+
ROW
{code}
So `where ROW(xxx) in` is a standard way.
Also the
{code:java}
{code}
appears in many syntax elements[ most are predicate ], eg: , , , , not
just in .
For sql-1999, the can also appears in many syntax
elements, not just which is the
same case as sql-2003
> Calcite unable to parse ROW value constructor in certain scenario
> -
>
> Key: CALCITE-2276
> URL: https://issues.apache.org/jira/browse/CALCITE-2276
> Project: Calcite
> Issue Type: Bug
> Components: core
>Reporter: Shuyi Chen
>Assignee: Julian Hyde
>Priority: Major
>
> h2. Original dev mailing list question:
> Now for our production, we can parse a query successfully like this :
> {code}
> -- correlated IN subquery
> -- TC 01.01
> SELECT t1a,
>t1b,
>t1h
> FROM t1
> WHERE ( t1a, t1h ) NOT IN (SELECT t2a,
>t2h
> FROM t2
> WHERE t2a = t1a
> ORDER BY t2a)
> AND t1a = 'val1a'
> {code}
> but if we add in `Row`:
> {code}
> -- correlated IN subquery
> -- TC 01.01
> SELECT t1a,
>t1b,
>t1h
> FROM t1
> WHERE ROW( t1a, t1h ) NOT IN (SELECT t2a,
>t2h
> FROM t2
> WHERE t2a = t1a
> ORDER BY t2a)
> AND t1a = 'val1a'
> {code}
> it will throw exception:
> {noformat}
> Caused by: org.apache.calcite.sql.parser.SqlParseException: ROW expression
> encountered in illegal context
> at
> org.apache.calcite.sql.parser.impl.SqlParserImpl.convertException(SqlParserImpl.java:351)
> at
> org.apache.calcite.sql.parser.impl.SqlParserImpl.normalizeException(SqlParserImpl.java:133)
> at org.apache.calcite.sql.parser.SqlParser.parseQuery(SqlParser.java:138)
> at org.apache.calcite.sql.parser.SqlParser.parseStmt(SqlParser.java:163)
> at
> org.apache.flink.table.calcite.FlinkPlannerImpl.parse(FlinkPlannerImpl.scala:81)
> ... 8 more
> {noformat}
> For the success query, if we exec parsed AST tree rootNode.toString(), it
> will return a query like:
> {code}
> SELECT `t1a`,
>`t1b`,
>`t1h`
> FROM `t1`
> WHERE ROW(`t1a`, `t1h`) NOT IN (SELECT `t2a`, `t2h`
> FROM `t2`
> WHERE `t2a` = `t1a`
> ORDER BY `t2a`)
> AND `t1a` = 'val1a'
> {code}
> This is inconsistent by Calcite itself semantic.
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
[jira] [Commented] (CALCITE-2276) Calcite unable to parse ROW value constructor in certain scenario
[
https://issues.apache.org/jira/browse/CALCITE-2276?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16453580#comment-16453580
]
Julian Hyde commented on CALCITE-2276:
--
{{>}} and so forth can only occur inside VALUES, right?
Not where you would use an expression, such as in a SELECT clause. So that's
why what you want is non-standard.
> Calcite unable to parse ROW value constructor in certain scenario
> -
>
> Key: CALCITE-2276
> URL: https://issues.apache.org/jira/browse/CALCITE-2276
> Project: Calcite
> Issue Type: Bug
> Components: core
>Reporter: Shuyi Chen
>Assignee: Julian Hyde
>Priority: Major
>
> h2. Original dev mailing list question:
> Now for our production, we can parse a query successfully like this :
> {code}
> -- correlated IN subquery
> -- TC 01.01
> SELECT t1a,
>t1b,
>t1h
> FROM t1
> WHERE ( t1a, t1h ) NOT IN (SELECT t2a,
>t2h
> FROM t2
> WHERE t2a = t1a
> ORDER BY t2a)
> AND t1a = 'val1a'
> {code}
> but if we add in `Row`:
> {code}
> -- correlated IN subquery
> -- TC 01.01
> SELECT t1a,
>t1b,
>t1h
> FROM t1
> WHERE ROW( t1a, t1h ) NOT IN (SELECT t2a,
>t2h
> FROM t2
> WHERE t2a = t1a
> ORDER BY t2a)
> AND t1a = 'val1a'
> {code}
> it will throw exception:
> {noformat}
> Caused by: org.apache.calcite.sql.parser.SqlParseException: ROW expression
> encountered in illegal context
> at
> org.apache.calcite.sql.parser.impl.SqlParserImpl.convertException(SqlParserImpl.java:351)
> at
> org.apache.calcite.sql.parser.impl.SqlParserImpl.normalizeException(SqlParserImpl.java:133)
> at org.apache.calcite.sql.parser.SqlParser.parseQuery(SqlParser.java:138)
> at org.apache.calcite.sql.parser.SqlParser.parseStmt(SqlParser.java:163)
> at
> org.apache.flink.table.calcite.FlinkPlannerImpl.parse(FlinkPlannerImpl.scala:81)
> ... 8 more
> {noformat}
> For the success query, if we exec parsed AST tree rootNode.toString(), it
> will return a query like:
> {code}
> SELECT `t1a`,
>`t1b`,
>`t1h`
> FROM `t1`
> WHERE ROW(`t1a`, `t1h`) NOT IN (SELECT `t2a`, `t2h`
> FROM `t2`
> WHERE `t2a` = `t1a`
> ORDER BY `t2a`)
> AND `t1a` = 'val1a'
> {code}
> This is inconsistent by Calcite itself semantic.
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
[jira] [Commented] (CALCITE-2276) Calcite unable to parse ROW value constructor in certain scenario
[
https://issues.apache.org/jira/browse/CALCITE-2276?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16453578#comment-16453578
]
Yuzhao Chen commented on CALCITE-2276:
--
[~julianhyde]
I read the syntax format in sql-1999 section 7.1 as below:
{code:java}
::=
| [ ROW ]
|
::=
[ { }... ]
::=
::=
| [ ROW ]
::=
[ { }... ]
::=
|
{code}
Also i have format the code.
> Calcite unable to parse ROW value constructor in certain scenario
> -
>
> Key: CALCITE-2276
> URL: https://issues.apache.org/jira/browse/CALCITE-2276
> Project: Calcite
> Issue Type: Bug
> Components: core
>Reporter: Shuyi Chen
>Assignee: Julian Hyde
>Priority: Major
>
> h2. Original dev mailing list question:
> Now for our production, we can parse a query successfully like this :
> {code}
> -- correlated IN subquery
> -- TC 01.01
> SELECT t1a,
>t1b,
>t1h
> FROM t1
> WHERE ( t1a, t1h ) NOT IN (SELECT t2a,
>t2h
> FROM t2
> WHERE t2a = t1a
> ORDER BY t2a)
> AND t1a = 'val1a'
> {code}
> but if we add in `Row`:
> {code}
> -- correlated IN subquery
> -- TC 01.01
> SELECT t1a,
>t1b,
>t1h
> FROM t1
> WHERE ROW( t1a, t1h ) NOT IN (SELECT t2a,
>t2h
> FROM t2
> WHERE t2a = t1a
> ORDER BY t2a)
> AND t1a = 'val1a'
> {code}
> it will throw exception:
> {noformat}
> Caused by: org.apache.calcite.sql.parser.SqlParseException: ROW expression
> encountered in illegal context
> at
> org.apache.calcite.sql.parser.impl.SqlParserImpl.convertException(SqlParserImpl.java:351)
> at
> org.apache.calcite.sql.parser.impl.SqlParserImpl.normalizeException(SqlParserImpl.java:133)
> at org.apache.calcite.sql.parser.SqlParser.parseQuery(SqlParser.java:138)
> at org.apache.calcite.sql.parser.SqlParser.parseStmt(SqlParser.java:163)
> at
> org.apache.flink.table.calcite.FlinkPlannerImpl.parse(FlinkPlannerImpl.scala:81)
> ... 8 more
> {noformat}
> For the success query, if we exec parsed AST tree rootNode.toString(), it
> will return a query like:
> {code}
> SELECT `t1a`,
>`t1b`,
>`t1h`
> FROM `t1`
> WHERE ROW(`t1a`, `t1h`) NOT IN (SELECT `t2a`, `t2h`
> FROM `t2`
> WHERE `t2a` = `t1a`
> ORDER BY `t2a`)
> AND `t1a` = 'val1a'
> {code}
> This is inconsistent by Calcite itself semantic.
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
[jira] [Commented] (CALCITE-2276) Calcite unable to parse ROW value constructor in certain scenario
[
https://issues.apache.org/jira/browse/CALCITE-2276?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16453512#comment-16453512
]
Julian Hyde commented on CALCITE-2276:
--
In CALCITE-877 (which is when those lines in misc.iq were added) I state that
ROW is only allowed inside VALUES. So, my statement contradicts your statement
that ROW(...) and (...) are equivalent. Can you read the standard, so we know
where we stand?
Even if the standard disallows ROW, I'm open to the idea of adding a compliance
flag to allow it.
[~danny0405], Your change looks good, but please use the same indentation as
the other code.
> Calcite unable to parse ROW value constructor in certain scenario
> -
>
> Key: CALCITE-2276
> URL: https://issues.apache.org/jira/browse/CALCITE-2276
> Project: Calcite
> Issue Type: Bug
> Components: core
>Reporter: Shuyi Chen
>Assignee: Julian Hyde
>Priority: Major
>
> h2. Original dev mailing list question:
> Now for our production, we can parse a query successfully like this :
> {code}
> -- correlated IN subquery
> -- TC 01.01
> SELECT t1a,
>t1b,
>t1h
> FROM t1
> WHERE ( t1a, t1h ) NOT IN (SELECT t2a,
>t2h
> FROM t2
> WHERE t2a = t1a
> ORDER BY t2a)
> AND t1a = 'val1a'
> {code}
> but if we add in `Row`:
> {code}
> -- correlated IN subquery
> -- TC 01.01
> SELECT t1a,
>t1b,
>t1h
> FROM t1
> WHERE ROW( t1a, t1h ) NOT IN (SELECT t2a,
>t2h
> FROM t2
> WHERE t2a = t1a
> ORDER BY t2a)
> AND t1a = 'val1a'
> {code}
> it will throw exception:
> {noformat}
> Caused by: org.apache.calcite.sql.parser.SqlParseException: ROW expression
> encountered in illegal context
> at
> org.apache.calcite.sql.parser.impl.SqlParserImpl.convertException(SqlParserImpl.java:351)
> at
> org.apache.calcite.sql.parser.impl.SqlParserImpl.normalizeException(SqlParserImpl.java:133)
> at org.apache.calcite.sql.parser.SqlParser.parseQuery(SqlParser.java:138)
> at org.apache.calcite.sql.parser.SqlParser.parseStmt(SqlParser.java:163)
> at
> org.apache.flink.table.calcite.FlinkPlannerImpl.parse(FlinkPlannerImpl.scala:81)
> ... 8 more
> {noformat}
> For the success query, if we exec parsed AST tree rootNode.toString(), it
> will return a query like:
> {code}
> SELECT `t1a`,
>`t1b`,
>`t1h`
> FROM `t1`
> WHERE ROW(`t1a`, `t1h`) NOT IN (SELECT `t2a`, `t2h`
> FROM `t2`
> WHERE `t2a` = `t1a`
> ORDER BY `t2a`)
> AND `t1a` = 'val1a'
> {code}
> This is inconsistent by Calcite itself semantic.
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
[jira] [Commented] (CALCITE-2276) Calcite unable to parse ROW value constructor in certain scenario
[
https://issues.apache.org/jira/browse/CALCITE-2276?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16453479#comment-16453479
]
Yuzhao Chen commented on CALCITE-2276:
--
[~julianhyde] [~suez1224]
I hava fire a PR here https://github.com/apache/calcite/pull/679
But i also see the test case in misc.iq:
```xml
# Explicit ROW
select deptno, row (empno, deptno) as r
from "scott".emp;
-ROW expression encountered in illegal context
-!error
```
So i want to know why we abandon explicit Row value constructor now?
> Calcite unable to parse ROW value constructor in certain scenario
> -
>
> Key: CALCITE-2276
> URL: https://issues.apache.org/jira/browse/CALCITE-2276
> Project: Calcite
> Issue Type: Bug
> Components: core
>Reporter: Shuyi Chen
>Assignee: Julian Hyde
>Priority: Major
>
> h2. Original dev mailing list question:
> Now for our production, we can parse a query successfully like this :
> {code}
> -- correlated IN subquery
> -- TC 01.01
> SELECT t1a,
>t1b,
>t1h
> FROM t1
> WHERE ( t1a, t1h ) NOT IN (SELECT t2a,
>t2h
> FROM t2
> WHERE t2a = t1a
> ORDER BY t2a)
> AND t1a = 'val1a'
> {code}
> but if we add in `Row`:
> {code}
> -- correlated IN subquery
> -- TC 01.01
> SELECT t1a,
>t1b,
>t1h
> FROM t1
> WHERE ROW( t1a, t1h ) NOT IN (SELECT t2a,
>t2h
> FROM t2
> WHERE t2a = t1a
> ORDER BY t2a)
> AND t1a = 'val1a'
> {code}
> it will throw exception:
> {noformat}
> Caused by: org.apache.calcite.sql.parser.SqlParseException: ROW expression
> encountered in illegal context
> at
> org.apache.calcite.sql.parser.impl.SqlParserImpl.convertException(SqlParserImpl.java:351)
> at
> org.apache.calcite.sql.parser.impl.SqlParserImpl.normalizeException(SqlParserImpl.java:133)
> at org.apache.calcite.sql.parser.SqlParser.parseQuery(SqlParser.java:138)
> at org.apache.calcite.sql.parser.SqlParser.parseStmt(SqlParser.java:163)
> at
> org.apache.flink.table.calcite.FlinkPlannerImpl.parse(FlinkPlannerImpl.scala:81)
> ... 8 more
> {noformat}
> For the success query, if we exec parsed AST tree rootNode.toString(), it
> will return a query like:
> {code}
> SELECT `t1a`,
>`t1b`,
>`t1h`
> FROM `t1`
> WHERE ROW(`t1a`, `t1h`) NOT IN (SELECT `t2a`, `t2h`
> FROM `t2`
> WHERE `t2a` = `t1a`
> ORDER BY `t2a`)
> AND `t1a` = 'val1a'
> {code}
> This is inconsistent by Calcite itself semantic.
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
[jira] [Commented] (CALCITE-2276) Calcite unable to parse ROW value constructor in certain scenario
[
https://issues.apache.org/jira/browse/CALCITE-2276?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16451864#comment-16451864
]
Shuyi Chen commented on CALCITE-2276:
-
awesome, thanks for contributing, [~danny0405], Let us know if you need help.
> Calcite unable to parse ROW value constructor in certain scenario
> -
>
> Key: CALCITE-2276
> URL: https://issues.apache.org/jira/browse/CALCITE-2276
> Project: Calcite
> Issue Type: Bug
> Components: core
>Reporter: Shuyi Chen
>Assignee: Julian Hyde
>Priority: Major
>
> h2. Original dev mailing list question:
> Now for our production, we can parse a query successfully like this :
> {code}
> -- correlated IN subquery
> -- TC 01.01
> SELECT t1a,
>t1b,
>t1h
> FROM t1
> WHERE ( t1a, t1h ) NOT IN (SELECT t2a,
>t2h
> FROM t2
> WHERE t2a = t1a
> ORDER BY t2a)
> AND t1a = 'val1a'
> {code}
> but if we add in `Row`:
> {code}
> -- correlated IN subquery
> -- TC 01.01
> SELECT t1a,
>t1b,
>t1h
> FROM t1
> WHERE ROW( t1a, t1h ) NOT IN (SELECT t2a,
>t2h
> FROM t2
> WHERE t2a = t1a
> ORDER BY t2a)
> AND t1a = 'val1a'
> {code}
> it will throw exception:
> {noformat}
> Caused by: org.apache.calcite.sql.parser.SqlParseException: ROW expression
> encountered in illegal context
> at
> org.apache.calcite.sql.parser.impl.SqlParserImpl.convertException(SqlParserImpl.java:351)
> at
> org.apache.calcite.sql.parser.impl.SqlParserImpl.normalizeException(SqlParserImpl.java:133)
> at org.apache.calcite.sql.parser.SqlParser.parseQuery(SqlParser.java:138)
> at org.apache.calcite.sql.parser.SqlParser.parseStmt(SqlParser.java:163)
> at
> org.apache.flink.table.calcite.FlinkPlannerImpl.parse(FlinkPlannerImpl.scala:81)
> ... 8 more
> {noformat}
> For the success query, if we exec parsed AST tree rootNode.toString(), it
> will return a query like:
> {code}
> SELECT `t1a`,
>`t1b`,
>`t1h`
> FROM `t1`
> WHERE ROW(`t1a`, `t1h`) NOT IN (SELECT `t2a`, `t2h`
> FROM `t2`
> WHERE `t2a` = `t1a`
> ORDER BY `t2a`)
> AND `t1a` = 'val1a'
> {code}
> This is inconsistent by Calcite itself semantic.
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
[jira] [Commented] (CALCITE-2276) Calcite unable to parse ROW value constructor in certain scenario
[
https://issues.apache.org/jira/browse/CALCITE-2276?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16451858#comment-16451858
]
Yuzhao Chen commented on CALCITE-2276:
--
[~suez1224] [~michaelmior] [~julianhyde]
Yeah, in sql-1999 ROW(...) and (...) is equivalent, and i'm working on fix this.
> Calcite unable to parse ROW value constructor in certain scenario
> -
>
> Key: CALCITE-2276
> URL: https://issues.apache.org/jira/browse/CALCITE-2276
> Project: Calcite
> Issue Type: Bug
> Components: core
>Reporter: Shuyi Chen
>Assignee: Julian Hyde
>Priority: Major
>
> h2. Original dev mailing list question:
> Now for our production, we can parse a query successfully like this :
> {code}
> -- correlated IN subquery
> -- TC 01.01
> SELECT t1a,
>t1b,
>t1h
> FROM t1
> WHERE ( t1a, t1h ) NOT IN (SELECT t2a,
>t2h
> FROM t2
> WHERE t2a = t1a
> ORDER BY t2a)
> AND t1a = 'val1a'
> {code}
> but if we add in `Row`:
> {code}
> -- correlated IN subquery
> -- TC 01.01
> SELECT t1a,
>t1b,
>t1h
> FROM t1
> WHERE ROW( t1a, t1h ) NOT IN (SELECT t2a,
>t2h
> FROM t2
> WHERE t2a = t1a
> ORDER BY t2a)
> AND t1a = 'val1a'
> {code}
> it will throw exception:
> {noformat}
> Caused by: org.apache.calcite.sql.parser.SqlParseException: ROW expression
> encountered in illegal context
> at
> org.apache.calcite.sql.parser.impl.SqlParserImpl.convertException(SqlParserImpl.java:351)
> at
> org.apache.calcite.sql.parser.impl.SqlParserImpl.normalizeException(SqlParserImpl.java:133)
> at org.apache.calcite.sql.parser.SqlParser.parseQuery(SqlParser.java:138)
> at org.apache.calcite.sql.parser.SqlParser.parseStmt(SqlParser.java:163)
> at
> org.apache.flink.table.calcite.FlinkPlannerImpl.parse(FlinkPlannerImpl.scala:81)
> ... 8 more
> {noformat}
> For the success query, if we exec parsed AST tree rootNode.toString(), it
> will return a query like:
> {code}
> SELECT `t1a`,
>`t1b`,
>`t1h`
> FROM `t1`
> WHERE ROW(`t1a`, `t1h`) NOT IN (SELECT `t2a`, `t2h`
> FROM `t2`
> WHERE `t2a` = `t1a`
> ORDER BY `t2a`)
> AND `t1a` = 'val1a'
> {code}
> This is inconsistent by Calcite itself semantic.
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
[jira] [Commented] (CALCITE-2276) Calcite unable to parse ROW value constructor in certain scenario
[
https://issues.apache.org/jira/browse/CALCITE-2276?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16451672#comment-16451672
]
Shuyi Chen commented on CALCITE-2276:
-
I recalled in standard SQL, ROW(...) and (...) is equivalent and ROW is
optional.
[~michaelmior], [~julianhyde] do you want take on this? Otherwise, I can take a
look at it while working on
[CALCITE-2251|https://issues.apache.org/jira/browse/CALCITE-2251] as this will
need some fix in the parser.
> Calcite unable to parse ROW value constructor in certain scenario
> -
>
> Key: CALCITE-2276
> URL: https://issues.apache.org/jira/browse/CALCITE-2276
> Project: Calcite
> Issue Type: Bug
> Components: core
>Reporter: Shuyi Chen
>Assignee: Julian Hyde
>Priority: Major
>
> h2. Original dev mailing list question:
> Now for our production, we can parse a query successfully like this :
> {code}
> -- correlated IN subquery
> -- TC 01.01
> SELECT t1a,
>t1b,
>t1h
> FROM t1
> WHERE ( t1a, t1h ) NOT IN (SELECT t2a,
>t2h
> FROM t2
> WHERE t2a = t1a
> ORDER BY t2a)
> AND t1a = 'val1a'
> {code}
> but if we add in `Row`:
> {code}
> -- correlated IN subquery
> -- TC 01.01
> SELECT t1a,
>t1b,
>t1h
> FROM t1
> WHERE ROW( t1a, t1h ) NOT IN (SELECT t2a,
>t2h
> FROM t2
> WHERE t2a = t1a
> ORDER BY t2a)
> AND t1a = 'val1a'
> {code}
> it will throw exception:
> {noformat}
> Caused by: org.apache.calcite.sql.parser.SqlParseException: ROW expression
> encountered in illegal context
> at
> org.apache.calcite.sql.parser.impl.SqlParserImpl.convertException(SqlParserImpl.java:351)
> at
> org.apache.calcite.sql.parser.impl.SqlParserImpl.normalizeException(SqlParserImpl.java:133)
> at org.apache.calcite.sql.parser.SqlParser.parseQuery(SqlParser.java:138)
> at org.apache.calcite.sql.parser.SqlParser.parseStmt(SqlParser.java:163)
> at
> org.apache.flink.table.calcite.FlinkPlannerImpl.parse(FlinkPlannerImpl.scala:81)
> ... 8 more
> {noformat}
> For the success query, if we exec parsed AST tree rootNode.toString(), it
> will return a query like:
> {code}
> SELECT `t1a`,
>`t1b`,
>`t1h`
> FROM `t1`
> WHERE ROW(`t1a`, `t1h`) NOT IN (SELECT `t2a`, `t2h`
> FROM `t2`
> WHERE `t2a` = `t1a`
> ORDER BY `t2a`)
> AND `t1a` = 'val1a'
> {code}
> This is inconsistent by Calcite itself semantic.
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
[jira] [Commented] (CALCITE-2276) Calcite unable to parse ROW value constructor in certain scenario
[ https://issues.apache.org/jira/browse/CALCITE-2276?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16449048#comment-16449048 ] Julian Hyde commented on CALCITE-2276: -- If ROW is in the SQL standard, the answer to those questions is straightforward. > Calcite unable to parse ROW value constructor in certain scenario > - > > Key: CALCITE-2276 > URL: https://issues.apache.org/jira/browse/CALCITE-2276 > Project: Calcite > Issue Type: Bug > Components: core >Reporter: Shuyi Chen >Assignee: Julian Hyde >Priority: Major > > h2. Original dev mailing list question: > Now for our production, we can parse a query successfully like this : > -- correlated IN subquery > -- TC 01.01 > SELECT t1a, >t1b, >t1h > FROM t1 > WHERE ( t1a, t1h ) NOT IN (SELECT t2a, >t2h > FROM t2 > WHERE t2a = t1a > ORDER BY t2a) > AND t1a = 'val1a' > but if we add in `Row`: > -- correlated IN subquery > -- TC 01.01 > SELECT t1a, >t1b, >t1h > FROM t1 > WHERE ROW( t1a, t1h ) NOT IN (SELECT t2a, >t2h > FROM t2 > WHERE t2a = t1a > ORDER BY t2a) > AND t1a = 'val1a' > it will throw exception: > Caused by: org.apache.calcite.sql.parser.SqlParseException: ROW expression > encountered in illegal context > at > org.apache.calcite.sql.parser.impl.SqlParserImpl.convertException(SqlParserImpl.java:351) > at > org.apache.calcite.sql.parser.impl.SqlParserImpl.normalizeException(SqlParserImpl.java:133) > at org.apache.calcite.sql.parser.SqlParser.parseQuery(SqlParser.java:138) > at org.apache.calcite.sql.parser.SqlParser.parseStmt(SqlParser.java:163) > at > org.apache.flink.table.calcite.FlinkPlannerImpl.parse(FlinkPlannerImpl.scala:81) > ... 8 more > For the success query, if we exec parsed AST tree rootNode.toString(), it > will return a query like: > SELECT `t1a`, >`t1b`, >`t1h` > FROM `t1` > WHERE ROW(`t1a`, `t1h`) NOT IN (SELECT `t2a`, `t2h` > FROM `t2` > WHERE `t2a` = `t1a` > ORDER BY `t2a`) > AND `t1a` = 'val1a' > This is inconsistent by Calcite itself semantic. -- This message was sent by Atlassian JIRA (v7.6.3#76005)
[jira] [Commented] (CALCITE-2276) Calcite unable to parse ROW value constructor in certain scenario
[ https://issues.apache.org/jira/browse/CALCITE-2276?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16449027#comment-16449027 ] Michael Mior commented on CALCITE-2276: --- The question to me seems to be what the correct behaviour should be. # What should Calcite accept as a valid query? # What should be returned when the query is unparsed? > Calcite unable to parse ROW value constructor in certain scenario > - > > Key: CALCITE-2276 > URL: https://issues.apache.org/jira/browse/CALCITE-2276 > Project: Calcite > Issue Type: Bug > Components: core >Reporter: Shuyi Chen >Assignee: Julian Hyde >Priority: Major > > h2. Original dev mailing list question: > Now for our production, we can parse a query successfully like this : > -- correlated IN subquery > -- TC 01.01 > SELECT t1a, >t1b, >t1h > FROM t1 > WHERE ( t1a, t1h ) NOT IN (SELECT t2a, >t2h > FROM t2 > WHERE t2a = t1a > ORDER BY t2a) > AND t1a = 'val1a' > but if we add in `Row`: > -- correlated IN subquery > -- TC 01.01 > SELECT t1a, >t1b, >t1h > FROM t1 > WHERE ROW( t1a, t1h ) NOT IN (SELECT t2a, >t2h > FROM t2 > WHERE t2a = t1a > ORDER BY t2a) > AND t1a = 'val1a' > it will throw exception: > Caused by: org.apache.calcite.sql.parser.SqlParseException: ROW expression > encountered in illegal context > at > org.apache.calcite.sql.parser.impl.SqlParserImpl.convertException(SqlParserImpl.java:351) > at > org.apache.calcite.sql.parser.impl.SqlParserImpl.normalizeException(SqlParserImpl.java:133) > at org.apache.calcite.sql.parser.SqlParser.parseQuery(SqlParser.java:138) > at org.apache.calcite.sql.parser.SqlParser.parseStmt(SqlParser.java:163) > at > org.apache.flink.table.calcite.FlinkPlannerImpl.parse(FlinkPlannerImpl.scala:81) > ... 8 more > For the success query, if we exec parsed AST tree rootNode.toString(), it > will return a query like: > SELECT `t1a`, >`t1b`, >`t1h` > FROM `t1` > WHERE ROW(`t1a`, `t1h`) NOT IN (SELECT `t2a`, `t2h` > FROM `t2` > WHERE `t2a` = `t1a` > ORDER BY `t2a`) > AND `t1a` = 'val1a' > This is inconsistent by Calcite itself semantic. -- This message was sent by Atlassian JIRA (v7.6.3#76005)
