Bug #38195 Executing query causes memory access violation error in mysql service
Submitted: 17 Jul 2008 12:29 Modified: 27 Nov 2008 16:07
Reporter: Maxim Taldykin Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S2 (Serious)
Version:5.0.51b-community-nt/5.0/5.1/6.0BK OS:Any (win xp sp 2 build 2600.xpsp_sp2_qfe.070227-2300)
Assigned to: Evgeny Potemkin
Tags: myisam, regression, segfault
Triage: D1 (Critical) / R2 (Low) / E3 (Medium)

[17 Jul 2008 12:29] Maxim Taldykin
Description:

Executing query causes memory access violation error in mysql service

How to repeat:
drop table test;
create table test (a int, b int, primary key (a,b), key `index` (a,b)) engine=MyISAM;
insert into  test (a,b) values (0,0),(0,1),(0,2),(0,3),(0,4),(0,5),(0,6),(0,7),(0,8),(0,9),(0,10),(0,11),(0,12),(0,13);
insert into  test (a,b) select a, max(b)+1 from test where a = 0 group by a;
[17 Jul 2008 12:52] Susanne Ebrecht
Crashes on Linux either 5.1 bzr tree as 5.0 bzr tree.
[17 Jul 2008 13:01] Miguel Solorzano
Thank you for the bug report.

Repeatable on current sources tree 5.0/5.1/6.0 and older version 5.0.41.

Not repeatable with InnoDB engine and older version 5.0.22.

c:\dbs>c:\dbs\5.0\bin\mysqld --defaults-file=c:\dbs\5.0\my.ini --standalone --console
080717  9:32:06  InnoDB: Started; log sequence number 0 111589
080717  9:32:07 [Note] c:\dbs\5.0\bin\mysqld: ready for connections.
Version: '5.0.66-nt-debug-log'  socket: ''  port: 3500  Source distribution
080717  9:32:36 - mysqld got exception 0xc0000005 ;
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=8384512
read_buffer_size=131072
max_used_connections=1
max_connections=100
threads_connected=1
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 225784 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

thd=0156BEC8
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...
0043EB31    mysqld.exe!Field::set_notnull()[field.h:214]
0048F0B8    mysqld.exe!Item::save_in_field()[item.cc:4709]
0049AC97    mysqld.exe!Item_result_field::save_in_result_field()[item.h:1887]
006783E7    mysqld.exe!copy_funcs()[sql_select.cc:14621]
00670CAA    mysqld.exe!end_write()[sql_select.cc:11685]
0066E8CC    mysqld.exe!evaluate_join_record()[sql_select.cc:10766]
0066E5EF    mysqld.exe!sub_select()[sql_select.cc:10650]
0066E125    mysqld.exe!do_select()[sql_select.cc:10413]
0065909C    mysqld.exe!JOIN::exec()[sql_select.cc:1712]
0065AA84    mysqld.exe!mysql_select()[sql_select.cc:2276]
00654892    mysqld.exe!handle_select()[sql_select.cc:257]
006368A4    mysqld.exe!mysql_execute_command()[sql_parse.cc:3651]
0063D2DC    mysqld.exe!mysql_parse()[sql_parse.cc:6173]
006326F4    mysqld.exe!dispatch_command()[sql_parse.cc:1875]
00631E93    mysqld.exe!do_command()[sql_parse.cc:1581]
00630239    mysqld.exe!handle_one_connection()[sql_parse.cc:1187]
0078DD96    mysqld.exe!pthread_start()[my_winthread.c:85]
008D7F07    mysqld.exe!_threadstart()[thread.c:196]
7C80B713    kernel32.dll!GetModuleFileNameA()
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd->query at 015A3EF8=insert into  test (a,b) select a, max(b)+1 from test where a = 0 group by a
thd->thread_id=1
The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
information that should help you find out what is causing the crash.

c:\dbs>

C:\temp\mysql-5.0.22-win32>bin\mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.22-community

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

mysql> drop table test;
ERROR 1051 (42S02): Unknown table 'test'
mysql> create table test (a int, b int, primary key (a,b), key `index` (a,b)) engine=MyISAM;
Query OK, 0 rows affected (0.20 sec)

mysql> insert into  test (a,b) values
    -> (0,0),(0,1),(0,2),(0,3),(0,4),(0,5),(0,6),(0,7),(0,8),(0,9),(0,10),(0,11),(0,12),(0,13);
Query OK, 14 rows affected (0.05 sec)
Records: 14  Duplicates: 0  Warnings: 0

mysql> insert into  test (a,b) select a, max(b)+1 from test where a = 0 group by a;
Query OK, 1 row affected (0.05 sec)
Records: 1  Duplicates: 0  Warnings: 0
[1 Aug 2008 5:24] Giuseppe Maxia
also crashes on Mac OSX
[4 Aug 2008 18:37] Evgeny Potemkin
Query "select SQL_BUFFER_RESULT a, max(b)+1 from test where a = 0 group by a;"
also crashes server.
For this query the optimizer chooses query execution plan which doesn't use (and thus doesn't create) the result_field of the max function. But the combination of the SQL_BUFFER_RESULT option (or INSERT_SELECT statement) and the loose index scan access method forces server to use it. Usage of non-created field causes server crash.
[6 Aug 2008 14: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/51011

2611 Evgeny Potemkin	2008-08-06
      Bug#38195: Incorrect handling of aggregate functions when loose index scan is
      used causes server crash.
      
      When the loose index scan access method is used values of aggregated functions
      are precomputed by it. Aggregation of such functions shouldn't be performed
      in this case and functions should be treated as normal ones.
      The create_tmp_table function wasn't taking this into account and this led to
      a crash.
      
      Now the JOIN::exec and the create_tmp_table functions treat aggregate
      functions as normal ones when the loose index scan is used.
[6 Aug 2008 20: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/51039

2611 Evgeny Potemkin	2008-08-06
      Bug#38195: Incorrect handling of aggregate functions when loose index scan is
      used causes server crash.
      
      When the loose index scan access method is used values of aggregated functions
      are precomputed by it. Aggregation of such functions shouldn't be performed
      in this case and functions should be treated as normal ones.
      The create_tmp_table function wasn't taking this into account and this led to
      a crash if a query has MIN/MAX aggregate functions and employs temporary table
      and loose index scan.
      Now the JOIN::exec and the create_tmp_table functions treat MIN/MAX aggregate
      functions as normal ones when the loose index scan is used.
[6 Aug 2008 20: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/51040

2611 Evgeny Potemkin	2008-08-06
      Bug#38195: Incorrect handling of aggregate functions when loose index scan is
      used causes server crash.
      
      When the loose index scan access method is used values of aggregated functions
      are precomputed by it. Aggregation of such functions shouldn't be performed
      in this case and functions should be treated as normal ones.
      The create_tmp_table function wasn't taking this into account and this led to
      a crash if a query has MIN/MAX aggregate functions and employs temporary table
      and loose index scan.
      Now the JOIN::exec and the create_tmp_table functions treat MIN/MAX aggregate
      functions as normal ones when the loose index scan is used.
[14 Aug 2008 19:55] 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/51665

2661 Evgeny Potemkin	2008-08-14
      Fixed failing test case for the bug#38195.
[14 Aug 2008 20:00] 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/51666

2661 Evgeny Potemkin	2008-08-14
      Fixed failing test case for the bug#38195.
[19 Aug 2008 10:38] 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/51932

2669 Georgi Kodinov	2008-08-19
      Bug#38195: Incorrect handling of aggregate functions when loose index scan
      is used causes server crash.
        Revert the fix : unstable test case revealed by pushbuild
[19 Aug 2008 10:40] 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/51933

2669 Georgi Kodinov	2008-08-19
      Bug#38195: Incorrect handling of aggregate functions when loose index scan
      is used causes server crash.
        Revert the fix : unstable test case revealed by pushbuild
[21 Aug 2008 18:09] Bugs System
Pushed into 5.1.28  (revid:kgeorge@mysql.com-20080819103624-t90ec2jvvbm7su6t) (version source revid:sergefp@mysql.com-20080819132519-eimtstp3bx89ya9d) (pib:3)
[27 Aug 2008 13:04] 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/52702

2678 Evgeny Potemkin	2008-08-27
      Bug#38195: Incorrect handling of aggregate functions when loose index scan is
      used causes server crash.
            
      When the loose index scan access method is used values of aggregated functions
      are precomputed by it. Aggregation of such functions shouldn't be performed
      in this case and functions should be treated as normal ones.
      The create_tmp_table function wasn't taking this into account and this led to
      a crash if a query has MIN/MAX aggregate functions and employs temporary table
      and loose index scan.
      Now the JOIN::exec and the create_tmp_table functions treat MIN/MAX aggregate
      functions as normal ones when the loose index scan is used.
[27 Aug 2008 13:21] 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/52705

2678 Evgeny Potemkin	2008-08-27
      Bug#38195: Incorrect handling of aggregate functions when loose index scan is
      used causes server crash.
            
      When the loose index scan access method is used values of aggregated functions
      are precomputed by it. Aggregation of such functions shouldn't be performed
      in this case and functions should be treated as normal ones.
      The create_tmp_table function wasn't taking this into account and this led to
      a crash if a query has MIN/MAX aggregate functions and employs temporary table
      and loose index scan.
      Now the JOIN::exec and the create_tmp_table functions treat MIN/MAX aggregate
      functions as normal ones when the loose index scan is used.
[28 Aug 2008 11:05] Bugs System
Pushed into 5.1.28  (revid:epotemkin@mysql.com-20080827130317-1et3jr5v2qs06w47) (version source revid:kgeorge@mysql.com-20080828095450-xpq0biuyihkqgb05) (pib:3)
[28 Aug 2008 12:19] 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/52837
[28 Aug 2008 12:20] Georgi Kodinov
Pushed in 5.0.70
[2 Sep 2008 18:46] Paul Dubois
Noted in 5.0.70, 5.1.28 changelogs.

Incorrect handling of aggregate functions when loose index scan was
used caused a server crash.

Setting report to NDI pending push into 6.0.x.
[13 Sep 2008 19:59] Bugs System
Pushed into 6.0.6-alpha  (revid:epotemkin@mysql.com-20080814195518-h7bfkqm9uaztix03) (version source revid:sergefp@mysql.com-20080611231653-nmuqmw6dedjra79i) (pib:3)
[16 Sep 2008 14:41] Paul Dubois
Noted in 6.0.6 changelog.
[27 Nov 2008 16:03] Timour Katchaounov
I move back to "in progress" because the EXPLAIN added to the test case
appears after the actual statement, and after inserting one extra row.
As a result, the query is executed with a different plan compared to
the EXPLAIN plan. Even worse, the query is not executed with the strategy
that needs to be tested.
[27 Nov 2008 16:07] Georgi Kodinov
Timour,

please open another bug report for this
[27 Nov 2008 16:09] Timour Katchaounov
Bug#40884 is a duplicate of this bug, please make sure to test specifically
the case described in Bug#40884.
[15 Dec 2008 21: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/61718

2809 First_name Last_name	2008-12-15
      Bug#40884: Failure of the test case for the bug#38195.
      
      Fix for the bug#38195 was removed by an incorrect merge.
      
      Fix is restored.
[20 Jan 2009 18:53] Bugs System
Pushed into 6.0.10-alpha (revid:joro@sun.com-20090119171328-2hemf2ndc1dxl0et) (version source revid:azundris@mysql.com-20081230114916-c290n83z25wkt6e4) (merge vers: 6.0.9-alpha) (pib:6)