[MDEV-16930] Crash when VALUES in derived table contains expressions Created: 2018-08-09  Updated: 2018-08-27  Resolved: 2018-08-27

Status: Closed
Project: MariaDB Server
Component/s: Data Manipulation - Subquery, Server
Affects Version/s: 10.3.7, 10.3.8
Fix Version/s: 10.3.10

Type: Bug Priority: Critical
Reporter: Markus Winand Assignee: Igor Babaev
Resolution: Fixed Votes: 0
Labels: None
Environment:

Linux (Ubuntu)


 Description   

Running the following query crashes the server:

SELECT * FROM (VALUES(1+1,2)) t;

It seems that the combination of VALUES in a derived table plus expression (as opposed to literal values) causes the problem.

The following crashes too:

SELECT * FROM (VALUES(now(),2)) t

However, these don't crash:

VALUES (now(), 2);
 
VALUES (1+2, 2);
 
SELECT * FROM (VALUES(1,2)) t;

Reproducible on head of 10.3 branch (340c8a2a32dcbe0bb9bc88bd0a6bda5d5e76ed02).

From the logs:

180809 16:08:02 [ERROR] 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.
 
 
 
To report this bug, see https://mariadb.com/kb/en/reporting-bugs
 
 
 
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.
 
 
 
Server version: 10.3.9-MariaDB
 
key_buffer_size=134217728
 
read_buffer_size=131072
 
max_used_connections=1
 
max_threads=153
 
thread_count=7
 
It is possible that mysqld could use up to
 
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 467396 K  bytes of memory
 
Hope that's ok; if not, decrease some variables in the equation.
 
 
 
Thread pointer: 0x7fda140009a8
 
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 = 0x7fda84191cf8 thread_stack 0x49000
 
/opt/mariadb/master/bin/mysqld(my_print_stacktrace+0x2e)[0x56324db7026e]
 
mysys/stacktrace.c:270(my_print_stacktrace)[0x56324d616097]
 
/lib/x86_64-linux-gnu/libpthread.so.0(+0x11390)[0x7fda873bb390]
 
/opt/mariadb/master/bin/mysqld(+0xd3c2eb)[0x56324dbfd2eb]
 
strings/ctype-utf8.c:5304(my_strcasecmp_utf8)[0x56324d4db4a5]
 
sql/sql_view.cc:143(check_duplicate_names(THD*, List<Item>&, bool))[0x56324d4018ad]
 
sql/sql_derived.cc:772(mysql_derived_prepare(THD*, LEX*, TABLE_LIST*))[0x56324d401fe8]
 
sql/sql_derived.cc:197(mysql_handle_single_derived(LEX*, TABLE_LIST*, unsigned int))[0x56324d41cd17]
 
sql/sql_lex.cc:4073(st_select_lex::handle_derived(LEX*, unsigned int))[0x56324d481269]
 
sql/sql_select.cc:997(JOIN::prepare(TABLE_LIST*, unsigned int, Item*, unsigned int, st_order*, bool, st_order*, Item*, st_order*, st_select_lex*, st_select_lex_unit*))[0x56324d49137c]
 
sql/sql_select.cc:4205(mysql_select(THD*, 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*))[0x56324d4914d8]
 
sql/sql_select.cc:382(handle_select(THD*, LEX*, select_result*, unsigned long))[0x56324d352d64]
 
sql/sql_parse.cc:6548(execute_sqlcom_select(THD*, TABLE_LIST*) [clone .constprop.240])[0x56324d43e6fd]
 
sql/sql_parse.cc:3768(mysql_execute_command(THD*))[0x56324d44068a]
 
sql/sql_parse.cc:8088(mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool))[0x56324d443051]
 
sql/sql_parse.cc:1850(dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool))[0x56324d443b3c]
 
sql/sql_parse.cc:1397(do_command(THD*))[0x56324d5146c2]
 
sql/sql_connect.cc:1402(do_handle_one_connection(CONNECT*))[0x56324d51483d]
 
/lib/x86_64-linux-gnu/libpthread.so.0(+0x76ba)[0x7fda873b16ba]
 
x86_64/clone.S:111(clone)[0x7fda86a5c41d]
 
 
 
Trying to get some variables.
 
Some pointers may be invalid and cause the dump to abort.
 
Query (0x7fda140145f0): is an invalid pointer
 
Connection ID (thread ID): 8
 
Status: NOT_KILLED
 
 
 
Optimizer switch: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on
 
 
 
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.
 
 
 
We think the query pointer is invalid, but we will try to print it anyway.
 
Query: SELECT * FROM (VALUES(1,now())) t



 Comments   
Comment by Elena Stepanova [ 2018-08-09 ]

Thanks for the report! Reproducible as described.

SELECT * FROM (VALUES(1+1,2)) t;

10.3 482d4da0a7a

 
#3  <signal handler called>
 
#4  0x000055a204d1eb3d in my_strcasecmp_utf8 (cs=0x55a205982e80 <my_charset_utf8_general_ci>, s=0x7fe6ac015248 "2", t=0x0) at /data/src/10.3/strings/ctype-utf8.c:5304
 
#5  0x000055a204336cea in lex_string_cmp (charset=0x55a205982e80 <my_charset_utf8_general_ci>, a=0x7fe6ac0171a0, b=0x7fe6ac0170f8) at /data/src/10.3/sql/lex_string.h:28
 
#6  0x000055a2043370cb in check_duplicate_names (thd=0x7fe6ac000b00, item_list=..., gen_unique_view_name=false) at /data/src/10.3/sql/sql_view.cc:143
 
#7  0x000055a2041ec8a2 in mysql_derived_prepare (thd=0x7fe6ac000b00, lex=0x7fe6ac0048e0, derived=0x7fe6ac015f10) at /data/src/10.3/sql/sql_derived.cc:772
 
#8  0x000055a2041eb609 in mysql_handle_single_derived (lex=0x7fe6ac0048e0, derived=0x7fe6ac015f10, phases=2) at /data/src/10.3/sql/sql_derived.cc:197
 
#9  0x000055a204355dbe in TABLE_LIST::handle_derived (this=0x7fe6ac015f10, lex=0x7fe6ac0048e0, phases=2) at /data/src/10.3/sql/table.cc:8149
 
#10 0x000055a20420ec32 in st_select_lex::handle_derived (this=0x7fe6ac005118, lex=0x7fe6ac0048e0, phases=2) at /data/src/10.3/sql/sql_lex.cc:4073
 
#11 0x000055a204277d15 in JOIN::prepare (this=0x7fe6ac016638, tables_init=0x7fe6ac015f10, wild_num=1, conds_init=0x0, og_num=0, order_init=0x0, skip_order_by=false, group_init=0x0, having_init=0x0, proc_param_init=0x0, select_lex_arg=0x7fe6ac005118, unit_arg=0x7fe6ac0049a8) at /data/src/10.3/sql/sql_select.cc:997
 
#12 0x000055a2042835a5 in mysql_select (thd=0x7fe6ac000b00, tables=0x7fe6ac015f10, wild_num=1, fields=..., conds=0x0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748608, result=0x7fe6ac016618, unit=0x7fe6ac0049a8, select_lex=0x7fe6ac005118) at /data/src/10.3/sql/sql_select.cc:4205
 
#13 0x000055a20427557a in handle_select (thd=0x7fe6ac000b00, lex=0x7fe6ac0048e0, result=0x7fe6ac016618, setup_tables_done_option=0) at /data/src/10.3/sql/sql_select.cc:382
 
#14 0x000055a20423feed in execute_sqlcom_select (thd=0x7fe6ac000b00, all_tables=0x7fe6ac015f10) at /data/src/10.3/sql/sql_parse.cc:6546
 
#15 0x000055a204236619 in mysql_execute_command (thd=0x7fe6ac000b00) at /data/src/10.3/sql/sql_parse.cc:3768
 
#16 0x000055a204243e74 in mysql_parse (thd=0x7fe6ac000b00, rawbuf=0x7fe6ac014e50 "SELECT * FROM (VALUES(1+1,2)) t", length=31, parser_state=0x7fe6c97c55f0, is_com_multi=false, is_next_command=false) at /data/src/10.3/sql/sql_parse.cc:8088
 
#17 0x000055a20423112e in dispatch_command (command=COM_QUERY, thd=0x7fe6ac000b00, packet=0x7fe6ac1251e1 "SELECT * FROM (VALUES(1+1,2)) t", packet_length=31, is_com_multi=false, is_next_command=false) at /data/src/10.3/sql/sql_parse.cc:1850
 
#18 0x000055a20422fb52 in do_command (thd=0x7fe6ac000b00) at /data/src/10.3/sql/sql_parse.cc:1395
 
#19 0x000055a204396463 in do_handle_one_connection (connect=0x55a207e49d00) at /data/src/10.3/sql/sql_connect.cc:1402
 
#20 0x000055a2043961e7 in handle_one_connection (arg=0x55a207e49d00) at /data/src/10.3/sql/sql_connect.cc:1308
 
#21 0x000055a204827aa1 in pfs_spawn_thread (arg=0x55a207e6d080) at /data/src/10.3/storage/perfschema/pfs.cc:1862
 
#22 0x00007fe6d133f494 in start_thread (arg=0x7fe6c97c6700) at pthread_create.c:333
 
#23 0x00007fe6cf72593f in clone () from /lib/x86_64-linux-gnu/libc.so.6

Comment by Igor Babaev [ 2018-08-11 ]

We also have a problem here:

MariaDB [test]> SELECT * FROM (VALUES(2,2)) t;
 
ERROR 1060 (42S21): Duplicate column name '2'

Comment by Sergei Golubchik [ 2018-08-11 ]

I suspect that the last example is fine, or, at least, perfectly standard compliant. Not a problem.

Comment by Sergei Golubchik [ 2018-08-12 ]

Re commit 226138774a, I think VALUES(1,2,3) should produce the same result as SELECT 1,2,3. Same column names too.

In particular:

MariaDB [test]> select * from (select 2,2) t;
 
ERROR 1060 (42S21): Duplicate column name '2'

Comment by Igor Babaev [ 2018-08-13 ]

Ok, the following 

MariaDB [test]> SELECT * FROM (VALUES(2,2)) t;
 
ERROR 1060 (42S21): Duplicate column name '2'


is not a problem as we have in 5.5 and upstream

 MariaDB [test]> select * from (select 2, 2) as t;
 
ERROR 1060 (42S21): Duplicate column name '2'

Comment by Markus Winand [ 2018-08-13 ]

I think the field names of table value constructors are implementation-dependent (I guess SQL-2:2016, 7.1, SR 5b applies).

However, not accepting that query is a poor choice IMHO:

SELECT * FROM (VALUES(2,2)) t;

All other databases I tested accept it (if they support VALUES at all).

Implementing E051-09 “Rename columns in the FROM clause” would, of course, also help here. That's anyway a gap to MySQL 8.0

Comment by Sergei Golubchik [ 2018-08-13 ]

I've tried this on sqlfiddle. Both SELECT * FROM (SELECT 1,1) x and SELECT * FROM (VALUES(1,1)) x.

  • PostgreSQL — both work
  • SQL Server — both fail with "No column name was specified for column 1"
  • SQLite — both work

couldn't make this to work in Oracle ☹

So the conclusion so far is that SELECT 1,1 and VALUES (1,1) indeed behave identically.

I'm not saying there should be a "Duplicate column" error, and you are right, names are implementation-dependent. I'm saying that SELECT 1,1 and VALUES (1,1) should behave identically. Perhaps both should automatically give different names to columns?

Comment by Markus Winand [ 2018-08-13 ]

Oracle 18:

20:39:44 SQL> select 1, 1 from dual;
 
 
 
	 1	    1
 
---------- ----------
 
	 1	    1
 
 
 
Elapsed: 00:00:00.04
 
20:39:52 SQL> select * from (select 1, 1 from dual);
 
select * from (select 1, 1 from dual)
 
       *
 
ERROR at line 1:
 
ORA-00918: column ambiguously defined
 
 
 
 
 
Elapsed: 00:00:00.01

MySQL 8.0:

mysql> select 1,1;
 
+---+---+
 
| 1 | 1 |
 
+---+---+
 
| 1 | 1 |
 
+---+---+
 
1 row in set (0.00 sec)
 
 
 
mysql> select * from (select 1, 1) t;
 
ERROR 1060 (42S21): Duplicate column name '1'
 
mysql>

But.....

mysql> select * from (select 1, 1) t(a,b);
 
+---+---+
 
| a | b |
 
+---+---+
 
| 1 | 1 |
 
+---+---+
 
1 row in set (0.00 sec)

I think the "same behaviour" argument is also a bad one because you can work around the problem for the SELECT by naming the columns.

SELECT * FROM (SELECT 1 a, 1 b) t;

But VALUES itself doesn't offer a possibility to rename the implementation-defined column names. That's why feature E051-09 is super useful here, because then it would be easy to work around the problem if someone faces it.

As of now, the only way (I can think of) to get it working in MariaDB is to use an WITH clause to assign column names:

WITH cte (a, b) AS (VALUES (1,1))
 
SELECT * from cte;

I find that an overkill just for renaming system-assigned column names.

Perhaps both should automatically give different names to columns?

That would be in spec and useful. But probably an incompatible change.

IMHO, the problem boils down to the fact that VALUES does not allow user-defined column names. Thus there is feature E051-09 — a mandatory one, btw.

Adding that feature doesn't bring the risk of incompatible changes, allows to cope with that problem in a pretty easy way, closes a gap to MySQL 8.0, and, above all, is a perfect compliment to VALUES because it is the easiest way to rename the columns from a VALUES clause.

Comment by Sergei Golubchik [ 2018-08-14 ]

VALUES does not allow user-defined column names, indeed. Still, using

select * from (select 1, 1) t(a,b);
 
select * from (values (1, 1)) t(a,b);

should work, right? Wouldn't that be a good solution that consistently works in all cases? At least until we'll implement better "implementation dependent" names that don't conflict automatically.

Comment by Markus Winand [ 2018-08-15 ]

Wouldn't that be a good solution that consistently works in all cases?

That's what I was trying to say all time long. It's E051-09 “Rename columns in the FROM clause”.

Comment by Sergei Golubchik [ 2018-08-15 ]

Ah, I see. Then this is MDEV-16901 (which was originally reported only for VALUES as MDEV-16771).

Comment by Oleksandr Byelkin [ 2018-08-16 ]

OK to push.
(as the fix, but I think we both agreed that in 10.4 or above something should be done about same name conflicts avoiding)

Comment by Oleksandr Byelkin [ 2018-08-16 ]

Serg give gut idea about names from parser, will check it (do not push).

Comment by Oleksandr Byelkin [ 2018-08-16 ]

revision-id: adaafcf5184375369c0c4b3ccb1e82e846024ef8 (mariadb-10.3.7-137-gadaafcf5184)
parent(s): 8716bb3b72b7f5fed69b6dde413c2138f6d175b2
author: Oleksandr Byelkin
committer: Oleksandr Byelkin
timestamp: 2018-08-16 13:55:36 +0200
message:

MDEV-16930: Crash when VALUES in derived table contains expressions

Give names to the value constructor columns as in SELECT-list.

—

Comment by Oleksandr Byelkin [ 2018-08-16 ]

Igor, please review my solution.

Comment by Igor Babaev [ 2018-08-27 ]

This bug ultimately was fixed in 10.3

Generated at Thu Feb 08 08:32:37 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.