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

group by in subquery returns wrong result

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Duplicate
    • Affects Version/s: 10.5.10
    • Fix Version/s: N/A
    • Component/s: Optimizer
    • Labels:
      None
    • Environment:
      CentOS, Amazon EC2

      Description

      Depending on the existence of "default null" on column definition, it leads to different query result.

      • Case 1

        create table AN_185324_T1 (CHANGE_REQUEST_ID int(11) NOT NULL, GLOBAL_DOC_ID int(11) DEFAULT NULL, PRIMARY KEY (CHANGE_REQUEST_ID)) ENGINE=InnoDB DEFAULT CHARSET=utf8;
        create table AN_185324_T2 (DOC_ID int(11) {color:red}DEFAULT NULL{color}, LLE_ID int(11) DEFAULT NULL, KEY (LLE_ID), KEY(DOC_ID)) ENGINE=InnoDB DEFAULT CHARSET=utf8;
         
        insert into AN_185324_T1 VALUES (1,2155),(2,2155);
        insert into AN_185324_T2 VALUES (2154,NULL),(2155,NULL);
         
        MariaDB [CS0312790]> select t1.CHANGE_REQUEST_ID FROM ( AN_185324_T1 t1 join (select * FROM AN_185324_T2 WHERE LLE_ID IS NULL and DOC_ID IS NOT NULL GROUP BY DOC_ID) t2 ON (t1.GLOBAL_DOC_ID = t2.DOC_ID)) WHERE t1.CHANGE_REQUEST_ID=1;
        Empty set (0.001 sec)
         
        MariaDB [CS0312790]> SELECT DOC_ID FROM AN_185324_T2 WHERE LLE_ID IS NULL AND DOC_ID IS NOT NULL GROUP BY DOC_ID;
        +--------+
        | DOC_ID |
        +--------+
        |   2154 |
        |   2155 |
        +--------+
        2 rows in set (0.000 sec)
         
        Once replacing group by with select distinct, it returns OK.
        MariaDB [CS0312790]> select t1.CHANGE_REQUEST_ID FROM ( AN_185324_T1 t1 join (select distinct DOC_ID FROM AN_185324_T2 WHERE LLE_ID IS NULL and DOC_ID IS NOT NULL) t2 ON (t1.GLOBAL_DOC_ID = t2.DOC_ID)) WHERE t1.CHANGE_REQUEST_ID=1;
        +-------------------+
        | CHANGE_REQUEST_ID |
        +-------------------+
        |                 1 |
        +-------------------+
        1 row in set (0.000 sec)
        

      • sql_mode=NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
      • optimizer_switch = index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=off,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=on,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,condition_pushdown_for_subquery=on,rowid_filter=on,condition_pushdown_from_having=on,not_null_range_scan=off
      • Case 2 - removing `default null` and make DOC_ID column into not null, it returns the right result as well.

      CREATE TABLE AN_185324_T2 (DOC_ID INT NOT NULL, LLE_ID INT DEFAULT NULL, KEY (LLE_ID), KEY(DOC_ID)) ENGINE=INNODB DEFAULT CHARSET=utf8;
       
      MariaDB [CS0312790]> select t1.CHANGE_REQUEST_ID FROM ( AN_185324_T1 t1 join (select * FROM AN_185324_T2 WHERE LLE_ID IS NULL and DOC_ID IS NOT NULL GROUP BY DOC_ID) t2 ON (t1.GLOBAL_DOC_ID = t2.DOC_ID)) WHERE t1.CHANGE_REQUEST_ID=1;
      +-------------------+
      | CHANGE_REQUEST_ID |
      +-------------------+
      |                 1 |
      +-------------------+
      1 row in set (0.001 sec)
      

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              Unassigned
              Reporter:
              allen.lee@mariadb.com Allen Lee
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved:

                  Git Integration