Materialize: Spurious division by zero?

[jamii]

These queries produce null in postgres and "division by zero" in mz.

SELECT ALL + CASE - 59 WHEN + 55 * - 56 + - - 95 THEN - 78 + 98 * CASE - COUNT ( * ) WHEN NULLIF ( - AVG ( DISTINCT - + NULLIF ( + - ( 11 ), 89 / ( 0 ) ) ), - COUNT ( * ) ) + 62 THEN 37 + + COUNT ( DISTINCT + 77 ) / 61 ELSE + 26 / 10 - + 14 * - 10 END / 79 END * 60 col2

SELECT ALL CASE - 6 WHEN + 70 + ( 81 ) THEN NULL WHEN COALESCE ( - 22, - + COALESCE ( - 64, + 65 / 49 + - + 62, - - NULLIF ( + 57, COUNT ( ALL - CASE 58 WHEN + 17 - 80 THEN 72 * - 40 + 54 * + 58 WHEN - CAST ( NULL AS INTEGER ) THEN NULL WHEN - 59 + 45 / + COALESCE ( 54 / 88, CAST ( NULL AS INTEGER ) * - 22 + - 58 * + 98 ) THEN - 18 / 20 ELSE NULL END ) * 3 ) ) * - MAX ( DISTINCT + 46 * - 27 ), AVG ( ALL 22 ) ) THEN NULL WHEN 23 THEN 42 - + MAX ( 52 ) END

SELECT ALL 56 * 97 - SUM ( - 51 ) + NULLIF ( + - 36, + + CAST ( NULL AS INTEGER ) ) * + - 91 / - 0 * - 45 * - 10 * - CAST ( NULL AS INTEGER ) * - - 85

[jamii]

I'm guessing that that is down to overeager constant folding - we produce errors on code that postgres short-circuits away from.

[frankmcsherry]

ScalarExpr::reduce certainly reduces all sub-expressions without much care. For example, it seems to reduce both branches of an If and then use constant-ness to fill itself in. But, perhaps it and maybe other operators should exercise more care in their recursive calls. Probably not too hard to fix!

[benesch]

@jamii fixed by #2432, or is there more to do here?

[jamii]

All I did was move the tests in question to the postgres incompatibility section. We should probably still figure out where we fall on short-circuiting vs optimization.

[benesch]

Summarizing my position from our Slack discussion. This is what Postgres allows us to do: https://www.postgresql.org/docs/current/sql-expressions.html#SYNTAX-EXPRESS-EVAL

The order of evaluation of subexpressions is not defined. In particular, the inputs of an operator or function are not necessarily evaluated left-to-right or in any other fixed order.

In short: if we can statically eliminate an error, as in the expression FALSE AND 1 / 0, we might produce that error... or we might not! We might produce the error if you write FALSE AND 1 / 0, but not if you write 1 / 0 AND FALSE. We have license to do any of these things, or not do any of these things, according to Postgres.

This is somewhat user hostile, but it's very friendly to a SQL query optimizer that wants to rewrite expressions according to all possible laws of relational + boolean + algebra.

Where possible I think we should try to follow Postgres, but the three examples here are cases where it's not even clear how to describe what Postgres is doing. Specifically

SELECT nullif(1, 2) / 0 * null;

does not cause a division by zero error in Postgres, while the same query with the nullif manually constant propagated

select 1 / 0 * null;

_does_ cause a division by zero error in Postgres.

I'm personally comfortable writing these off as weird edge cases we don't care about, until someone shows up with a compelling reason about why we should care about them.