[MDEV-10880] Assertions `keypart_map' or `prebuilt->search_tuple->n_fields > 0' fail on DISTINCT and GROUP BY constant Created: 2016-09-23  Updated: 2018-08-05  Resolved: 2017-06-23

Status: Closed
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 10.1, 10.2
Fix Version/s: 10.1.25, 10.2.7

Type: Bug Priority: Major
Reporter: Elena Stepanova Assignee: Oleksandr Byelkin
Resolution: Fixed Votes: 0
Labels: None

Issue Links:
Relates
relates to MDEV-9602 crash in st_key::actual_rec_per_key w... Closed
relates to MDEV-15433 Optimizer does not use group by optim... Closed

 Description    

CREATE TABLE t1 (pk INT PRIMARY KEY);
 
INSERT INTO t1 VALUES (1),(2),(3);
 
SELECT DISTINCT pk FROM t1 GROUP BY 'foo';

*With MyISAM* - Stack trace from 10.1 5d001d13c2

 
mysqld: /data/src/10.1/storage/myisam/mi_rkey.c:59: mi_rkey: Assertion `keypart_map' failed.
 
160923 23:54:46 [ERROR] mysqld got signal 6 ;
 
 
 
#7  0x00007f1e40510312 in __GI___assert_fail (assertion=0x7f1e4380974a "keypart_map", file=0x7f1e438096e0 "/data/src/10.1/storage/myisam/mi_rkey.c", line=59, function=0x7f1e438097f2 <__PRETTY_FUNCTION__.13343> "mi_rkey") at assert.c:101
 
#8  0x00007f1e4355368b in mi_rkey (info=0x7f1e37882a70, buf=0x7f1e397d3688 "\377\001", inx=0, key=0x7f1e37995090 "\001", keypart_map=0, search_flag=HA_READ_AFTER_KEY) at /data/src/10.1/storage/myisam/mi_rkey.c:59
 
#9  0x00007f1e434fa64d in ha_myisam::index_read_map (this=0x7f1e378af888, buf=0x7f1e397d3688 "\377\001", key=0x7f1e37995090 "\001", keypart_map=0, find_flag=HA_READ_AFTER_KEY) at /data/src/10.1/storage/myisam/ha_myisam.cc:1747
 
#10 0x00007f1e42f55e6d in handler::ha_index_read_map (this=0x7f1e378af888, buf=0x7f1e397d3688 "\377\001", key=0x7f1e37995090 "\001", keypart_map=0, find_flag=HA_READ_AFTER_KEY) at /data/src/10.1/sql/handler.cc:2622
 
#11 0x00007f1e4308a237 in index_next_different (is_index_scan=false, file=0x7f1e378af888, key_part=0x7f1e397d3810, record=0x7f1e397d3688 "\377\001", group_prefix=0x7f1e37995090 "\001", group_prefix_len=0, group_key_parts=0) at /data/src/10.1/sql/opt_range.cc:13945
 
#12 0x00007f1e4308a389 in QUICK_GROUP_MIN_MAX_SELECT::next_prefix (this=0x7f1e3797e140) at /data/src/10.1/sql/opt_range.cc:13998
 
#13 0x00007f1e43089c0f in QUICK_GROUP_MIN_MAX_SELECT::get_next (this=0x7f1e3797e140) at /data/src/10.1/sql/opt_range.cc:13720
 
#14 0x00007f1e43093e09 in rr_quick (info=0x7f1e37844d08) at /data/src/10.1/sql/records.cc:349
 
#15 0x00007f1e42d823e7 in sub_select (join=0x7f1e37843a60, join_tab=0x7f1e37844c48, end_of_records=false) at /data/src/10.1/sql/sql_select.cc:18315
 
#16 0x00007f1e42d81ba8 in do_select (join=0x7f1e37843a60, fields=0x7f1e37843e50, table=0x0, procedure=0x0) at /data/src/10.1/sql/sql_select.cc:17951
 
#17 0x00007f1e42d5c921 in JOIN::exec_inner (this=0x7f1e37843a60) at /data/src/10.1/sql/sql_select.cc:3215
 
#18 0x00007f1e42d59bcb in JOIN::exec (this=0x7f1e37843a60) at /data/src/10.1/sql/sql_select.cc:2505
 
#19 0x00007f1e42d5d139 in mysql_select (thd=0x7f1e397fa070, rref_pointer_array=0x7f1e397fe430, tables=0x7f1e37843290, wild_num=0, fields=..., conds=0x0, og_num=1, order=0x0, group=0x7f1e37843930, having=0x0, proc_param=0x0, select_options=2147748609, result=0x7f1e37843a40, unit=0x7f1e397fdab8, select_lex=0x7f1e397fe1b8) at /data/src/10.1/sql/sql_select.cc:3438
 
#20 0x00007f1e42d52c2a in handle_select (thd=0x7f1e397fa070, lex=0x7f1e397fd9f0, result=0x7f1e37843a40, setup_tables_done_option=0) at /data/src/10.1/sql/sql_select.cc:384
 
#21 0x00007f1e42d22e09 in execute_sqlcom_select (thd=0x7f1e397fa070, all_tables=0x7f1e37843290) at /data/src/10.1/sql/sql_parse.cc:5895
 
#22 0x00007f1e42d18d16 in mysql_execute_command (thd=0x7f1e397fa070) at /data/src/10.1/sql/sql_parse.cc:2961
 
#23 0x00007f1e42d26562 in mysql_parse (thd=0x7f1e397fa070, rawbuf=0x7f1e37843088 "SELECT DISTINCT pk FROM t1 GROUP BY 'foo'", length=41, parser_state=0x7f1e43c285e0) at /data/src/10.1/sql/sql_parse.cc:7318
 
#24 0x00007f1e42d14f6b in dispatch_command (command=COM_QUERY, thd=0x7f1e397fa070, packet=0x7f1e3b13e071 "", packet_length=41) at /data/src/10.1/sql/sql_parse.cc:1487
 
#25 0x00007f1e42d13ca2 in do_command (thd=0x7f1e397fa070) at /data/src/10.1/sql/sql_parse.cc:1108
 
#26 0x00007f1e42e49b47 in do_handle_one_connection (thd_arg=0x7f1e397fa070) at /data/src/10.1/sql/sql_connect.cc:1350
 
#27 0x00007f1e42e498ab in handle_one_connection (arg=0x7f1e397fa070) at /data/src/10.1/sql/sql_connect.cc:1262
 
#28 0x00007f1e4312c4e6 in pfs_spawn_thread (arg=0x7f1e3fc27ef0) at /data/src/10.1/storage/perfschema/pfs.cc:1860
 
#29 0x00007f1e424120a4 in start_thread (arg=0x7f1e43c29b00) at pthread_create.c:309
 
#30 0x00007f1e405ca87d in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:111

*With InnoDB* - Stack trace from 10.1 5d001d13c2

 
mysqld: /data/src/10.1/storage/xtradb/handler/ha_innodb.cc:9819: virtual int ha_innobase::index_read(uchar*, const uchar*, uint, ha_rkey_function): Assertion `prebuilt->search_tuple->n_fields > 0' failed.
 
160923 23:56:10 [ERROR] mysqld got signal 6 ;
 
 
 
#7  0x00007fa53a77b312 in __GI___assert_fail (assertion=0x7fa53d9b2918 "prebuilt->search_tuple->n_fields > 0", file=0x7fa53d9ae570 "/data/src/10.1/storage/xtradb/handler/ha_innodb.cc", line=9819, function=0x7fa53d9bcac0 <ha_innobase::index_read(unsigned char*, unsigned char const*, unsigned int, ha_rkey_function)::__PRETTY_FUNCTION__> "virtual int ha_innobase::index_read(uchar*, const uchar*, uint, ha_rkey_function)") at assert.c:101
 
#8  0x00007fa53d3b6878 in ha_innobase::index_read (this=0x7fa524cb0888, buf=0x7fa531765088 "\377\001", key_ptr=0x7fa524d81090 "\001", key_len=0, find_flag=HA_READ_AFTER_KEY) at /data/src/10.1/storage/xtradb/handler/ha_innodb.cc:9819
 
#9  0x00007fa53d1ca1e8 in handler::index_read_map (this=0x7fa524cb0888, buf=0x7fa531765088 "\377\001", key=0x7fa524d81090 "\001", keypart_map=0, find_flag=HA_READ_AFTER_KEY) at /data/src/10.1/sql/handler.h:3052
 
#10 0x00007fa53d1c0e6d in handler::ha_index_read_map (this=0x7fa524cb0888, buf=0x7fa531765088 "\377\001", key=0x7fa524d81090 "\001", keypart_map=0, find_flag=HA_READ_AFTER_KEY) at /data/src/10.1/sql/handler.cc:2622
 
#11 0x00007fa53d2f5237 in index_next_different (is_index_scan=false, file=0x7fa524cb0888, key_part=0x7fa531765210, record=0x7fa531765088 "\377\001", group_prefix=0x7fa524d81090 "\001", group_prefix_len=0, group_key_parts=0) at /data/src/10.1/sql/opt_range.cc:13945
 
#12 0x00007fa53d2f5389 in QUICK_GROUP_MIN_MAX_SELECT::next_prefix (this=0x7fa524dd0280) at /data/src/10.1/sql/opt_range.cc:13998
 
#13 0x00007fa53d2f4c0f in QUICK_GROUP_MIN_MAX_SELECT::get_next (this=0x7fa524dd0280) at /data/src/10.1/sql/opt_range.cc:13720
 
#14 0x00007fa53d2fee09 in rr_quick (info=0x7fa524c44d08) at /data/src/10.1/sql/records.cc:349
 
#15 0x00007fa53cfed3e7 in sub_select (join=0x7fa524c43a60, join_tab=0x7fa524c44c48, end_of_records=false) at /data/src/10.1/sql/sql_select.cc:18315
 
#16 0x00007fa53cfecba8 in do_select (join=0x7fa524c43a60, fields=0x7fa524c43e50, table=0x0, procedure=0x0) at /data/src/10.1/sql/sql_select.cc:17951
 
#17 0x00007fa53cfc7921 in JOIN::exec_inner (this=0x7fa524c43a60) at /data/src/10.1/sql/sql_select.cc:3215
 
#18 0x00007fa53cfc4bcb in JOIN::exec (this=0x7fa524c43a60) at /data/src/10.1/sql/sql_select.cc:2505
 
#19 0x00007fa53cfc8139 in mysql_select (thd=0x7fa531776070, rref_pointer_array=0x7fa53177a430, tables=0x7fa524c43290, wild_num=0, fields=..., conds=0x0, og_num=1, order=0x0, group=0x7fa524c43930, having=0x0, proc_param=0x0, select_options=2147748609, result=0x7fa524c43a40, unit=0x7fa531779ab8, select_lex=0x7fa53177a1b8) at /data/src/10.1/sql/sql_select.cc:3438
 
#20 0x00007fa53cfbdc2a in handle_select (thd=0x7fa531776070, lex=0x7fa5317799f0, result=0x7fa524c43a40, setup_tables_done_option=0) at /data/src/10.1/sql/sql_select.cc:384
 
#21 0x00007fa53cf8de09 in execute_sqlcom_select (thd=0x7fa531776070, all_tables=0x7fa524c43290) at /data/src/10.1/sql/sql_parse.cc:5895
 
#22 0x00007fa53cf83d16 in mysql_execute_command (thd=0x7fa531776070) at /data/src/10.1/sql/sql_parse.cc:2961
 
#23 0x00007fa53cf91562 in mysql_parse (thd=0x7fa531776070, rawbuf=0x7fa524c43088 "SELECT DISTINCT pk FROM t1 GROUP BY 'foo'", length=41, parser_state=0x7fa53de835e0) at /data/src/10.1/sql/sql_parse.cc:7318
 
#24 0x00007fa53cf7ff6b in dispatch_command (command=COM_QUERY, thd=0x7fa531776070, packet=0x7fa5316a4071 "", packet_length=41) at /data/src/10.1/sql/sql_parse.cc:1487
 
#25 0x00007fa53cf7eca2 in do_command (thd=0x7fa531776070) at /data/src/10.1/sql/sql_parse.cc:1108
 
#26 0x00007fa53d0b4b47 in do_handle_one_connection (thd_arg=0x7fa531776070) at /data/src/10.1/sql/sql_connect.cc:1350
 
#27 0x00007fa53d0b48ab in handle_one_connection (arg=0x7fa531776070) at /data/src/10.1/sql/sql_connect.cc:1262
 
#28 0x00007fa53d3974e6 in pfs_spawn_thread (arg=0x7fa53168cd70) at /data/src/10.1/storage/perfschema/pfs.cc:1860
 
#29 0x00007fa53c67d0a4 in start_thread (arg=0x7fa53de84b00) at pthread_create.c:309
 
#30 0x00007fa53a83587d in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:111

*With Aria* - Stack trace from 10.1 5d001d13c2

 
mysqld: /data/src/10.1/storage/maria/ma_rkey.c:69: maria_rkey: Assertion `keypart_map' failed.
 
160923 23:57:14 [ERROR] mysqld got signal 6 ;
 
 
 
#7  0x00007fc3de90b312 in __GI___assert_fail (assertion=0x7fc3e1bf2e0a "keypart_map", file=0x7fc3e1bf2da0 "/data/src/10.1/storage/maria/ma_rkey.c", line=69, function=0x7fc3e1bf2e60 <__PRETTY_FUNCTION__.14934> "maria_rkey") at assert.c:101
 
#8  0x00007fc3e187e104 in maria_rkey (info=0x7fc3d5dce070, buf=0x7fc3d7bd3688 "\377\001", inx=0, key_data=0x7fc3d5dd4090 "\001", keypart_map=0, search_flag=HA_READ_AFTER_KEY) at /data/src/10.1/storage/maria/ma_rkey.c:69
 
#9  0x00007fc3e183a0d1 in ha_maria::index_read_map (this=0x7fc3d5caf888, buf=0x7fc3d7bd3688 "\377\001", key=0x7fc3d5dd4090 "\001", keypart_map=0, find_flag=HA_READ_AFTER_KEY) at /data/src/10.1/storage/maria/ha_maria.cc:2324
 
#10 0x00007fc3e1350e6d in handler::ha_index_read_map (this=0x7fc3d5caf888, buf=0x7fc3d7bd3688 "\377\001", key=0x7fc3d5dd4090 "\001", keypart_map=0, find_flag=HA_READ_AFTER_KEY) at /data/src/10.1/sql/handler.cc:2622
 
#11 0x00007fc3e1485237 in index_next_different (is_index_scan=false, file=0x7fc3d5caf888, key_part=0x7fc3d7bd3810, record=0x7fc3d7bd3688 "\377\001", group_prefix=0x7fc3d5dd4090 "\001", group_prefix_len=0, group_key_parts=0) at /data/src/10.1/sql/opt_range.cc:13945
 
#12 0x00007fc3e1485389 in QUICK_GROUP_MIN_MAX_SELECT::next_prefix (this=0x7fc3d5da6280) at /data/src/10.1/sql/opt_range.cc:13998
 
#13 0x00007fc3e1484c0f in QUICK_GROUP_MIN_MAX_SELECT::get_next (this=0x7fc3d5da6280) at /data/src/10.1/sql/opt_range.cc:13720
 
#14 0x00007fc3e148ee09 in rr_quick (info=0x7fc3d5c44d08) at /data/src/10.1/sql/records.cc:349
 
#15 0x00007fc3e117d3e7 in sub_select (join=0x7fc3d5c43a60, join_tab=0x7fc3d5c44c48, end_of_records=false) at /data/src/10.1/sql/sql_select.cc:18315
 
#16 0x00007fc3e117cba8 in do_select (join=0x7fc3d5c43a60, fields=0x7fc3d5c43e50, table=0x0, procedure=0x0) at /data/src/10.1/sql/sql_select.cc:17951
 
#17 0x00007fc3e1157921 in JOIN::exec_inner (this=0x7fc3d5c43a60) at /data/src/10.1/sql/sql_select.cc:3215
 
#18 0x00007fc3e1154bcb in JOIN::exec (this=0x7fc3d5c43a60) at /data/src/10.1/sql/sql_select.cc:2505
 
#19 0x00007fc3e1158139 in mysql_select (thd=0x7fc3d7bfa070, rref_pointer_array=0x7fc3d7bfe430, tables=0x7fc3d5c43290, wild_num=0, fields=..., conds=0x0, og_num=1, order=0x0, group=0x7fc3d5c43930, having=0x0, proc_param=0x0, select_options=2147748609, result=0x7fc3d5c43a40, unit=0x7fc3d7bfdab8, select_lex=0x7fc3d7bfe1b8) at /data/src/10.1/sql/sql_select.cc:3438
 
#20 0x00007fc3e114dc2a in handle_select (thd=0x7fc3d7bfa070, lex=0x7fc3d7bfd9f0, result=0x7fc3d5c43a40, setup_tables_done_option=0) at /data/src/10.1/sql/sql_select.cc:384
 
#21 0x00007fc3e111de09 in execute_sqlcom_select (thd=0x7fc3d7bfa070, all_tables=0x7fc3d5c43290) at /data/src/10.1/sql/sql_parse.cc:5895
 
#22 0x00007fc3e1113d16 in mysql_execute_command (thd=0x7fc3d7bfa070) at /data/src/10.1/sql/sql_parse.cc:2961
 
#23 0x00007fc3e1121562 in mysql_parse (thd=0x7fc3d7bfa070, rawbuf=0x7fc3d5c43088 "SELECT DISTINCT pk FROM t1 GROUP BY 'foo'", length=41, parser_state=0x7fc3e20235e0) at /data/src/10.1/sql/sql_parse.cc:7318
 
#24 0x00007fc3e110ff6b in dispatch_command (command=COM_QUERY, thd=0x7fc3d7bfa070, packet=0x7fc3d953e071 "", packet_length=41) at /data/src/10.1/sql/sql_parse.cc:1487
 
#25 0x00007fc3e110eca2 in do_command (thd=0x7fc3d7bfa070) at /data/src/10.1/sql/sql_parse.cc:1108
 
#26 0x00007fc3e1244b47 in do_handle_one_connection (thd_arg=0x7fc3d7bfa070) at /data/src/10.1/sql/sql_connect.cc:1350
 
#27 0x00007fc3e12448ab in handle_one_connection (arg=0x7fc3d7bfa070) at /data/src/10.1/sql/sql_connect.cc:1262
 
#28 0x00007fc3e15274e6 in pfs_spawn_thread (arg=0x7fc3de027ef0) at /data/src/10.1/storage/perfschema/pfs.cc:1860
 
#29 0x00007fc3e080d0a4 in start_thread (arg=0x7fc3e2024b00) at pthread_create.c:309
 
#30 0x00007fc3de9c587d in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:111

It seems to be a leftoever from MDEV-9602.



 Comments   
Comment by Oleksandr Byelkin [ 2017-06-20 ]

Difference is in !s->const_keys.is_clear_all() in make_join_statistics(). So last version try to perform range analysys and so on...

Comment by Oleksandr Byelkin [ 2017-06-20 ]

add_group_and_distinct_keys() adds the const_keys

Comment by Oleksandr Byelkin [ 2017-06-20 ]

The difference is that group_by is removed.

Comment by Oleksandr Byelkin [ 2017-06-20 ]

Why key is ok for DISTINCT even if there is no GROUP BY?

Comment by Oleksandr Byelkin [ 2017-06-20 ]

Problem was that ORDER BY was removed, and JOIN::simple_group was set instead. but add_group_and_distinct_keys() did not checked simple_join trying optimise distinct without aggregating.

Comment by Oleksandr Byelkin [ 2017-06-21 ]

revision-id: f7fde4e26f2bff2bccda9d33654db3c49cc4b9cf (mariadb-10.1.24-23-gf7fde4e26f2)
parent(s): 056bab0880544d91ea67d18fe8db65b4f6625482
committer: Oleksandr Byelkin
timestamp: 2017-06-21 13:46:27 +0200
message:

MDEV-10880: Assertions `keypart_map' or `prebuilt->search_tuple->n_fields > 0' fail on DISTINCT and GROUP BY constant

add_group_and_distinct_keys() should take into account JOIN::simple_group.

—

Comment by Igor Babaev [ 2017-06-22 ]

Ok to push

Comment by Varun Gupta (Inactive) [ 2018-08-03 ]

The explain for the query:

explain
 
SELECT DISTINCT pk FROM t1 GROUP BY 'foo';
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 
1	SIMPLE	t1	range	NULL	pk	0	NULL	2	Using index for group-by

key_len= 0 looks very suspicious here, this is incorrect it makes no sense to use an index to do group by on a group with length=0, so looks like the code which handles the case of doing group by with indexes is incorrect somewhere.

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