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