|
1
by brian
clean slate |
1 |
#
|
2 |
# Bug when using comparions of strings and integers. |
|
3 |
#
|
|
4 |
||
5 |
--disable_warnings
|
|
6 |
drop table if exists t1; |
|
7 |
--enable_warnings
|
|
8 |
||
9 |
CREATE TABLE t1 (id CHAR(12) not null, PRIMARY KEY (id)); |
|
10 |
insert into t1 values ('000000000001'),('000000000002'); |
|
11 |
explain select * from t1 where id=000000000001; |
|
12 |
select * from t1 where id=000000000001; |
|
13 |
delete from t1 where id=000000000002; |
|
14 |
select * from t1; |
|
15 |
drop table t1; |
|
16 |
||
17 |
#
|
|
18 |
# Check the following: |
|
19 |
# "a" == "a " |
|
20 |
# "a\0" < "a" |
|
21 |
# "a\0" < "a " |
|
22 |
||
23 |
SELECT 'a' = 'a '; |
|
24 |
SELECT 'a\0' < 'a'; |
|
25 |
SELECT 'a\0' < 'a '; |
|
26 |
SELECT 'a\t' < 'a'; |
|
27 |
SELECT 'a\t' < 'a '; |
|
28 |
||
29 |
CREATE TABLE t1 (a char(10) not null); |
|
30 |
INSERT INTO t1 VALUES ('a'),('a\0'),('a\t'),('a '); |
|
31 |
SELECT hex(a),STRCMP(a,'a'), STRCMP(a,'a ') FROM t1; |
|
32 |
DROP TABLE t1; |
|
33 |
||
34 |
# Bug #8134: Comparison against CHAR(31) at end of string |
|
35 |
SELECT CHAR(31) = '', '' = CHAR(31); |
|
36 |
# Extra test |
|
37 |
SELECT CHAR(30) = '', '' = CHAR(30); |
|
38 |
||
39 |
# End of 4.1 tests |
|
40 |
||
41 |
#
|
|
42 |
#Bug #21159: Optimizer: wrong result after AND with different data types |
|
43 |
#
|
|
44 |
create table t1 (a tinyint(1),b binary(1)); |
|
45 |
insert into t1 values (0x01,0x01); |
|
46 |
select * from t1 where a=b; |
|
47 |
select * from t1 where a=b and b=0x01; |
|
48 |
drop table if exists t1; |
|
49 |
||
50 |
#
|
|
51 |
# Bug #31887: DML Select statement not returning same results when executed |
|
52 |
# in version 5 |
|
53 |
#
|
|
54 |
||
55 |
CREATE TABLE t1 (b int(2) zerofill, c int(2) zerofill); |
|
56 |
INSERT INTO t1 (b,c) VALUES (1,2), (1,1), (2,2); |
|
57 |
||
58 |
SELECT CONCAT(b,c), CONCAT(b,c) = '0101' FROM t1; |
|
59 |
||
60 |
EXPLAIN EXTENDED SELECT b,c FROM t1 WHERE b = 1 AND CONCAT(b,c) = '0101'; |
|
61 |
SELECT b,c FROM t1 WHERE b = 1 AND CONCAT(b,c) = '0101'; |
|
62 |
||
63 |
CREATE TABLE t2 (a int); |
|
64 |
INSERT INTO t2 VALUES (1),(2); |
|
65 |
||
66 |
SELECT a, |
|
67 |
(SELECT COUNT(*) FROM t1 |
|
68 |
WHERE b = t2.a AND CONCAT(b,c) = CONCAT('0',t2.a,'01')) x |
|
69 |
FROM t2 ORDER BY a; |
|
70 |
||
71 |
EXPLAIN EXTENDED |
|
72 |
SELECT a, |
|
73 |
(SELECT COUNT(*) FROM t1 |
|
74 |
WHERE b = t2.a AND CONCAT(b,c) = CONCAT('0',t2.a,'01')) x |
|
75 |
FROM t2 ORDER BY a; |
|
76 |
||
77 |
DROP TABLE t1,t2; |
|
78 |
||
79 |
--echo End of 5.0 tests
|