Debian Codegraph @

mysql-test/suite/innodb/r/bug32554667.result

raw
1 create table ts(a int primary key, b int, c int, d int, index(b,c));
2 insert into ts
3 values(1,1,1,1),(2,2,2,2),(3,3,3,3),(4,4,4,4),(5,5,5,5),(6,6,6,6),(7,7,7,7),(8
4 ,8,8,8),(9,9,9,9);
5 ANALYZE TABLE ts;
6 Table Op Msg_type Msg_text
7 test.ts analyze status OK
8 SELECT @@optimizer_switch LIKE '%index_condition_pushdown=on%';
9 @@optimizer_switch LIKE '%index_condition_pushdown=on%'
10 1
11 explain select b,c,d from ts where b>=5 and b<8 and c=7 for update;
12 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
13 1 SIMPLE ts NULL range b b 10 NULL 2 11.11 Using index condition
14 Warnings:
15 Note 1003 /* select#1 */ select `test`.`ts`.`b` AS `b`,`test`.`ts`.`c` AS `c`,`test`.`ts`.`d` AS `d` from `test`.`ts` where ((`test`.`ts`.`c` = 7) and (`test`.`ts`.`b` >= 5) and (`test`.`ts`.`b` < 8))
16 SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
17 begin;
18 select b,c,d from ts where b>=5 and b<8 and c=7 for update;
19 b c d
20 7 7 7
21 select INDEX_NAME,LOCK_TYPE,LOCK_MODE,LOCK_STATUS,LOCK_DATA from performance_schema.data_locks;
22 INDEX_NAME LOCK_TYPE LOCK_MODE LOCK_STATUS LOCK_DATA
23 NULL TABLE IX GRANTED NULL
24 PRIMARY RECORD X,REC_NOT_GAP GRANTED 7
25 b RECORD X,REC_NOT_GAP GRANTED 7, 7, 7
26 commit;
27 SET @@optimizer_switch='index_condition_pushdown=off';
28 SELECT @@optimizer_switch LIKE '%index_condition_pushdown=on%';
29 @@optimizer_switch LIKE '%index_condition_pushdown=on%'
30 0
31 explain select b,c,d from ts where b>=5 and b<8 and c=7 for update;
32 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
33 1 SIMPLE ts NULL range b b 10 NULL 2 11.11 Using where
34 Warnings:
35 Note 1003 /* select#1 */ select `test`.`ts`.`b` AS `b`,`test`.`ts`.`c` AS `c`,`test`.`ts`.`d` AS `d` from `test`.`ts` where ((`test`.`ts`.`c` = 7) and (`test`.`ts`.`b` >= 5) and (`test`.`ts`.`b` < 8))
36 SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
37 begin;
38 select b,c,d from ts where b>=5 and b<8 and c=7 for update;
39 b c d
40 7 7 7
41 select INDEX_NAME,LOCK_TYPE,LOCK_MODE,LOCK_STATUS,LOCK_DATA from performance_schema.data_locks;
42 INDEX_NAME LOCK_TYPE LOCK_MODE LOCK_STATUS LOCK_DATA
43 NULL TABLE IX GRANTED NULL
44 PRIMARY RECORD X,REC_NOT_GAP GRANTED 7
45 b RECORD X,REC_NOT_GAP GRANTED 7, 7, 7
46 commit;
47 SET @@optimizer_switch='index_condition_pushdown=off';
48 DROP TABLE ts;