Debian Codegraph @

mysql-test/suite/gcol_ndb/inc/gcol_select_ndb_extra_tests.inc

raw
1 # set default_storage_engine = 'ndbcluster';
2
3
4 -- echo TESTS FROM gcol_select_ndb_extra_tests.inc
5
6 # t1 has an auto-increment pk
7 create table t1
8 (t1_seq_pk int auto_increment not null primary key,
9 t1_base_col int,
10 t1_vir_gcol int generated always as (-t1_base_col) virtual,
11 t1_sto_gcol int generated always as (-t1_base_col) stored,
12 index (t1_sto_gcol));
13
14 insert into t1 (t1_base_col) values (2), (1), (1), (3), (NULL);
15
16 # t2 has a visible pk and a unique index on a stored gcol
17 create table t2
18 (t2_pk int primary key,
19 t2_vir_gcol int generated always as (-t2_pk) virtual,
20 t2_sto_gcol_unique int generated always as (-t2_pk) stored unique);
21
22 insert into t2 (t2_pk) values (1),(2),(3),(4),(5),(6);
23
24 # t3 has a hidden pk, and the virtual column is after the stored column
25 create table t3
26 (t3_base_col int,
27 t3_sto_gcol int generated always as (-t3_base_col) stored,
28 t3_vir_gcol int generated always as (-t3_base_col) virtual,
29 index(t3_sto_gcol)
30 );
31
32 insert into t3 (t3_base_col) values (2), (1), (1), (3), (NULL);
33
34 # t4 is like t1 but changes relative order of stored & virtual gcols
35 create table t4
36 (t4_seq_pk int auto_increment not null primary key,
37 t4_base_col int,
38 t4_sto_gcol int generated always as (-t4_base_col) stored,
39 t4_vir_gcol int generated always as (-t4_base_col) virtual,
40 index (t4_sto_gcol));
41
42 insert into t4 (t4_base_col) values (2), (1), (1), (3), (NULL);
43
44 # t5 is like t4 but moves base_col relative to gcols
45 create table t5
46 (t5_seq_pk int auto_increment not null primary key,
47 t5_sto_gcol int generated always as (-t5_base_col) stored,
48 t5_vir_gcol int generated always as (-t5_base_col) virtual,
49 t5_base_col int,
50 index (t5_sto_gcol));
51
52 insert into t5 (t5_base_col) values (2), (1), (1), (3), (NULL);
53
54
55 --echo # TABLE SCAN
56 --sorted_result
57 select * from t2;
58
59 --echo # TABLE SCAN WITH MASK
60 --sorted_result
61 select t2_pk, t2_vir_gcol from t2;
62 --sorted_result
63 select t2_sto_gcol_unique from t2;
64
65 --echo # RANGE SCANS
66 --sorted_result
67 select * from t2 where t2_pk = 2 or t2_pk = 4;
68 --sorted_result
69 select t2_vir_gcol from t2 where t2_pk = 2 or t2_pk = 4;
70 --sorted_result
71 select * from t2 where t2_sto_gcol_unique = -2 or t2_sto_gcol_unique = -4;
72 --sorted_result
73 select t2_vir_gcol from t2 where t2_sto_gcol_unique = -2 or t2_sto_gcol_unique = -4;
74
75 --echo # Join t1 to t2
76 # set debug = 'O,/Users/jdd/Desktop/TraceFiles/tf-mtr:t:N:d';
77 --sorted_result
78 select t1_base_col from t1
79 join t2 on (t1_sto_gcol = t2_sto_gcol_unique)
80 where t2_sto_gcol_unique between -2 and -1 ;
81 # set debug = '';
82
83 --echo # As above but join t3 to t2
84 --sorted_result
85 select t3_base_col from t3
86 join t2 on (t3_sto_gcol = t2_sto_gcol_unique)
87 where t2_sto_gcol_unique between -2 and -1 ;
88
89 --echo # Join t4 to t2
90 --sorted_result
91 select t4_base_col from t4
92 join t2 on (t4_sto_gcol = t2_sto_gcol_unique)
93 where t2_sto_gcol_unique between -2 and -1 ;
94
95 --echo # Join t5 to t2
96 --sorted_result
97 select t5_base_col from t5
98 join t2 on (t5_sto_gcol = t2_sto_gcol_unique)
99 where t2_sto_gcol_unique between -2 and -1 ;
100
101
102 --echo # Subquery transformed to semi-join
103 --sorted_result
104 select * from t1
105 where t1_sto_gcol in
106 (select t2_sto_gcol_unique from t2
107 where t2_sto_gcol_unique between -2 and -1);
108
109
110
111 # Extra cleanup
112 drop table t4;
113 drop table t5;