Yu Xu created CALCITE-7005:
------------------------------
Summary: Invalid unparse for IS TRUE,IS FALSE,IS NOT TRUE and IS
NOT FALSE in Hive/Presto
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
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)
