|
Two consequent stack trace from 10.0 commit 5f2f3c4fa81851b45dcee33601f14e05f6407333:
#0 0x0000000000a9b546 in mach_read_from_2 (b=0x7f7b8f170026 "") at /src/10.0/storage/xtradb/include/mach0data.ic:172
|
#1 0x0000000000a9e46d in page_header_get_field (page=0x7f7b8f170000 "Pm", <incomplete sequence \372>, field=0) at /src/10.0/storage/xtradb/include/page0page.ic:118
|
#2 0x0000000000a9ec29 in page_dir_get_n_slots (page=0x7f7b8f170000 "Pm", <incomplete sequence \372>) at /src/10.0/storage/xtradb/include/page0page.ic:583
|
#3 0x0000000000aa3330 in page_check_dir (page=0x7f7b8f170000 "Pm", <incomplete sequence \372>) at /src/10.0/storage/xtradb/page/page0page.cc:1940
|
#4 0x0000000000a9748b in page_cur_search_with_match (block=0x7f7b873e37e0, index=0x7f7b7121d4f8, tuple=0x7f7b713463f8, mode=2, iup_matched_fields=0x7f7b9a3a4708, iup_matched_bytes=0x7f7b9a3a4700, ilow_matched_fields=0x7f7b9a3a46f8, ilow_matched_bytes=0x7f7b9a3a46f0, cursor=0x7f7b713460f8) at /src/10.0/storage/xtradb/page/page0cur.cc:318
|
#5 0x0000000000bb0e65 in btr_cur_search_to_nth_level (index=0x7f7b7121d4f8, level=0, tuple=0x7f7b713463f8, mode=2, latch_mode=1, cursor=0x7f7b713460f0, has_search_latch=0, file=0x1014ee8 "/src/10.0/storage/xtradb/row/row0sel.cc", line=4133, mtr=0x7f7b9a3a4ba0) at /src/10.0/storage/xtradb/btr/btr0cur.cc:816
|
#6 0x0000000000b2428d in btr_pcur_open_with_no_init_func (index=0x7f7b7121d4f8, tuple=0x7f7b713463f8, mode=2, latch_mode=1, cursor=0x7f7b713460f0, has_search_latch=0, file=0x1014ee8 "/src/10.0/storage/xtradb/row/row0sel.cc", line=4133, mtr=0x7f7b9a3a4ba0) at /src/10.0/storage/xtradb/include/btr0pcur.ic:485
|
#7 0x0000000000b2bbab in row_search_for_mysql (buf=0x7f7b7126b288 "\377\256 \006", mode=2, prebuilt=0x7f7b71346078, match_mode=1, direction=0) at /src/10.0/storage/xtradb/row/row0sel.cc:4133
|
#8 0x0000000000a07cef in ha_innobase::index_read (this=0x7f7b71310088, buf=0x7f7b7126b288 "\377\256 \006", key_ptr=0x7f7b712ef4a0 "", key_len=29, find_flag=HA_READ_KEY_EXACT) at /src/10.0/storage/xtradb/handler/ha_innodb.cc:8635
|
#9 0x00000000008694c2 in handler::index_read_map (this=0x7f7b71310088, buf=0x7f7b7126b288 "\377\256 \006", key=0x7f7b712ef4a0 "", keypart_map=7, find_flag=HA_READ_KEY_EXACT) at /src/10.0/sql/handler.h:2920
|
#10 0x0000000000861149 in handler::ha_index_read_map (this=0x7f7b71310088, buf=0x7f7b7126b288 "\377\256 \006", key=0x7f7b712ef4a0 "", keypart_map=7, find_flag=HA_READ_KEY_EXACT) at /src/10.0/sql/handler.cc:2641
|
#11 0x000000000098630a in QUICK_GROUP_MIN_MAX_SELECT::get_next (this=0x7f7b97eb6480) at /src/10.0/sql/opt_range.cc:14442
|
#12 0x000000000098f535 in rr_quick (info=0x7f7b712d8628) at /src/10.0/sql/records.cc:348
|
#13 0x00000000006cca06 in sub_select (join=0x7f7b712d65d8, join_tab=0x7f7b712d8580, end_of_records=false) at /src/10.0/sql/sql_select.cc:17859
|
#14 0x00000000006cc1c7 in do_select (join=0x7f7b712d65d8, fields=0x7f7b712d69b0, table=0x0, procedure=0x0) at /src/10.0/sql/sql_select.cc:17502
|
#15 0x00000000006a9438 in JOIN::exec_inner (this=0x7f7b712d65d8) at /src/10.0/sql/sql_select.cc:3084
|
#16 0x00000000006a6908 in JOIN::exec (this=0x7f7b712d65d8) at /src/10.0/sql/sql_select.cc:2373
|
#17 0x00000000006a9cb5 in mysql_select (thd=0x7f7b712952b0, rref_pointer_array=0x7f7b71299588, tables=0x7f7b712d5460, wild_num=0, fields=..., conds=0x7f7b712d60a8, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748608, result=0x7f7b712d65b8, unit=0x7f7b71298c20, select_lex=0x7f7b71299310) at /src/10.0/sql/sql_select.cc:3308
|
#18 0x00000000006a0031 in handle_select (thd=0x7f7b712952b0, lex=0x7f7b71298b58, result=0x7f7b712d65b8, setup_tables_done_option=0) at /src/10.0/sql/sql_select.cc:373
|
#19 0x00000000006748e4 in execute_sqlcom_select (thd=0x7f7b712952b0, all_tables=0x7f7b712d5460) at /src/10.0/sql/sql_parse.cc:5304
|
#20 0x000000000066cccd in mysql_execute_command (thd=0x7f7b712952b0) at /src/10.0/sql/sql_parse.cc:2562
|
#21 0x000000000067746b in mysql_parse (thd=0x7f7b712952b0, rawbuf=0x7f7b712d5088 "select count(distinct name) from t2 where a=8366 and b>=5 and b<=5", length=66, parser_state=0x7f7b9a3a66a0) at /src/10.0/sql/sql_parse.cc:6565
|
#22 0x0000000000669f37 in dispatch_command (command=COM_QUERY, thd=0x7f7b712952b0, packet=0x7f7b7ffb5471 "", packet_length=66) at /src/10.0/sql/sql_parse.cc:1308
|
#23 0x0000000000669204 in do_command (thd=0x7f7b712952b0) at /src/10.0/sql/sql_parse.cc:998
|
#24 0x0000000000787899 in do_handle_one_connection (thd_arg=0x7f7b712952b0) at /src/10.0/sql/sql_connect.cc:1377
|
#25 0x000000000078760b in handle_one_connection (arg=0x7f7b712952b0) at /src/10.0/sql/sql_connect.cc:1292
|
#26 0x00007f7b9a0360a4 in start_thread () from /lib64/libpthread.so.0
|
#27 0x00007f7b983ca04d in clone () from /lib64/libc.so.6
|
#0 0x0000000000ac0fa8 in rec_offs_n_fields (offsets=0x0) at /src/10.0/storage/xtradb/include/rem0rec.ic:937
|
#1 0x0000000000ac12f4 in rec_get_nth_field_offs (offsets=0x7f7b9a3a3fb0, n=0, len=0x7f7b9a3a3e30) at /src/10.0/storage/xtradb/include/rem0rec.ic:1040
|
#2 0x0000000000ac369f in cmp_debug_dtuple_rec_with_match (dtuple=0x7f7b713463f8, rec=0x7f7b8f1700a2 "lawrin\240\256\200", offsets=0x7f7b9a3a3fb0, n_cmp=3, matched_fields=0x7f7b9a3a42e8) at /src/10.0/storage/xtradb/rem/rem0cmp.cc:1429
|
#3 0x0000000000ac260d in cmp_dtuple_rec_with_match_low (dtuple=0x7f7b713463f8, rec=0x7f7b8f1700a2 "lawrin\240\256\200", offsets=0x7f7b9a3a3fb0, n_cmp=3, matched_fields=0x7f7b9a3a42e8, matched_bytes=0x7f7b9a3a42e0) at /src/10.0/storage/xtradb/rem/rem0cmp.cc:847
|
#4 0x0000000000a97808 in page_cur_search_with_match (block=0x7f7b873e37e0, index=0x7f7b7121d4f8, tuple=0x7f7b713463f8, mode=2, iup_matched_fields=0x7f7b9a3a4708, iup_matched_bytes=0x7f7b9a3a4700, ilow_matched_fields=0x7f7b9a3a46f8, ilow_matched_bytes=0x7f7b9a3a46f0, cursor=0x7f7b713460f8) at /src/10.0/storage/xtradb/page/page0cur.cc:441
|
#5 0x0000000000bb0e65 in btr_cur_search_to_nth_level (index=0x7f7b7121d4f8, level=0, tuple=0x7f7b713463f8, mode=2, latch_mode=1, cursor=0x7f7b713460f0, has_search_latch=0, file=0x1014ee8 "/src/10.0/storage/xtradb/row/row0sel.cc", line=4133, mtr=0x7f7b9a3a4ba0) at /src/10.0/storage/xtradb/btr/btr0cur.cc:816
|
#6 0x0000000000b2428d in btr_pcur_open_with_no_init_func (index=0x7f7b7121d4f8, tuple=0x7f7b713463f8, mode=2, latch_mode=1, cursor=0x7f7b713460f0, has_search_latch=0, file=0x1014ee8 "/src/10.0/storage/xtradb/row/row0sel.cc", line=4133, mtr=0x7f7b9a3a4ba0) at /src/10.0/storage/xtradb/include/btr0pcur.ic:485
|
#7 0x0000000000b2bbab in row_search_for_mysql (buf=0x7f7b7126b288 "\377\256 \005", mode=2, prebuilt=0x7f7b71346078, match_mode=1, direction=0) at /src/10.0/storage/xtradb/row/row0sel.cc:4133
|
#8 0x0000000000a07cef in ha_innobase::index_read (this=0x7f7b71310088, buf=0x7f7b7126b288 "\377\256 \005", key_ptr=0x7f7b712ef4a0 "", key_len=29, find_flag=HA_READ_KEY_EXACT) at /src/10.0/storage/xtradb/handler/ha_innodb.cc:8635
|
#9 0x00000000008694c2 in handler::index_read_map (this=0x7f7b71310088, buf=0x7f7b7126b288 "\377\256 \005", key=0x7f7b712ef4a0 "", keypart_map=7, find_flag=HA_READ_KEY_EXACT) at /src/10.0/sql/handler.h:2920
|
#10 0x0000000000861149 in handler::ha_index_read_map (this=0x7f7b71310088, buf=0x7f7b7126b288 "\377\256 \005", key=0x7f7b712ef4a0 "", keypart_map=7, find_flag=HA_READ_KEY_EXACT) at /src/10.0/sql/handler.cc:2641
|
#11 0x000000000098630a in QUICK_GROUP_MIN_MAX_SELECT::get_next (this=0x7f7b97eb6480) at /src/10.0/sql/opt_range.cc:14442
|
#12 0x000000000098f535 in rr_quick (info=0x7f7b712d8628) at /src/10.0/sql/records.cc:348
|
#13 0x00000000006cca06 in sub_select (join=0x7f7b712d65d8, join_tab=0x7f7b712d8580, end_of_records=false) at /src/10.0/sql/sql_select.cc:17859
|
#14 0x00000000006cc1c7 in do_select (join=0x7f7b712d65d8, fields=0x7f7b712d69b0, table=0x0, procedure=0x0) at /src/10.0/sql/sql_select.cc:17502
|
#15 0x00000000006a9438 in JOIN::exec_inner (this=0x7f7b712d65d8) at /src/10.0/sql/sql_select.cc:3084
|
#16 0x00000000006a6908 in JOIN::exec (this=0x7f7b712d65d8) at /src/10.0/sql/sql_select.cc:2373
|
#17 0x00000000006a9cb5 in mysql_select (thd=0x7f7b712952b0, rref_pointer_array=0x7f7b71299588, tables=0x7f7b712d5460, wild_num=0, fields=..., conds=0x7f7b712d60a8, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748608, result=0x7f7b712d65b8, unit=0x7f7b71298c20, select_lex=0x7f7b71299310) at /src/10.0/sql/sql_select.cc:3308
|
#18 0x00000000006a0031 in handle_select (thd=0x7f7b712952b0, lex=0x7f7b71298b58, result=0x7f7b712d65b8, setup_tables_done_option=0) at /src/10.0/sql/sql_select.cc:373
|
#19 0x00000000006748e4 in execute_sqlcom_select (thd=0x7f7b712952b0, all_tables=0x7f7b712d5460) at /src/10.0/sql/sql_parse.cc:5304
|
#20 0x000000000066cccd in mysql_execute_command (thd=0x7f7b712952b0) at /src/10.0/sql/sql_parse.cc:2562
|
#21 0x000000000067746b in mysql_parse (thd=0x7f7b712952b0, rawbuf=0x7f7b712d5088 "select count(distinct name) from t2 where a=8366 and b>=5 and b<=5", length=66, parser_state=0x7f7b9a3a66a0) at /src/10.0/sql/sql_parse.cc:6565
|
#22 0x0000000000669f37 in dispatch_command (command=COM_QUERY, thd=0x7f7b712952b0, packet=0x7f7b7ffb5471 "", packet_length=66) at /src/10.0/sql/sql_parse.cc:1308
|
#23 0x0000000000669204 in do_command (thd=0x7f7b712952b0) at /src/10.0/sql/sql_parse.cc:998
|
#24 0x0000000000787899 in do_handle_one_connection (thd_arg=0x7f7b712952b0) at /src/10.0/sql/sql_connect.cc:1377
|
#25 0x000000000078760b in handle_one_connection (arg=0x7f7b712952b0) at /src/10.0/sql/sql_connect.cc:1292
|
#26 0x00007f7b9a0360a4 in start_thread () from /lib64/libpthread.so.0
|
#27 0x00007f7b983ca04d in clone () from /lib64/libc.so.6
|
MariaDB [test]> explain extended select count(distinct name) from t2 where a=8366 and b>=5 and b<=5;
|
+------+-------------+-------+-------+---------------+------+---------+------+------+----------+--------------------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|
+------+-------------+-------+-------+---------------+------+---------+------+------+----------+--------------------------------------------------+
|
| 1 | SIMPLE | t2 | range | PRIMARY | name | 29 | NULL | 5 | 20.00 | Using where; Using index for group-by (scanning) |
|
+------+-------------+-------+-------+---------------+------+---------+------+------+----------+--------------------------------------------------+
|
1 row in set, 1 warning (0.00 sec)
|
|
MariaDB [test]> show warnings;
|
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| Level | Code | Message |
|
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| Note | 1003 | select count(distinct `test`.`t2`.`name`) AS `count(distinct name)` from `test`.`t2` where ((`test`.`t2`.`a` = 8366) and (`test`.`t2`.`b` >= 5) and (`test`.`t2`.`b` <= 5)) |
|
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
1 row in set (0.00 sec)
|
|
|
Not sure if this is important, but I see no problem on Percona Server 5.7:
[openxs@centos ~]$ mysql -uroot test
|
Reading table information for completion of table and column names
|
You can turn off this feature to get a quicker startup with -A
|
|
Welcome to the MySQL monitor. Commands end with ; or \g.
|
Your MySQL connection id is 3
|
Server version: 5.7.10-3-log Percona Server (GPL), Release 3, Revision 63dafaf
|
|
Copyright (c) 2009-2015 Percona LLC and/or its affiliates
|
Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.
|
|
Oracle is a registered trademark of Oracle Corporation and/or its
|
affiliates. Other names may be trademarks of their respective
|
owners.
|
|
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
|
mysql> drop table if exists t2;
|
Query OK, 0 rows affected (0.13 sec)
|
|
mysql> create table t2 (a smallint(6) not null, b int(10) not null, name varchar(20), primary key(a,b), key(name)) engine=InnoDB;
|
Query OK, 0 rows affected (0.18 sec)
|
|
mysql>
|
mysql> insert into t2 values (8355,3,"sanja"),(8355,4,"wlad"),(8366,5, "lawrin"),(8366,6,"markusjm");
|
Query OK, 4 rows affected (0.07 sec)
|
Records: 4 Duplicates: 0 Warnings: 0
|
|
mysql> select count(distinct name) from t2 where a=8366 and b>=5 and b<=5;
|
+----------------------+
|
| count(distinct name) |
|
+----------------------+
|
| 1 |
|
+----------------------+
|
1 row in set (0.06 sec)
|
|
mysql> explain select count(distinct name) from t2 where a=8366 and b>=5 and b<=5;
|
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+--------------------------------------------------+
|
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+--------------------------------------------------+
|
| 1 | SIMPLE | t2 | NULL | range | PRIMARY,name | name | 29 | NULL | 5 | 100.00 | Using where; Using index for group-by (scanning) |
|
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+--------------------------------------------------+
|
1 row in set, 1 warning (0.01 sec)
|
No problem with Percona Server 5.6.28 as well:
[openxs@centos p5.6]$ bin/mysql -uroot test
|
Reading table information for completion of table and column names
|
You can turn off this feature to get a quicker startup with -A
|
|
Welcome to the MySQL monitor. Commands end with ; or \g.
|
Your MySQL connection id is 1
|
Server version: 5.6.28-76.1 MySQL Community Server (GPL)
|
|
Copyright (c) 2009-2015 Percona LLC and/or its affiliates
|
Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.
|
|
Oracle is a registered trademark of Oracle Corporation and/or its
|
affiliates. Other names may be trademarks of their respective
|
owners.
|
|
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
|
mysql> drop table if exists t2;
|
Query OK, 0 rows affected, 1 warning (0.00 sec)
|
|
mysql> create table t2 (a smallint(6) not null, b int(10) not null, name varchar(20), primary key(a,b), key(name)) engine=InnoDB;
|
Query OK, 0 rows affected (0.13 sec)
|
|
mysql> insert into t2 values (8355,3,"sanja"),(8355,4,"wlad"),(8366,5, "lawrin"),(8366,6,"markusjm");
|
Query OK, 4 rows affected (0.02 sec)
|
Records: 4 Duplicates: 0 Warnings: 0
|
|
mysql> explain select count(distinct name) from t2 where a=8366 and b>=5 and b<=5;
|
+----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------------------------------+
|
| 1 | SIMPLE | t2 | range | PRIMARY,name | name | 29 | NULL | 5 | Using where; Using index for group-by (scanning) |
|
+----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------------------------------+
|
1 row in set (0.00 sec)
|
|
mysql> select count(distinct name) from t2 where a=8366 and b>=5 and b<=5;
|
+----------------------+
|
| count(distinct name) |
|
+----------------------+
|
| 1 |
|
+----------------------+
|
1 row in set (0.00 sec)
|
|