If you are lucky, it will be sufficient to use the the same intermediate values that are used to execute NOT IN: * does the value occur in the sub-query? * how many values does the sub-query return? * how many null values does the sub-query return?
If you know that x is not one of the values returned by the sub-query, then the result is either false or unknown, depending on the number of nulls. If you know that x is one of the values returned by the sub-query, then the result is either true or false, depending on whether there is 1 not-null value or more than one. Write a good test of tests, write a truth table, and keep changing the logic until all of the tests pass. Julian On Mon, Apr 15, 2019 at 5:15 PM Vineet Garg <[email protected]> wrote: > > Hello folks, > > I am currently stuck on a problem and I wonder if the SQL folks here could > help me make progress. > > There is a bug in CALCITE where subqueries with =ANY and <>ANY are > transformed incorrectly (CALCITE-2986 > <https://issues.apache.org/jira/browse/CALCITE-2986>). I was able to figure > out the transformation for =ANY but I am unable to figure out how to > rewrite <>ANY clause. > I came across some sources online suggesting <>ANY could be rewritten using > EXISTS or NOT IN but I fail to see how. Using EXISTS will not work because > EXISTS only produce TRUE/FALSE but ANY requires three valued logic. NOT IN > is equivalent to column<>value1 AND column<>value2 but <>ANY is equivalent > to column<>value1 OR column<>value2 so I am not sure how these are > equivalent either. > > There is some discussion on CALCITE-2986 regarding using LEFT OUTER JOIN, > SEMI JOIN or Nested loop join but I don't believe any of them will produce > correct semantics. > > Does anyone know what is the correct rewrite for <>ANY? > > P.S. Can someone review pull request to fix =ANY rewrite? - #1161 > <https://github.com/apache/calcite/pull/1161> > > Thanks, > Vineet Garg
