[
https://issues.apache.org/jira/browse/CALCITE-7005?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Yu Xu updated CALCITE-7005:
---------------------------
Description:
IS TRUE/ IS FALSE/IS NOT TRUE/IS NOT FALSE can not support in Hive/Presto
System.
Hive:
{code:java}
select 1>0 is true;
NoViableAltException(295@[])
at
org.apache.hadoop.hive.ql.parse.HiveParser_IdentifiersParser.nullCondition(HiveParser_IdentifiersParser.java:7144)
at
org.apache.hadoop.hive.ql.parse.HiveParser_IdentifiersParser.precedenceUnarySuffixExpression(HiveParser_IdentifiersParser.java:7369)
at
org.apache.hadoop.hive.ql.parse.HiveParser_IdentifiersParser.precedenceBitwiseXorExpression(HiveParser_IdentifiersParser.java:7516)
at
org.apache.hadoop.hive.ql.parse.HiveParser_IdentifiersParser.precedenceStarExpression(HiveParser_IdentifiersParser.java:7659)
at
org.apache.hadoop.hive.ql.parse.HiveParser_IdentifiersParser.precedencePlusExpression(HiveParser_IdentifiersParser.java:7802)
at
org.apache.hadoop.hive.ql.parse.HiveParser_IdentifiersParser.precedenceConcatenateExpression(HiveParser_IdentifiersParser.java:7941)
at
org.apache.hadoop.hive.ql.parse.HiveParser_IdentifiersParser.precedenceAmpersandExpression(HiveParser_IdentifiersParser.java:8151)
{code}
{code:java}
select (1>0) is true; NoViableAltException(295@[]) at
org.apache.hadoop.hive.ql.parse.HiveParser_IdentifiersParser.nullCondition(HiveParser_IdentifiersParser.java:7144)
at
org.apache.hadoop.hive.ql.parse.HiveParser_IdentifiersParser.precedenceUnarySuffixExpression(HiveParser_IdentifiersParser.java:7369)
at
org.apache.hadoop.hive.ql.parse.HiveParser_IdentifiersParser.precedenceBitwiseXorExpression(HiveParser_IdentifiersParser.java:7516)
at
org.apache.hadoop.hive.ql.parse.HiveParser_IdentifiersParser.precedenceStarExpression(HiveParser_IdentifiersParser.java:7659)
at
org.apache.hadoop.hive.ql.parse.HiveParser_IdentifiersParser.precedencePlusExpression(HiveParser_IdentifiersParser.java:7802)
at
org.apache.hadoop.hive.ql.parse.HiveParser_IdentifiersParser.precedenceConcatenateExpression(HiveParser_IdentifiersParser.java:7941)
at
org.apache.hadoop.hive.ql.parse.HiveParser_IdentifiersParser.precedenceAmpersandExpression(HiveParser_IdentifiersParser.java:8151)
{code}
presto:
{code:java}
presto> select 1>0 is true;
Query 20250509_090525_40291_cqcdm failed: line 1:12: mismatched input 'is'.
Expecting: '%', '*', '+', ',', '-', '.', '/', 'AND', 'AS', 'AT', 'EXCEPT',
'FROM', 'GROUP', 'HAVING', 'INTERSECT', 'LIMIT', 'OR', 'ORDER', 'UNION',
'WHERE', '[', '||', <EOF>, <identifier> {code}
{code:java}
presto> select (1>0) is true;
Query 20250509_090519_40277_cqcdm failed: line 1:17: mismatched input 'true'.
Expecting: 'DISTINCT', 'NOT', 'NULL'
{code}
*and convert to IS NOT NULL and BOOLEAN can work well:*
Hive:
{code:java}
select 1 > 0 IS NOT NULL AND 1 > 0;
> 1 {code}
Presto
note：1>0 IS NOT NULL is not support
{code:java}
presto> select 1 > 0 IS NOT NULL AND 1 > 0;
Query 20250509_094813_43212_cqcdm failed: line 1:14: mismatched input 'IS'.
Expecting: '%', '*', '+', ',', '-', '.', '/', 'AND', 'AS', 'AT', 'EXCEPT',
'FROM', 'GROUP', 'HAVING', 'INTERSECT', 'LIMIT', 'OR', 'ORDER', 'UNION',
'WHERE', '[', '||', <EOF>, <identifier>
select 1 > 0 IS NOT NULL AND 1 > 0
{code}
*need to convert to (1>0) IS NOT NULL*
{code:java}
presto> select (1 > 0) IS NOT NULL AND 1 > 0;
_col0
-------
true
(1 row) {code}
so In Hive:
*we should convert the syntax as:*
A IS TRUE: A IS NOT NULL AND A
A IS FALSE: A IS NOT NULL AND NOT A
A IS NOT TRUE: A IS NULL OR NOT A
A IS NOT FALSE: A IS NULL OR NOT A
and in Presto:
*we should convert the syntax with () around A:*
(A) IS TRUE: (A) IS NOT NULL AND A
(A) IS FALSE: (A) IS NOT NULL AND NOT A
(A) IS NOT TRUE: (A) IS NULL OR NOT A
(A) IS NOT FALSE: (A) IS NULL OR NOT A
was:
IS TRUE/ IS FALSE/IS NOT TRUE/IS NOT FALSE can not support in Hive/Presto
System.
Hive:
{code:java}
select 1>0 is true;
NoViableAltException(295@[])
at
org.apache.hadoop.hive.ql.parse.HiveParser_IdentifiersParser.nullCondition(HiveParser_IdentifiersParser.java:7144)
at
org.apache.hadoop.hive.ql.parse.HiveParser_IdentifiersParser.precedenceUnarySuffixExpression(HiveParser_IdentifiersParser.java:7369)
at
org.apache.hadoop.hive.ql.parse.HiveParser_IdentifiersParser.precedenceBitwiseXorExpression(HiveParser_IdentifiersParser.java:7516)
at
org.apache.hadoop.hive.ql.parse.HiveParser_IdentifiersParser.precedenceStarExpression(HiveParser_IdentifiersParser.java:7659)
at
org.apache.hadoop.hive.ql.parse.HiveParser_IdentifiersParser.precedencePlusExpression(HiveParser_IdentifiersParser.java:7802)
at
org.apache.hadoop.hive.ql.parse.HiveParser_IdentifiersParser.precedenceConcatenateExpression(HiveParser_IdentifiersParser.java:7941)
at
org.apache.hadoop.hive.ql.parse.HiveParser_IdentifiersParser.precedenceAmpersandExpression(HiveParser_IdentifiersParser.java:8151)
{code}
{code:java}
select (1>0) is true; NoViableAltException(295@[]) at
org.apache.hadoop.hive.ql.parse.HiveParser_IdentifiersParser.nullCondition(HiveParser_IdentifiersParser.java:7144)
at
org.apache.hadoop.hive.ql.parse.HiveParser_IdentifiersParser.precedenceUnarySuffixExpression(HiveParser_IdentifiersParser.java:7369)
at
org.apache.hadoop.hive.ql.parse.HiveParser_IdentifiersParser.precedenceBitwiseXorExpression(HiveParser_IdentifiersParser.java:7516)
at
org.apache.hadoop.hive.ql.parse.HiveParser_IdentifiersParser.precedenceStarExpression(HiveParser_IdentifiersParser.java:7659)
at
org.apache.hadoop.hive.ql.parse.HiveParser_IdentifiersParser.precedencePlusExpression(HiveParser_IdentifiersParser.java:7802)
at
org.apache.hadoop.hive.ql.parse.HiveParser_IdentifiersParser.precedenceConcatenateExpression(HiveParser_IdentifiersParser.java:7941)
at
org.apache.hadoop.hive.ql.parse.HiveParser_IdentifiersParser.precedenceAmpersandExpression(HiveParser_IdentifiersParser.java:8151)
{code}
presto:
{code:java}
presto> select 1>0 is true;
Query 20250509_090525_40291_cqcdm failed: line 1:12: mismatched input 'is'.
Expecting: '%', '*', '+', ',', '-', '.', '/', 'AND', 'AS', 'AT', 'EXCEPT',
'FROM', 'GROUP', 'HAVING', 'INTERSECT', 'LIMIT', 'OR', 'ORDER', 'UNION',
'WHERE', '[', '||', <EOF>, <identifier> {code}
{code:java}
presto> select (1>0) is true;
Query 20250509_090519_40277_cqcdm failed: line 1:17: mismatched input 'true'.
Expecting: 'DISTINCT', 'NOT', 'NULL'
{code}
*and convert to IS NOT NULL and BOOLEAN can work well:*
Hive:
{code:java}
select 1 > 0 IS NOT NULL AND 1 > 0;
> 1 {code}
Presto
note：1>0 IS NOT NULL is not support
{code:java}
presto> select 1 > 0 IS NOT NULL AND 1 > 0;
Query 20250509_094813_43212_cqcdm failed: line 1:14: mismatched input 'IS'.
Expecting: '%', '*', '+', ',', '-', '.', '/', 'AND', 'AS', 'AT', 'EXCEPT',
'FROM', 'GROUP', 'HAVING', 'INTERSECT', 'LIMIT', 'OR', 'ORDER', 'UNION',
'WHERE', '[', '||', <EOF>, <identifier>
select 1 > 0 IS NOT NULL AND 1 > 0
{code}
*need to convert to (1>0) IS NOT NULL*
{code:java}
presto> select (1 > 0) IS NOT NULL AND 1 > 0;
_col0
-------
true
(1 row) {code}
so In Hive:
*we should convert the syntax as:*
A IS TRUE: A IS NOT NULL AND A
A IS FALSE: A IS NOT NULL AND NOT A
A IS NOT TRUE: A IS NULL OR NOT A
A IS NOT FALSE: A IS NULL OR NOT A
and in Presto:
*we should convert the syntax with () around A:*
(A) IS TRUE: (A) IS NOT NULL AND A
(A) IS FALSE: (A) IS NOT NULL AND NOT A
(A) IS NOT TRUE: (A) IS NULL OR NOT A
(A) IS NOT FALSE: (A) IS NULL OR NOT A
> Invalid unparse for IS TRUE,IS FALSE,IS NOT TRUE and IS NOT FALSE in
> Hive/Presto Dialect
> ----------------------------------------------------------------------------------------
>
> Key: CALCITE-7005
> URL: https://issues.apache.org/jira/browse/CALCITE-7005
> Project: Calcite
> Issue Type: Bug
> Components: core
> Affects Versions: 1.39.0
> Reporter: Yu Xu
> Assignee: Yu Xu
> Priority: Major
> Labels: pull-request-available
> Fix For: 1.40.0
>
>
> IS TRUE/ IS FALSE/IS NOT TRUE/IS NOT FALSE can not support in Hive/Presto
> System.
> Hive:
> {code:java}
> select 1>0 is true;
> NoViableAltException(295@[])
> at
> org.apache.hadoop.hive.ql.parse.HiveParser_IdentifiersParser.nullCondition(HiveParser_IdentifiersParser.java:7144)
> at
> org.apache.hadoop.hive.ql.parse.HiveParser_IdentifiersParser.precedenceUnarySuffixExpression(HiveParser_IdentifiersParser.java:7369)
> at
> org.apache.hadoop.hive.ql.parse.HiveParser_IdentifiersParser.precedenceBitwiseXorExpression(HiveParser_IdentifiersParser.java:7516)
> at
> org.apache.hadoop.hive.ql.parse.HiveParser_IdentifiersParser.precedenceStarExpression(HiveParser_IdentifiersParser.java:7659)
> at
> org.apache.hadoop.hive.ql.parse.HiveParser_IdentifiersParser.precedencePlusExpression(HiveParser_IdentifiersParser.java:7802)
> at
> org.apache.hadoop.hive.ql.parse.HiveParser_IdentifiersParser.precedenceConcatenateExpression(HiveParser_IdentifiersParser.java:7941)
> at
> org.apache.hadoop.hive.ql.parse.HiveParser_IdentifiersParser.precedenceAmpersandExpression(HiveParser_IdentifiersParser.java:8151)
> {code}
> {code:java}
> select (1>0) is true; NoViableAltException(295@[]) at
> org.apache.hadoop.hive.ql.parse.HiveParser_IdentifiersParser.nullCondition(HiveParser_IdentifiersParser.java:7144)
> at
> org.apache.hadoop.hive.ql.parse.HiveParser_IdentifiersParser.precedenceUnarySuffixExpression(HiveParser_IdentifiersParser.java:7369)
> at
> org.apache.hadoop.hive.ql.parse.HiveParser_IdentifiersParser.precedenceBitwiseXorExpression(HiveParser_IdentifiersParser.java:7516)
> at
> org.apache.hadoop.hive.ql.parse.HiveParser_IdentifiersParser.precedenceStarExpression(HiveParser_IdentifiersParser.java:7659)
> at
> org.apache.hadoop.hive.ql.parse.HiveParser_IdentifiersParser.precedencePlusExpression(HiveParser_IdentifiersParser.java:7802)
> at
> org.apache.hadoop.hive.ql.parse.HiveParser_IdentifiersParser.precedenceConcatenateExpression(HiveParser_IdentifiersParser.java:7941)
> at
> org.apache.hadoop.hive.ql.parse.HiveParser_IdentifiersParser.precedenceAmpersandExpression(HiveParser_IdentifiersParser.java:8151)
> {code}
> presto:
> {code:java}
> presto> select 1>0 is true;
> Query 20250509_090525_40291_cqcdm failed: line 1:12: mismatched input 'is'.
> Expecting: '%', '*', '+', ',', '-', '.', '/', 'AND', 'AS', 'AT', 'EXCEPT',
> 'FROM', 'GROUP', 'HAVING', 'INTERSECT', 'LIMIT', 'OR', 'ORDER', 'UNION',
> 'WHERE', '[', '||', <EOF>, <identifier> {code}
> {code:java}
> presto> select (1>0) is true;
> Query 20250509_090519_40277_cqcdm failed: line 1:17: mismatched input 'true'.
> Expecting: 'DISTINCT', 'NOT', 'NULL'
> {code}
> *and convert to IS NOT NULL and BOOLEAN can work well:*
> Hive:
> {code:java}
> select 1 > 0 IS NOT NULL AND 1 > 0;
> > 1 {code}
>
> Presto
> note：1>0 IS NOT NULL is not support
> {code:java}
> presto> select 1 > 0 IS NOT NULL AND 1 > 0;
> Query 20250509_094813_43212_cqcdm failed: line 1:14: mismatched input 'IS'.
> Expecting: '%', '*', '+', ',', '-', '.', '/', 'AND', 'AS', 'AT', 'EXCEPT',
> 'FROM', 'GROUP', 'HAVING', 'INTERSECT', 'LIMIT', 'OR', 'ORDER', 'UNION',
> 'WHERE', '[', '||', <EOF>, <identifier>
> select 1 > 0 IS NOT NULL AND 1 > 0
> {code}
> *need to convert to (1>0) IS NOT NULL*
> {code:java}
> presto> select (1 > 0) IS NOT NULL AND 1 > 0;
> _col0
> -------
> true
> (1 row) {code}
> so In Hive:
> *we should convert the syntax as:*
> A IS TRUE: A IS NOT NULL AND A
> A IS FALSE: A IS NOT NULL AND NOT A
> A IS NOT TRUE: A IS NULL OR NOT A
> A IS NOT FALSE: A IS NULL OR NOT A
>
> and in Presto:
> *we should convert the syntax with () around A:*
> (A) IS TRUE: (A) IS NOT NULL AND A
> (A) IS FALSE: (A) IS NOT NULL AND NOT A
> (A) IS NOT TRUE: (A) IS NULL OR NOT A
> (A) IS NOT FALSE: (A) IS NULL OR NOT A
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
