[MDEV-33683] Unexpected values appeared in select query statements that should logically imply each other - Jira

XML

Word

Printable

Details

Type: Bug
Status: Open (View Workflow)
Priority: Major
Resolution: Unresolved
Affects Version/s: 11.3.2
Fix Version/s: None
Component/s: Optimizer
Labels:
None
Environment:
ubuntu 20.04

Description

DESCRIPTION

In theory,the result of sql2(distinct) ∈ the result of sql1

however,the value 19.377576771444826 and -56.62242322855517 change to 19 and -57 after adding DISTINCT,which seems like a logical bug

sql1

WITH `MYWITH` AS ((SELECT (_UTF8MB4'a') AS `f1`,(`f4`) AS `f2`,(DATE_ADD(DAYOFYEAR(_UTF8MB4'2015-02-11'), INTERVAL 1 YEAR)) AS `f3` FROM (SELECT `col_varchar(20)_undef_signed` AS `f4`,`col_float_undef_signed` AS `f7`,`col_char(20)_key_signed` AS `f6` FROM `table_3_utf8_undef` IGNORE INDEX (`col_varchar(20)_key_signed`, `col_decimal(40, 20)_key_unsigned`)) AS `t1` JOIN (SELECT `col_char(20)_key_signed` AS `f8`,`col_float_key_signed` AS `f5`,`col_bigint_key_unsigned` AS `f9` FROM `table_7_utf8_undef` USE INDEX (`col_decimal(40, 20)_key_unsigned`)) AS `t2`) UNION ALL (SELECT (-`f10`) AS `f1`,(ORD(9) DIV `f12`+BINARY TAN(0.36102784154800843)) AS `f2`,(-COLLATION(9)) AS `f3` FROM (SELECT `col_bigint_key_signed` AS `f10`,`col_double_undef_signed` AS `f11`,`col_char(20)_key_signed` AS `f12` FROM `table_7_utf8_undef` USE INDEX (`col_double_key_signed`)) AS `t3`)) SELECT * FROM `MYWITH`;

the result:

+------+--------------------+------+

| f1   | f2                 | f3   |

+------+--------------------+------+

| a    | -0                 | NULL |

| a    | 3                  | NULL |

| a    | -1                 | NULL |

| a    | -0                 | NULL |

| a    | 3                  | NULL |

| a    | -1                 | NULL |

| a    | -0                 | NULL |

| a    | 3                  | NULL |

| a    | -1                 | NULL |

| a    | -0                 | NULL |

| a    | 3                  | NULL |

| a    | -1                 | NULL |

| a    | -0                 | NULL |

| a    | 3                  | NULL |

| a    | -1                 | NULL |

| a    | -0                 | NULL |

| a    | 3                  | NULL |

| a    | -1                 | NULL |

| a    | -0                 | NULL |

| a    | 3                  | NULL |

| a    | -1                 | NULL |

| -1   | 19.377576771444826 | -0   |

| -107 | -56.62242322855517 | -0   |

| 9    | NULL               | -0   |

| -1   | NULL               | -0   |

| 1    | 19.377576771444826 | -0   |

| 0    | 19.377576771444826 | -0   |

| -38  | NULL               | -0   |

+------+--------------------+------+

sql2

WITH `MYWITH` AS ((SELECT (_UTF8MB4'a') AS `f1`,(`f4`) AS `f2`,(DATE_ADD(DAYOFYEAR(_UTF8MB4'2015-02-11'), INTERVAL 1 YEAR)) AS `f3` FROM (SELECT `col_varchar(20)_undef_signed` AS `f4`,`col_float_undef_signed` AS `f7`,`col_char(20)_key_signed` AS `f6` FROM `table_3_utf8_undef` IGNORE INDEX (`col_varchar(20)_key_signed`, `col_decimal(40, 20)_key_unsigned`)) AS `t1` JOIN (SELECT `col_char(20)_key_signed` AS `f8`,`col_float_key_signed` AS `f5`,`col_bigint_key_unsigned` AS `f9` FROM `table_7_utf8_undef` USE INDEX (`col_decimal(40, 20)_key_unsigned`)) AS `t2`) UNION ALL (SELECT DISTINCT /* in this we add DISTINCT */ (-`f10`) AS `f1`,(ORD(9) DIV `f12`+BINARY TAN(0.36102784154800843)) AS `f2`,(-COLLATION(9)) AS `f3` FROM (SELECT `col_bigint_key_signed` AS `f10`,`col_double_undef_signed` AS `f11`,`col_char(20)_key_signed` AS `f12` FROM `table_7_utf8_undef` USE INDEX (`col_double_key_signed`)) AS `t3`)) SELECT * FROM `MYWITH`;

result :

{code:java}

+------+------+------+

| f1   | f2   | f3   |

+------+------+------+

| a    | -0   | NULL |

| a    | 3    | NULL |

| a    | -1   | NULL |

| a    | -0   | NULL |

| a    | 3    | NULL |

| a    | -1   | NULL |

| a    | -0   | NULL |

| a    | 3    | NULL |

| a    | -1   | NULL |

| a    | -0   | NULL |

| a    | 3    | NULL |

| a    | -1   | NULL |

| a    | -0   | NULL |

| a    | 3    | NULL |

| a    | -1   | NULL |

| a    | -0   | NULL |

| a    | 3    | NULL |

| a    | -1   | NULL |

| a    | -0   | NULL |

| a    | 3    | NULL |

| a    | -1   | NULL |

| -1   | 19   | -0   |

| -107 | -57  | -0   |

| 9    | NULL | -0   |

| -1   | NULL | -0   |

| 1    | 19   | -0   |

| 0    | 19   | -0   |

| -38  | NULL | -0   |

+------+------+------+

HOW TO REPEAT

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

');

WITH `MYWITH` AS (

    SELECT

      (_UTF8MB4'a') AS `f1`,

      (`f4`) AS `f2`,

      (DATE_ADD(DAYOFYEAR(_UTF8MB4'2015-02-11'), INTERVAL 1 YEAR)) AS `f3`

    FROM

        SELECT

          `col_varchar(20)_undef_signed` AS `f4`,

          `col_float_undef_signed` AS `f7`,

          `col_char(20)_key_signed` AS `f6`

        FROM `table_3_utf8_undef`

        IGNORE INDEX (`col_varchar(20)_key_signed`, `col_decimal(40, 20)_key_unsigned`)

      ) AS `t1`

      JOIN

        SELECT

          `col_char(20)_key_signed` AS `f8`,

          `col_float_key_signed` AS `f5`,

          `col_bigint_key_unsigned` AS `f9`

        FROM `table_7_utf8_undef`

        USE INDEX (`col_decimal(40, 20)_key_unsigned`)

      ) AS `t2`

  UNION ALL

    SELECT

      (-`f10`) AS `f1`,

      (ORD(9) DIV `f12` + BINARY TAN(0.36102784154800843)) AS `f2`,

      (-COLLATION(9)) AS `f3`

    FROM

        SELECT

          `col_bigint_key_signed` AS `f10`,

          `col_double_undef_signed` AS `f11`,

          `col_char(20)_key_signed` AS `f12`

        FROM `table_7_utf8_undef`

        USE INDEX (`col_double_key_signed`)

      ) AS `t3`

SELECT * FROM `MYWITH`;

+------+--------------------+------+

| f1   | f2                 | f3   |

+------+--------------------+------+

| a    | -0                 | NULL |

| a    | 3                  | NULL |

| a    | -1                 | NULL |

| a    | -0                 | NULL |

| a    | 3                  | NULL |

| a    | -1                 | NULL |

| a    | -0                 | NULL |

| a    | 3                  | NULL |

| a    | -1                 | NULL |

| a    | -0                 | NULL |

| a    | 3                  | NULL |

| a    | -1                 | NULL |

| a    | -0                 | NULL |

| a    | 3                  | NULL |

| a    | -1                 | NULL |

| a    | -0                 | NULL |

| a    | 3                  | NULL |

| a    | -1                 | NULL |

| a    | -0                 | NULL |

| a    | 3                  | NULL |

| a    | -1                 | NULL |

| -1   | 19.377576771444826 | -0   |

| -107 | -56.62242322855517 | -0   |

| 9    | NULL               | -0   |

| -1   | NULL               | -0   |

| 1    | 19.377576771444826 | -0   |

| 0    | 19.377576771444826 | -0   |

| -38  | NULL               | -0   |

+------+--------------------+------+

WITH `MYWITH` AS (

    SELECT

      (_UTF8MB4'a') AS `f1`,

      (`f4`) AS `f2`,

      (DATE_ADD(DAYOFYEAR(_UTF8MB4'2015-02-11'), INTERVAL 1 YEAR)) AS `f3`

    FROM

        SELECT

          `col_varchar(20)_undef_signed` AS `f4`,

          `col_float_undef_signed` AS `f7`,

          `col_char(20)_key_signed` AS `f6`

        FROM `table_3_utf8_undef`

        IGNORE INDEX (`col_varchar(20)_key_signed`, `col_decimal(40, 20)_key_unsigned`)

      ) AS `t1`

      JOIN

        SELECT

          `col_char(20)_key_signed` AS `f8`,

          `col_float_key_signed` AS `f5`,

          `col_bigint_key_unsigned` AS `f9`

        FROM `table_7_utf8_undef`

        USE INDEX (`col_decimal(40, 20)_key_unsigned`)

      ) AS `t2`

  UNION ALL

    SELECT DISTINCT

      (-`f10`) AS `f1`,

      (ORD(9) DIV `f12` + BINARY TAN(0.36102784154800843)) AS `f2`,

      (-COLLATION(9)) AS `f3`

    FROM

        SELECT

          `col_bigint_key_signed` AS `f10`,

          `col_double_undef_signed` AS `f11`,

          `col_char(20)_key_signed` AS `f12`

        FROM `table_7_utf8_undef`

        USE INDEX (`col_double_key_signed`)

      ) AS `t3`

SELECT * FROM `MYWITH`;

+------+------+------+

| f1   | f2   | f3   |

+------+------+------+

| a    | -0   | NULL |

| a    | 3    | NULL |

| a    | -1   | NULL |

| a    | -0   | NULL |

| a    | 3    | NULL |

| a    | -1   | NULL |

| a    | -0   | NULL |

| a    | 3    | NULL |

| a    | -1   | NULL |

| a    | -0   | NULL |

| a    | 3    | NULL |

| a    | -1   | NULL |

| a    | -0   | NULL |

| a    | 3    | NULL |

| a    | -1   | NULL |

| a    | -0   | NULL |

| a    | 3    | NULL |

| a    | -1   | NULL |

| a    | -0   | NULL |

| a    | 3    | NULL |

| a    | -1   | NULL |

| -1   | 19   | -0   |

| -107 | -57  | -0   |

| 9    | NULL | -0   |

| -1   | NULL | -0   |

| 1    | 19   | -0   |

| 0    | 19   | -0   |

| -38  | NULL | -0   |

+------+------+------+

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

Attachments

Activity

People

Assignee:: Unassigned

Reporter:: yll

Votes:: 0 Vote for this issue

Watchers:: 2 Start watching this issue

Dates

Created:: 2024-03-16 14:25

Updated:: 2024-03-16 15:25

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.