diff options
| -rw-r--r-- | mysql-test/r/subselect.result | 33 | ||||
| -rw-r--r-- | mysql-test/t/subselect.test | 24 |
2 files changed, 40 insertions, 17 deletions
diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result index e84c3957760..f5abc4ed42a 100644 --- a/mysql-test/r/subselect.result +++ b/mysql-test/r/subselect.result @@ -2895,14 +2895,25 @@ select * from t1 where NOT(s1 = ALL (select s1/s1 from t1)); s1 2 drop table t1; -CREATE TABLE t1 (a INT, b INT, PRIMARY KEY (a,b)); -INSERT INTO t1 VALUES(26, 1), (48, 2); -SELECT * FROM t1 r WHERE (r.a,r.b) IN (SELECT a,MAX(b) FROM t1 GROUP BY a); -a b -26 1 -48 2 -SELECT * FROM t1 r WHERE (r.a,r.b) IN (SELECT a + 0,MAX(b) FROM t1 GROUP BY a); -a b -26 1 -48 2 -DROP TABLE t1; +create table t1 ( +retailerID varchar(8) NOT NULL, +statusID int(10) unsigned NOT NULL, +changed datetime NOT NULL, +UNIQUE KEY retailerID (retailerID, statusID, changed) +); +INSERT INTO t1 VALUES("0026", "1", "2005-12-06 12:18:56"); +INSERT INTO t1 VALUES("0026", "2", "2006-01-06 12:25:53"); +INSERT INTO t1 VALUES("0037", "1", "2005-12-06 12:18:56"); +INSERT INTO t1 VALUES("0037", "2", "2006-01-06 12:25:53"); +INSERT INTO t1 VALUES("0048", "1", "2006-01-06 12:37:50"); +INSERT INTO t1 VALUES("0059", "1", "2006-01-06 12:37:50"); +select * from t1 r1 +where (r1.retailerID,(r1.changed)) in +(SELECT r2.retailerId,(max(changed)) from t1 r2 +group by r2.retailerId); +retailerID statusID changed +0026 2 2006-01-06 12:25:53 +0037 2 2006-01-06 12:25:53 +0048 1 2006-01-06 12:37:50 +0059 1 2006-01-06 12:37:50 +drop table t1; diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test index 780a4f3a002..10dfb788c10 100644 --- a/mysql-test/t/subselect.test +++ b/mysql-test/t/subselect.test @@ -1865,12 +1865,24 @@ drop table t1; # # Bug #16255: Subquery in where # -CREATE TABLE t1 (a INT, b INT, PRIMARY KEY (a,b)); - -INSERT INTO t1 VALUES(26, 1), (48, 2); +create table t1 ( + retailerID varchar(8) NOT NULL, + statusID int(10) unsigned NOT NULL, + changed datetime NOT NULL, + UNIQUE KEY retailerID (retailerID, statusID, changed) +); -SELECT * FROM t1 r WHERE (r.a,r.b) IN (SELECT a,MAX(b) FROM t1 GROUP BY a); -SELECT * FROM t1 r WHERE (r.a,r.b) IN (SELECT a + 0,MAX(b) FROM t1 GROUP BY a); +INSERT INTO t1 VALUES("0026", "1", "2005-12-06 12:18:56"); +INSERT INTO t1 VALUES("0026", "2", "2006-01-06 12:25:53"); +INSERT INTO t1 VALUES("0037", "1", "2005-12-06 12:18:56"); +INSERT INTO t1 VALUES("0037", "2", "2006-01-06 12:25:53"); +INSERT INTO t1 VALUES("0048", "1", "2006-01-06 12:37:50"); +INSERT INTO t1 VALUES("0059", "1", "2006-01-06 12:37:50"); + +select * from t1 r1 + where (r1.retailerID,(r1.changed)) in + (SELECT r2.retailerId,(max(changed)) from t1 r2 + group by r2.retailerId); +drop table t1; -DROP TABLE t1; # End of 4.1 tests |