MySQL Bugs: #13938: Server Crash during SELECT query w/ SUBQUERY in MERGE VIEW

Bug #13938	Server Crash during SELECT query w/ SUBQUERY in MERGE VIEW
Submitted:	11 Oct 2005 20:34	Modified:	13 Apr 2006 13:37
Reporter:	Wesley Dyk	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: Views	Severity:	S2 (Serious)
Version:	5.0.13-rc/5.0 BK source	OS:	Windows (Windows / FreeBSD)
Assigned to:	Evgeny Potemkin	CPU Architecture:	Any

Description:
Executing a specific SELECT query results in a server crash. This first came up with my 5.0.13 install on Windows Server 2003. To verify the bug, I dumped the database to my FreeBSD 5.4 box with the same version (MySQL binaries, not the "port"). The server crashed on that machine as well. I trimmed the dataset down a bit and still was able to crash mysqld. The query aggregates data from a view. The intended result of the query is a summary of gas volumes produced by area and date. The view uses a subquery to retrieve the latest "version" of a row. The subquery is a dependent subquery intended to return the rows with a groupwise maximum of a specific column (the version number). The view uses the MERGE algorithm. The workaround is to use a self-join and group by method in the view. This requires the view to use the TEMP algorithm. Since the full table is relatively large the TEMP algorithm will kill performance. The full table has ~1 MM rows and grows by ~75 K rows a day.

Here is the crash in the error log:

Version: '5.0.13-rc-max-log' socket: '/tmp/mysql.sock' port: 3306 MySQL Commu
nity Edition - Experimental (GPL)
mysqld got signal 11;
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=16777216
read_buffer_size=45056
max_used_connections=3
max_connections=100
threads_connected=3
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 25583
K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

051011 13:28:25 mysqld restarted

How to repeat:
Run the BUG_X.sql script in the archive I will provide once this bug is submitted. After the script is finished, run the suqquery.sql script (contains only the problem query). MySQL will crash.

Thank you for the bug report and feedback.

miguel@hegel:~/dbs/5.0> bin/mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.15-rc-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>  create database platteville_devel;
Query OK, 1 row affected (0.00 sec)

mysql> use platteville_devel;
Database changed

mysql> source /home/miguel/v/BUG_X.sql

mysql> select area_id, gasday `volume_date`, sum(volume) from v_hourly_volume v
    -> join nb_l_area_pden a on a.pden_id = v.pden_id and a.pden_type = v.pden_type
    -> join nb_l_gasday on volume_date = actualdate
    -> group by area_id, volume_date;
ERROR 2013 (HY000): Lost connection to MySQL server during query

051011 18:53:37 [Note] /home/miguel/dbs/5.0/libexec/mysqld: ready for connections.
Version: '5.0.15-rc-debug'  socket: '/tmp/mysql.sock'  port: 3306  Source distribution
[New Thread 1132456880 (LWP 24982)]

Program received signal SIGSEGV, Segmentation fault.
[Switching to Thread 1132456880 (LWP 24982)]
0x082431e6 in find_order_in_list (thd=0x8e3a5a0, ref_pointer_array=0x8ec4180, tables=0x8e65c98, order=0x8e66e20, fields=@0x8e3a884, 
    all_fields=@0x8eab988, is_group_field=true) at sql_select.cc:11865
11865         push_warning_printf(thd, MYSQL_ERROR::WARN_LEVEL_WARN, ER_NON_UNIQ_ERROR,
(gdb) bt full
#0  0x082431e6 in find_order_in_list (thd=0x8e3a5a0, ref_pointer_array=0x8ec4180, tables=0x8e65c98, order=0x8e66e20, fields=@0x8e3a884, 
    all_fields=@0x8eab988, is_group_field=true) at sql_select.cc:11865
        view_ref = (class Item *) 0x8ec44c0
        order_item = (class Item *) 0x8e66d68
        order_item_type = FIELD_ITEM
        select_item = (class Item **) 0x8e65a54
        from_field = (class Field *) 0x2
        counter = 1
        unaliased = false
        el = 149701824
#1  0x082433bc in setup_group (thd=0x8e3a5a0, ref_pointer_array=0x8ec4180, tables=0x8e65c98, fields=@0x8e3a884, all_fields=@0x8eab988, 
    order=0x8e66e20, hidden_group_fields=0x8eab96e) at sql_select.cc:11960
        org_fields = 3
#2  0x08248ca2 in setup_without_group (thd=0x8e3a5a0, ref_pointer_array=0x8ec4180, tables=0x8e65c98, leaves=0x8ebf910, fields=@0x8e3a884, 
    all_fields=@0x8eab988, conds=0x8eab9f0, order=0x0, group=0x8e66d28, hidden_group_fields=0x8eab96e) at sql_select.cc:285
        save_allow_sum_func = true
        res = 0
        _db_func_ = 0x87ae3b4 "´ãz\b"
        _db_file_ = 0x8e65c30 "´ãz\b([æ\bsum(volume)"
        _db_level_ = 142271412
        _db_framep_ = (char **) 0x8e3a884
#3  0x0822752a in JOIN::prepare (this=0x8eaac58, rref_pointer_array=0x8e3a91c, tables_init=0x8e65c98, wild_num=0, conds_init=0x0, og_num=2, 
    order_init=0x0, group_init=0x8e66d28, having_init=0x0, proc_param_init=0x0, select_lex_arg=0x8e3a804, unit_arg=0x8e3a5f0)
    at sql_select.cc:336
        _db_func_ = 0x80844a00 ""
        _db_file_ = 0x8e3a884 "PYæ\b0\\æ\b\003"
        _db_level_ = 149136800
        _db_framep_ = (char **) 0x8eaac58
#4  0x0822be22 in mysql_select (thd=0x8e3a5a0, rref_pointer_array=0x8e3a91c, tables=0x8e65c98, wild_num=0, fields=@0x8e3a884, conds=0x0, 
    og_num=2, order=0x0, group=0x8e66d28, having=0x0, proc_param=0x0, select_options=2156153344, result=0x8ec4130, unit=0x8e3a5f0, 
    select_lex=0x8e3a804) at sql_select.cc:1811
        err = 8
        free_join = true
        _db_func_ = 0x8147446 "\203Ä\020\211Eð\211Uôë\016ÇEðÿÿÿÿÇEôÿÿÿÿ\213Eð\213Uô\211Eø\211Uü\213U\b\211Uì\213E\f\203¸\024\001"
        _db_file_ = 0x437fda58 ""
        _db_level_ = 140754991
        _db_framep_ = (char **) 0x863c2fd
        join = (JOIN *) 0x8eaac58
#5  0x082271cd in handle_select (thd=0x8e3a5a0, lex=0x8e3a5e0, result=0x8ec4130, setup_tables_done_option=0) at sql_select.cc:234
---Type <return> to continue, or q <return> to quit---
        unit = (SELECT_LEX_UNIT *) 0x8e3a5f0
        res = false
        select_lex = (SELECT_LEX *) 0x8e3a804
        _db_func_ = 0x0
        _db_file_ = 0x0
        _db_level_ = 149314712
        _db_framep_ = (char **) 0x0
#6  0x081ef579 in mysql_execute_command (thd=0x8e3a5a0) at sql_parse.cc:2499
        result = (class select_result *) 0x8ec4130
        res = false
        result = 0
        lex = (LEX *) 0x8e3a5e0
        select_lex = (SELECT_LEX *) 0x8e3a804
        slave_fake_lock = false
        fake_prev_lock = (MYSQL_LOCK *) 0x0
        first_table = (TABLE_LIST *) 0x8e65c98
        all_tables = (TABLE_LIST *) 0x8e65c98
        unit = (SELECT_LEX_UNIT *) 0x8e3a5f0
        _db_func_ = 0x437fdfe8 "\030à\177C5p\037\b ¥ã\b ¥ã\bhWæ\bà"
        _db_file_ = 0x8e65758 ""
        _db_level_ = 149136832
        _db_framep_ = (char **) 0x437fdfac
#7  0x081f7035 in mysql_parse (thd=0x8e3a5a0, 
    inBuf=0x8e65768 "select area_id, gasday `volume_date`, sum(volume) from v_hourly_volume v\njoin nb_l_area_pden a on a.pden_id = v.pden_id and a.pden_type = v.pden_type\njoin nb_l_gasday on volume_date = actualdate\ngroup"..., length=224) at sql_parse.cc:5525
        lex = (LEX *) 0x8e3a5e0
        _db_func_ = 0x87b7f20 "è\221b\b"
        _db_file_ = 0x81ed7b1 "\203Ä \203=ô\204|\b"
        _db_level_ = 1132453908
        _db_framep_ = (char **) 0x0
#8  0x081ed82d in dispatch_command (command=COM_QUERY, thd=0x8e3a5a0, 
    packet=0x4387e021 "select area_id, gasday `volume_date`, sum(volume) from v_hourly_volume v\njoin nb_l_area_pden a on a.pden_id = v.pden_id and a.pden_type = v.pden_type\njoin nb_l_gasday on volume_date = actualdate\ngroup"..., packet_length=225) at sql_parse.cc:1697
        packet_end = 0x8e65848 ""
        net = (NET *) 0x8e3ad68
        error = false
        _db_func_ = 0xe1 <Address 0xe1 out of bounds>
        _db_file_ = 0x8 <Address 0x8 out of bounds>
        _db_level_ = 0
---Type <return> to continue, or q <return> to quit---
        _db_framep_ = (char **) 0x2
#9  0x081ed124 in do_command (thd=0x8e3a5a0) at sql_parse.cc:1498
        packet = 0x4387e020 "\003select area_id, gasday `volume_date`, sum(volume) from v_hourly_volume v\njoin nb_l_area_pden a on a.pden_id = v.pden_id and a.pden_type = v.pden_type\njoin nb_l_gasday on volume_date = actualdate\ngrou"...
        old_timeout = 30
        packet_length = 225
        net = (NET *) 0x8e3ad68
        command = COM_QUERY
        _db_func_ = 0x8e3b6e4 "\001"
        _db_file_ = 0x81c5bc7 "\203Ä\020\213E\bÆ\200Ô\021"
        _db_level_ = 1132454760
        _db_framep_ = (char **) 0x1010
#10 0x081ec2bf in handle_one_connection (arg=0x8e3a5a0) at sql_parse.cc:1143
        error = 0
        net = (NET *) 0x8e3ad68
        sctx = (Security_context *) 0x8e3b540
        thd = (class THD *) 0x8e3a5a0
        launch_time = 0
        set = {__val = {0 <repeats 32 times>}}
#11 0x40174aa7 in start_thread () from /lib/tls/libpthread.so.0
No symbol table info available.
#12 0x402a5c2e in clone () from /lib/tls/libc.so.6

Igor, the data loads successfully now.
I was also able to get a stacktrace, the cause of the bug is very simple:
Program received signal SIGSEGV, Segmentation fault.
[Switching to Thread 1099492272 (LWP 20396)]
0x08251748 in find_order_in_list (thd=0x8e5dce0, ref_pointer_array=0x8f52ed0, 
    tables=0x8f49ea8, order=0x8f4f2b0, fields=@0x8e5dfe0, 
    all_fields=@0x8f54b48, is_group_field=true) at sql_select.cc:12376
12376	      push_warning_printf(thd, MYSQL_ERROR::WARN_LEVEL_WARN, ER_NON_UNIQ_ERROR,
(gdb) bt
#0  0x08251748 in find_order_in_list (thd=0x8e5dce0, 
    ref_pointer_array=0x8f52ed0, tables=0x8f49ea8, order=0x8f4f2b0, 
    fields=@0x8e5dfe0, all_fields=@0x8f54b48, is_group_field=true)
    at sql_select.cc:12376
#1  0x082519aa in setup_group (thd=0x8e5dce0, ref_pointer_array=0x8f52ed0, 
    tables=0x8f49ea8, fields=@0x8e5dfe0, all_fields=@0x8f54b48, 
    order=0x8f4f2b0, hidden_group_fields=0x8f54b2e) at sql_select.cc:12476
#2  0x082569e2 in setup_without_group (thd=0x8e5dce0, 
    ref_pointer_array=0x8f52ed0, tables=0x8f49ea8, leaves=0x8f4f750, 
    fields=@0x8e5dfe0, all_fields=@0x8f54b48, conds=0x8f54bdc, order=0x0, 
    group=0x8f4f030, hidden_group_fields=0x8f54b2e) at sql_select.cc:288
#3  0x08235458 in JOIN::prepare (this=0x8f53e18, rref_pointer_array=0x8e5e09c, 
    tables_init=0x8f49ea8, wild_num=0, conds_init=0x0, og_num=2, 
    order_init=0x0, group_init=0x8f4f030, having_init=0x0, 
    proc_param_init=0x0, select_lex_arg=0x8e5df58, unit_arg=0x8e5dd30)
    at sql_select.cc:339
#4  0x0823a31f in mysql_select (thd=0x8e5dce0, rref_pointer_array=0x8e5e09c, 
    tables=0x8f49ea8, wild_num=0, fields=@0x8e5dfe0, conds=0x0, og_num=2, 
    order=0x0, group=0x8f4f030, having=0x0, proc_param=0x0, 
    select_options=2156153344, result=0x8f52e50, unit=0x8e5dd30, 
    select_lex=0x8e5df58) at sql_select.cc:1864
#5  0x08235086 in handle_select (thd=0x8e5dce0, lex=0x8e5dd20, 
    result=0x8f52e50, setup_tables_done_option=0) at sql_select.cc:238
#6  0x081f9e09 in mysql_execute_command (thd=0x8e5dce0) at sql_parse.cc:2500
#7  0x08202bc2 in mysql_parse (thd=0x8e5dce0, 
    inBuf=0x8f51620 "select area_id, gasday `volume_date`, sum(volume) from v_hourly_volume v join nb_l_area_pden a on a.pden_id = v.pden_id and a.pden_type = v.pden_type join nb_l_gasday on volume_date = actualdate group"..., length=224)
    at sql_parse.cc:5630
#8  0x081f7e2b in dispatch_command (command=COM_QUERY, thd=0x8e5dce0, 
    packet=0x8e611d1 "select area_id, gasday `volume_date`, sum(volume) from v_hourly_volume v join nb_l_area_pden a on a.pden_id = v.pden_id and a.pden_type = v.pden_type join nb_l_gasday on volume_date = actualdate group"..., 
    packet_length=225) at sql_parse.cc:1719
#9  0x081f764f in do_command (thd=0x8e5dce0) at sql_parse.cc:1515
#10 0x081f66e4 in handle_one_connection (arg=0x8e5dce0) at sql_parse.cc:1158
#11 0x40175297 in start_thread () from /lib/tls/libpthread.so.0
#12 0x402b037e in clone () from /lib/tls/libc.so.6
#13 0x4188ebb0 in ?? ()
(gdb) frame 0
#0  0x08251748 in find_order_in_list (thd=0x8e5dce0, 
    ref_pointer_array=0x8f52ed0, tables=0x8f49ea8, order=0x8f4f2b0, 
    fields=@0x8e5dfe0, all_fields=@0x8f54b48, is_group_field=true)
    at sql_select.cc:12376
12376	      push_warning_printf(thd, MYSQL_ERROR::WARN_LEVEL_WARN, ER_NON_UNIQ_ERROR,
(gdb) list
12371	        There is a field with the same name in the FROM clause. This
12372	        is the field that will be chosen. In this case we issue a
12373	        warning so the user knows that the field from the FROM clause
12374	        overshadows the column reference from the SELECT list.
12375	      */
12376	      push_warning_printf(thd, MYSQL_ERROR::WARN_LEVEL_WARN, ER_NON_UNIQ_ERROR,
12377	                          ER(ER_NON_UNIQ_ERROR), from_field->field_name,
12378	                          current_thd->where);
12379	    }
12380	  }
(gdb) p from_field->field_name
Cannot access memory at address 0x2

Evidently, from_field can't be printed as it points at not_found_field.

mysql> select area_id, gasday `volume_date`, sum(volume) from v_hourly_volume v 
        join nb_l_area_pden a on a.pden_id = v.pden_id and a.pden_type = v.pden_
type     join nb_l_gasday on volume_date = actualdate                           
          group by area_id, volume_date;                                        
+-------------+---------------------+-------------+                             
| area_id     | volume_date         | sum(volume) |                             
+-------------+---------------------+-------------+                             
| A           | 2005-09-22 00:00:00 |    53.17000 |                             

[skip]

| WTBRG       | 2005-10-10 00:00:00 |  3633.54000 | 
+-------------+---------------------+-------------+
14831 rows in set, 1 warning (49.04 sec)

mysql> show warnings;
+---------+------+------------------------------------------------------+
| Level   | Code | Message                                              |
+---------+------+------------------------------------------------------+
| Warning | 1052 | Column 'volume_date' in group statement is ambiguous | 
+---------+------+------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select version();
+--------------+
| version()    |
+--------------+
| 5.0.21-debug | 
+--------------+
1 row in set (0.00 sec)

Tested on max and non-max builds.