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

Wrong result upon materialized subquery with aggregate functions

    XMLWordPrintable

Details

    • Bug
    • Status: Open (View Workflow)
    • Major
    • Resolution: Unresolved
    • 10.3(EOL), 10.4(EOL), 10.5, 10.6, 10.7(EOL), 10.8(EOL), 10.9(EOL), 10.10(EOL), 10.11
    • 10.5, 10.6
    • Optimizer

    Description

      CREATE TABLE t1 (a INT);
      INSERT INTO t1 VALUES (8),(9);
       
      CREATE TABLE t2 (b INT);
      INSERT INTO t2 VALUES (1),(2);
       
      CREATE TABLE t3 (c INT, d DATE);
      INSERT INTO t3 VALUES (2,'2022-12-12'),(3,'2023-12-12');
       
      SELECT c, (SELECT MIN(a) FROM t1 WHERE t3.c IN (SELECT b FROM t2)) AS f1, GROUP_CONCAT(d) AS f2 FROM t3 GROUP BY c, f1 ORDER BY f2;
      

      10.3 0ca3aaa7

      SELECT c, (SELECT MIN(a) FROM t1 WHERE t3.c IN (SELECT b FROM t2)) AS f1, GROUP_CONCAT(d) AS f2 FROM t3 GROUP BY c, f1 ORDER BY f2;
      c	f1	f2
      2	NULL	2022-12-12
      3	NULL	2023-12-12
      

      EXPLAIN EXTENDED SELECT c, (SELECT MIN(a) FROM t1 WHERE t3.c IN (SELECT b FROM t2)) AS f1, GROUP_CONCAT(d) AS f2 FROM t3 GROUP BY c, f1 ORDER BY f2;
      id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
      1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	100.00	Using temporary; Using filesort
      2	DEPENDENT SUBQUERY	<subquery3>	eq_ref	distinct_key	distinct_key	4	func	1	100.00	
      2	DEPENDENT SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	Using join buffer (flat, BNL join)
      3	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	2	100.00	
      Warnings:
      Note	1276	Field or reference 'test.t3.c' of SELECT #2 was resolved in SELECT #1
      Note	1003	/* select#1 */ select `test`.`t3`.`c` AS `c`,<expr_cache><`test`.`t3`.`c`>((/* select#2 */ select min(`test`.`t1`.`a`) from `test`.`t1` semi join (`test`.`t2`) where `test`.`t3`.`c` = `test`.`t2`.`b`)) AS `f1`,group_concat(`test`.`t3`.`d` separator ',') AS `f2` from `test`.`t3` group by `test`.`t3`.`c`,<expr_cache><`test`.`t3`.`c`>((/* select#2 */ select min(`test`.`t1`.`a`) from `test`.`t1` semi join (`test`.`t2`) where `test`.`t3`.`c` = `test`.`t2`.`b`)) order by group_concat(`test`.`t3`.`d` separator ',')
      

      Expected result is

      c	f1	f2
      2	8	2022-12-12
      3	NULL	2023-12-12
      

      On 10.3 it can be achieved with materialization=off.

      Reproducible with at least MyISAM and InnoDB.
      Reproducible on 5.5 (and materialization=off doesn't seem to help there).
      Not reproducible on MySQL (5.6, 5.7, 8.0).

      On bb-11.0 (a.k.a improved selectivity tree MDEV-26974) the plan with default settings is different and the result is correct, but the wrong one can still be triggered with firstmatch=off.

      Attachments

        Activity

          People

            psergei Sergei Petrunia
            elenst Elena Stepanova
            Votes:
            0 Vote for this issue
            Watchers:
            1 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.