BUG #18420: Unexpected values appeared in select query statements that should logically imply each other
The following bug has been logged on the website:
Bug reference: 18420
Logged by: Heil
Email address: akuluasan@163.com
PostgreSQL version: 16.2
Operating system: ubuntu 20.04
Description:
In theory,the result of sql2 should ∈ the result of sql1.The constraint of a
WHERE clause should be greater than or equal to WHERE FALSE.
however,the value 1 change to 1.0000000000000000 after changing the WHERE
clause,which seems like a logical bug
----create data----
create table table_3_utf8_undef (
id SERIAL PRIMARY KEY,
"col_int_undef_signed" int ,
"col_int_key_signed" int ,
"col_bigint_undef_signed" bigint ,
"col_bigint_key_signed" bigint ,
"col_real_undef_signed" real ,
"col_real_key_signed" real ,
"col_double precision_undef_signed" double precision ,
"col_double precision_key_signed" double precision ,
"col_numeric(40, 20)_undef_signed" numeric(40, 20) ,
"col_numeric(40, 20)_key_signed" numeric(40, 20) ,
"col_char(20)_undef_signed" char(20) ,
"col_char(20)_key_signed" char(20) ,
"col_varchar(20)_undef_signed" varchar(20) ,
"col_varchar(20)_key_signed" varchar(20)
) ;
insert into table_3_utf8_undef values
(0,82.1847,1,39.0425,-0.0001,-9.183,-1,-9.183,38.1089,-1,-9.183,'just','3 ','3
','-1'),(1,12.991,-0,-2,19755,-13064,-9.183,0,1,-0,79.1429,'3
','3 ','well','3
'),(2,9.1194,1,20.0078,-9.183,68.1957,1,2,1,-1,0.0001,'
3','
3','-0','-1');
create table table_7_utf8_undef (
id SERIAL PRIMARY KEY,
"col_int_undef_signed" int ,
"col_int_key_signed" int ,
"col_bigint_undef_signed" bigint ,
"col_bigint_key_signed" bigint ,
"col_real_undef_signed" real ,
"col_real_key_signed" real ,
"col_double precision_undef_signed" double precision ,
"col_double precision_key_signed" double precision ,
"col_numeric(40, 20)_undef_signed" numeric(40, 20) ,
"col_numeric(40, 20)_key_signed" numeric(40, 20) ,
"col_char(20)_undef_signed" char(20) ,
"col_char(20)_key_signed" char(20) ,
"col_varchar(20)_undef_signed" varchar(20) ,
"col_varchar(20)_key_signed" varchar(20)
) ;
insert into table_7_utf8_undef values
(0,2,61,-0,-0.0001,-9.183,1,-1,2,12.991,-14616,'0','
3','3 ','1'),(1,1,1,-2,-0.0001,1,-21247,1.009,2,1.009,0.0001,'-0','and','
3','3
'),(2,-2,2,-0,0.0001,-2,0.1598,47.1515,1.009,2,-0,'n','e','
3','-0'),(3,1.1384,2,15.1271,-0,12.991,-2,0.0001,36.1270,79.1819,0.0001,'0','-1','-1','t'),(4,52.0818,-0,0.0001,-0,1,-0,-2,79,12.991,107,'3
','j','1','1'),(5,0,1.009,1.009,34,-9,1,-1,-114,69.0208,1,'
3','3
','v','a'),(6,12.991,-24657,3775,-0.0001,0.0001,-2,1,-9.183,1,12.991,'1','3 ','n','0');
----sql1----
WITH "MYWITH" AS ((SELECT (CEILING(0.248382)) AS "f1",(CHAR_LENGTH(
'gqnjruy')) AS "f2",(CHAR_LENGTH( 'vftdxup')) AS "f3" FROM (SELECT
"col_bigint_key_signed" AS "f4", "col_real_key_signed" AS "f5", "col_double
precision_undef_signed" AS "f6" FROM "table_3_utf8_undef") AS "t1"
WHERE (((NOT ((LEFT( 'wxynhwi', 3)) IN (SELECT
"col_varchar(20)_undef_signed" FROM "table_3_utf8_undef"))) OR ((ROW(ASCII(
'ydadiyx')<<970,4) NOT IN (SELECT "col_int_undef_signed", "col_double
precision_key_signed" FROM "table_3_utf8_undef")) IS FALSE)) AND
(((ROW(ABS(0.277315),ACOS(0.976515)-PI()) IN (SELECT "col_double
precision_key_signed", "col_numeric(40, 20)_key_signed" FROM
"table_3_utf8_undef")) IS FALSE) OR ((TRUE) IS FALSE))) IS TRUE ORDER BY
"f4")
UNION (SELECT (CEILING(0.567733)^CEIL(0.891845)) AS "f1",(CHAR_LENGTH(
'pzdkwfk')) AS "f2",(CHAR_LENGTH( 'yrwglcb')) AS "f3" FROM (SELECT
"col_real_key_signed" AS "f10", "col_bigint_key_signed" AS "f8",
"col_int_undef_signed" AS "f11" FROM "table_3_utf8_undef") AS "t2" NATURAL
JOIN (SELECT "col_double precision_undef_signed" AS "f7",
"col_int_undef_signed" AS "f12", "col_numeric(40, 20)_undef_signed" AS "f9"
FROM "table_3_utf8_undef") AS "t3")) SELECT * FROM "MYWITH";
f1 | f2 | f3
----+----+----
1 | 7 | 7
----sql2----
WITH "MYWITH" AS ((SELECT (CEILING(0.248382)) AS "f1",(CHAR_LENGTH(
'gqnjruy')) AS "f2",(CHAR_LENGTH( 'vftdxup')) AS "f3" FROM (SELECT
"col_bigint_key_signed" AS "f4", "col_real_key_signed" AS "f5", "col_double
precision_undef_signed" AS "f6" FROM "table_3_utf8_undef") AS "t1"
WHERE FALSE ORDER BY "f4")
UNION (SELECT (CEILING(0.567733)^CEIL(0.891845)) AS "f1",(CHAR_LENGTH(
'pzdkwfk')) AS "f2",(CHAR_LENGTH( 'yrwglcb')) AS "f3" FROM (SELECT
"col_real_key_signed" AS "f10", "col_bigint_key_signed" AS "f8",
"col_int_undef_signed" AS "f11" FROM "table_3_utf8_undef") AS "t2" NATURAL
JOIN (SELECT "col_double precision_undef_signed" AS "f7",
"col_int_undef_signed" AS "f12", "col_numeric(40, 20)_undef_signed" AS "f9"
FROM "table_3_utf8_undef") AS "t3")) SELECT * FROM "MYWITH";
f1 | f2 | f3
--------------------+----+----
1.0000000000000000 | 7 | 7
I am using my tool to simplify the SQL query. Can you please confirm if the simplification process helps you diagnose and locate bugs?
----create data----
create table table_3_utf8_undef (
id SERIAL PRIMARY KEY,
"col_varchar(20)_undef_signed" varchar(20)
) ;
insert into table_3_utf8_undef values (0,'3
'),(1,'well'),(2,'-0');
----sql1----
(SELECT (CEILING(0.248382)) AS "f1"FROM "table_3_utf8_undef"
WHERE (NOT ('wxynhwi' IN (SELECT "col_varchar(20)_undef_signed" FROM "table_3_utf8_undef")))
)
UNION (SELECT (CEILING(0.567733)^CEIL(0.891845)) AS "f1" FROM "table_3_utf8_undef");
f1
----
1
----sql2----
(SELECT (CEILING(0.248382)) AS "f1" FROM "table_3_utf8_undef"
WHERE FALSE )
UNION (SELECT (CEILING(0.567733)^CEIL(0.891845)) AS "f1" FROM"table_3_utf8_undef");
f1
--------------------
1.0000000000000000
On Thu, 4 Apr 2024 at 22:44, 王子涵4620 <1290874854@qq.com> wrote:
create table table_3_utf8_undef (
id SERIAL PRIMARY KEY,
"col_varchar(20)_undef_signed" varchar(20)) ;
insert into table_3_utf8_undef values (0,'3
'),(1,'well'),(2,'-0');
What's going on here is just the same as the following.
postgres=# select '1'::numeric union select '1.00000'::numeric;
numeric
---------
1
(1 row)
postgres=# select '1.00000'::numeric union select '1'::numeric;
numeric
---------
1.00000
(1 row)
Essentially, numerics 1 and 1.00000 are equal but which one is output
from the query depends on which one was seen first by the code that
eliminates the duplicates.
Something similar can be seen with:
postgres=# select sum(n) from (values('1'::numeric),
('10.00000'::numeric), ('-10'::numeric)) n(n);
sum
---------
1.00000
(1 row)
You might expect this returns "1" rather than "1.00000". This is just
another example of us not reducing the numeric's dscale down to the
minimum value it can be without losing precision.
If we always reduced the numeric down to its minimum dscale, then the
following query would return "1" rather than "1.00000". The "n"
column type is numeric(8,5) so having 5 digits after the decimal point
seems correct here.
create table n (n numeric(8,5));
insert into n values('1'),('10'),('-10');
select sum(n) from n;
sum
---------
1.00000
I'm unsure how we'd change the behaviour of one without changing the
behaviour of the other.
David