Bug #47371 reference by same column name

• Submitted: 16 Sep 2009 12:33
• Modified: 12 Mar 2010 17:30
• Reporter: Sebastian Ropek
• Status: Closed
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• Version: 5.0.84, 5.0.87-bzr, 5.1.40-bzr
• OS: Any
• Assigned to: Sergei Glukhov
• CPU Architecture: Any

[16 Sep 2009 12:33] Sebastian Ropek

Description:
This query crash mysql instance. Table t2 have column `foo`, same as first select reference `foo`;

How to repeat:
SELECT  MIN( z.t2 ) AS foo,
@bar:=(
  SELECT COUNT(w.t3)
  FROM t3 w
  WHERE   w.t3  > foo
)
FROM t1 q, t2 z
WHERE q.id = z.id
ORDER BY z.id ASC

CREATE TABLE `t1` (
  `id` int(11) default NULL,
  `t1` int(11) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin2;

CREATE TABLE `t2` (
  `id` int(11) default NULL,
  `t2` int(11) default NULL,
  `foo` int(11) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin2;

CREATE TABLE `t3` (
  `id` int(11) default NULL,
  `t3` int(11) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin2;

[16 Sep 2009 12:45] Valeriy Kravchuk

Please, try to repeat with a newer version, 5.0.85. I've got no crash with it:

C:\Program Files\MySQL\MySQL Server 5.1\bin>mysql -uroot -proot -P3308 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 5.0.85-community-nt MySQL Community Edition (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> drop table t1;
Query OK, 0 rows affected (0.17 sec)

mysql> drop table t2;
Query OK, 0 rows affected (0.48 sec)

mysql> drop table t3;
ERROR 1051 (42S02): Unknown table 't3'
mysql> CREATE TABLE `t1` (
    ->   `id` int(11) default NULL,
    ->   `t1` int(11) default NULL
    -> ) ENGINE=MyISAM DEFAULT CHARSET=latin2;
Query OK, 0 rows affected (0.13 sec)

mysql>
mysql> CREATE TABLE `t2` (
    ->   `id` int(11) default NULL,
    ->   `t2` int(11) default NULL,
    ->   `foo` int(11) default NULL
    -> ) ENGINE=MyISAM DEFAULT CHARSET=latin2;
Query OK, 0 rows affected (0.06 sec)

mysql>
mysql> CREATE TABLE `t3` (
    ->   `id` int(11) default NULL,
    ->   `t3` int(11) default NULL
    -> ) ENGINE=MyISAM DEFAULT CHARSET=latin2;
Query OK, 0 rows affected (0.06 sec)

mysql> SELECT  MIN( z.t2 ) AS foo,
    -> @bar:=(
    ->   SELECT COUNT(w.t3)
    ->   FROM t3 w
    ->   WHERE   w.t3  > foo
    -> )
    -> FROM t1 q, t2 z
    -> WHERE q.id = z.id
    -> ORDER BY z.id ASC;
+------+------------------------------------------------------------------+
| foo  | @bar:=(
  SELECT COUNT(w.t3)
  FROM t3 w
  WHERE   w.t3  > foo
) |
+------+------------------------------------------------------------------+
| NULL |                                                                0 |
+------+------------------------------------------------------------------+
1 row in set (0.11 sec)

[16 Sep 2009 12:58] Sebastian Ropek

Try insert some data.

mysql> insert into t2 values(10, 10, 10);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t1 values(10, 10);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t3 values(10, 10);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t3 values(10, 10);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t1 values(10, 10);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t2 values(10, 10, 10);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT  MIN( z.t2 ) AS foo, @bar:=(   SELECT COUNT(w.t3)   FROM t3 w   WHERE   w.t3  > foo ) FROM t1 q, t2 z WHERE q.id = z.id ORDER BY z.id ASC;
ERROR 2013 (HY000): Lost connection to MySQL server during query

[16 Sep 2009 17:20] Valeriy Kravchuk

Verified just as described (after adding some data) with recent 5.0.87 from bzr on Mac OS X:

mysql> CREATE TABLE `t1` (
    ->   `id` int(11) default NULL,
    ->   `t1` int(11) default NULL
    -> ) ENGINE=MyISAM DEFAULT CHARSET=latin2;
Query OK, 0 rows affected (0.00 sec)

mysql> 
mysql> CREATE TABLE `t2` (
    ->   `id` int(11) default NULL,
    ->   `t2` int(11) default NULL,
    ->   `foo` int(11) default NULL
    -> ) ENGINE=MyISAM DEFAULT CHARSET=latin2;
Query OK, 0 rows affected (0.00 sec)

mysql> 
mysql> CREATE TABLE `t3` (
    ->   `id` int(11) default NULL,
    ->   `t3` int(11) default NULL
    -> ) ENGINE=MyISAM DEFAULT CHARSET=latin2;
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t2 values(10, 10, 10);
Query OK, 1 row affected (0.00 sec)

mysql>  insert into t1 values(10, 10);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t3 values(10, 10);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t3 values(10, 10);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t2 values(10, 10, 10);
Query OK, 1 row affected (0.00 sec)

mysql>  insert into t1 values(10, 10);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT  MIN( z.t2 ) AS foo, @bar:=(   SELECT COUNT(w.t3)   FROM t3 w   WHERE   w.t3
    ->  > foo ) FROM t1 q, t2 z WHERE q.id = z.id ORDER BY z.id ASC;
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql> 090916 20:14:27  mysqld restarted

[16 Sep 2009 17:48] Valeriy Kravchuk

Same crash with recent 5.1.40 from bzr. Stack trace is:

0   mysqld                              0x0056ee9e my_print_stacktrace + 45
1   mysqld                              0x000f92de handle_segfault + 888
2   libSystem.B.dylib                   0x940472bb _sigtramp + 43
3   ???                                 0xffffffff 0x0 + 4294967295
4   mysqld                              0x001925c2 _ZN4JOIN5clearEv + 14
5   mysqld                              0x0019b653 _Z11setup_orderP3THDPP4ItemP10TABLE_LISTR4ListIS1_ES8_P8st_order + 1705
6   mysqld                              0x0018dfdb _Z10sub_selectP4JOINP13st_join_tableb + 65
7   mysqld                              0x0018e512 _Z10sub_selectP4JOINP13st_join_tableb + 1400
8   mysqld                              0x001a6ae3 _ZN4JOIN4execEv + 9077
9   mysqld                              0x000aacd6 _ZN30subselect_single_select_engine4execEv + 1304
10  mysqld                              0x000a51ae _ZN14Item_subselect4execEv + 96
11  mysqld                              0x000a605e _ZN24Item_singlerow_subselect7val_intEv + 94
12  mysqld                              0x00053d81 _ZN22Item_func_set_user_var5checkEb + 343
13  mysqld                              0x000543fd _ZN22Item_func_set_user_var7val_intEv + 93
14  mysqld                              0x0002309c _ZN13Item_copy_int4copyEv + 30
15  mysqld                              0x00192591 _Z11copy_fieldsP15TMP_TABLE_PARAM + 97
16  mysqld                              0x0019b95c _Z11setup_orderP3THDPP4ItemP10TABLE_LISTR4ListIS1_ES8_P8st_order + 2482
17  mysqld                              0x0018dd13 _Z21setup_end_select_funcP4JOIN + 1361
18  mysqld                              0x0018e0bc _Z10sub_selectP4JOINP13st_join_tableb + 290
19  mysqld                              0x0018e4e9 _Z10sub_selectP4JOINP13st_join_tableb + 1359
20  mysqld                              0x001a6ae3 _ZN4JOIN4execEv + 9077
21  mysqld                              0x001a6f7f _Z12mysql_selectP3THDPPP4ItemP10TABLE_LISTjR4ListIS1_ES2_jP8st_orderSB_S2_SB_yP13select_resultP18st_select_lex_unitP13st_select_lex + 867
22  mysqld                              0x001a7303 _Z13handle_selectP3THDP6st_lexP13select_resultm + 559
23  mysqld                              0x0010afc1 _Z20prepare_schema_tableP3THDP6st_lexP11Table_ident18enum_schema_tables + 2099
24  mysqld                              0x00111a00 _Z21mysql_execute_commandP3THD + 2890
25  mysqld                              0x0011ba8b _Z11mysql_parseP3THDPKcjPS2_ + 581
26  mysqld                              0x0011c816 _Z16dispatch_command19enum_server_commandP3THDPcj + 3030
27  mysqld                              0x0011db2b _Z10do_commandP3THD + 655
28  mysqld                              0x00108c27 handle_one_connection + 385
29  libSystem.B.dylib                   0x9400c095 _pthread_start + 321
30  libSystem.B.dylib                   0x9400bf52 thread_start + 34

EXPLAIN results:

mysql> explain SELECT  MIN( z.t2 ) AS foo, @bar:=(   SELECT COUNT(w.t3)   FROM t3 w   WHERE   w.t3  > foo ) FROM t1 q, t2 z WHERE q.id = z.id ORDER BY z.id ASC\G
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: q
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 2
        Extra: Using temporary
*************************** 2. row ***************************
           id: 1
  select_type: PRIMARY
        table: z
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 2
        Extra: Using where; Using join buffer
*************************** 3. row ***************************
           id: 2
  select_type: DEPENDENT SUBQUERY
        table: w
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 2
        Extra: Using where
3 rows in set (0.00 sec)

[7 Oct 2009 8:02] Bugs System

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/85993

2819 Sergey Glukhov	2009-10-07
      Bug#47371 reference by same column name
      On field resolving stage 'foo' field in subquery is resolved as
      t2.foo field. So the test subquery can be transformed into
      '... FROM t3 WHERE t3.f2 > t2.foo...'.
      At the end of execution top level join execution
      we cleanup this join with true argument.
      It leads to underlying join cleanup(subquery) with true argument too
      and to tmp_table_param->field array cleanup which is required later.
      The fix is to call cleanup_all_joins() for with 'true' argument only if
      subquery->is_uncacheable is false.
     @ mysql-test/r/user_var.result
        test result
     @ mysql-test/t/user_var.test
        test case
     @ sql/sql_select.cc
        On field resolving stage 'foo' field in subquery is resolved as
        t2.foo field. So the test subquery can be transformed into
        '... FROM t3 WHERE t3.f2 > t2.foo...'.
        At the end of execution top level join execution
        we cleanup this join with true argument.
        It leads to underlying join cleanup(subquery) with true argument too
        and to tmp_table_param->field array cleanup which is required later.
        The fix is to call cleanup_all_joins() for with 'true' argument only if
        subquery->is_uncacheable is false.

[13 Oct 2009 8:16] Bugs System

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/86652

2819 Sergey Glukhov	2009-10-13
      Bug#47371 reference by same column name
      At the end of execution top level join execution
      we cleanup this join with true argument
      (this join is not used anymore as result set is based on temporary table 
       in case of SELECT MIN() ... ORDER BY ...).
      It leads to underlying join cleanup(subquery) with true argument too
      and to tmp_table_param->field array cleanup for subquery join 
      which is required later(we need to execute subquery for each row in result set).
      The fix is to clear ubderlying subquery joins with 'true' argument(full cleanup)
      only if subquery is not uncacheable.
     @ mysql-test/r/user_var.result
        test result
     @ mysql-test/t/user_var.test
        test case
     @ sql/sql_select.cc
        At the end of execution top level join execution
        we cleanup this join with true argument
        (this join is not used anymore as result set is based on temporary table 
         in case of SELECT MIN() ... ORDER BY ...).
        It leads to underlying join cleanup(subquery) with true argument too
        and to tmp_table_param->field array cleanup for subquery join 
        which is required later(we need to execute subquery for each row in result set).
        The fix is to clear ubderlying subquery joins with 'true' argument(full cleanup)
        only if subquery is not uncacheable.

[13 Oct 2009 9:30] Bugs System

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/86679

2819 Sergey Glukhov	2009-10-13
      Bug#47371 reference by same column name
      At the end of execution top level join execution
      we do full cleanup for this join. 
      It leads to underlying join full cleanup(subquery) too
      as we don't take into account 'uncacheable' flag for
      subquery joins. It may cause a crash in case when
      we need to evaluate subquery later.
      The fix is to perform full cleanup for underlying subquery joins
      only if subquery is not uncacheable.
     @ mysql-test/r/user_var.result
        test result
     @ mysql-test/t/user_var.test
        test case
     @ sql/mysql_priv.h
        reverted fix for bug#37460 as unnecessary
     @ sql/sql_select.cc
        At the end of execution top level join execution
        we do full cleanup for this join. 
        It leads to underlying join full cleanup(subquery) too
        as we don't take into account 'uncacheable' flag for
        subquery joins. It may cause a crash in case when
        we need to evaluate subquery later.
        The fix is to perform full cleanup for underlying subquery joins
        only if subquery is not uncacheable.
     @ sql/sql_update.cc
        reverted fix for bug#37460 as unnecessary

[1 Dec 2009 17:19] Sergei Golubchik

Another test case (use the data from the beginning of count_distinct.test):

select ct2.isbn,city,concat('>', ct1.libname),count(distinct ct1.libname) as a from ct3 left join ct1 on ct3.libname=ct1.libname left join ct2 on ct3.isbn=ct2.isbn group by city having count(distinct ct1.libname) > 1;

select ct2.isbn,city,@bar:=concat('>', ct1.libname),count(distinct ct1.libname) as a from ct3 left join ct1 on ct3.libname=ct1.libname left join ct2 on ct3.isbn=ct2.isbn group by city having count(distinct ct1.libname) > 1;

select ct2.isbn,city,left(@bar:=concat('>', ct1.libname), 100),count(distinct ct1.libname) as a from ct3 left join ct1 on ct3.libname=ct1.libname left join ct2 on ct3.isbn=ct2.isbn group by city having count(distinct ct1.libname) > 1;

[8 Dec 2009 8:43] Bugs System

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/93121

2819 Sergey Glukhov	2009-12-08
      Bug#47371 reference by same column name
      At the end of execution top level join execution
      we cleanup this join with true argument.
      It leads to underlying join cleanup(subquery) with true argument too
      and to tmp_table_param->field array cleanup which is required later.
      The problem is that Item_func_set_user_var does not set
      result_filed which leads to unnecessary repeated excution of subquery
      on final stage.
      The fix is to set result_field for Item_func_set_user_var.
     @ mysql-test/r/count_distinct.result
        test result
     @ mysql-test/r/user_var.result
        test result
     @ mysql-test/t/count_distinct.test
        test case
     @ mysql-test/t/user_var.test
        test case
     @ sql/item_func.cc
        At the end of execution top level join execution
        we cleanup this join with true argument.
        It leads to underlying join cleanup(subquery) with true argument too
        and to tmp_table_param->field array cleanup which is required later.
        The problem is that Item_func_set_user_var does not set
        result_filed which leads to unnecessary repeated excution of subquery
        on final stage.
        The fix is to set result_field for Item_func_set_user_var.

[22 Dec 2009 10:48] Bugs System

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/95349

2828 Sergey Glukhov	2009-12-22
      Bug#47371 reference by same column name
      At the end of execution top level join execution
      we cleanup this join with true argument.
      It leads to underlying join cleanup(subquery) with true argument too
      and to tmp_table_param->field array cleanup which is required later.
      The problem is that Item_func_set_user_var does not set
      result_filed which leads to unnecessary repeated excution of subquery
      on final stage.
      The fix is to set result_field for Item_func_set_user_var.
     @ mysql-test/r/count_distinct.result
        test result
     @ mysql-test/r/user_var.result
        test result
     @ mysql-test/t/count_distinct.test
        test case
     @ mysql-test/t/user_var.test
        test case
     @ sql/item_func.cc
        At the end of execution top level join execution
        we cleanup this join with true argument.
        It leads to underlying join cleanup(subquery) with true argument too
        and to tmp_table_param->field array cleanup which is required later.
        The problem is that Item_func_set_user_var does not set
        result_filed which leads to unnecessary repeated excution of subquery
        on final stage.
        The fix is to set result_field for Item_func_set_user_var.

[14 Jan 2010 8:26] Bugs System

Pushed into 5.0.90 (revid:[e-mail]-20100114082402-05fod2h6z9x9wok8) (version source revid:[e-mail]-20091222095223-px3yog2zvazux84l) (merge vers: 5.0.89) (pib:16)

[15 Jan 2010 9:01] Bugs System

Pushed into 5.1.43 (revid:[e-mail]-20100115085139-qkh0i0fpohd9u9p5) (version source revid:[e-mail]-20091223070903-6rn5kgl3chedqnxd) (merge vers: 5.1.42) (pib:16)

[5 Feb 2010 11:46] Bugs System

Pushed into mysql-next-mr (revid:[e-mail]-20100204063540-9czpdmpixi3iw2yb) (version source revid:[e-mail]-20100119163614-172adculixyu26j5) (pib:16)

[5 Feb 2010 11:52] Bugs System

Pushed into 6.0.14-alpha (revid:[e-mail]-20100205113942-oqovjy0eoqbarn7i) (version source revid:[e-mail]-20100204064210-ljwanqvrjs83s1gq) (merge vers: 6.0.14-alpha) (pib:16)

[5 Feb 2010 11:58] Bugs System

Pushed into 5.5.2-m2 (revid:[e-mail]-20100203172258-1n5dsotny40yufxw) (version source revid:[e-mail]-20091225105650-qletdbs0wz9sx5nc) (merge vers: 5.5.1-m2) (pib:16)

[11 Feb 2010 13:44] Paul DuBois

Noted in 5.0.90, 5.1.43, 5.5.2, 6.0.14 changelogs.

A crash occurred when a user variable that was assigned to a subquery
result was used as a result field in a SELECT statement with 
aggregate functions.

Setting report to Need Merge pending push to Celosia.

[12 Mar 2010 14:15] Bugs System

Pushed into 5.1.44-ndb-7.0.14 (revid:[e-mail]-20100312135944-t0z8s1da2orvl66x) (version source revid:[e-mail]-20100312115609-woou0te4a6s4ae9y) (merge vers: 5.1.44-ndb-7.0.14) (pib:16)

[12 Mar 2010 14:31] Bugs System

Pushed into 5.1.44-ndb-6.2.19 (revid:[e-mail]-20100312134846-tuqhd9w3tv4xgl3d) (version source revid:[e-mail]-20100312060623-mx6407w2vx76h3by) (merge vers: 5.1.44-ndb-6.2.19) (pib:16)

[12 Mar 2010 14:47] Bugs System

Pushed into 5.1.44-ndb-6.3.33 (revid:[e-mail]-20100312135724-xcw8vw2lu3mijrhn) (version source revid:[e-mail]-20100312103652-snkltsd197l7q2yg) (merge vers: 5.1.44-ndb-6.3.33) (pib:16)

[12 Mar 2010 17:30] Paul DuBois

Fixed in earlier 5.1.x, 5.5.x.