Re: BUG #19350: Short circuit optimization missed when runningsqlscriptes in JDBC - Mailing list pgsql-bugs

Hi Greg,

Thanks for your reply. I can understand what you describe.

But my question is that in the CLI, the test case (like foo2) I showed can return the results. But when I run the same test case in JDBC, the test case triggers an error. I wonder why there is an inconsistency.

```
SET plan_cache_mode = force_generic_plan;
CREATE TABLE t0(c0 int4range);
INSERT INTO t0(c0) VALUES('[-1920846305,-1018839689)'::int4range);
PREPARE prepare_query (int8, int8) AS SELECT
((((((upper(t0.c0))))/($1)))*(($2::int8))) FROM ONLY t0;
EXECUTE prepare_query(0, NULL); -- trigger error in JDBC but no error in CLI

```

Fails as expected from the command line:

prepare foo1 as select 99 / $1::int * $2;
PREPARE
execute foo1(0, null);
ERROR:  division by zero

prepare foo2 as select 99 / ($1::int * $2);
PREPARE
execute foo2(0, null);
 ?column?
----------
        ☃
(1 row)

prepare foo3 as select (99 / $1::int) * $2;
PREPARE
execute foo3(0, null);
ERROR:  division by zero

If you want the "null combined with anything = null" to always happen before the "divide by zero is wrong" make your query specific like in foo2 above.

Cheers,

Greg

--

Crunchy Data - https://www.crunchydata.com

Enterprise Postgres Software Products & Tech Support