Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.2(EOL)
-
None
Description
It is hard to find exact conditions which reliably trigger the problem, but following steps did demonstrate it (see outputs later):
set global rocksdb_strict_collation_check=off; |
drop table t; |
CREATE TABLE t( |
a varchar(10) NOT NULL, |
b char(1) DEFAULT 'X', |
c char(2) NOT NULL DEFAULT '??', |
d varchar(10) NOT NULL, |
e int(11) DEFAULT 0, |
PRIMARY KEY (a,d), |
KEY (e) |
) ENGINE=ROCKSDB DEFAULT CHARSET=utf8; |
|
insert into t select 1,1,1,1,0; |
insert into t select 2,1,1,1,0; |
insert into t select 3,1,1,1,0; |
|
select a from t; |
explain select * from t where not e; |
select a from t; |
MariaDB [test]> drop table t;
|
Query OK, 0 rows affected (0.04 sec)
|
|
MariaDB [test]> CREATE TABLE t(
|
-> a varchar(10) NOT NULL,
|
-> b char(1) DEFAULT 'X',
|
-> c char(2) NOT NULL DEFAULT '??',
|
-> d varchar(10) NOT NULL,
|
-> e int(11) DEFAULT 0,
|
-> PRIMARY KEY (a,d),
|
-> KEY (e)
|
-> ) ENGINE=ROCKSDB DEFAULT CHARSET=utf8;
|
Query OK, 0 rows affected (0.22 sec)
|
|
MariaDB [test]>
|
MariaDB [test]> insert into t select 1,1,1,1,0;
|
Query OK, 1 row affected (0.07 sec)
|
Records: 1 Duplicates: 0 Warnings: 0
|
|
MariaDB [test]> insert into t select 2,1,1,1,0;
|
Query OK, 1 row affected (0.03 sec)
|
Records: 1 Duplicates: 0 Warnings: 0
|
|
MariaDB [test]> insert into t select 3,1,1,1,0;
|
Query OK, 1 row affected (0.03 sec)
|
Records: 1 Duplicates: 0 Warnings: 0
|
|
MariaDB [test]>
|
MariaDB [test]> select a from t;
|
+---+
|
| a |
|
+---+
|
| 1 |
|
| 2 |
|
| 3 |
|
+---+
|
3 rows in set (0.00 sec)
|
|
MariaDB [test]> explain select * from t where not e;
|
+------+-------------+-------+------+---------------+------+---------+-------+------+-------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+------+---------------+------+---------+-------+------+-------+
|
| 1 | SIMPLE | t | ref | e | e | 5 | const | 2 | |
|
+------+-------------+-------+------+---------------+------+---------+-------+------+-------+
|
1 row in set (0.00 sec)
|
|
MariaDB [test]> select a from t;
|
+---+
|
| a |
|
+---+
|
| |
|
| |
|
| |
|
+---+
|
3 rows in set (0.00 sec)
|
|
MariaDB [test]> \r
|
Connection id: 11
|
Current database: test
|
|
MariaDB [test]> select a from t;
|
+---+
|
| a |
|
+---+
|
| |
|
| |
|
| |
|
+---+
|
3 rows in set (0.00 sec)
|
|
MariaDB [test]> flush tables;
|
Query OK, 0 rows affected (0.00 sec)
|
|
MariaDB [test]> select a from t;
|
+---+
|
| a |
|
+---+
|
| |
|
| |
|
| |
|
+---+
|
3 rows in set (0.00 sec)
|
|
MariaDB [test]> select a from t;
|
ERROR 2006 (HY000): MySQL server has gone away
|
No connection. Trying to reconnect...
|
Connection id: 9
|
Current database: test
|
|
+---+
|
| a |
|
+---+
|
| |
|
| |
|
| |
|
+---+
|
3 rows in set (0.00 sec)
|
|
MariaDB [test]> optimize table t;
|
+--------+----------+----------+----------+
|
| Table | Op | Msg_type | Msg_text |
|
+--------+----------+----------+----------+
|
| test.t | optimize | status | OK |
|
+--------+----------+----------+----------+
|
1 row in set (0.15 sec)
|
|
MariaDB [test]> select a from t;
|
+---+
|
| a |
|
+---+
|
| |
|
| |
|
| |
|
+---+
|
3 rows in set (0.00 sec)
|
|
MariaDB [test]> select a,b from t;
|
+---+------+
|
| a | b |
|
+---+------+
|
| 1 | 1 |
|
| 2 | 1 |
|
| 3 | 1 |
|
+---+------+
|
3 rows in set (0.00 sec)
|
|
MariaDB [test]> select a from t;
|
+---+
|
| a |
|
+---+
|
| |
|
| |
|
| |
|
+---+
|
3 rows in set (0.00 sec)
|
|
MariaDB [test]> alter table t drop primary key;
|
Query OK, 3 rows affected (0.27 sec)
|
Records: 3 Duplicates: 0 Warnings: 0
|
|
MariaDB [test]> select a from t;
|
+---+
|
| a |
|
+---+
|
| 1 |
|
| 2 |
|
| 3 |
|
+---+
|
3 rows in set (0.00 sec)
|
|
MariaDB [test]> select a from t;
|
+---+
|
| a |
|
+---+
|
| 1 |
|
| 2 |
|
| 3 |
|
+---+
|
3 rows in set (0.00 sec)
|
|
MariaDB [test]> explain select * from t where not e;
|
+------+-------------+-------+------+---------------+------+---------+-------+------+-------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+------+---------------+------+---------+-------+------+-------+
|
| 1 | SIMPLE | t | ref | e | e | 5 | const | 1 | |
|
+------+-------------+-------+------+---------------+------+---------+-------+------+-------+
|
1 row in set (0.00 sec)
|
|
MariaDB [test]> select a from t;
|
+---+
|
| a |
|
+---+
|
| 1 |
|
| 2 |
|
| 3 |
|
+---+
|
3 rows in set (0.00 sec)
|
|
MariaDB [test]> alter table t add primary key(a,d);
|
Query OK, 3 rows affected (0.27 sec)
|
Records: 3 Duplicates: 0 Warnings: 0
|
|
MariaDB [test]> select a from t;
|
+---+
|
| a |
|
+---+
|
| |
|
| |
|
| |
|
+---+
|
3 rows in set (0.00 sec)
|
|
MariaDB [test]> drop table t;
|
Query OK, 0 rows affected (0.03 sec)
|
|
MariaDB [test]> CREATE TABLE t(
|
-> a varchar(10) NOT NULL,
|
-> b char(1) DEFAULT 'X',
|
-> c char(2) NOT NULL DEFAULT '??',
|
-> d varchar(10) NOT NULL,
|
-> e int(11) DEFAULT 0,
|
-> PRIMARY KEY (a,d),
|
-> KEY (e)
|
-> ) ENGINE=ROCKSDB DEFAULT CHARSET=utf8;
|
ERROR 4063 (HY000): Unsupported collation on string indexed column test.t.a Use binary collation (latin1_bin, binary, utf8_bin).
|
MariaDB [test]>
|
MariaDB [test]> insert into t select 1,1,1,1,0;
|
ERROR 1146 (42S02): Table 'test.t' doesn't exist
|
MariaDB [test]> insert into t select 2,1,1,1,0;
|
ERROR 1146 (42S02): Table 'test.t' doesn't exist
|
MariaDB [test]> insert into t select 3,1,1,1,0;
|
ERROR 1146 (42S02): Table 'test.t' doesn't exist
|
MariaDB [test]>
|
MariaDB [test]> select a from t;
|
ERROR 1146 (42S02): Table 'test.t' doesn't exist
|
MariaDB [test]> set global rocksdb_strict_collation_check=off;
|
Query OK, 0 rows affected (0.00 sec)
|
|
MariaDB [test]> drop table t;
|
ERROR 1051 (42S02): Unknown table 'test.t'
|
MariaDB [test]> CREATE TABLE t(
|
-> a varchar(10) NOT NULL,
|
-> b char(1) DEFAULT 'X',
|
-> c char(2) NOT NULL DEFAULT '??',
|
-> d varchar(10) NOT NULL,
|
-> e int(11) DEFAULT 0,
|
-> PRIMARY KEY (a,d),
|
-> KEY (e)
|
-> ) ENGINE=ROCKSDB DEFAULT CHARSET=utf8;
|
Query OK, 0 rows affected (0.14 sec)
|
|
MariaDB [test]>
|
MariaDB [test]> insert into t select 1,1,1,1,0;
|
Query OK, 1 row affected (0.03 sec)
|
Records: 1 Duplicates: 0 Warnings: 0
|
|
MariaDB [test]> insert into t select 2,1,1,1,0;
|
Query OK, 1 row affected (0.03 sec)
|
Records: 1 Duplicates: 0 Warnings: 0
|
|
MariaDB [test]> insert into t select 3,1,1,1,0;
|
Query OK, 1 row affected (0.04 sec)
|
Records: 1 Duplicates: 0 Warnings: 0
|
|
MariaDB [test]>
|
MariaDB [test]> select a from t;
|
+---+
|
| a |
|
+---+
|
| 1 |
|
| 2 |
|
| 3 |
|
+---+
|
3 rows in set (0.00 sec)
|
|
MariaDB [test]> select a from t;
|
+---+
|
| a |
|
+---+
|
| 1 |
|
| 2 |
|
| 3 |
|
+---+
|
3 rows in set (0.00 sec)
|
|
MariaDB [test]> select a,b from t;
|
+---+------+
|
| a | b |
|
+---+------+
|
| 1 | 1 |
|
| 2 | 1 |
|
| 3 | 1 |
|
+---+------+
|
3 rows in set (0.00 sec)
|
|
MariaDB [test]> select a from t;
|
+---+
|
| a |
|
+---+
|
| 1 |
|
| 2 |
|
| 3 |
|
+---+
|
3 rows in set (0.00 sec)
|
|
MariaDB [test]> explain select a from t;
|
+------+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
|
| 1 | SIMPLE | t | index | NULL | PRIMARY | 64 | NULL | 0 | Using index |
|
+------+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
|
1 row in set (0.00 sec)
|
|
MariaDB [test]> select a from t;
|
+---+
|
| a |
|
+---+
|
| 1 |
|
| 2 |
|
| 3 |
|
+---+
|
3 rows in set (0.00 sec)
|
|
MariaDB [test]> explain select a from t where not 1;
|
+------+-------------+-------+------+---------------+------+---------+------+------+------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+------+---------------+------+---------+------+------+------------------+
|
| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE |
|
+------+-------------+-------+------+---------------+------+---------+------+------+------------------+
|
1 row in set (0.00 sec)
|
|
MariaDB [test]> select a from t;
|
+---+
|
| a |
|
+---+
|
| 1 |
|
| 2 |
|
| 3 |
|
+---+
|
3 rows in set (0.00 sec)
|
|
MariaDB [test]> explain select a from t where not a;
|
+------+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+
|
| 1 | SIMPLE | t | index | PRIMARY | PRIMARY | 64 | NULL | 0 | Using where; Using index |
|
+------+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+
|
1 row in set (0.00 sec)
|
|
MariaDB [test]> select a from t;
|
+---+
|
| a |
|
+---+
|
| 1 |
|
| 2 |
|
| 3 |
|
+---+
|
3 rows in set (0.00 sec)
|
|
MariaDB [test]> explain select a from t where not e;
|
+------+-------------+-------+------+---------------+------+---------+-------+------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+------+---------------+------+---------+-------+------+-------------+
|
| 1 | SIMPLE | t | ref | e | e | 5 | const | 1 | Using index |
|
+------+-------------+-------+------+---------------+------+---------+-------+------+-------------+
|
1 row in set (0.00 sec)
|
|
MariaDB [test]> select a from t;
|
+---+
|
| a |
|
+---+
|
| 1 |
|
| 2 |
|
| 3 |
|
+---+
|
3 rows in set (0.00 sec)
|
|
MariaDB [test]> explain select * from t where not e;
|
+------+-------------+-------+------+---------------+------+---------+-------+------+-------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+------+---------------+------+---------+-------+------+-------+
|
| 1 | SIMPLE | t | ref | e | e | 5 | const | 2 | |
|
+------+-------------+-------+------+---------------+------+---------+-------+------+-------+
|
1 row in set (0.00 sec)
|
|
MariaDB [test]> select a from t;
|
+---+
|
| a |
|
+---+
|
| |
|
| |
|
| |
|
+---+
|
3 rows in set (0.00 sec)
|
|
MariaDB [test]> alter table t engine=rocksdb;
|
Query OK, 3 rows affected (0.27 sec)
|
Records: 3 Duplicates: 0 Warnings: 0
|
|
MariaDB [test]> select a from t;
|
+---+
|
| a |
|
+---+
|
| |
|
| |
|
| |
|
+---+
|
3 rows in set (0.00 sec)
|
|
MariaDB [test]> set global rocksdb_strict_collation_check=off;
|
Query OK, 0 rows affected (0.00 sec)
|
|
MariaDB [test]> drop table t;
|
Query OK, 0 rows affected (0.03 sec)
|
|
MariaDB [test]> CREATE TABLE t(
|
-> a varchar(10) NOT NULL,
|
-> b char(1) DEFAULT 'X',
|
-> c char(2) NOT NULL DEFAULT '??',
|
-> d varchar(10) NOT NULL,
|
-> e int(11) DEFAULT 0,
|
-> PRIMARY KEY (a,d),
|
-> KEY (e)
|
-> ) ENGINE=ROCKSDB DEFAULT CHARSET=utf8;
|
Query OK, 0 rows affected (0.20 sec)
|
|
MariaDB [test]>
|
MariaDB [test]> insert into t select 1,1,1,1,0;
|
Query OK, 1 row affected (0.04 sec)
|
Records: 1 Duplicates: 0 Warnings: 0
|
|
MariaDB [test]> insert into t select 2,1,1,1,0;
|
Query OK, 1 row affected (0.03 sec)
|
Records: 1 Duplicates: 0 Warnings: 0
|
|
MariaDB [test]> insert into t select 3,1,1,1,0;
|
Query OK, 1 row affected (0.03 sec)
|
Records: 1 Duplicates: 0 Warnings: 0
|
|
MariaDB [test]>
|
MariaDB [test]> select a from t;
|
+---+
|
| a |
|
+---+
|
| 1 |
|
| 2 |
|
| 3 |
|
+---+
|
3 rows in set (0.00 sec)
|
|
MariaDB [test]> explain select a from t where not e;
|
+------+-------------+-------+------+---------------+------+---------+-------+------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+------+---------------+------+---------+-------+------+-------------+
|
| 1 | SIMPLE | t | ref | e | e | 5 | const | 1 | Using index |
|
+------+-------------+-------+------+---------------+------+---------+-------+------+-------------+
|
1 row in set (0.00 sec)
|
|
MariaDB [test]> select a from t;
|
+---+
|
| a |
|
+---+
|
| 1 |
|
| 2 |
|
| 3 |
|
+---+
|
3 rows in set (0.00 sec)
|
|
MariaDB [test]> explain select * from t where not a;
|
+------+-------------+-------+------+---------------+------+---------+------+------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+------+---------------+------+---------+------+------+-------------+
|
| 1 | SIMPLE | t | ALL | PRIMARY | NULL | NULL | NULL | 0 | Using where |
|
+------+-------------+-------+------+---------------+------+---------+------+------+-------------+
|
1 row in set (0.00 sec)
|
|
MariaDB [test]> select a from t;
|
+---+
|
| a |
|
+---+
|
| 1 |
|
| 2 |
|
| 3 |
|
+---+
|
3 rows in set (0.00 sec)
|
|
MariaDB [test]> explain select * from t where not e;
|
+------+-------------+-------+------+---------------+------+---------+-------+------+-------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+------+---------------+------+---------+-------+------+-------+
|
| 1 | SIMPLE | t | ref | e | e | 5 | const | 1 | |
|
+------+-------------+-------+------+---------------+------+---------+-------+------+-------+
|
1 row in set (0.00 sec)
|
|
MariaDB [test]> select a from t;
|
+---+
|
| a |
|
+---+
|
| 1 |
|
| 2 |
|
| 3 |
|
+---+
|
3 rows in set (0.00 sec)
|
|
MariaDB [test]> explain select * from t where not e;
|
+------+-------------+-------+------+---------------+------+---------+-------+------+-------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+------+---------------+------+---------+-------+------+-------+
|
| 1 | SIMPLE | t | ref | e | e | 5 | const | 5 | |
|
+------+-------------+-------+------+---------------+------+---------+-------+------+-------+
|
1 row in set (0.00 sec)
|
|
MariaDB [test]> select a from t;
|
+---+
|
| a |
|
+---+
|
| |
|
| |
|
| |
|
+---+
|
3 rows in set (0.00 sec)
|
|
MariaDB [test]> flush query cache;
|
Query OK, 0 rows affected (0.00 sec)
|
|
MariaDB [test]> select a from t;
|
+---+
|
| a |
|
+---+
|
| |
|
| |
|
| |
|
+---+
|
3 rows in set (0.00 sec)
|
Attachments
Issue Links
- includes
-
MDEV-14563 Wrong query plan for query with no PK
-
- Closed
-
- relates to
-
MDEV-14293 MyRocks lacks basic functionality
-
- Closed
-
There are some difficult cases:
innodb_ext_key failure:
"Extended keys" feature the way it works on the SQL layer does not always fully extend the index. One example: when the length of the key reaches MAX_KEY_LENGTH, it will not append any more PK key parts to the secondary key (even if the storage engine actually does).
So, SQL layer thinks that not all of PK key parts are added to the secondary index. Well, the only way to know if a PK column can be decoded from the secondary index is to make a call like this:
handler->index(secondary_key, extended_pk_key_part, 0) & HA_KEYREAD_ONLY
if there is no extended_pk_key_part, there is no way to check...
(A testcase in innodb_ext_key.test assumes the following: if a table has ha_table_flags() & HA_PRIMARY_KEY_IN_READ_INDEX, then all PK columns can be obtained by doing a secondary index read).