[MDEV-3992] Server crash or valgrind errors in test_if_skip_sort_order/test_if_cheaper_ordering on GROUP BY with indexes on InnoDB table Created: 2012-12-29  Updated: 2013-08-16  Resolved: 2013-08-16

Status: Closed
Project: MariaDB Server
Component/s: None
Affects Version/s: 10.0.0, 5.5.28a, 5.3.11, 5.2.13, 5.1.66
Fix Version/s: 10.0.4, 5.5.29

Type: Bug Priority: Major
Reporter: Elena Stepanova Assignee: Igor Babaev
Resolution: Fixed Votes: 0
Labels: None

Issue Links:
Relates

 Description   

The following test case 

--source include/have_innodb.inc
 
 
 
CREATE TABLE t1 (
 
  pk INT PRIMARY KEY,
 
  a VARCHAR(1) NOT NULL,
 
  KEY (pk)
 
) ENGINE=InnoDB;
 
 
 
INSERT INTO t1 VALUES (1,'a'),(2,'b');
 
 
 
SELECT COUNT(*), pk field1, pk AS field2 
FROM t1 WHERE a = 'r' OR pk = 183 
GROUP BY field1, field2;

causes either server crash or valgrind warnings on all of maria/5.1, maria/5.2, maria/5.3, maria/5.5, maria/10.0-base, both with InnoDB and XtraDB.

Reproducible both with default optimizer_switch and with all OFF values.

Could not reproduce with MyISAM.
Could not reproduce on MySQL 5.1, 5.5, 5.6.

Crash (stack trace from 5.5):

#3  <signal handler called>
 
#4  0x000000000067ad67 in test_if_cheaper_ordering (tab=0x2d5a258, order=0x2d71bf8, table=0x2d54610, usable_keys=..., ref_key=-1, select_limit_arg=18446744073709551615, new_key=0x7f54fada4564, new_key_direction=0x7f54fada4570, new_select_limit=0x7f54fada44b8, new_used_key_parts=0x7f54fada4568, saved_best_key_parts=0x7f54fada456c) at maria-5.5/sql/sql_select.cc:23012
 
#5  0x000000000066eec0 in test_if_skip_sort_order (tab=0x2d5a258, order=0x2d71bf8, select_limit=18446744073709551615, no_changes=false, map=0x2d54698) at maria-5.5/sql/sql_select.cc:18625
 
#6  0x000000000064463b in JOIN::optimize (this=0x2d537a8) at maria-5.5/sql/sql_select.cc:1682
 
#7  0x0000000000649255 in mysql_select (thd=0x2c63050, rref_pointer_array=0x2c65e20, tables=0x2d779d8, wild_num=0, fields=..., conds=0x2d561b8, og_num=2, order=0x0, group=0x2d71bf8, having=0x0, proc_param=0x0, select_options=2147748608, result=0x2c8bcd8, unit=0x2c65480, select_lex=0x2c65b78) at maria-5.5/sql/sql_select.cc:3040
 
#8  0x000000000063fd3f in handle_select (thd=0x2c63050, lex=0x2c653d0, result=0x2c8bcd8, setup_tables_done_option=0) at maria-5.5/sql/sql_select.cc:316
 
#9  0x000000000061825b in execute_sqlcom_select (thd=0x2c63050, all_tables=0x2d779d8) at maria-5.5/sql/sql_parse.cc:4622
 
#10 0x0000000000610d1d in mysql_execute_command (thd=0x2c63050) at maria-5.5/sql/sql_parse.cc:2182
 
#11 0x000000000061aac3 in mysql_parse (thd=0x2c63050, rawbuf=0x2d56e88 "SELECT COUNT(*), pk field1, pk AS field2 \nFROM t1 WHERE a = 'r' OR pk = 183 \nGROUP BY field1, field2", length=100, parser_state=0x7f54fada54f0) at maria-5.5/sql/sql_parse.cc:5737
 
#12 0x000000000060e290 in dispatch_command (command=COM_QUERY, thd=0x2c63050, packet=0x2d3cd81 "SELECT COUNT(*), pk field1, pk AS field2 \nFROM t1 WHERE a = 'r' OR pk = 183 \nGROUP BY field1, field2", packet_length=100) at maria-5.5/sql/sql_parse.cc:1055
 
#13 0x000000000060d547 in do_command (thd=0x2c63050) at maria-5.5/sql/sql_parse.cc:794
 
#14 0x0000000000715a45 in do_handle_one_connection (thd_arg=0x2c63050) at maria-5.5/sql/sql_connect.cc:1253
 
#15 0x0000000000715430 in handle_one_connection (arg=0x2c63050) at maria-5.5/sql/sql_connect.cc:1168
 
#16 0x0000000000bb7c11 in pfs_spawn_thread (arg=0x2d964e0) at maria-5.5/storage/perfschema/pfs.cc:1015
 
#17 0x00007f5511266efc in start_thread (arg=0x7f54fada6700) at pthread_create.c:304
 
#18 0x00007f55105d8f4d in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:112

Valgrind warnings:

==26562== Thread 14:
 
==26562== Conditional jump or move depends on uninitialised value(s)
 
==26562==    at 0x67AB90: test_if_cheaper_ordering(st_join_table const*, st_order*, TABLE*, Bitmap<64u>, int, unsigned long long, int*, int*, unsigned long long*, unsigned int*, unsigned int*) (sql_select.cc:22981)
 
==26562==    by 0x66EEBF: test_if_skip_sort_order(st_join_table*, st_order*, unsigned long long, bool, Bitmap<64u> const*) (sql_select.cc:18625)
 
==26562==    by 0x64463A: JOIN::optimize() (sql_select.cc:1682)
 
==26562==    by 0x649254: mysql_select(THD*, Item***, TABLE_LIST*, unsigned int, List<Item>&, Item*, unsigned int, st_order*, st_order*, Item*, st_order*, unsigned long long, select_result*, st_select_lex_unit*, st_select_lex*) (sql_select.cc:3040)
 
==26562==    by 0x63FD3E: handle_select(THD*, LEX*, select_result*, unsigned long) (sql_select.cc:316)
 
==26562==    by 0x61825A: execute_sqlcom_select(THD*, TABLE_LIST*) (sql_parse.cc:4622)
 
==26562==    by 0x610D1C: mysql_execute_command(THD*) (sql_parse.cc:2182)
 
==26562==    by 0x61AAC2: mysql_parse(THD*, char*, unsigned int, Parser_state*) (sql_parse.cc:5737)
 
==26562==    by 0x60E28F: dispatch_command(enum_server_command, THD*, char*, unsigned int) (sql_parse.cc:1055)
 
==26562==    by 0x60D546: do_command(THD*) (sql_parse.cc:794)
 
==26562==    by 0x715A44: do_handle_one_connection(THD*) (sql_connect.cc:1253)
 
==26562==    by 0x71542F: handle_one_connection (sql_connect.cc:1168)
 
==26562==    by 0xBB7C10: pfs_spawn_thread (pfs.cc:1015)
 
==26562==    by 0x5458EFB: start_thread (pthread_create.c:304)
 
==26562==    by 0x611AF4C: clone (clone.S:112)
 
==26562== Conditional jump or move depends on uninitialised value(s)
 
==26562==    at 0x67AC47: test_if_cheaper_ordering(st_join_table const*, st_order*, TABLE*, Bitmap<64u>, int, unsigned long long, int*, int*, unsigned long long*, unsigned int*, unsigned int*) (sql_select.cc:22994)
 
==26562==    by 0x66EEBF: test_if_skip_sort_order(st_join_table*, st_order*, unsigned long long, bool, Bitmap<64u> const*) (sql_select.cc:18625)
 
==26562==    by 0x64463A: JOIN::optimize() (sql_select.cc:1682)
 
==26562==    by 0x649254: mysql_select(THD*, Item***, TABLE_LIST*, unsigned int, List<Item>&, Item*, unsigned int, st_order*, st_order*, Item*, st_order*, unsigned long long, select_result*, st_select_lex_unit*, st_select_lex*) (sql_select.cc:3040)
 
==26562==    by 0x63FD3E: handle_select(THD*, LEX*, select_result*, unsigned long) (sql_select.cc:316)
 
==26562==    by 0x61825A: execute_sqlcom_select(THD*, TABLE_LIST*) (sql_parse.cc:4622)
 
==26562==    by 0x610D1C: mysql_execute_command(THD*) (sql_parse.cc:2182)
 
==26562==    by 0x61AAC2: mysql_parse(THD*, char*, unsigned int, Parser_state*) (sql_parse.cc:5737)
 
==26562==    by 0x60E28F: dispatch_command(enum_server_command, THD*, char*, unsigned int) (sql_parse.cc:1055)
 
==26562==    by 0x60D546: do_command(THD*) (sql_parse.cc:794)
 
==26562==    by 0x715A44: do_handle_one_connection(THD*) (sql_connect.cc:1253)
 
==26562==    by 0x71542F: handle_one_connection (sql_connect.cc:1168)
 
==26562==    by 0xBB7C10: pfs_spawn_thread (pfs.cc:1015)
 
==26562==    by 0x5458EFB: start_thread (pthread_create.c:304)
 
==26562==    by 0x611AF4C: clone (clone.S:112)
 
==26562== Conditional jump or move depends on uninitialised value(s)
 
==26562==    at 0x67AE0C: test_if_cheaper_ordering(st_join_table const*, st_order*, TABLE*, Bitmap<64u>, int, unsigned long long, int*, int*, unsigned long long*, unsigned int*, unsigned int*) (sql_select.cc:23018)
 
==26562==    by 0x66EEBF: test_if_skip_sort_order(st_join_table*, st_order*, unsigned long long, bool, Bitmap<64u> const*) (sql_select.cc:18625)
 
==26562==    by 0x64463A: JOIN::optimize() (sql_select.cc:1682)
 
==26562==    by 0x649254: mysql_select(THD*, Item***, TABLE_LIST*, unsigned int, List<Item>&, Item*, unsigned int, st_order*, st_order*, Item*, st_order*, unsigned long long, select_result*, st_select_lex_unit*, st_select_lex*) (sql_select.cc:3040)
 
==26562==    by 0x63FD3E: handle_select(THD*, LEX*, select_result*, unsigned long) (sql_select.cc:316)
 
==26562==    by 0x61825A: execute_sqlcom_select(THD*, TABLE_LIST*) (sql_parse.cc:4622)
 
==26562==    by 0x610D1C: mysql_execute_command(THD*) (sql_parse.cc:2182)
 
==26562==    by 0x61AAC2: mysql_parse(THD*, char*, unsigned int, Parser_state*) (sql_parse.cc:5737)
 
==26562==    by 0x60E28F: dispatch_command(enum_server_command, THD*, char*, unsigned int) (sql_parse.cc:1055)
 
==26562==    by 0x60D546: do_command(THD*) (sql_parse.cc:794)
 
==26562==    by 0x715A44: do_handle_one_connection(THD*) (sql_connect.cc:1253)
 
==26562==    by 0x71542F: handle_one_connection (sql_connect.cc:1168)
 
==26562==    by 0xBB7C10: pfs_spawn_thread (pfs.cc:1015)
 
==26562==    by 0x5458EFB: start_thread (pthread_create.c:304)
 
==26562==    by 0x611AF4C: clone (clone.S:112)
 
==26562== Conditional jump or move depends on uninitialised value(s)
 
==26562==    at 0x67AB90: test_if_cheaper_ordering(st_join_table const*, st_order*, TABLE*, Bitmap<64u>, int, unsigned long long, int*, int*, unsigned long long*, unsigned int*, unsigned int*) (sql_select.cc:22981)
 
==26562==    by 0x66EEBF: test_if_skip_sort_order(st_join_table*, st_order*, unsigned long long, bool, Bitmap<64u> const*) (sql_select.cc:18625)
 
==26562==    by 0x66FB1D: create_sort_index(THD*, JOIN*, st_order*, unsigned long long, unsigned long long, bool) (sql_select.cc:18968)
 
==26562==    by 0x6488A8: JOIN::exec() (sql_select.cc:2804)
 
==26562==    by 0x6492E6: mysql_select(THD*, Item***, TABLE_LIST*, unsigned int, List<Item>&, Item*, unsigned int, st_order*, st_order*, Item*, st_order*, unsigned long long, select_result*, st_select_lex_unit*, st_select_lex*) (sql_select.cc:3054)
 
==26562==    by 0x63FD3E: handle_select(THD*, LEX*, select_result*, unsigned long) (sql_select.cc:316)
 
==26562==    by 0x61825A: execute_sqlcom_select(THD*, TABLE_LIST*) (sql_parse.cc:4622)
 
==26562==    by 0x610D1C: mysql_execute_command(THD*) (sql_parse.cc:2182)
 
==26562==    by 0x61AAC2: mysql_parse(THD*, char*, unsigned int, Parser_state*) (sql_parse.cc:5737)
 
==26562==    by 0x60E28F: dispatch_command(enum_server_command, THD*, char*, unsigned int) (sql_parse.cc:1055)
 
==26562==    by 0x60D546: do_command(THD*) (sql_parse.cc:794)
 
==26562==    by 0x715A44: do_handle_one_connection(THD*) (sql_connect.cc:1253)
 
==26562==    by 0x71542F: handle_one_connection (sql_connect.cc:1168)
 
==26562==    by 0xBB7C10: pfs_spawn_thread (pfs.cc:1015)
 
==26562==    by 0x5458EFB: start_thread (pthread_create.c:304)
 
==26562==    by 0x611AF4C: clone (clone.S:112)
 
==26562== Conditional jump or move depends on uninitialised value(s)
 
==26562==    at 0x67AC47: test_if_cheaper_ordering(st_join_table const*, st_order*, TABLE*, Bitmap<64u>, int, unsigned long long, int*, int*, unsigned long long*, unsigned int*, unsigned int*) (sql_select.cc:22994)
 
==26562==    by 0x66EEBF: test_if_skip_sort_order(st_join_table*, st_order*, unsigned long long, bool, Bitmap<64u> const*) (sql_select.cc:18625)
 
==26562==    by 0x66FB1D: create_sort_index(THD*, JOIN*, st_order*, unsigned long long, unsigned long long, bool) (sql_select.cc:18968)
 
==26562==    by 0x6488A8: JOIN::exec() (sql_select.cc:2804)
 
==26562==    by 0x6492E6: mysql_select(THD*, Item***, TABLE_LIST*, unsigned int, List<Item>&, Item*, unsigned int, st_order*, st_order*, Item*, st_order*, unsigned long long, select_result*, st_select_lex_unit*, st_select_lex*) (sql_select.cc:3054)
 
==26562==    by 0x63FD3E: handle_select(THD*, LEX*, select_result*, unsigned long) (sql_select.cc:316)
 
==26562==    by 0x61825A: execute_sqlcom_select(THD*, TABLE_LIST*) (sql_parse.cc:4622)
 
==26562==    by 0x610D1C: mysql_execute_command(THD*) (sql_parse.cc:2182)
 
==26562==    by 0x61AAC2: mysql_parse(THD*, char*, unsigned int, Parser_state*) (sql_parse.cc:5737)
 
==26562==    by 0x60E28F: dispatch_command(enum_server_command, THD*, char*, unsigned int) (sql_parse.cc:1055)
 
==26562==    by 0x60D546: do_command(THD*) (sql_parse.cc:794)
 
==26562==    by 0x715A44: do_handle_one_connection(THD*) (sql_connect.cc:1253)
 
==26562==    by 0x71542F: handle_one_connection (sql_connect.cc:1168)
 
==26562==    by 0xBB7C10: pfs_spawn_thread (pfs.cc:1015)
 
==26562==    by 0x5458EFB: start_thread (pthread_create.c:304)
 
==26562==    by 0x611AF4C: clone (clone.S:112)
 
==26562== Conditional jump or move depends on uninitialised value(s)
 
==26562==    at 0x67AE0C: test_if_cheaper_ordering(st_join_table const*, st_order*, TABLE*, Bitmap<64u>, int, unsigned long long, int*, int*, unsigned long long*, unsigned int*, unsigned int*) (sql_select.cc:23018)
 
==26562==    by 0x66EEBF: test_if_skip_sort_order(st_join_table*, st_order*, unsigned long long, bool, Bitmap<64u> const*) (sql_select.cc:18625)
 
==26562==    by 0x66FB1D: create_sort_index(THD*, JOIN*, st_order*, unsigned long long, unsigned long long, bool) (sql_select.cc:18968)
 
==26562==    by 0x6488A8: JOIN::exec() (sql_select.cc:2804)
 
==26562==    by 0x6492E6: mysql_select(THD*, Item***, TABLE_LIST*, unsigned int, List<Item>&, Item*, unsigned int, st_order*, st_order*, Item*, st_order*, unsigned long long, select_result*, st_select_lex_unit*, st_select_lex*) (sql_select.cc:3054)
 
==26562==    by 0x63FD3E: handle_select(THD*, LEX*, select_result*, unsigned long) (sql_select.cc:316)
 
==26562==    by 0x61825A: execute_sqlcom_select(THD*, TABLE_LIST*) (sql_parse.cc:4622)
 
==26562==    by 0x610D1C: mysql_execute_command(THD*) (sql_parse.cc:2182)
 
==26562==    by 0x61AAC2: mysql_parse(THD*, char*, unsigned int, Parser_state*) (sql_parse.cc:5737)
 
==26562==    by 0x60E28F: dispatch_command(enum_server_command, THD*, char*, unsigned int) (sql_parse.cc:1055)
 
==26562==    by 0x60D546: do_command(THD*) (sql_parse.cc:794)
 
==26562==    by 0x715A44: do_handle_one_connection(THD*) (sql_connect.cc:1253)
 
==26562==    by 0x71542F: handle_one_connection (sql_connect.cc:1168)
 
==26562==    by 0xBB7C10: pfs_spawn_thread (pfs.cc:1015)
 
==26562==    by 0x5458EFB: start_thread (pthread_create.c:304)
 
==26562==    by 0x611AF4C: clone (clone.S:112)
 
 
 

revision-id: sergii@pisem.net-20121206163022-iyc7rupgh2oete89
 
date: 2012-12-06 17:30:22 +0100
 
revno: 3604
 
branch: maria/5.5



 Comments   
Comment by Patryk Pomykalski [ 2013-01-05 ]

I took a moment to analyze this bug and checked the difference between mariadb 5.5 and mysql 5.6. It looks like an edge case of sorting by the same column twice. test_if_order_by_key function returns that there should be 2 key parts used (1 from index, 1 from primary key) even though the index has only 1 part same as pk. This problem doesn't occur in mysql 5.6 because the duplication in orderby is removed by function duplicate_order() in sql/sql_optimizer.cc. I think it would be easiest to backport that function from mysql.

Comment by Timour Katchaounov (Inactive) [ 2013-01-07 ]

A bit simpler query:

SELECT COUNT, pk field1 FROM t1 WHERE a = 'r' OR pk = 183 GROUP BY field1, field1;

Comment by Timour Katchaounov (Inactive) [ 2013-01-07 ]

explain SELECT COUNT, pk field1, pk AS field2 FROM t1 WHERE a = 'r' OR pk = 183 GROUP BY field1, field2;

MySQL 5.5:
--------------------------------------------------------------------+

id select_type table type possible_keys key key_len ref rows Extra

--------------------------------------------------------------------+

1 SIMPLE t1 index PRIMARY,pk pk 4 NULL 2 Using where

--------------------------------------------------------------------+

MySQL 5.6:
-----------------------------------------------------------------------+

id select_type table type possible_keys key key_len ref rows Extra

-----------------------------------------------------------------------+

1 SIMPLE t1 index PRIMARY,pk PRIMARY 4 NULL 2 Using where

-----------------------------------------------------------------------+

Comment by Timour Katchaounov (Inactive) [ 2013-01-11 ]

The following commits and discussions are relevant for this bug:
https://lists.launchpad.net/maria-developers/msg03145.html
http://lists.askmonty.org/pipermail/commits/2010-May/000039.html

Comment by Timour Katchaounov (Inactive) [ 2013-01-14 ]

Pushed to 5.5

Comment by Timour Katchaounov (Inactive) [ 2013-01-16 ]

assigned to igor because he wants to fix it in 5.1

Generated at Thu Feb 08 06:52:53 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.