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
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
|
#
# Bug when using comparions of strings and integers.
#
--disable_warnings
drop table if exists t1;
--enable_warnings
CREATE TABLE t1 (id CHAR(12) not null, PRIMARY KEY (id));
insert into t1 values ('000000000001'),('000000000002');
analyze table t1;
explain select * from t1 where id=000000000001;
select * from t1 where id=000000000001;
delete from t1 where id=000000000002;
select * from t1;
drop table t1;
#
# Check the following:
# "a" == "a "
# "a\0" < "a"
# "a\0" < "a "
set names latin1;
SELECT 'a' = 'a ';
SELECT 'a\0' < 'a';
SELECT 'a\0' < 'a ';
SELECT 'a\t' < 'a';
SELECT 'a\t' < 'a ';
CREATE TABLE t1 (a char(10) not null) charset latin1;
INSERT INTO t1 VALUES ('a'),('a\0'),('a\t'),('a ');
SELECT hex(a),STRCMP(a,'a'), STRCMP(a,'a ') FROM t1;
DROP TABLE t1;
set names default;
# Bug #8134: Comparison against CHAR(31) at end of string
SELECT CHAR(31) = '', '' = CHAR(31);
# Extra test
SELECT CHAR(30) = '', '' = CHAR(30);
# End of 4.1 tests
#
#Bug #21159: Optimizer: wrong result after AND with different data types
#
create table t1 (a tinyint(1),b binary(1));
insert into t1 values (0x01,0x01);
select * from t1 where a=b;
select * from t1 where a=b and b=0x01;
drop table if exists t1;
#
# Bug #31887: DML Select statement not returning same results when executed
# in version 5
#
CREATE TABLE t1 (b int(2) zerofill, c int(2) zerofill);
INSERT INTO t1 (b,c) VALUES (1,2), (1,1), (2,2);
SELECT CONCAT(b,c), CONCAT(b,c) = '0101' FROM t1;
analyze table t1;
--replace_column 10 x
EXPLAIN SELECT b,c FROM t1 WHERE b = 1 AND CONCAT(b,c) = '0101';
SELECT b,c FROM t1 WHERE b = 1 AND CONCAT(b,c) = '0101';
CREATE TABLE t2 (a int);
INSERT INTO t2 VALUES (1),(2);
SELECT a,
(SELECT COUNT(*) FROM t1
WHERE b = t2.a AND CONCAT(b,c) = CONCAT('0',t2.a,'01')) x
FROM t2 ORDER BY a;
analyze table t1;
analyze table t2;
--replace_column 10 x
EXPLAIN
SELECT a,
(SELECT COUNT(*) FROM t1
WHERE b = t2.a AND CONCAT(b,c) = CONCAT('0',t2.a,'01')) x
FROM t2 ORDER BY a;
DROP TABLE t1,t2;
#
# Bug #39353: Multiple conditions on timestamp column crashes server
#
SET sql_mode = 'NO_ENGINE_SUBSTITUTION';
CREATE TABLE t1 (a TIMESTAMP);
INSERT INTO t1 VALUES (NOW()),(NOW()),(NOW());
SELECT * FROM t1 WHERE a > '2008-01-01' AND a = '0000-00-00';
DROP TABLE t1;
SET sql_mode = default;
--echo End of 5.0 tests
#
# Bug #11764818 57692: Crash in item_func_in::val_int() with ZEROFILL
#
CREATE TABLE t1(a INT ZEROFILL);
SELECT 1 FROM t1 WHERE t1.a IN (1, t1.a) AND t1.a=2;
DROP TABLE t1;
--echo
--echo Bug #27452148: ASSERTION FAILED: (SLEN % 4) == 0 IN
--echo MY_STRNNCOLLSP_UTF32_BIN
--echo
SET NAMES latin1;
CREATE TABLE t1(a CHAR(10) CHARACTER SET utf32 COLLATE utf32_bin);
INSERT INTO t1 VALUES('a'),('b'),('c');
SELECT ROW('1', '1') > ROW(a, '1') FROM t1;
SELECT ROW(a, '1') > ROW('1', '1') FROM t1;
DROP TABLE t1;
SET NAMES default;
--echo #
--echo # Bug#30961924 AND OF TWO PREDICATES WITH DIFFERENT COLLATION ON SAME COLUMN GIVES WRONG RESULT
--echo #
CREATE TABLE t1(
firstname CHAR(20),
lastname CHAR(20)
) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
INSERT INTO t1 VALUES("john","doe"), ("John","Doe");
# Case-insensitive comparison, john is John
SELECT * FROM t1 WHERE firstname = 'john';
SELECT * FROM t1 WHERE firstname = 'john' COLLATE utf8mb4_0900_ai_ci;
# Case-insensitive comparison, john is not John
SELECT * FROM t1 WHERE firstname = 'john' COLLATE utf8mb4_0900_as_cs;
SELECT * FROM t1
WHERE firstname = 'john' COLLATE utf8mb4_0900_ai_ci AND
firstname = 'john' COLLATE utf8mb4_0900_as_cs;
SELECT * FROM t1
WHERE firstname = 'john' COLLATE utf8mb4_0900_as_cs AND
firstname = 'john' COLLATE utf8mb4_0900_ai_ci;
DROP TABLE t1;
--echo Comparisons with parameters and extreme values
CREATE TABLE t(a DECIMAL(30,0));
INSERT INTO t VALUES(0);
SELECT * FROM t;
set @maxint=18446744073709551615;
SELECT @maxint;
# was wrong empty
SELECT * FROM t WHERE a < @maxint;
SELECT * FROM t WHERE a < 18446744073709551615;
SELECT * FROM t WHERE 0 < 18446744073709551615;
SELECT * FROM t WHERE 0 < @maxint;
DROP TABLE t;
|
