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
111
112
113
|
# set default_storage_engine = 'ndbcluster';
-- echo TESTS FROM gcol_select_ndb_extra_tests.inc
# t1 has an auto-increment pk
create table t1
(t1_seq_pk int auto_increment not null primary key,
t1_base_col int,
t1_vir_gcol int generated always as (-t1_base_col) virtual,
t1_sto_gcol int generated always as (-t1_base_col) stored,
index (t1_sto_gcol));
insert into t1 (t1_base_col) values (2), (1), (1), (3), (NULL);
# t2 has a visible pk and a unique index on a stored gcol
create table t2
(t2_pk int primary key,
t2_vir_gcol int generated always as (-t2_pk) virtual,
t2_sto_gcol_unique int generated always as (-t2_pk) stored unique);
insert into t2 (t2_pk) values (1),(2),(3),(4),(5),(6);
# t3 has a hidden pk, and the virtual column is after the stored column
create table t3
(t3_base_col int,
t3_sto_gcol int generated always as (-t3_base_col) stored,
t3_vir_gcol int generated always as (-t3_base_col) virtual,
index(t3_sto_gcol)
);
insert into t3 (t3_base_col) values (2), (1), (1), (3), (NULL);
# t4 is like t1 but changes relative order of stored & virtual gcols
create table t4
(t4_seq_pk int auto_increment not null primary key,
t4_base_col int,
t4_sto_gcol int generated always as (-t4_base_col) stored,
t4_vir_gcol int generated always as (-t4_base_col) virtual,
index (t4_sto_gcol));
insert into t4 (t4_base_col) values (2), (1), (1), (3), (NULL);
# t5 is like t4 but moves base_col relative to gcols
create table t5
(t5_seq_pk int auto_increment not null primary key,
t5_sto_gcol int generated always as (-t5_base_col) stored,
t5_vir_gcol int generated always as (-t5_base_col) virtual,
t5_base_col int,
index (t5_sto_gcol));
insert into t5 (t5_base_col) values (2), (1), (1), (3), (NULL);
--echo # TABLE SCAN
--sorted_result
select * from t2;
--echo # TABLE SCAN WITH MASK
--sorted_result
select t2_pk, t2_vir_gcol from t2;
--sorted_result
select t2_sto_gcol_unique from t2;
--echo # RANGE SCANS
--sorted_result
select * from t2 where t2_pk = 2 or t2_pk = 4;
--sorted_result
select t2_vir_gcol from t2 where t2_pk = 2 or t2_pk = 4;
--sorted_result
select * from t2 where t2_sto_gcol_unique = -2 or t2_sto_gcol_unique = -4;
--sorted_result
select t2_vir_gcol from t2 where t2_sto_gcol_unique = -2 or t2_sto_gcol_unique = -4;
--echo # Join t1 to t2
# set debug = 'O,/Users/jdd/Desktop/TraceFiles/tf-mtr:t:N:d';
--sorted_result
select t1_base_col from t1
join t2 on (t1_sto_gcol = t2_sto_gcol_unique)
where t2_sto_gcol_unique between -2 and -1 ;
# set debug = '';
--echo # As above but join t3 to t2
--sorted_result
select t3_base_col from t3
join t2 on (t3_sto_gcol = t2_sto_gcol_unique)
where t2_sto_gcol_unique between -2 and -1 ;
--echo # Join t4 to t2
--sorted_result
select t4_base_col from t4
join t2 on (t4_sto_gcol = t2_sto_gcol_unique)
where t2_sto_gcol_unique between -2 and -1 ;
--echo # Join t5 to t2
--sorted_result
select t5_base_col from t5
join t2 on (t5_sto_gcol = t2_sto_gcol_unique)
where t2_sto_gcol_unique between -2 and -1 ;
--echo # Subquery transformed to semi-join
--sorted_result
select * from t1
where t1_sto_gcol in
(select t2_sto_gcol_unique from t2
where t2_sto_gcol_unique between -2 and -1);
# Extra cleanup
drop table t4;
drop table t5;
|
