diff options
Diffstat (limited to 'mysql-test/t/distinct.test')
| -rw-r--r-- | mysql-test/t/distinct.test | 53 |
1 files changed, 50 insertions, 3 deletions
diff --git a/mysql-test/t/distinct.test b/mysql-test/t/distinct.test index a057eee8e37..b671d7b06cd 100644 --- a/mysql-test/t/distinct.test +++ b/mysql-test/t/distinct.test @@ -326,11 +326,11 @@ drop table t1,t2; CREATE TABLE t1 ( html varchar(5) default NULL, rin int(11) default '0', - out int(11) default '0' + rout int(11) default '0' ) ENGINE=MyISAM; INSERT INTO t1 VALUES ('1',1,0); -SELECT DISTINCT html,SUM(out)/(SUM(rin)+1) as 'prod' FROM t1 GROUP BY rin; +SELECT DISTINCT html,SUM(rout)/(SUM(rin)+1) as 'prod' FROM t1 GROUP BY rin; drop table t1; # @@ -378,6 +378,13 @@ EXPLAIN SELECT DISTINCT a,b,d FROM t2 GROUP BY c,b,d; DROP TABLE t1,t2; +# Bug 9784 DISTINCT IFNULL truncates data +# +create table t1 (id int, dsc varchar(50)); +insert into t1 values (1, "line number one"), (2, "line number two"), (3, "line number three"); +select distinct id, IFNULL(dsc, '-') from t1; +drop table t1; + # # Bug 21456: SELECT DISTINCT(x) produces incorrect results when using order by # @@ -389,6 +396,47 @@ explain SELECT DISTINCT a, b FROM t1 ORDER BY b; SELECT DISTINCT a, b FROM t1 ORDER BY b; DROP TABLE t1; +# End of 4.1 tests + + +# +# Bug #15745 ( COUNT(DISTINCT CONCAT(x,y)) returns wrong result) +# +CREATE TABLE t1 ( + ID int(11) NOT NULL auto_increment, + x varchar(20) default NULL, + y decimal(10,0) default NULL, + PRIMARY KEY (ID), + KEY (y) +) ENGINE=MyISAM DEFAULT CHARSET=latin1; + +INSERT INTO t1 VALUES +(1,'ba','-1'), +(2,'ba','1150'), +(306,'ba','-1'), +(307,'ba','1150'), +(611,'ba','-1'), +(612,'ba','1150'); + +select count(distinct x,y) from t1; +select count(distinct concat(x,y)) from t1; +drop table t1; + +# +# Bug #18068: SELECT DISTINCT +# +CREATE TABLE t1 (a INT, b INT, PRIMARY KEY (a,b)); + +INSERT INTO t1 VALUES (1, 101); +INSERT INTO t1 SELECT a + 1, a + 101 FROM t1; +INSERT INTO t1 SELECT a + 2, a + 102 FROM t1; +INSERT INTO t1 SELECT a + 4, a + 104 FROM t1; +INSERT INTO t1 SELECT a + 8, a + 108 FROM t1; + +EXPLAIN SELECT DISTINCT a,a FROM t1 WHERE b < 12 ORDER BY a; +SELECT DISTINCT a,a FROM t1 WHERE b < 12 ORDER BY a; + +DROP TABLE t1; # #Bug #20836: Selecting into variables results in wrong results being returned # @@ -454,4 +502,3 @@ SELECT * FROM t2; DROP TABLE t1; DROP TABLE t2; -# End of 4.1 tests |