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
