Debian Codegraph @

mysql-test/suite/ndb/t/ndb_hidden_pk.test

raw
1 -- source include/have_ndb.inc
2
3 --disable_warnings
4 DROP TABLE IF EXISTS t1;
5 --enable_warnings
6
7 #
8 # ndb_hidden_pk.test
9 # Test use of tables with hidden primary key
10 #
11
12 # Bag of nullable ints
13 CREATE TABLE t1 (
14 attr1 INT
15 ) ENGINE=ndbcluster;
16
17 insert into t1 values (0), (1), (2), (3), (4), (1), (1), (1), (2), (2), (2), (3), (3), (3), (NULL), (NULL), (NULL);
18
19 # all
20 select * from t1 order by attr1;
21
22 # many
23 select * from t1 where attr1 = 1;
24
25 # many NULLs
26 select * from t1 where attr1 IS NULL;
27
28 # one
29 select * from t1 where attr1 = 4;
30
31 # none
32 select * from t1 where attr1 = 1000;
33
34 # Single value update
35 update t1 set attr1=7 where attr1=4;
36 select * from t1 order by attr1;
37
38
39 # Multi value update
40 update t1 set attr1=10 where attr1=1;
41 select * from t1 order by attr1;
42
43 # Multi NULL value update
44 update t1 set attr1=20 where attr1 IS NULL;
45 select * from t1 order by attr1;
46
47 # Put them back...
48 update t1 set attr1=NULL where attr1=20;
49 select * from t1 order by attr1;
50
51 # Single value delete
52 delete from t1 where attr1=0;
53 select * from t1 order by attr1;
54
55
56 # Multi value delete
57 delete from t1 where attr1 IS NULL;
58 select * from t1 order by attr1;
59
60
61 drop table t1;
62
63
64 # Hidden primary key and blob only
65 CREATE TABLE t1 (
66 b blob
67 ) ENGINE=ndbcluster;
68
69 insert into t1 values (NULL), (NULL), ('Something'), (''), (REPEAT('Lots', 2000));
70
71 # all
72 select * from t1 order by b;
73
74 # many null
75 select * from t1 where b IS NULL;
76
77 # one
78 select * from t1 where b='Something';
79
80 # large
81 select count(*) from t1 where b=REPEAT('Lots', 2000);
82
83 # none
84 select * from t1 where b='Imaginary';
85
86 drop table t1;
87
88
89 # Unique index instead of PK
90 #
91 CREATE TABLE t1 (
92 a int,
93 b int,
94 UNIQUE(a)
95 ) ENGINE=NDBCLUSTER;
96
97
98 insert into t1 values (NULL, NULL), (NULL, NULL), (NULL, 1), (1, 1), (2, 2), (3, 3);
99
100 # select all
101 select * from t1 order by a, b;
102
103 # select many null
104 select * from t1 where a IS NULL order by b;
105
106 # select one
107 select * from t1 where a=2;
108
109 # select none
110 select * from t1 where a=10;
111
112 # update none
113 update t1 set b=12 where a=12;
114 select * from t1 order by a, b;
115
116 # update one
117 update t1 set b=4 where a=3;
118 select * from t1 order by a, b;
119
120 # update many
121 update t1 set b=2 where a=1;
122 select * from t1 order by a, b;
123
124 # update many null
125 update t1 set b=14 where a IS NULL;
126 select * from t1 order by a,b;
127
128 # Bug # 37516 had problems with delete via unique index
129 # on table with hidden PK
130 # delete none
131 delete from t1 where a = 999;
132 select * from t1 order by a, b;
133
134 # delete one
135 delete from t1 where a=3;
136 select * from t1 order by a, b;
137
138 # delete many null
139 delete from t1 where a IS NULL;
140 select * from t1 order by a, b;
141
142 drop table t1;