Debian Codegraph @

mysql-test/suite/ndb_opt/join_pushdown_bka.test

raw
1 #
2 # Test of pushed joins, aka SPJ, or AQL.
3 # (Test with defaults + 'batched_key_access=on')
4 #
5 # NOTE: BKA will turn of lots of pushed join, we
6 # are unsure if this is the best strategy, or
7 # if its better/possible to sacrify the BKA
8 #
9
10 --source include/have_ndb.inc
11 if (!`select locate('batched_key_access', @@global.optimizer_switch) > 0`)
12 {
13 skip Need batched_key_access;
14 }
15
16 --source ../../include/elide_costs.inc
17
18 set @@global.optimizer_switch='batched_key_access=on';
19
20 --source join_pushdown.inc
21
22 ################################
23 # BKA specific test cases / bugs
24 #
25
26 #Bug#32774281 MULTI-RANGE LOOKUP FAILS,
27 # ERROR 4316 'KEY ATTRIBUTES ARE NOT ALLOWED TO BE NULL'
28 #
29 # The MRR flag HA_MRR_NO_NULL_ENDPOINTS was specified even if
30 # such a guarantee could not be made: If the join condition
31 # contained a IS NULL predicate, or the '<=>' 'NULL-safe' equal
32 # operator was used in the predicate, NULL values keys _are_
33 # generated during execution.
34 #
35 # Note1: The HA_MRR_NO_NULL_ENDPOINTS flag is only used by the
36 # ha_ndcluster handler.
37 #
38 # Note2: HA_MRR_NO_NULL_ENDPOINTS is only checked for a
39 # 'UNIQUE KEY ... USING HASH'
40
41
42 CREATE TABLE f (
43 col_int_key int DEFAULT NULL
44 ) ENGINE=ndbcluster;
45
46 CREATE TABLE p (
47 col_int int DEFAULT NULL,
48 col_int_unique int DEFAULT NULL,
49 UNIQUE KEY ix1 (col_int,col_int_unique) USING HASH
50 ) ENGINE=ndbcluster;
51
52 insert into f values (1),(NULL);
53 insert into p values (NULL,1);
54
55 # A IS NULL predicate will generate MRR NULL value keys
56 # -> A BKA access should not be generated
57 let $query=
58 SELECT * FROM f AS table2
59 STRAIGHT_JOIN p AS table4 ON table4.col_int IS NULL
60 AND table4.col_int_unique = table2.col_int_key;
61
62 --replace_regex $elide_costs_and_rows
63 eval explain format=tree $query;
64 --sorted_result
65 eval $query;
66
67 # Using a literal instead will -> BKA access
68 let $query=
69 SELECT * FROM f AS table2
70 STRAIGHT_JOIN p AS table4 ON table4.col_int = 1
71 AND table4.col_int_unique = table2.col_int_key;
72
73 --replace_regex $elide_costs_and_rows
74 eval explain format=tree $query;
75 --sorted_result
76 eval $query;
77
78
79 # Similar test cases with '<=>' vs '='
80 # Only '=' guarantee HA_MRR_NO_NULL_ENDPOINTS
81 let $query=
82 SELECT * FROM f AS table2
83 STRAIGHT_JOIN p AS table4 ON table4.col_int <=> table2.col_int_key
84 AND table4.col_int_unique = table2.col_int_key;
85 --replace_regex $elide_costs_and_rows
86 eval explain format=tree $query;
87 --sorted_result
88 eval $query;
89
90 let $query=
91 SELECT * FROM f AS table2
92 STRAIGHT_JOIN p AS table4 ON table4.col_int = table2.col_int_key
93 AND table4.col_int_unique <=> table2.col_int_key;
94 --replace_regex $elide_costs_and_rows
95 eval explain format=tree $query;
96 --sorted_result
97 eval $query;
98
99 let $query=
100 SELECT * FROM f AS table2
101 STRAIGHT_JOIN p AS table4 ON table4.col_int = table2.col_int_key
102 AND table4.col_int_unique = table2.col_int_key;
103 --replace_regex $elide_costs_and_rows
104 eval explain format=tree $query;
105 --sorted_result
106 eval $query;
107
108 DROP TABLE f,p;
109
110 set @@global.optimizer_switch=default;