[MDEV-33027] Unexpected values appeared in select query statements that should logically imply each other Created: 2023-12-14  Updated: 2023-12-18

Status: Open
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 10.6.16
Fix Version/s: 10.6, 10.11, 11.0, 11.1

Type: Bug Priority: Major
Reporter: akuluasan Assignee: Sergei Petrunia
Resolution: Unresolved Votes: 0
Labels: None
Environment:

ubuntu 20.04


 Description    

create table table_3_utf8_undef (
 
`pk` int primary key,
 
`col_bigint_undef_signed` bigint  ,
 
`col_bigint_undef_unsigned` bigint unsigned ,
 
`col_bigint_key_signed` bigint  ,
 
`col_bigint_key_unsigned` bigint unsigned ,
 
`col_float_undef_signed` float  ,
 
`col_float_undef_unsigned` float unsigned ,
 
`col_float_key_signed` float  ,
 
`col_float_key_unsigned` float unsigned ,
 
`col_double_undef_signed` double  ,
 
`col_double_undef_unsigned` double unsigned ,
 
`col_double_key_signed` double  ,
 
`col_double_key_unsigned` double unsigned ,
 
`col_decimal(40, 20)_undef_signed` decimal(40, 20)  ,
 
`col_decimal(40, 20)_undef_unsigned` decimal(40, 20) unsigned ,
 
`col_decimal(40, 20)_key_signed` decimal(40, 20)  ,
 
`col_decimal(40, 20)_key_unsigned` decimal(40, 20) unsigned ,
 
`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)  ,
 
key (`col_bigint_key_signed`),
 
key (`col_bigint_key_unsigned`),
 
key (`col_float_key_signed`),
 
key (`col_float_key_unsigned`),
 
key (`col_double_key_signed`),
 
key (`col_double_key_unsigned`),
 
key (`col_decimal(40, 20)_key_signed`),
 
key (`col_decimal(40, 20)_key_unsigned`),
 
key (`col_char(20)_key_signed`),
 
key (`col_varchar(20)_key_signed`)
 
) character set utf8 ;
 
insert into table_3_utf8_undef values (0,82.1847,1,39.0425,38.1089,-1,1,94.1106,1.009,12.991,19755,-13064,0,1,79.1429,-2,1,"well",'3
 
','-0','e'),(1,1,20.0078,-9.183,68.1957,1,2,1,0.0001,12.991,2,71.0510,1,-1,2,12.991,12.991,'3	','1','3	','-0'),(2,-2,1,-21247,1.009,2,1.009,0.0001,36.0002,-2,2,-0,0.0001,-2,0.1598,47.1515,1.009,'3	','w','-1','e');
 
create table table_7_utf8_undef (
 
`pk` int primary key,
 
`col_bigint_undef_signed` bigint  ,
 
`col_bigint_undef_unsigned` bigint unsigned ,
 
`col_bigint_key_signed` bigint  ,
 
`col_bigint_key_unsigned` bigint unsigned ,
 
`col_float_undef_signed` float  ,
 
`col_float_undef_unsigned` float unsigned ,
 
`col_float_key_signed` float  ,
 
`col_float_key_unsigned` float unsigned ,
 
`col_double_undef_signed` double  ,
 
`col_double_undef_unsigned` double unsigned ,
 
`col_double_key_signed` double  ,
 
`col_double_key_unsigned` double unsigned ,
 
`col_decimal(40, 20)_undef_signed` decimal(40, 20)  ,
 
`col_decimal(40, 20)_undef_unsigned` decimal(40, 20) unsigned ,
 
`col_decimal(40, 20)_key_signed` decimal(40, 20)  ,
 
`col_decimal(40, 20)_key_unsigned` decimal(40, 20) unsigned ,
 
`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)  ,
 
key (`col_bigint_key_signed`),
 
key (`col_bigint_key_unsigned`),
 
key (`col_float_key_signed`),
 
key (`col_float_key_unsigned`),
 
key (`col_double_key_signed`),
 
key (`col_double_key_unsigned`),
 
key (`col_decimal(40, 20)_key_signed`),
 
key (`col_decimal(40, 20)_key_unsigned`),
 
key (`col_char(20)_key_signed`),
 
key (`col_varchar(20)_key_signed`)
 
) character set utf8 ;
 
insert into table_7_utf8_undef values (0,-9.183,1,1.1384,2,15.1271,12.991,-2,0.0001,36.1270,79.1819,0.0001,0.0001,3.1387,52.0818,-0,0.0001,'1','3	','0','0'),(1,79,12.991,107,2,-0.0001,0,1.009,1.009,34,1,-1,69.0208,1,2,120,12.991,'3	','-1',"if",'b'),(2,-2,1,-9.183,1,12.991,0.0001,53,12.991,1.009,12.991,12.991,0.0001,-0.0001,12.991,0.0001,2,'3
 
','p','0','3	'),(3,-0.0001,12.991,1.009,1.009,-9.183,2,0,1,-2,1,2,1,2,1.009,2,12.991,'3
 
','0','k','0'),(4,1.009,0.0001,-1,12.991,2,47,2,0,12.991,12.991,1.009,0,1.009,1.009,-0.0001,6949,'-1','	3','1','m'),(5,-0,1,0,0,0.0001,28.1237,12.991,0,12.991,12.991,-0,12.991,2,2,2,1.009,'0','	3','0','	3'),(6,45.0855,1,38.1166,1,1.009,80.0284,2,122,0.0001,0,-1,11130,0,1,1,0,"know",'-0','
 
3','3
 
');


(SELECT (BINARY CHARSET(3)&BINARY `f4`+~`f5`) AS `f1`,(COERCIBILITY(`f6`)) AS `f2`,(`f4`) AS `f3` FROM (SELECT `col_float_undef_unsigned` AS `f4`,`col_decimal(40, 20)_key_unsigned` AS `f5`,`col_char(20)_key_signed` AS `f6` FROM `table_7_utf8_undef` FORCE INDEX (`col_float_key_unsigned`, `col_float_key_unsigned`)) AS `t1` ORDER BY `f4`) UNION (SELECT (~`f7`) AS `f1`,(`f8`-`f8`^`f9`) AS `f2`,(COERCIBILITY(`f9`)<<~LOG2(3717912650637241729)) AS `f3` FROM (SELECT `col_decimal(40, 20)_undef_unsigned` AS `f10`,`col_decimal(40, 20)_key_unsigned` AS `f8`,`col_double_key_unsigned` AS `f11` FROM `table_3_utf8_undef` FORCE INDEX (`col_char(20)_key_signed`, `col_decimal(40, 20)_key_signed`)) AS `t2` NATURAL JOIN (SELECT `col_bigint_undef_unsigned` AS `f7`,`col_char(20)_key_signed` AS `f12`,`col_double_undef_unsigned` AS `f9` FROM `table_3_utf8_undef` IGNORE INDEX (`col_float_key_signed`)) AS `t3`);


+----------------------+-----------------------------+------------------------+
 
| f1                   | f2                          | f3                     |
 
+----------------------+-----------------------------+------------------------+
 
|                    0 |      2.00000000000000000000 |     12.991000175476074 |
 
|                    0 |      2.00000000000000000000 |                      0 |
 
|                    0 |      2.00000000000000000000 | 0.00009999999747378752 |
 
|                    0 |      2.00000000000000000000 |                      2 |
 
|                    0 |      2.00000000000000000000 |                     47 |
 
|                    0 |      2.00000000000000000000 |     28.123699188232422 |
 
|                    0 |      2.00000000000000000000 |      80.02839660644531 |
 
| 18446744073709551614 | -19753.00000000000000000000 |                      0 |
 
| 18446744073709551614 | -19737.00900000000000000000 |                      0 |
 
| 18446744073709551614 | -19752.99100000000000000000 |                      0 |
 
| 18446744073709551595 |     -2.00000000000000000000 |                      0 |
 
| 18446744073709551595 |     -2.00900000000000000000 |                      0 |
 
| 18446744073709551595 |     -1.99100000000000000000 |                      0 |
 
| 18446744073709551614 |     -2.00000000000000000000 |                      0 |
 
| 18446744073709551614 |     -2.00900000000000000000 |                      0 |
 
| 18446744073709551614 |     -1.99100000000000000000 |                      0 |
 
+----------------------+-----------------------------+------------------------+


(SELECT (BINARY CHARSET(3)&BINARY `f4`+~`f5`) AS `f1`,(COERCIBILITY(`f6`)) AS `f2`,(`f4`) AS `f3` FROM (SELECT `col_float_undef_unsigned` AS `f4`,`col_decimal(40, 20)_key_unsigned` AS `f5`,`col_char(20)_key_signed` AS `f6` FROM `table_7_utf8_undef` FORCE INDEX (`col_float_key_unsigned`, `col_float_key_unsigned`)) AS `t1` ORDER BY `f4`) UNION (SELECT DISTINCT (~`f7`) AS `f1`,(`f8`-`f8`^`f9`) AS `f2`,(COERCIBILITY(`f9`)<<~LOG2(3717912650637241729)) AS `f3` FROM (SELECT `col_decimal(40, 20)_undef_unsigned` AS `f10`,`col_decimal(40, 20)_key_unsigned` AS `f8`,`col_double_key_unsigned` AS `f11` FROM `table_3_utf8_undef` FORCE INDEX (`col_char(20)_key_signed`, `col_decimal(40, 20)_key_signed`)) AS `t2` NATURAL JOIN (SELECT `col_bigint_undef_unsigned` AS `f7`,`col_char(20)_key_signed` AS `f12`,`col_double_undef_unsigned` AS `f9` FROM `table_3_utf8_undef` IGNORE INDEX (`col_float_key_signed`)) AS `t3`);


+----------------------+------------------------+------------------------+
 
| f1                   | f2                     | f3                     |
 
+----------------------+------------------------+------------------------+
 
|                    0 | 2.00000000000000000000 |     12.991000175476074 |
 
|                    0 | 2.00000000000000000000 |                      0 |
 
|                    0 | 2.00000000000000000000 | 0.00009999999747378752 |
 
|                    0 | 2.00000000000000000000 |                      2 |
 
|                    0 | 2.00000000000000000000 |                     47 |
 
|                    0 | 2.00000000000000000000 |     28.123699188232422 |
 
|                    0 | 2.00000000000000000000 |      80.02839660644531 |
 
| 18446744073709551614 | 0.00000000000000000000 |                      0 |
 
| 18446744073709551595 | 0.00000000000000000000 |                      0 |
 
+----------------------+------------------------+------------------------+

The results should have had a containment relationship, but now they are not.

 Comments   
Comment by HeShan [ 2023-12-14 ]

Could you please confirm whether the simplification process we applied has been helpful for developers in diagnosing and addressing this issue?

SELECT (`f8`-`f8`^`f9`) AS `f2` FROM (SELECT `col_decimal(40, 20)_key_unsigned` AS `f8` FROM `table_3_utf8_undef` ) AS `t2`
 
NATURAL JOIN (SELECT `col_double_undef_unsigned` AS `f9` FROM `table_3_utf8_undef`) AS `t3`;


+-----------------------------+
 
| f2                          |
 
+-----------------------------+
 
| -19753.00000000000000000000 |
 
| -19752.99100000000000000000 |
 
| -19737.00900000000000000000 |
 
|     -2.00000000000000000000 |
 
|     -1.99100000000000000000 |
 
|     -2.00900000000000000000 |
 
|     -2.00000000000000000000 |
 
|     -1.99100000000000000000 |
 
|     -2.00900000000000000000 |
 
+-----------------------------+


SELECT DISTINCT (`f8`-`f8`^`f9`) AS `f2` FROM (SELECT `col_decimal(40, 20)_key_unsigned` AS `f8` FROM `table_3_utf8_undef` ) AS `t2` 
NATURAL JOIN (SELECT `col_double_undef_unsigned` AS `f9` FROM `table_3_utf8_undef` ) AS `t3`;


+------------------------+
 
| f2                     |
 
+------------------------+
 
| 0.00000000000000000000 |
 
+------------------------+

Comment by Sergei Golubchik [ 2023-12-18 ]

yes, thanks, it's much clearer now

Generated at Thu Feb 08 10:35:50 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.