Обсуждение: BUG #18420: Unexpected values appeared in select query statements that should logically imply each other
BUG #18420: Unexpected values appeared in select query statements that should logically imply each other
От
PG Bug reporting form
Дата:
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