diff options
Diffstat (limited to 'mysql-test')
| -rw-r--r-- | mysql-test/r/subselect.result | 49 | ||||
| -rw-r--r-- | mysql-test/t/subselect.test | 42 |
2 files changed, 91 insertions, 0 deletions
diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result index dd579d5e8a6..25df7a56aa3 100644 --- a/mysql-test/r/subselect.result +++ b/mysql-test/r/subselect.result @@ -4150,6 +4150,55 @@ SELECT ((a1,a2) IN (SELECT * FROM t2 WHERE b2 > 0)) IS NULL FROM t1; 0 0 DROP TABLE t1, t2; +CREATE TABLE t1 (s1 BINARY(5), s2 VARBINARY(5)); +INSERT INTO t1 VALUES (0x41,0x41), (0x42,0x42), (0x43,0x43); +SELECT s1, s2 FROM t1 WHERE s2 IN (SELECT s1 FROM t1); +s1 s2 +SELECT s1, s2 FROM t1 WHERE (s2, 10) IN (SELECT s1, 10 FROM t1); +s1 s2 +CREATE INDEX I1 ON t1 (s1); +CREATE INDEX I2 ON t1 (s2); +SELECT s1, s2 FROM t1 WHERE s2 IN (SELECT s1 FROM t1); +s1 s2 +SELECT s1, s2 FROM t1 WHERE (s2, 10) IN (SELECT s1, 10 FROM t1); +s1 s2 +TRUNCATE t1; +INSERT INTO t1 VALUES (0x41,0x41); +SELECT * FROM t1 WHERE s1 = (SELECT s2 FROM t1); +s1 s2 +DROP TABLE t1; +CREATE TABLE t1 (a1 VARBINARY(2) NOT NULL DEFAULT '0', PRIMARY KEY (a1)); +CREATE TABLE t2 (a2 BINARY(2) default '0', INDEX (a2)); +CREATE TABLE t3 (a3 BINARY(2) default '0'); +INSERT INTO t1 VALUES (1),(2),(3),(4); +INSERT INTO t2 VALUES (1),(2),(3); +INSERT INTO t3 VALUES (1),(2),(3); +SELECT LEFT(t2.a2, 1) FROM t2,t3 WHERE t3.a3=t2.a2; +LEFT(t2.a2, 1) +1 +2 +3 +SELECT t1.a1, t1.a1 in (SELECT t2.a2 FROM t2,t3 WHERE t3.a3=t2.a2) FROM t1; +a1 t1.a1 in (SELECT t2.a2 FROM t2,t3 WHERE t3.a3=t2.a2) +1 0 +2 0 +3 0 +4 0 +DROP TABLE t1,t2,t3; +CREATE TABLE t1 (a1 BINARY(3) PRIMARY KEY, b1 VARBINARY(3)); +CREATE TABLE t2 (a2 VARBINARY(3) PRIMARY KEY); +CREATE TABLE t3 (a3 VARBINARY(3) PRIMARY KEY); +INSERT INTO t1 VALUES (1,10), (2,20), (3,30), (4,40); +INSERT INTO t2 VALUES (2), (3), (4), (5); +INSERT INTO t3 VALUES (10), (20), (30); +SELECT LEFT(t1.a1,1) FROM t1,t3 WHERE t1.b1=t3.a3; +LEFT(t1.a1,1) +1 +2 +3 +SELECT a2 FROM t2 WHERE t2.a2 IN (SELECT t1.a1 FROM t1,t3 WHERE t1.b1=t3.a3); +a2 +DROP TABLE t1, t2, t3; End of 5.0 tests. CREATE TABLE t1 (a int, b int); INSERT INTO t1 VALUES (2,22),(1,11),(2,22); diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test index 1bf1dda9b15..df51239b256 100644 --- a/mysql-test/t/subselect.test +++ b/mysql-test/t/subselect.test @@ -3002,6 +3002,48 @@ INSERT INTO t2 VALUES (103, 203); SELECT ((a1,a2) IN (SELECT * FROM t2 WHERE b2 > 0)) IS NULL FROM t1; DROP TABLE t1, t2; +# +# Bug #28076: inconsistent binary/varbinary comparison +# + +CREATE TABLE t1 (s1 BINARY(5), s2 VARBINARY(5)); +INSERT INTO t1 VALUES (0x41,0x41), (0x42,0x42), (0x43,0x43); + +SELECT s1, s2 FROM t1 WHERE s2 IN (SELECT s1 FROM t1); +SELECT s1, s2 FROM t1 WHERE (s2, 10) IN (SELECT s1, 10 FROM t1); + +CREATE INDEX I1 ON t1 (s1); +CREATE INDEX I2 ON t1 (s2); + +SELECT s1, s2 FROM t1 WHERE s2 IN (SELECT s1 FROM t1); +SELECT s1, s2 FROM t1 WHERE (s2, 10) IN (SELECT s1, 10 FROM t1); + +TRUNCATE t1; +INSERT INTO t1 VALUES (0x41,0x41); +SELECT * FROM t1 WHERE s1 = (SELECT s2 FROM t1); + +DROP TABLE t1; + +CREATE TABLE t1 (a1 VARBINARY(2) NOT NULL DEFAULT '0', PRIMARY KEY (a1)); +CREATE TABLE t2 (a2 BINARY(2) default '0', INDEX (a2)); +CREATE TABLE t3 (a3 BINARY(2) default '0'); +INSERT INTO t1 VALUES (1),(2),(3),(4); +INSERT INTO t2 VALUES (1),(2),(3); +INSERT INTO t3 VALUES (1),(2),(3); +SELECT LEFT(t2.a2, 1) FROM t2,t3 WHERE t3.a3=t2.a2; +SELECT t1.a1, t1.a1 in (SELECT t2.a2 FROM t2,t3 WHERE t3.a3=t2.a2) FROM t1; +DROP TABLE t1,t2,t3; + +CREATE TABLE t1 (a1 BINARY(3) PRIMARY KEY, b1 VARBINARY(3)); +CREATE TABLE t2 (a2 VARBINARY(3) PRIMARY KEY); +CREATE TABLE t3 (a3 VARBINARY(3) PRIMARY KEY); +INSERT INTO t1 VALUES (1,10), (2,20), (3,30), (4,40); +INSERT INTO t2 VALUES (2), (3), (4), (5); +INSERT INTO t3 VALUES (10), (20), (30); +SELECT LEFT(t1.a1,1) FROM t1,t3 WHERE t1.b1=t3.a3; +SELECT a2 FROM t2 WHERE t2.a2 IN (SELECT t1.a1 FROM t1,t3 WHERE t1.b1=t3.a3); +DROP TABLE t1, t2, t3; + --echo End of 5.0 tests. # |