Uploaded image for project: 'Calcite'
  1. Calcite
  2. CALCITE-7005

Invalid unparse for IS TRUE,IS FALSE,IS NOT TRUE and IS NOT FALSE in Hive/Presto Dialect

    XMLWordPrintableJSON

Details

    Description

      IS TRUE/ IS FALSE/IS NOT TRUE/IS NOT FALSE can not support  in Hive/Presto System.

      Hive:

      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)


      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)

      presto:

      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> 
      presto> select (1>0) is true;
Query 20250509_090519_40277_cqcdm failed: line 1:17: mismatched input 'true'. Expecting: 'DISTINCT', 'NOT', 'NULL'

      and convert to IS NOT NULL and BOOLEAN can work well:

      Hive:

      select 1 > 0 IS NOT NULL AND 1 > 0; 
> 1

       

      Presto

      note：1>0 IS NOT NULL   is not support 

      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

      need to convert to (1>0) IS NOT NULL

      presto> select (1 > 0) IS NOT NULL AND 1 > 0;
 _col0 
-------
 true  
(1 row)

      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

      Attachments

        Issue Links

          Blocked

          Bug - A problem which impairs or prevents the functions of the product. CALCITE-6979 Invalid unparse for IS TRUE,IS FALSE,IS NOT TRUE and IS NOT FALSE in ClickHouseDialect

          • Major - Major loss of function.
          • Closed
          links to

          Web Link GitHub Pull Request #4367

          Activity

            People

              xuzifu666 Yu Xu
              xuzifu666 Yu Xu
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved: