adriangb commented on issue #23692: URL: https://github.com/apache/datafusion/issues/23692#issuecomment-5336100456
The diagnosis here looks slightly off, and the bug is worse than a planning error: this is operator precedence in the parser, not type coercion. The right operand of `IS [NOT] DISTINCT FROM` is parsed at a lower precedence than `AND`/`OR`, so it swallows the following clause. The ON condition above binds as: ``` l.a IS NOT DISTINCT FROM (r.a AND (l.b IS NOT DISTINCT FROM r.b)) ``` which is where `Int32 AND Boolean` comes from. Parenthesizing each comparison, with nothing else changed, makes the same query return the expected row: ```sql -- datafusion-cli 54.0.0 CREATE TABLE l (a INT, b VARCHAR) AS VALUES (1,'x'), (2,'y'); CREATE TABLE r (a INT, b VARCHAR) AS VALUES (1,'x'); SELECT l.* FROM l LEFT ANTI JOIN r ON l.a IS NOT DISTINCT FROM r.a AND l.b IS NOT DISTINCT FROM r.b; -- Error during planning: Cannot infer common argument type for logical boolean operation Int32 AND Boolean SELECT l.* FROM l LEFT ANTI JOIN r ON (l.a IS NOT DISTINCT FROM r.a) AND (l.b IS NOT DISTINCT FROM r.b); -- 2 | y ``` If the coercion rule were mistyping the result, the parenthesized form would fail too. Worth flagging separately: when the swallowed operands happen to be type-compatible there is no error at all, just a wrong answer. ```sql SELECT false IS NOT DISTINCT FROM false OR true; -- false, should be true SELECT false = false OR true; -- true (control: `=` binds correctly) ``` Same statements across engines: | | `false IS NOT DISTINCT FROM false OR true` | `'x' IS NOT DISTINCT FROM 'x' OR true` | |---|---|---| | PostgreSQL 17.11 | `true` | `true` | | DuckDB 1.5.2 | `true` | `true` | | DataFusion 54.0.0 | `false` | planning error | Applies to both `IS DISTINCT FROM` and `IS NOT DISTINCT FROM`, with both `AND` and `OR`, on any operand type. `=` and `IS NOT NULL` are unaffected, and putting the comparison last is safe since nothing follows it to swallow. The workaround is to parenthesize the comparison. This looks like a concrete instance of #22461: that issue lists `IS DISTINCT FROM` at a precedence above `AND`/`OR`, which is not what the parser does here. -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: [email protected] For queries about this service, please contact Infrastructure at: [email protected] --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
