[MDEV-6129] Server crashes during UNION with ORDER BY field IS NULL Created: 2014-04-17  Updated: 2014-04-26  Resolved: 2014-04-24

Status: Closed
Project: MariaDB Server
Component/s: None
Affects Version/s: 5.5.36, 5.5.37, 10.0.10
Fix Version/s: 5.5.38, 10.0.11

Type: Bug Priority: Critical
Reporter: Cary Converse Assignee: Michael Widenius
Resolution: Fixed Votes: 0
Labels: None


 Description   

Queries such as the following will crash the MariaDb server:

SELECT 1 AS test UNION SELECT 2 AS test ORDER BY test IS NULL ASC;



 Comments   
Comment by Elena Stepanova [ 2014-04-21 ]

Hi,

Could you please provide your my.ini file(s) and specify which build you are using (msi package, zip package, custom build from source)?

Please also quote the error log or console output, whatever they say upon the crash.

Comment by Cary Converse [ 2014-04-21 ]

I'm using the 64-bit build from the msi package.

The ini file is:

[client]
port=3306

[mysql]
default-character-set=latin1

[mysqld]
port=3306
datadir="E:\MariaDB\Data"
character-set-client-handshake=FALSE
character-set-server=latin1
default-storage-engine=INNODB
max_connections=1000
query_cache_size=0M
table_cache=100
tmp_table_size=100M
group_concat_max_len=1000000
thread_cache_size=8
event_scheduler=ON
innodb_additional_mem_pool_size=5M
innodb_flush_log_at_trx_commit=0
innodb_log_buffer_size=50M
innodb_buffer_pool_size=2048M
innodb_log_file_size=1024M
innodb_thread_concurrency=8
innodb_file_per_table = 1
innodb_support_xa = 0
innodb_autoinc_lock_mode = 0
skip-name-resolve
sql_mode='NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ONLY_FULL_GROUP_BY'

As for the error log, nothing seems to be written to it upon the crash - only when the service is restarted, it'll say "140421 10:30:47 InnoDB: Database was not shut down normally!"
On the console:

MariaDB [(none)]> SELECT 1 AS test UNION SELECT 2 AS test ORDER BY test IS NULL ASC;
ERROR 2013 (HY000): Lost connection to MySQL server during query

Comment by Elena Stepanova [ 2014-04-21 ]

Thank you, I was able to reproduce it with your config, the culprit was ONLY_FULL_GROUP_BY.

Comment by Elena Stepanova [ 2014-04-21 ]

The problem was introduced in 5.5 tree with the following revision:

revno: 4063
 
revision-id: monty@askmonty.org-20140211145849-zosjbz0pbh9bngmj
 
parent: monty@askmonty.org-20140211145728-16tf8mygvqb8b2u5
 
committer: Michael Widenius <monty@askmonty.org>
 
branch nick: maria-5.5
 
timestamp: Tue 2014-02-11 16:58:49 +0200
 
message:
 
  Fixed MDEV-5617: mysqld crashes when running a query with ONLY_FULL_GROUP_BY
 
  Problem was that we used cache_table in some cases where it was not initialized

Test case is the same 1-line as in the description, only executed with sql_mode='ONLY_FULL_GROUP_BY':

SET sql_mode='ONLY_FULL_GROUP_BY';
 
SELECT 1 AS test UNION SELECT 2 AS test ORDER BY test IS NULL ASC;

Stack trace (from the current 5.5, revno 4159):

#3  <signal handler called>
 
#4  0x000000000069df8f in st_select_lex::set_non_agg_field_used (this=0x0, val=true) at 5.5/sql/sql_lex.h:1043
 
#5  0x00000000007fe37b in Item_field::fix_fields (this=0x7f0dc69489c8, thd=0x7f0dca749060, reference=0x7f0dc6948b58) at 5.5/sql/item.cc:5286
 
#6  0x000000000083cf30 in Item_func::fix_fields (this=0x7f0dc6948ac8, thd=0x7f0dca749060, ref=0x7f0dc6948b98) at 5.5/sql/item_func.cc:204
 
#7  0x0000000000691eed in find_order_in_list (thd=0x7f0dca749060, ref_pointer_array=0x7f0dc695b078, tables=0x7f0dca74c3d0, order=0x7f0dc6948b88, fields=..., all_fields=..., is_group_field=false) at 5.5/sql/sql_select.cc:20458
 
#8  0x0000000000692008 in setup_order (thd=0x7f0dca749060, ref_pointer_array=0x7f0dc695b078, tables=0x7f0dca74c3d0, fields=..., all_fields=..., order=0x7f0dc6948b88) at 5.5/sql/sql_select.cc:20484
 
#9  0x00000000006a0750 in setup_without_group (thd=0x7f0dca749060, ref_pointer_array=0x7f0dc695b078, tables=0x7f0dca74c3d0, leaves=..., fields=..., all_fields=..., conds=0x7f0dc6949f00, order=0x7f0dc6948b88, group=0x0, hidden_group_fields=0x7f0dc6949de8) at 5.5/sql/sql_select.cc:584
 
#10 0x000000000065fcd2 in JOIN::prepare (this=0x7f0dc6949ae8, rref_pointer_array=0x7f0dc6948808, tables_init=0x7f0dca74c3d0, wild_num=0, conds_init=0x0, og_num=1, order_init=0x7f0dc6948b88, skip_order_by=false, group_init=0x0, having_init=0x0, proc_param_init=0x0, select_lex_arg=0x7f0dc6948598, unit_arg=0x7f0dca74c380) at 5.5/sql/sql_select.cc:725
 
#11 0x0000000000668204 in mysql_select (thd=0x7f0dca749060, rref_pointer_array=0x7f0dc6948808, tables=0x7f0dca74c3d0, wild_num=0, fields=..., conds=0x0, og_num=1, order=0x7f0dc6948b88, group=0x0, having=0x0, proc_param=0x0, select_options=268435456, result=0x7f0dc6948c58, unit=0x7f0dca74c380, select_lex=0x7f0dc6948598) at 5.5/sql/sql_select.cc:3043
 
#12 0x00000000006e1d30 in st_select_lex_unit::exec (this=0x7f0dca74c380) at 5.5/sql/sql_union.cc:801
 
#13 0x00000000006df528 in mysql_union (thd=0x7f0dca749060, lex=0x7f0dca74c2d0, result=0x7f0dc6948c58, unit=0x7f0dca74c380, setup_tables_done_option=0) at 5.5/sql/sql_union.cc:38
 
#14 0x000000000065ee24 in handle_select (thd=0x7f0dca749060, lex=0x7f0dca74c2d0, result=0x7f0dc6948c58, setup_tables_done_option=0) at 5.5/sql/sql_select.cc:297
 
#15 0x0000000000638521 in execute_sqlcom_select (thd=0x7f0dca749060, all_tables=0x0) at 5.5/sql/sql_parse.cc:4689
 
#16 0x00000000006316d9 in mysql_execute_command (thd=0x7f0dca749060) at 5.5/sql/sql_parse.cc:2233
 
#17 0x000000000063ac30 in mysql_parse (thd=0x7f0dca749060, rawbuf=0x7f0dc6948078 "SELECT 1 AS test UNION SELECT 2 AS test ORDER BY test IS NULL ASC", length=65, parser_state=0x7f0dc73b5630) at 5.5/sql/sql_parse.cc:5800
 
#18 0x000000000062ec51 in dispatch_command (command=COM_QUERY, thd=0x7f0dca749060, packet=0x7f0dc7afa061 "SELECT 1 AS test UNION SELECT 2 AS test ORDER BY test IS NULL ASC", packet_length=65) at 5.5/sql/sql_parse.cc:1079
 
#19 0x000000000062dddd in do_command (thd=0x7f0dca749060) at 5.5/sql/sql_parse.cc:793
 
#20 0x000000000072f51e in do_handle_one_connection (thd_arg=0x7f0dca749060) at 5.5/sql/sql_connect.cc:1266
 
#21 0x000000000072efdd in handle_one_connection (arg=0x7f0dca749060) at 5.5/sql/sql_connect.cc:1181
 
#22 0x0000000000b61a59 in pfs_spawn_thread (arg=0x7f0dc87fa8e0) at 5.5/storage/perfschema/pfs.cc:1015
 
#23 0x00007f0dcd442b50 in start_thread (arg=<optimized out>) at pthread_create.c:304
 
#24 0x00007f0dcbf91a7d in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:112

Comment by Michael Widenius [ 2014-04-24 ]

Fixed and pushed into MariaDB 5.5

Problem was with queries without any real tables, in which case table_list->pos_in_tables did not include all expected fields.

Comment by Cary Converse [ 2014-04-24 ]

Just to note - I've also been able to reproduce this bug on queries with real tables.

CREATE TABLE test (a int unsigned, b int unsigned) ENGINE = InnoDb;
 
SELECT a,b FROM test WHERE a = 1
 
UNION
 
SELECT a,b FROM test WHERE a = 2
 
ORDER BY a IS NULL;

Comment by Elena Stepanova [ 2014-04-26 ]

Yes, the patch fixes this variation of the problem as well.

Generated at Thu Feb 08 07:09:34 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.