Justin Ng, on Wednesday, March 11, 2020 07:03 AM, wrote... > > This happens in SQLite 3.28 and 3.31. > > Consider the following queries, > > -- Query 1 > SELECT > COALESCE( > NULL, > (SELECT SUM(9223372036854775807) FROM (SELECT NULL UNION ALL SELECT > NULL)) > );
On 3.24.0, this worked correctly: 8:32:20.56>sqlite3 SQLite version 3.24.0 2018-06-04 19:24:41 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> SELECT ...> COALESCE( ...> NULL, ...> (SELECT SUM(9223372036854775807) FROM (SELECT NULL UNION ALL SELECT NULL)) ...> ); Error: integer overflow > -- Query 2 > SELECT > COALESCE( > (SELECT 'hello'), > (SELECT SUM(9223372036854775807) FROM (SELECT NULL UNION ALL SELECT > NULL)) > ); Also, on 3.24.0. this worked correctly: sqlite> SELECT ...> COALESCE( ...> (SELECT 'hello'), ...> (SELECT SUM(9223372036854775807) FROM (SELECT NULL UNION ALL SELECT NULL)) ...> ); hello > -- Query 3 > SELECT > COALESCE( > (SELECT 'hello'), > ABS(-9223372036854775808) > ); This one, above, was bad at 3.24.0 also. sqlite> SELECT ...> COALESCE( ...> (SELECT 'hello'), ...> ABS(-9223372036854775808) ...> ); Error: integer overflow > Query 1 should throw `integer overflow`. > Query 2 should return 'hello'. > Query 3 should return 'hello'. > > However, Query 3 throws `integer overflow`. It should short-circuit and > not evaluate ABS() Why do you think that that it should not evaluate ABS? It is there and you asked for it. I believe it's a good idea to say, "hey, the amount you placed here is out of boundary, think about what you are doing here." IMO, of course. Thanks. josé _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
