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

LP:430669 - Wrong output of EXPLAIN EXTENDED on subquery with unknown column error

    XMLWordPrintable

    Details

      Description

      Seen in lp:maria in revision revid:<email address hidden> (after push of table elimination):

      CREATE TABLE t1 (a INT, b INT, c INT);
      INSERT INTO t1 VALUES (1,1,1), (1,1,1);
      EXPLAIN EXTENDED
      SELECT c FROM
      ( SELECT
      (SELECT COUNT(a) FROM
      (SELECT COUNT(b) FROM t1) AS x GROUP BY c
      ) FROM t1 GROUP BY b
      ) AS y;
      ERROR 42S22: Unknown column 'c' in 'field list'
      SHOW WARNINGS;
      Level Code Message
      Note 1276 Field or reference 'test.t1.a' of SELECT #3 was resolved in SELECT #2
      Note 1276 Field or reference 'test.t1.c' of SELECT #3 was resolved in SELECT #2
      Error 1054 Unknown column 'c' in 'field list'
      Note 1003 select `c` AS `c` from (select (select count(`test`.`t1`.`a`) AS `COUNT(a)` from (select count(`test`.`t1`.`b`) AS `COUNT(b)` from `test`.`t1`) `x` group by `t1`.`c`) AS `(SELECT COUNT(a) FROM
      (SELECT COUNT(b) FROM t1) AS x GROUP BY c
      )` from `test`.`t1` group by `test`.`t1`.`b`) `y`

      The problem here is this output "group by `t1`.`c`". The column `c` is unknown at that place in the query, so it makes no sense to qualify it with the table name t1.

      This is related to MySQL Bug#37362 (http://bugs.mysql.com/bug.php?id=37362), from which the test case originates.

      Before push of table elimination, the test case crashes the server, so the table elimination push fixes the crash, but produces wrong/strange output.

      I will push the test case into lp:maria with wrong/strange result file (to not block the merge of MySQL 5.1.38). This needs to be updated after fixing the bug:

      === modified file 'mysql-test/r/subselect3.result'
      — mysql-test/r/subselect3.result 2009-08-13 20:33:00 +0000
      +++ mysql-test/r/subselect3.result 2009-09-16 11:27:55 +0000
      @@ -864,7 +864,7 @@ Level Code Message
      Note 1276 Field or reference 'test.t1.a' of SELECT #3 was resolved in SELECT #2
      Note 1276 Field or reference 'test.t1.c' of SELECT #3 was resolved in SELECT #2
      Error 1054 Unknown column 'c' in 'field list'
      +Note 1003 select `c` AS `c` from (select (select count(`test`.`t1`.`a`) AS `COUNT(a)` from (select count(`test`.`t1`.`b`) AS `COUNT(b)` from `test`.`t1`) `x` group by `t1`.`c`) AS `(SELECT COUNT(a) FROM
      -Note 1003 select `c` AS `c` from (select (select count(`test`.`t1`.`a`) AS `COUNT(a)` from (select count(`test`.`t1`.`b`) AS `COUNT(b)` from `test`.`t1`) `x` group by `c`) AS `(SELECT COUNT(a) FROM
      (SELECT COUNT(b) FROM t1) AS x GROUP BY c
      )` from `test`.`t1` group by `test`.`t1`.`b`) `y`
      DROP TABLE t1;

        Attachments

          Activity

            People

            Assignee:
            psergey Sergei Petrunia
            Reporter:
            knielsen Kristian Nielsen
            Votes:
            0 Vote for this issue
            Watchers:
            0 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved: