group by in subquery returns wrong result

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)