BUG #18420: Unexpected values appeared in select query statements that should logically imply each other - Mailing list pgsql-bugs

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