Details
Description
Consider the following simple table:
Yuliyas-Air:maria10.6 Valerii$ bin/mysql 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 MariaDB monitor. Commands end with ; or \g.
|
Your MariaDB connection id is 3
|
Server version: 10.6.18-MariaDB MariaDB Server
|
|
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
|
|
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
|
|
MariaDB [test]> select * from tt1;
|
+------+------+------+
|
| id | c1 | c2 |
|
+------+------+------+
|
| 1 | 3 | 1 |
|
| 2 | 2 | 3 |
|
+------+------+------+
|
2 rows in set (0.014 sec)
|
|
MariaDB [test]> show create table tt1\G
|
*************************** 1. row ***************************
|
Table: tt1
|
Create Table: CREATE TABLE `tt1` (
|
`id` int(11) DEFAULT NULL,
|
`c1` int(11) DEFAULT NULL,
|
`c2` varchar(100) DEFAULT NULL,
|
KEY `c1` (`c1`),
|
KEY `c2` (`c2`)
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
|
1 row in set (0.004 sec)
|
|
MariaDB [test]> insert into tt1 select * from tt1;
|
Query OK, 2 rows affected (0.016 sec)
|
Records: 2 Duplicates: 0 Warnings: 0
|
|
MariaDB [test]> insert into tt1 select * from tt1;
|
Query OK, 4 rows affected (0.003 sec)
|
Records: 4 Duplicates: 0 Warnings: 0
|
|
...
|
|
MariaDB [test]> insert into tt1 select * from tt1;
|
Query OK, 32768 rows affected (0.297 sec)
|
Records: 32768 Duplicates: 0 Warnings: 0
|
|
MariaDB [test]> update tt1 set c1 = 4 limit 1;
|
Query OK, 1 row affected (0.012 sec)
|
Rows matched: 1 Changed: 1 Warnings: 0
|
|
MariaDB [test]> select c1, count(*) from tt1 group by c1;
|
+------+----------+
|
| c1 | count(*) |
|
+------+----------+
|
| 2 | 32768 |
|
| 3 | 32767 |
|
| 4 | 1 |
|
+------+----------+
|
3 rows in set (0.042 sec)
|
The following queries get expected execution plans:
MariaDB [test]> explain select * from tt1 where c1 = 3;
|
+------+-------------+-------+------+---------------+------+---------+------+-------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+------+---------------+------+---------+------+-------+-------------+
|
| 1 | SIMPLE | tt1 | ALL | c1 | NULL | NULL | NULL | 65758 | Using where |
|
+------+-------------+-------+------+---------------+------+---------+------+-------+-------------+
|
1 row in set (0.000 sec)
|
|
MariaDB [test]> explain select * from tt1 where c1 = 4;
|
+------+-------------+-------+------+---------------+------+---------+-------+------+-------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+------+---------------+------+---------+-------+------+-------+
|
| 1 | SIMPLE | tt1 | ref | c1 | c1 | 5 | const | 1 | |
|
+------+-------------+-------+------+---------------+------+---------+-------+------+-------+
|
1 row in set (0.003 sec)
|
|
MariaDB [test]> explain select * from tt1 where c1 = '4';
|
+------+-------------+-------+------+---------------+------+---------+-------+------+-------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+------+---------------+------+---------+-------+------+-------+
|
| 1 | SIMPLE | tt1 | ref | c1 | c1 | 5 | const | 1 | |
|
+------+-------------+-------+------+---------------+------+---------+-------+------+-------+
|
1 row in set (0.000 sec)
|
but if we add name_const() as it happens when the query is executed from the stored routine the plan changes to use index condition pushdown:
MariaDB [test]> explain select * from tt1 where c1 = name_const('a',4);
|
+------+-------------+-------+------+---------------+------+---------+-------+------+-----------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+------+---------------+------+---------+-------+------+-----------------------+
|
| 1 | SIMPLE | tt1 | ref | c1 | c1 | 5 | const | 1 | Using index condition |
|
+------+-------------+-------+------+---------------+------+---------+-------+------+-----------------------+
|
1 row in set (0.004 sec)
|
|
MariaDB [test]> explain select * from tt1 where c1 = name_const('a','4');
|
+------+-------------+-------+------+---------------+------+---------+-------+------+-----------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+------+---------------+------+---------+-------+------+-----------------------+
|
| 1 | SIMPLE | tt1 | ref | c1 | c1 | 5 | const | 1 | Using index condition |
|
+------+-------------+-------+------+---------------+------+---------+-------+------+-----------------------+
|
1 row in set (0.000 sec)
|
which is strange and not expected. Moreover, we may note somewhat slower execution as a result:
MariaDB [test]> analyze format=json select * from tt1 where c1 = 4\G
|
*************************** 1. row ***************************
|
ANALYZE: {
|
"query_block": {
|
"select_id": 1,
|
"r_loops": 1,
|
"r_total_time_ms": 2.356,
|
"table": {
|
"table_name": "tt1",
|
"access_type": "ref",
|
"possible_keys": ["c1"],
|
"key": "c1",
|
"key_length": "5",
|
"used_key_parts": ["c1"],
|
"ref": ["const"],
|
"r_loops": 1,
|
"rows": 1,
|
"r_rows": 1,
|
"r_table_time_ms": 0.203,
|
"r_other_time_ms": 0.01,
|
"r_engine_stats": {
|
"pages_accessed": 4
|
},
|
"filtered": 100,
|
"r_filtered": 100
|
}
|
}
|
}
|
1 row in set (0.004 sec)
|
|
MariaDB [test]> analyze format=json select * from tt1 where c1 = name_const('a',4)\G
|
*************************** 1. row ***************************
|
ANALYZE: {
|
"query_block": {
|
"select_id": 1,
|
"r_loops": 1,
|
"r_total_time_ms": 2.423,
|
"table": {
|
"table_name": "tt1",
|
"access_type": "ref",
|
"possible_keys": ["c1"],
|
"key": "c1",
|
"key_length": "5",
|
"used_key_parts": ["c1"],
|
"ref": ["const"],
|
"r_loops": 1,
|
"rows": 1,
|
"r_rows": 1,
|
"r_table_time_ms": 2.396,
|
"r_other_time_ms": 0.02,
|
"r_engine_stats": {
|
"pages_accessed": 4
|
},
|
"filtered": 100,
|
"r_filtered": 100,
|
"index_condition": "tt1.c1 = 4"
|
}
|
}
|
}
|
1 row in set (0.003 sec)
|
Why all these happens and can we force the plans to be the same for such cases?
When trying to see if we could always remove NAME_CONST, I've stumbled on a case where removing it creates issues even in the WHERE clause:
coercibility(var1)
2
SP variable has coercibility =2.
COERCIBILITY(NAME_CONST('name','test'))
2
NAME_CONST has the same, good.
String literal has:
SELECT COERCIBILITY('test');
COERCIBILITY('test')
4
Moreover, one can get NAME_CONST with string literals with explicit collation (which have COERCIBILITY=0):
--source include/have_log_bin.inc
);
show binlog events;
gives
master-bin.000001 1003 Query 1 1158 use `test`;
update t1 set b=b+1 where a= NAME_CONST('var1',_latin1'foo' COLLATE 'latin1_swedish_ci')
Trying to see how replication slave would work:
--source include/have_log_bin.inc
);
explain format=json
before this MDEV, I get:
"attached_condition": "t1.a = convert(NAME_CONST('var1',_latin1'foo' collate latin1_swedish_ci) using utf8mb3)"
after this MDEV, I get:
mysqltest: At line 8: query 'explain format=json
update t1 set b=b+1 where a= NAME_CONST('var1',_latin1'foo' COLLATE 'latin1_swedish_ci')' failed: ER_CANT_AGGREGATE_2COLLATIONS (1267): Illegal mix of collations (utf8mb3_unicode_ci,IMPLICIT) and (latin1_swedish_ci,EXPLICIT) for operation '='
Gosselin, any thoughts?