Details
Description
A simplified testcase without any customer data:
create function myfunc(a int) returns int DETERMINISTIC return (a+1); |
|
create table t1 ( |
pk int primary key, |
a int, |
b int, |
key(a) |
);
|
insert into t1 select seq,seq,seq from seq_1_to_1000; |
create view v1 as |
select
|
t1.a as col1, |
myfunc(t1.b) as col2 |
from
|
t1;
|
|
create view v2 as |
select
|
t1.a as col1, |
myfunc(t1.b) as col2 |
from
|
t1;
|
create view v3 as |
select col2, col1 from v1 |
union all |
select col2, col1 from v2; |
I get this both on 10.5.11 and 10.2.14:
explain select * from v3 where col1=123; |
+------+-------------+------------+------+---------------+------+---------+-------+------+-------------+ |
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | |
+------+-------------+------------+------+---------------+------+---------+-------+------+-------------+ |
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 2 | Using where | |
| 2 | DERIVED | t1 | ref | a | a | 5 | const | 1 | |
|
| 3 | UNION | t1 | ref | a | a | 5 | const | 1 | | |
+------+-------------+------------+------+---------------+------+---------+-------+------+-------------+ |
add col2=321 into the WHERE clause and on 10.5.11 I get this:
explain select * from v3 where col1=123 and col2=321; |
+------+-------------+------------+------+---------------+------+---------+------+------+-------------+ |
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | |
+------+-------------+------------+------+---------------+------+---------+------+------+-------------+ |
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 2000 | Using where | |
| 2 | DERIVED | t1 | ALL | NULL | NULL | NULL | NULL | 1000 | | |
| 3 | UNION | t1 | ALL | NULL | NULL | NULL | NULL | 1000 | | |
+------+-------------+------------+------+---------------+------+---------+------+------+-------------+ |
while on 10.2.14 table t1 is still accessed through ref access.