85
85
SELECT ROW(2,10) <=> ROW(3,4);
86
86
SELECT ROW(NULL,10) <=> ROW(3,NULL);
89
# Bug #27484: nested row expressions in IN predicate
93
SELECT ROW(1,ROW(2,3)) IN (ROW(1,ROW(2,3)),ROW(1,1));
95
SELECT ROW(1,ROW(2,3)) IN (ROW(1,ROW(2,3)),ROW(1,1),ROW(1,ROW(2,3)));
97
SELECT ROW(1,ROW(2,3)) IN (ROW(1,ROW(2,3)),ROW(1,ROW(2,2,2)));
99
SELECT ROW(1,ROW(2,3,4)) IN (ROW(1,ROW(2,3,4)),ROW(1,ROW(2,2)));
102
SELECT ROW(1,ROW(2,3)) IN (ROW(1,ROW(2,3)),(SELECT 1,1));
104
SELECT ROW(1,ROW(2,3)) IN (ROW(1,ROW(2,3)),(SELECT 1,1),ROW(1,ROW(2,4)));
106
SELECT ROW(1,ROW(2,3)) IN ((SELECT 1,1),ROW(1,ROW(2,3)));
109
SELECT ROW(2,1) IN (ROW(21,2),ROW(ROW(1,1,3),0));
111
SELECT ROW(2,1) IN (ROW(ROW(1,1,3),0),ROW(21,2));
114
# Bug#27704: erroneous comparison of rows with NULL components
116
CREATE TABLE t1(a int, b int, c int);
117
INSERT INTO t1 VALUES (1, 2, 3),
118
(NULL, 2, 3 ), (1, NULL, 3 ), (1, 2, NULL),
119
(NULL, 2, 3+1), (1, NULL, 3+1), (1, 2+1, NULL),
120
(NULL, 2, 3-1), (1, NULL, 3-1), (1, 2-1, NULL);
122
SELECT (1,2,3) = (1, NULL, 3);
123
SELECT (1,2,3) = (1+1, NULL, 3);
124
SELECT (1,2,3) = (1, NULL, 3+1);
125
SELECT * FROM t1 WHERE (a,b,c) = (1,2,3);
127
SELECT (1,2,3) <> (1, NULL, 3);
128
SELECT (1,2,3) <> (1+1, NULL, 3);
129
SELECT (1,2,3) <> (1, NULL, 3+1);
130
SELECT * FROM t1 WHERE (a,b,c) <> (1,2,3);
132
SELECT (1,2,3) < (NULL, 2, 3);
133
SELECT (1,2,3) < (1, NULL, 3);
134
SELECT (1,2,3) < (1-1, NULL, 3);
135
SELECT (1,2,3) < (1+1, NULL, 3);
136
SELECT * FROM t1 WHERE (a,b,c) < (1,2,3);
138
SELECT (1,2,3) <= (NULL, 2, 3);
139
SELECT (1,2,3) <= (1, NULL, 3);
140
SELECT (1,2,3) <= (1-1, NULL, 3);
141
SELECT (1,2,3) <= (1+1, NULL, 3);
142
SELECT * FROM t1 WHERE (a,b,c) <= (1,2,3);
144
SELECT (1,2,3) > (NULL, 2, 3);
145
SELECT (1,2,3) > (1, NULL, 3);
146
SELECT (1,2,3) > (1-1, NULL, 3);
147
SELECT (1,2,3) > (1+1, NULL, 3);
148
SELECT * FROM t1 WHERE (a,b,c) > (1,2,3);
150
SELECT (1,2,3) >= (NULL, 2, 3);
151
SELECT (1,2,3) >= (1, NULL, 3);
152
SELECT (1,2,3) >= (1-1, NULL, 3);
153
SELECT (1,2,3) >= (1+1, NULL, 3);
154
SELECT * FROM t1 WHERE (a,b,c) >= (1,2,3);
88
158
# End of 4.1 tests
139
209
SELECT * FROM t1,t2 WHERE t2.a=t1.a AND (t2.b,t2.c)=(2,1);
141
211
DROP TABLE t1,t2;
214
# Bug #27154: crash (memory corruption) when using row equalities
218
a int, b int, c int, d int, e int, f int, g int, h int,
219
PRIMARY KEY (a,b,c,d,e,f,g)
221
INSERT INTO t1 VALUES (1,2,3,4,5,6,7,99);
223
SELECT h FROM t1 WHERE (a,b,c,d,e,f,g)=(1,2,3,4,5,6,7);
225
SET @x:= (SELECT h FROM t1 WHERE (a,b,c,d,e,f,g)=(1,2,3,4,5,6,7));