mirror of
https://github.com/MariaDB/server.git
synced 2025-12-14 08:15:42 +01:00
54 lines
1.2 KiB
Text
54 lines
1.2 KiB
Text
#
|
|
# MDEV-31496 Make optimizer handle UCASE(varchar_col)=...
|
|
# Check the coverage in optimizer trace.
|
|
#
|
|
|
|
--source include/not_embedded.inc
|
|
--source include/have_sequence.inc
|
|
|
|
|
|
create table t1 (
|
|
col1 varchar(32),
|
|
col2 varchar(32),
|
|
key(col1),
|
|
key(col2)
|
|
) collate utf8mb3_general_ci;
|
|
|
|
insert into t1
|
|
select
|
|
concat('A-', seq),
|
|
concat('A-', seq)
|
|
from seq_1_to_100;
|
|
|
|
set
|
|
@tmp_ot= @@optimizer_trace,
|
|
@tmp_os=@@optimizer_switch,
|
|
optimizer_switch='sargable_casefold=on',
|
|
optimizer_trace=1;
|
|
|
|
#Enable after fix MDEV-32034
|
|
--disable_view_protocol
|
|
explain select * from t1 where 'abc'=upper(col1);
|
|
select
|
|
json_detailed(json_extract(trace, '$**.sargable_casefold_removal')) as JS
|
|
from information_schema.optimizer_trace;
|
|
|
|
explain select * from t1 where ucase(col2)=upper(col1);
|
|
select
|
|
json_detailed(json_extract(trace, '$**.sargable_casefold_removal')) as JS
|
|
from information_schema.optimizer_trace;
|
|
|
|
explain
|
|
select * from t1 where upper(col1) IN ('A-3','A-4','a-5');
|
|
--echo # Will show the rewrite:
|
|
select
|
|
json_detailed(json_extract(trace, '$**.sargable_casefold_removal')) as JS
|
|
from information_schema.optimizer_trace;
|
|
--enable_view_protocol
|
|
|
|
set
|
|
optimizer_trace=@tmp_ot,
|
|
optimizer_switch=@tmp_os;
|
|
|
|
drop table t1;
|
|
|