Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-33685

Unexpected values appeared in select query statements that should logically imply each other

    XMLWordPrintable

Details

    • Bug
    • Status: Open (View Workflow)
    • Major
    • Resolution: Unresolved
    • 11.3.2
    • None
    • Optimizer
    • None
    • ubuntu 20.04

    Description

      description

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

      however,the value -7342912915181579000 change to -100000000000000000 after adding DISTINCT,which seems like a logical bug

      sql1

       
      WITH `MYWITH` 
      AS (SELECT 
                (~`f6`) AS `f1`,(!COLLATION(`f5`)+BINARY CEILING(-7342912915181579179)) AS `f2`,(DATE_ADD(LOG(0.8294980637211848), INTERVAL 1 MINUTE_SECOND)) AS `f3` 
          FROM (SELECT 
                     `col_bigint_undef_unsigned` AS `f7`,`col_decimal(40, 20)_key_signed` AS `f5`,`col_decimal(40, 20)_key_signed` AS `f8` 
                FROM `table_7_utf8_undef` 
                IGNORE INDEX (`col_float_key_signed`, `col_double_key_unsigned`)) 
          AS `t1` 
          NATURAL JOIN 
                (SELECT (~COLLATION(`f11`)) AS `f4`,(DATE_SUB(`f12`, INTERVAL 1 HOUR_MICROSECOND)) AS `f9`,(`f12`) AS `f6` 
                FROM (SELECT `col_float_key_unsigned` AS `f10`,`col_float_undef_unsigned` AS `f11`,`col_bigint_key_signed` AS `f12` 
                     FROM `table_7_utf8_undef` 
                     FORCE INDEX (`col_bigint_key_unsigned`))
                AS `t2` 
                ORDER BY `f12`) 
          AS `t3`) 
      SELECT * FROM `MYWITH`;
      
      

      the result:

      +----------------------+----------------------+------+
      | f1                   | f2                   | f3   |
      +----------------------+----------------------+------+
      | 18446744073709551614 | -7342912915181579000 | NULL |
      | 18446744073709551614 | -7342912915181579000 | NULL |
      | 18446744073709551614 | -7342912915181579000 | NULL |
      | 18446744073709551614 | -7342912915181579000 | NULL |
      | 18446744073709551614 | -7342912915181579000 | NULL |
      | 18446744073709551614 | -7342912915181579000 | NULL |
      | 18446744073709551614 | -7342912915181579000 | NULL |
      | 18446744073709551508 | -7342912915181579000 | NULL |
      | 18446744073709551508 | -7342912915181579000 | NULL |
      | 18446744073709551508 | -7342912915181579000 | NULL |
      | 18446744073709551508 | -7342912915181579000 | NULL |
      | 18446744073709551508 | -7342912915181579000 | NULL |
      | 18446744073709551508 | -7342912915181579000 | NULL |
      | 18446744073709551508 | -7342912915181579000 | NULL |
      |                    8 | -7342912915181579000 | NULL |
      |                    8 | -7342912915181579000 | NULL |
      |                    8 | -7342912915181579000 | NULL |
      |                    8 | -7342912915181579000 | NULL |
      |                    8 | -7342912915181579000 | NULL |
      |                    8 | -7342912915181579000 | NULL |
      |                    8 | -7342912915181579000 | NULL |
      | 18446744073709551614 | -7342912915181579000 | NULL |
      | 18446744073709551614 | -7342912915181579000 | NULL |
      | 18446744073709551614 | -7342912915181579000 | NULL |
      | 18446744073709551614 | -7342912915181579000 | NULL |
      | 18446744073709551614 | -7342912915181579000 | NULL |
      | 18446744073709551614 | -7342912915181579000 | NULL |
      | 18446744073709551614 | -7342912915181579000 | NULL |
      |                    0 | -7342912915181579000 | NULL |
      |                    0 | -7342912915181579000 | NULL |
      |                    0 | -7342912915181579000 | NULL |
      |                    0 | -7342912915181579000 | NULL |
      |                    0 | -7342912915181579000 | NULL |
      |                    0 | -7342912915181579000 | NULL |
      |                    0 | -7342912915181579000 | NULL |
      | 18446744073709551615 | -7342912915181579000 | NULL |
      | 18446744073709551615 | -7342912915181579000 | NULL |
      | 18446744073709551615 | -7342912915181579000 | NULL |
      | 18446744073709551615 | -7342912915181579000 | NULL |
      | 18446744073709551615 | -7342912915181579000 | NULL |
      | 18446744073709551615 | -7342912915181579000 | NULL |
      | 18446744073709551615 | -7342912915181579000 | NULL |
      | 18446744073709551577 | -7342912915181579000 | NULL |
      | 18446744073709551577 | -7342912915181579000 | NULL |
      | 18446744073709551577 | -7342912915181579000 | NULL |
      | 18446744073709551577 | -7342912915181579000 | NULL |
      | 18446744073709551577 | -7342912915181579000 | NULL |
      | 18446744073709551577 | -7342912915181579000 | NULL |
      | 18446744073709551577 | -7342912915181579000 | NULL |
      +----------------------+----------------------+------+
      

      sql2

      WITH `MYWITH` 
      AS (SELECT DISTINCT /*add  DISTINCT in this */
                (~`f6`) AS `f1`,(!COLLATION(`f5`)+BINARY CEILING(-7342912915181579179)) AS `f2`,(DATE_ADD(LOG(0.8294980637211848), INTERVAL 1 MINUTE_SECOND)) AS `f3` 
          FROM (SELECT 
                     `col_bigint_undef_unsigned` AS `f7`,`col_decimal(40, 20)_key_signed` AS `f5`,`col_decimal(40, 20)_key_signed` AS `f8` 
                FROM `table_7_utf8_undef` 
                IGNORE INDEX (`col_float_key_signed`, `col_double_key_unsigned`)) 
          AS `t1` 
          NATURAL JOIN 
                (SELECT (~COLLATION(`f11`)) AS `f4`,(DATE_SUB(`f12`, INTERVAL 1 HOUR_MICROSECOND)) AS `f9`,(`f12`) AS `f6` 
                FROM (SELECT `col_float_key_unsigned` AS `f10`,`col_float_undef_unsigned` AS `f11`,`col_bigint_key_signed` AS `f12` 
                     FROM `table_7_utf8_undef` 
                     FORCE INDEX (`col_bigint_key_unsigned`))
                AS `t2` 
                ORDER BY `f12`) 
          AS `t3`) 
      SELECT * FROM `MYWITH`;
      

      result :

      +----------------------+---------------------+------+
      | f1                   | f2                  | f3   |
      +----------------------+---------------------+------+
      | 18446744073709551614 | -100000000000000000 | NULL |
      | 18446744073709551508 | -100000000000000000 | NULL |
      |                    8 | -100000000000000000 | NULL |
      |                    0 | -100000000000000000 | NULL |
      | 18446744073709551615 | -100000000000000000 | NULL |
      | 18446744073709551577 | -100000000000000000 | NULL |
      +----------------------+---------------------+------+
      

      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 
                (~`f6`) AS `f1`,(!COLLATION(`f5`)+BINARY CEILING(-7342912915181579179)) AS `f2`,(DATE_ADD(LOG(0.8294980637211848), INTERVAL 1 MINUTE_SECOND)) AS `f3` 
          FROM (SELECT 
                     `col_bigint_undef_unsigned` AS `f7`,`col_decimal(40, 20)_key_signed` AS `f5`,`col_decimal(40, 20)_key_signed` AS `f8` 
                FROM `table_7_utf8_undef` 
                IGNORE INDEX (`col_float_key_signed`, `col_double_key_unsigned`)) 
          AS `t1` 
          NATURAL JOIN 
                (SELECT (~COLLATION(`f11`)) AS `f4`,(DATE_SUB(`f12`, INTERVAL 1 HOUR_MICROSECOND)) AS `f9`,(`f12`) AS `f6` 
                FROM (SELECT `col_float_key_unsigned` AS `f10`,`col_float_undef_unsigned` AS `f11`,`col_bigint_key_signed` AS `f12` 
                     FROM `table_7_utf8_undef` 
                     FORCE INDEX (`col_bigint_key_unsigned`))
                AS `t2` 
                ORDER BY `f12`) 
          AS `t3`) 
      SELECT * FROM `MYWITH`;
      
      

       
      +----------------------+----------------------+------+
      | f1                   | f2                   | f3   |
      +----------------------+----------------------+------+
      | 18446744073709551614 | -7342912915181579000 | NULL |
      | 18446744073709551614 | -7342912915181579000 | NULL |
      | 18446744073709551614 | -7342912915181579000 | NULL |
      | 18446744073709551614 | -7342912915181579000 | NULL |
      | 18446744073709551614 | -7342912915181579000 | NULL |
      | 18446744073709551614 | -7342912915181579000 | NULL |
      | 18446744073709551614 | -7342912915181579000 | NULL |
      | 18446744073709551508 | -7342912915181579000 | NULL |
      | 18446744073709551508 | -7342912915181579000 | NULL |
      | 18446744073709551508 | -7342912915181579000 | NULL |
      | 18446744073709551508 | -7342912915181579000 | NULL |
      | 18446744073709551508 | -7342912915181579000 | NULL |
      | 18446744073709551508 | -7342912915181579000 | NULL |
      | 18446744073709551508 | -7342912915181579000 | NULL |
      |                    8 | -7342912915181579000 | NULL |
      |                    8 | -7342912915181579000 | NULL |
      |                    8 | -7342912915181579000 | NULL |
      |                    8 | -7342912915181579000 | NULL |
      |                    8 | -7342912915181579000 | NULL |
      |                    8 | -7342912915181579000 | NULL |
      |                    8 | -7342912915181579000 | NULL |
      | 18446744073709551614 | -7342912915181579000 | NULL |
      | 18446744073709551614 | -7342912915181579000 | NULL |
      | 18446744073709551614 | -7342912915181579000 | NULL |
      | 18446744073709551614 | -7342912915181579000 | NULL |
      | 18446744073709551614 | -7342912915181579000 | NULL |
      | 18446744073709551614 | -7342912915181579000 | NULL |
      | 18446744073709551614 | -7342912915181579000 | NULL |
      |                    0 | -7342912915181579000 | NULL |
      |                    0 | -7342912915181579000 | NULL |
      |                    0 | -7342912915181579000 | NULL |
      |                    0 | -7342912915181579000 | NULL |
      |                    0 | -7342912915181579000 | NULL |
      |                    0 | -7342912915181579000 | NULL |
      |                    0 | -7342912915181579000 | NULL |
      | 18446744073709551615 | -7342912915181579000 | NULL |
      | 18446744073709551615 | -7342912915181579000 | NULL |
      | 18446744073709551615 | -7342912915181579000 | NULL |
      | 18446744073709551615 | -7342912915181579000 | NULL |
      | 18446744073709551615 | -7342912915181579000 | NULL |
      | 18446744073709551615 | -7342912915181579000 | NULL |
      | 18446744073709551615 | -7342912915181579000 | NULL |
      | 18446744073709551577 | -7342912915181579000 | NULL |
      | 18446744073709551577 | -7342912915181579000 | NULL |
      | 18446744073709551577 | -7342912915181579000 | NULL |
      | 18446744073709551577 | -7342912915181579000 | NULL |
      | 18446744073709551577 | -7342912915181579000 | NULL |
      | 18446744073709551577 | -7342912915181579000 | NULL |
      | 18446744073709551577 | -7342912915181579000 | NULL |
      +----------------------+----------------------+------+
      
      

      WITH `MYWITH` 
      AS (SELECT DISTINCT
                (~`f6`) AS `f1`,(!COLLATION(`f5`)+BINARY CEILING(-7342912915181579179)) AS `f2`,(DATE_ADD(LOG(0.8294980637211848), INTERVAL 1 MINUTE_SECOND)) AS `f3` 
          FROM (SELECT 
                     `col_bigint_undef_unsigned` AS `f7`,`col_decimal(40, 20)_key_signed` AS `f5`,`col_decimal(40, 20)_key_signed` AS `f8` 
                FROM `table_7_utf8_undef` 
                IGNORE INDEX (`col_float_key_signed`, `col_double_key_unsigned`)) 
          AS `t1` 
          NATURAL JOIN 
                (SELECT (~COLLATION(`f11`)) AS `f4`,(DATE_SUB(`f12`, INTERVAL 1 HOUR_MICROSECOND)) AS `f9`,(`f12`) AS `f6` 
                FROM (SELECT `col_float_key_unsigned` AS `f10`,`col_float_undef_unsigned` AS `f11`,`col_bigint_key_signed` AS `f12` 
                     FROM `table_7_utf8_undef` 
                     FORCE INDEX (`col_bigint_key_unsigned`))
                AS `t2` 
                ORDER BY `f12`) 
          AS `t3`) 
      SELECT * FROM `MYWITH`;
      

       
      {code:java}
      +----------------------+---------------------+------+
      | f1                   | f2                  | f3   |
      +----------------------+---------------------+------+
      | 18446744073709551614 | -100000000000000000 | NULL |
      | 18446744073709551508 | -100000000000000000 | NULL |
      |                    8 | -100000000000000000 | NULL |
      |                    0 | -100000000000000000 | NULL |
      | 18446744073709551615 | -100000000000000000 | NULL |
      | 18446744073709551577 | -100000000000000000 | NULL |
      +----------------------+---------------------+------+
      

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

      Attachments

        Activity

          People

            Unassigned Unassigned
            big mother yll
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

              Created:
              Updated:

              Git Integration

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