[jira] [Created] (CALCITE-4449) When converting Sarg 'x IS NULL OR x NOT IN (1, 2)', generate better SQL

["Julian Hyde (Jira)" <ji...@apache.org>]

Julian Hyde created CALCITE-4449:
------------------------------------

             Summary: When converting Sarg 'x IS NULL OR x NOT IN (1, 2)', generate better SQL
                 Key: CALCITE-4449
                 URL: https://issues.apache.org/jira/browse/CALCITE-4449
             Project: Calcite
          Issue Type: Bug
            Reporter: Julian Hyde


When converting the Sarg
{code:java}
x IS NULL OR x NOT IN (1, 2){code}
to SQL, Calcite currently generates
{code:java}
x IS NULL OR x < 1 OR x > 1 AND x < 2 OR x > 2{code}
This is incorrect (because {{AND}} has higher precedence than {{OR}}) and also ugly. Adding parentheses makes it correct:
{code:java}
x IS NULL OR (x < 1 OR x > 1) AND (x < 2 OR x > 2){code}
But it's still ugly. Using {{NOT IN}} or {{<>}} would be ideal:
{code:java}
x IS NULL OR x <> 1 AND x <> 2{code}
 



--
This message was sent by Atlassian Jira
(v8.3.4#803005)