Debian Codegraph @

mysql-test/suite/gcol_ndb/t/gcol_ndb_record_layout.test

raw
1 ################################################################################
2 #
3 # Test various row layouts
4 ################################################################################
5
6 --source include/have_ndb.inc
7 --source suite/gcol_ndb/inc/mtr_conditionals.inc
8
9 eval SET @@session.default_storage_engine = 'ndbcluster';
10
11
12 ####### TEST gcol after blob
13
14 CREATE TABLE gcol_after_blob (
15 i int primary key,
16 b blob,
17 k int as (i+2)
18 );
19
20 insert into gcol_after_blob(i,b) values (1, "ABCD");
21
22 select i from gcol_after_blob;
23 select b from gcol_after_blob;
24 select k from gcol_after_blob;
25 select * from gcol_after_blob;
26 select k,b from gcol_after_blob where i = 1;
27 select b,i from gcol_after_blob where k = 3;
28 select b,i from gcol_after_blob where k = 1;
29 update gcol_after_blob set b = "ABCDEFGHIJK" where i = 1;
30 insert into gcol_after_blob(i,b) values (2, "ABCD");
31 select b,i from gcol_after_blob where k = 4;
32 update gcol_after_blob set b = "LMNOPQRST" where k = 4;
33 select b,i from gcol_after_blob where k = 4;
34
35 DROP TABLE gcol_after_blob;
36
37
38 ####### TEST stored generated blob
39
40 CREATE TABLE stored_blob_gcol (
41 i int primary key,
42 j varchar(20),
43 k text as (repeat(j,i)) stored
44 );
45
46 insert into stored_blob_gcol(i,j) values (1,"c__"), (2,"b__"), (3,"a__");
47
48 select i from stored_blob_gcol order by i;
49 select j from stored_blob_gcol order by j;
50 select k from stored_blob_gcol order by i;
51 select * from stored_blob_gcol order by j;
52 delete from stored_blob_gcol where length(j) = length(k);
53 select * from stored_blob_gcol where i < 3;
54
55 DROP TABLE stored_blob_gcol;
56
57
58 ####### TEST virtual generated blob
59
60 CREATE TABLE virtual_blob_gcol (
61 i int primary key,
62 j varchar(20),
63 k text as (repeat(j,i)) virtual
64 );
65
66 insert into virtual_blob_gcol(i,j) values (1,"c__"), (2,"b__"), (3,"a__");
67
68 select i from virtual_blob_gcol order by i;
69 select j from virtual_blob_gcol order by j;
70 select k from virtual_blob_gcol order by i;
71 select * from virtual_blob_gcol order by j;
72 delete from virtual_blob_gcol where length(j) = length(k);
73 select * from virtual_blob_gcol where i < 3;
74
75 DROP TABLE virtual_blob_gcol;
76
77
78 ####### TEST virtual generated blob with hidden pk
79
80 CREATE TABLE virtual_blob_gcol_hid_pk (
81 i int,
82 j varchar(20),
83 k text as (repeat(j,i)) virtual
84 );
85
86 insert into virtual_blob_gcol_hid_pk(i,j) values (2,"a__"), (2,"b__"), (2,"c__");
87 select * from virtual_blob_gcol_hid_pk order by j;
88
89 DROP TABLE virtual_blob_gcol_hid_pk;
90
91
92 ####### TEST gcol with char(0)
93
94 CREATE TABLE gcol_char0 (
95 i int primary key,
96 c char(0),
97 v int as (i % 2),
98 z varchar(30)
99 );
100
101 insert into gcol_char0(i,c) values(1, NULL), (2, NULL), (3, ""), (4, "");
102
103 select * from gcol_char0 order by i;
104 select c,v from gcol_char0 where i = 3;
105 select * from gcol_char0 where c is null and v = 0;
106 select * from gcol_char0 where c is not null and v = 1;
107 update gcol_char0 set z = repeat("ABC_", i);
108 select * from gcol_char0 order by i;
109
110 DROP TABLE gcol_char0;
111
112
113 ### TEST gcol as first column
114
115 CREATE TABLE gcol_first(
116 v int as (i+j),
117 i int primary key,
118 j int
119 );
120
121 insert into gcol_first (i,j) values (1, 1), (2, null), (3, 3);
122 --sorted_result
123 select v from gcol_first;
124 select * from gcol_first where v is null;
125 select i from gcol_first where j is null;
126
127 DROP TABLE gcol_first;
128