Bug #59793 crash in Item_field::register_field_in_read_map with view
Submitted: 28 Jan 2011 12:07 Modified: 8 Mar 2011 0:01
Reporter: Shane Bester (Platinum Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Views Severity:S1 (Critical)
Version:5.6.2 OS:Any
Assigned to: Jørgen Løland CPU Architecture:Any
Tags: regression

[28 Jan 2011 12:07] Shane Bester
Description:
Version: '5.6.2-m5-valgrind-max-debug'  socket: '/tmp/mysql.sock'  port: 3306  Source distribution
[New Thread 0x7fffe3f98710 (LWP 6090)]

Program received signal SIGSEGV, Segmentation fault.
[Switching to Thread 0x7fffe3f98710 (LWP 6090)]
0x0000000000770b4d in Item_field::register_field_in_read_map (this=0x7fffd403fbf0, arg=0x7fffd400e120  <incomplete sequence \360\267>) at ./sql/item.cc:753
753       if (field->table == table || !table)
(gdb) bt
#0  in Item_field::register_field_in_read_map at ./sql/item.cc:753
#1  in Item::walk  at ./sql/item.h:994
#2  in Item_func::walk  at ./sql/item_func.cc:268
#3  in Item_cond::walk  at ./sql/item_cmpfunc.cc:4410
#4  in Item_subselect::walk  at ./sql/item_subselect.cc:261
#5  in Item_func::walk  at ./sql/item_func.cc:268
#6  in Item_subselect::walk  at ./sql/item_subselect.cc:253
#7  in Item_func::walk  at ./sql/item_func.cc:268
#8  in Item_subselect::walk  at ./sql/item_subselect.cc:253
#9  in Item_func::walk  at ./sql/item_func.cc:268
#10 in Item_cond::walk  at ./sql/item_cmpfunc.cc:4408
#11 in Item_subselect::walk  at ./sql/item_subselect.cc:253
#12 in Item_func::walk  at ./sql/item_func.cc:268
#13 in find_all_keys  at ./sql/filesort.cc:611
#14 in filesort  at ./sql/filesort.cc:276
#15 in create_sort_index  at ./sql/sql_select.cc:20242
#16 in JOIN::exec  at ./sql/sql_select.cc:3300
#17 in mysql_select at ./sql/sql_select.cc:3558
#18 in handle_select  at ./sql/sql_select.cc:323
#19 in execute_sqlcom_select  at ./sql/sql_parse.cc:4513
#20 in mysql_execute_command  at ./sql/sql_parse.cc:2096
#21 in mysql_parse at ./sql/sql_parse.cc:5550
#22 in dispatch_command  at ./sql/sql_parse.cc:1078
#23 in do_command  at ./sql/sql_parse.cc:815
#24 in do_handle_one_connection  at ./sql/sql_connect.cc:748
#25 in handle_one_connection  at ./sql/sql_connect.cc:684
#26 in start_thread  at pthread_create.c:301
#27 in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:115
(gdb) 

How to repeat:
i'll put a testcase on the weekend.
[28 Jan 2011 15:02] MySQL Verification Team
this is a regression in mysql-trunk. 5.5.10 don't crash.

drop table if exists t1;
create table `t1`(`a` int)engine=innodb;
insert into `t1` values (1),(2);
create or replace view `v1` as select 1 as `a` from `t1`;

select 1 from `t1` where 1 = any 
  (select 1 from `t1` `a` where `t1`.`a`=
    (select 1 from `t1` `b` where 1 =
      (select 1 from `t1` `c` where 1  <>
        (select  `a` xor `a`  from v1 limit 0)
      )
    )
  )
  order by  `t1`.`a` ,  `t1`.`a`
;
[28 Jan 2011 15:08] Valeriy Kravchuk
Verified with current mysql-trunk on Mac OS X:

macbook-pro:trunk openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.2-m5-debug Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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

mysql> drop table if exists t1;
Query OK, 0 rows affected (0.00 sec)

mysql> create table `t1`(`a` int)engine=innodb;
Query OK, 0 rows affected (0.20 sec)

mysql> insert into `t1` values (1),(2);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> create or replace view `v1` as select 1 as `a` from `t1`;
Query OK, 0 rows affected (0.08 sec)

mysql> select 1 from `t1` where 1 = any 
    ->   (select 1 from `t1` `a` where `t1`.`a`=
    ->     (select 1 from `t1` `b` where 1 =
    ->       (select 1 from `t1` `c` where 1  <>
    ->         (select  `a` xor `a`  from v1 limit 0)
    ->       )
    ->     )
    ->   )
    ->   order by  `t1`.`a` ,  `t1`.`a`
    -> ;
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql> 110128 17:04:09 mysqld_safe mysqld restarted

mysql> exit
Bye
macbook-pro:trunk openxs$ tail -120 data/macbook-pro.err 
...
110128 17:04:05 [Note] /Users/openxs/dbs/trunk/bin/mysqld: ready for connections.
Version: '5.6.2-m5-debug'  socket: '/tmp/mysql.sock'  port: 3306  Source distribution
110128 17:04:09 - mysqld got signal 10 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help diagnose
the problem, but since we have already crashed, something is definitely wrong
and this may fail.

key_buffer_size=8388608
read_buffer_size=131072
max_used_connections=1
max_threads=151
thread_count=1
connection_count=1
It is possible that mysqld could use up to 
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 337971 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

Thread pointer: 0x1023a00
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
stack_bottom = 0xb0800f30 thread_stack 0x30000
0   mysqld                              0x003aad69 my_print_stacktrace + 44
1   mysqld                              0x00103eda handle_segfault + 892
2   libSystem.B.dylib                   0x940472bb _sigtramp + 43
3   ???                                 0xffffffff 0x0 + 4294967295
4   mysqld                              0x000784b5 _ZN4Item4walkEMS_FbPhEbS0_ + 73
5   mysqld                              0x000927ae _ZN9Item_func4walkEM4ItemFbPhEbS1_ + 108
6   mysqld                              0x0006a080 _ZN9Item_cond4walkEM4ItemFbPhEbS1_ + 170
7   mysqld                              0x000c955f _ZN14Item_subselect4walkEM4ItemFbPhEbS1_ + 321
8   mysqld                              0x000927ae _ZN9Item_func4walkEM4ItemFbPhEbS1_ + 108
9   mysqld                              0x000c94a3 _ZN14Item_subselect4walkEM4ItemFbPhEbS1_ + 133
10  mysqld                              0x000927ae _ZN9Item_func4walkEM4ItemFbPhEbS1_ + 108
11  mysqld                              0x000c94a3 _ZN14Item_subselect4walkEM4ItemFbPhEbS1_ + 133
12  mysqld                              0x000927ae _ZN9Item_func4walkEM4ItemFbPhEbS1_ + 108
13  mysqld                              0x0006a02a _ZN9Item_cond4walkEM4ItemFbPhEbS1_ + 84
14  mysqld                              0x000c94a3 _ZN14Item_subselect4walkEM4ItemFbPhEbS1_ + 133
15  mysqld                              0x000927ae _ZN9Item_func4walkEM4ItemFbPhEbS1_ + 108
16  mysqld                              0x00035b5d _Z21filesort_free_buffersP5TABLEb + 5343
17  mysqld                              0x000368e4 _Z8filesortP3THDP5TABLEP13st_sort_fieldjP10SQL_SELECTybPyS7_ + 1894
18  mysqld                              0x00222e04 _Z27create_tmp_field_from_fieldP3THDP5FieldPKcP5TABLEP10Item_fieldj + 7462
19  mysqld                              0x00247511 _ZN4JOIN4execEv + 8625
20  mysqld                              0x00241c34 _Z12mysql_selectP3THDPPP4ItemP10TABLE_LISTjR4ListIS1_ES2_jP8st_orderSB_S2_SB_yP13select_resultP18st_select_lex_unitP13st_select_lex + 916
21  mysqld                              0x00247ac5 _Z13handle_selectP3THDP3LEXP13select_resultm + 547
22  mysqld                              0x001d40d5 _Z15update_precheckP3THDP10TABLE_LIST + 1103
23  mysqld                              0x001d678a _Z21mysql_execute_commandP3THD + 2864
24  mysqld                              0x001de414 _Z11mysql_parseP3THDPcjP12Parser_state + 644
25  mysqld                              0x001defdc _Z16dispatch_command19enum_server_commandP3THDPcj + 2686
26  mysqld                              0x001e04bc _Z10do_commandP3THD + 664
27  mysqld                              0x002c8fbb _Z24do_handle_one_connectionP3THD + 1095
28  mysqld                              0x002c90a9 handle_one_connection + 37
29  libSystem.B.dylib                   0x9400c095 _pthread_start + 321
30  libSystem.B.dylib                   0x9400bf52 thread_start + 34

Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
Query (0x10d1410): select 1 from `t1` where 1 = any 
  (select 1 from `t1` `a` where `t1`.`a`=
    (select 1 from `t1` `b` where 1 =
      (select 1 from `t1` `c` where 1  <>
        (select  `a` xor `a`  from v1 limit 0)
      )
    )
  )
  order by  `t1`.`a` ,  `t1`.`a`
Connection ID (thread ID): 1
Status: NOT_KILLED
...
[30 Jan 2011 8:27] MySQL Verification Team
maybe related: bug #59817
[31 Jan 2011 10:45] Øystein Grøvlen
Verified that it is a regression since does not fail in 5.5.
Only fails in mysql-next-mr-opt-backporting when semijoin=off.
[31 Jan 2011 12:29] Øystein Grøvlen
Limited success with bzrfind on this since it hits several other errors, but it seems that this was introduced during back-porting of optimizer 6.0 features.
[31 Jan 2011 13:29] Øystein Grøvlen
Minimal test case:

create table t1(a int)engine=innodb;
create view v1 as select a from t1;

select 1 from t1 where a in 
 (select  a xor a  from v1)
order by a;

drop table t1;
drop view v1;

The following parts are significant:
  - "... a in (...) ". "... 1 in (...)" does not fail.
  - "a xor a".  If xor is replace with "and" or "or", server does not crash.
  - "... from v1". "... from t1" does not fail
  - order by is required.

Query will also fail with MyISAM if t1 contains at least 2 rows.
[1 Feb 2011 14:40] Øystein Grøvlen
This regression was introduced by the bug fix for Bug#45221.
Verified by reverting this bug fix.
[1 Feb 2011 15:20] Øystein Grøvlen
Bug#59817 is a duplicate.
[3 Feb 2011 12:42] Jørgen Løland
This is what happens:

With 45221, Item_cond_xor started storing it's arguments both in 
 1) list (the Item_cond way)
 2) args[] (the Item_func way)

The reason for storing the arguments both ways is that 
 1) Item_cond_xor is an Item_cond and is treated like that most of the time
 2) Item_cond_xor::type() is FUNC_ITEM because it is not yet optimized, and
    is therefore treated like Item_func in locations where type() is used to 
    determine what it is. 

In this bug, Item_cond_xor initially gets two Item_field arguments. These are stored in the list inherited from Item_cond and in args[] inherited from Item_func. During resolution, find_field_in_view() replaces the Item_fields stored in the list with Item_direct_view_refs, but args[] still points to the unresolved Item_fields.
[7 Feb 2011 14:07] 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/130582

3605 Jorgen Loland	2011-02-07
      Bug#59793: crash in Item_field::register_field_in_read_map 
                 with view
      
      Prior to the refactoring in this patch, Item_cond_xor behaved 
      partially as an Item_cond and partially as an Item_func. The
      reasoning behind this was that XOR is currently not optimized
      (thus should be Item_func instead of Item_cond), but it was 
      planned optimize it in the future (thus, made Item_cond anyway 
      to ease optimization later). 
      
      Even though Item_cond inherits from Item_func, there are 
      differences between these two. One difference is that the 
      arguments are stored differently. Item_cond stores them in a 
      list while Item_func store them in an args[]. 
      
      BUG no 45221 was caused by Item_cond_xor storing arguments in 
      the list while users of the objects would look for them in 
      args[]. The fix back then was to store the arguments in both 
      locations.
      
      In this bug, Item_cond_xor initially gets two Item_field 
      arguments. These are stored in the list inherited from 
      Item_cond and in args[] inherited from Item_func. During
      resolution, find_field_in_view() replaces the Item_fields 
      stored in the list with Item_direct_view_refs, but args[] 
      still points to the unresolved Item_fields. This shows that 
      the fix for 45221 was incorrect.
      
      The refactoring performed in this patch removes the confusion
      by making the XOR item an Item_func period.
     @ mysql-test/include/subquery.inc
        Add test for BUG#59793
     @ mysql-test/r/negation_elimination.result
        Add tests for negation of XOR
     @ mysql-test/r/subquery_nomat_nosj.result
        Add test for BUG#59793
     @ mysql-test/r/subquery_none.result
        Add test for BUG#59793
     @ mysql-test/t/negation_elimination.test
        Add tests for negation of XOR
     @ sql/item_cmpfunc.cc
        Refactor XOR item: it is now a pure Item_func, inheriting from Item_bool_func2 instead of Item_cond
     @ sql/item_cmpfunc.h
        Refactor XOR item: it is now a pure Item_func, inheriting from Item_bool_func2 instead of Item_cond
     @ sql/item_func.h
        Refactor XOR item: it is now a pure Item_func, inheriting from Item_bool_func2 instead of Item_cond
     @ sql/sql_yacc.yy
        Refactor XOR item: it is now a pure Item_func, inheriting from Item_bool_func2 instead of Item_cond
     @ unittest/gunit/item-t.cc
        Add unit test for Item_func_xor
[9 Feb 2011 13:26] 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/130863

3625 Jorgen Loland	2011-02-09
      Bug#59793: crash in Item_field::register_field_in_read_map 
                 with view
      
      Prior to the refactoring in this patch, Item_cond_xor behaved 
      partially as an Item_cond and partially as an Item_func. The
      reasoning behind this was that XOR is currently not optimized
      (thus should be Item_func instead of Item_cond), but it was 
      planned optimize it in the future (thus, made Item_cond anyway 
      to ease optimization later). 
      
      Even though Item_cond inherits from Item_func, there are 
      differences between these two. One difference is that the 
      arguments are stored differently. Item_cond stores them in a 
      list while Item_func store them in an args[]. 
      
      BUG no 45221 was caused by Item_cond_xor storing arguments in 
      the list while users of the objects would look for them in 
      args[]. The fix back then was to store the arguments in both 
      locations.
      
      In this bug, Item_cond_xor initially gets two Item_field 
      arguments. These are stored in the list inherited from 
      Item_cond and in args[] inherited from Item_func. During
      resolution, find_field_in_view() replaces the Item_fields 
      stored in the list with Item_direct_view_refs, but args[] 
      still points to the unresolved Item_fields. This shows that 
      the fix for 45221 was incorrect.
      
      The refactoring performed in this patch removes the confusion
      by making the XOR item an Item_func period.
     @ mysql-test/include/subquery.inc
        Add test for BUG#59793
     @ mysql-test/r/negation_elimination.result
        Add tests for negation of XOR
     @ mysql-test/r/subquery_nomat_nosj.result
        Add test for BUG#59793
     @ mysql-test/r/subquery_none.result
        Add test for BUG#59793
     @ mysql-test/t/negation_elimination.test
        Add tests for negation of XOR
     @ sql/item_cmpfunc.cc
        Refactor XOR item: it is now a pure Item_func, inheriting from Item_bool_func2 instead of Item_cond
     @ sql/item_cmpfunc.h
        Refactor XOR item: it is now a pure Item_func, inheriting from Item_bool_func2 instead of Item_cond
     @ sql/item_func.h
        Refactor XOR item: it is now a pure Item_func, inheriting from Item_bool_func2 instead of Item_cond
     @ sql/sql_yacc.yy
        Refactor XOR item: it is now a pure Item_func, inheriting from Item_bool_func2 instead of Item_cond
     @ unittest/gunit/item-t.cc
        Add unit test for Item_func_xor
[8 Mar 2011 0:01] Paul DuBois
Noted in 5.6.2 changelog.

Internally, XOR items partially behaved like functions and partially
as conditions. This resulted in inconsistent handling and crashes.
The issue is fixed by consistently treating XOR items as functions.

CHANGESET - http://lists.mysql.com/commits/131729