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

Wrong result (missing row) on a DISTINCT query with the same subquery in the SELECT list and GROUP BY

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 10.0.0, 5.5.28, 5.3.10, 5.2.12, 5.1.62
    • Fix Version/s: 10.0.1, 5.5.29, 5.3.12
    • Component/s: None
    • Labels:

      Description

      The following test case

      CREATE TABLE t1 (a INT) ENGINE=MyISAM;
      INSERT INTO t1 VALUES (0),(7);
       
      CREATE TABLE t2 (b INT) ENGINE=MyISAM;
      INSERT INTO t2 VALUES (7),(0),(3);
       
      CREATE ALGORITHM=TEMPTABLE VIEW v AS
      SELECT DISTINCT    
      ( SELECT MAX(a) FROM t1 WHERE alias.b = a ) AS field1 
      FROM t2 AS alias GROUP BY field1;
       
      SELECT * FROM v;

      returns two rows only:

      field1
      0
      7

      while the same query without the view returns 3 rows:

      field1
      NULL
      0
      7

      EXPLAIN with the default optimizer_switch:

      EXPLAIN EXTENDED
      SELECT * FROM v;
      id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
      1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	3	100.00	
      2	DERIVED	alias	ALL	NULL	NULL	NULL	NULL	3	100.00	Using temporary; Using filesort
      4	DEPENDENT SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
      3	DEPENDENT SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
      Warnings:
      Note	1276	Field or reference 'test.alias.b' of SELECT #3 was resolved in SELECT #2
      Note	1276	Field or reference 'test.alias.b' of SELECT #4 was resolved in SELECT #2
      Note	1003	select `v`.`field1` AS `field1` from `test`.`v`

      branch: maria/5.5
      bzr version-info
      revision-id: wlad@montyprogram.com-20121120142439-zvx42vxhc8lurmnv
      date: 2012-11-20 15:24:39 +0100
      revno: 3576

      Reproducible with the default optimizer_switch as well as with all OFF values, except for in_to_exists or materialization that have to be on to run the query.

      Also reproducible on all current versions of MariaDB, and on MySQL 5.1 and 5.5; but not reproducible on MySQL 5.6 revno 4458.

        Attachments

          Activity

            People

            • Assignee:
              serg Sergei Golubchik
              Reporter:
              elenst Elena Stepanova
            • Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: