Added support for hex strings to mysqlimport

A lot of new tests to mysqltest
Fixed bug with BDB tables and autocommit

1 files changed, 184 insertions, 0 deletions

diff --git a/mysql-test/t/distinct.test b/mysql-test/t/distinct.test
new file mode 100644
index 00000000000..9ddc7ac4a9b
--- /dev/null
+++ b/mysql-test/t/distinct.test
@@ -0,0 +1,184 @@
+#
+# Bug with distinct and INSERT INTO
+# Bug with group by and not used fields
+#
+
+drop table if exists t1,t2,t3;
+
+CREATE TABLE t1 (id int,facility char(20));
+CREATE TABLE t2 (facility char(20));
+INSERT INTO t1 VALUES (NULL,NULL);
+INSERT INTO t1 VALUES (-1,'');
+INSERT INTO t1 VALUES (0,'');
+INSERT INTO t1 VALUES (1,'/L');
+INSERT INTO t1 VALUES (2,'A01');
+INSERT INTO t1 VALUES (3,'ANC');
+INSERT INTO t1 VALUES (4,'F01');
+INSERT INTO t1 VALUES (5,'FBX');
+INSERT INTO t1 VALUES (6,'MT');
+INSERT INTO t1 VALUES (7,'P');
+INSERT INTO t1 VALUES (8,'RV');
+INSERT INTO t1 VALUES (9,'SRV');
+INSERT INTO t1 VALUES (10,'VMT');
+INSERT INTO t2 SELECT DISTINCT FACILITY FROM t1;
+
+select id from t1 group by id;
+select * from t1 order by id;
+select id-5,facility from t1 order by "id-5";
+select id,concat(facility) from t1 group by id ;
+select id+0 as a,max(id),concat(facility) as b from t1 group by a order by b desc,a;
+select id >= 0 and id <= 5 as grp,count(*) from t1 group by grp;
+
+SELECT DISTINCT FACILITY FROM t1;
+SELECT FACILITY FROM t2;
+SELECT count(*) from t1,t2 where t1.facility=t2.facility;
+select count(facility) from t1;
+select count(*) from t1;
+select count(*) from t1 where facility IS NULL;
+select count(*) from t1 where facility = NULL;
+select count(*) from t1 where facility IS NOT NULL;
+select count(*) from t1 where id IS NULL;
+select count(*) from t1 where id IS NOT NULL;
+
+drop table t1,t2;
+
+#
+# Problem with distinct without results
+#
+CREATE TABLE t1 (UserId int(11) DEFAULT '0' NOT NULL);
+INSERT INTO t1 VALUES (20);
+INSERT INTO t1 VALUES (27);
+
+SELECT UserId FROM t1 WHERE Userid=22;
+SELECT UserId FROM t1 WHERE UserId=22 group by Userid;
+SELECT DISTINCT UserId FROM t1 WHERE UserId=22 group by Userid;
+SELECT DISTINCT UserId FROM t1 WHERE UserId=22;
+drop table t1;
+
+#
+# Test of distinct
+#
+
+CREATE TABLE t1 (a int(10) unsigned not null primary key,b int(10) unsigned);
+INSERT INTO t1 VALUES (1,1),(2,1);
+CREATE TABLE t2 (a int(10) unsigned not null, key (A));
+INSERT INTO t2 VALUES (1),(2);
+CREATE TABLE t3 (a int(10) unsigned, key(A), b text);
+INSERT INTO t3 VALUES (1,'1'),(2,'2');
+SELECT DISTINCT t3.b FROM t3,t2,t1 WHERE t3.a=t1.b AND t1.a=t2.a;
+INSERT INTO t2 values (1),(2),(3);
+INSERT INTO t3 VALUES (1,'1'),(2,'2'),(1,'1'),(2,'2');
+explain SELECT distinct t3.a FROM t3,t2,t1 WHERE t3.a=t1.b AND t1.a=t2.a;
+SELECT distinct t3.a FROM t3,t2,t1 WHERE t3.a=t1.b AND t1.a=t2.a;
+
+# Create a lot of data into t3;
+create temporary table t4 select * from t3;
+insert into t3 select * from t4;
+insert into t4 select * from t3;
+insert into t3 select * from t4;
+insert into t4 select * from t3;
+insert into t3 select * from t4;
+insert into t4 select * from t3;
+insert into t3 select * from t4;
+
+explain select distinct t1.a from t1,t3 where t1.a=t3.a;
+#flush status;
+select distinct t1.a from t1,t3 where t1.a=t3.a;
+#show status like 'Handler%';
+#flush status;
+select distinct 1 from t1,t3 where t1.a=t3.a;
+#show status like 'Handler%';
+drop table t1,t2,t3,t4;
+
+CREATE TABLE t1 (name varchar(255));
+INSERT INTO t1 VALUES ('aa'),('ab'),('ac'),('ad'),('ae');
+SELECT DISTINCT * FROM t1 LIMIT 2;
+SELECT DISTINCT name FROM t1 LIMIT 2;
+SELECT DISTINCT 1 FROM t1 LIMIT 2;
+drop table t1;
+
+CREATE TABLE t1 (
+  ID int(11) NOT NULL auto_increment,
+  NAME varchar(75) DEFAULT '' NOT NULL,
+  LINK_ID int(11) DEFAULT '0' NOT NULL,
+  PRIMARY KEY (ID),
+  KEY NAME (NAME),
+  KEY LINK_ID (LINK_ID)
+);
+
+INSERT INTO t1 (ID, NAME, LINK_ID) VALUES (1,'Mike',0),(2,'Jack',0),(3,'Bill',0);
+
+CREATE TABLE t2 (
+  ID int(11) NOT NULL auto_increment,
+  NAME varchar(150) DEFAULT '' NOT NULL,
+  PRIMARY KEY (ID),
+  KEY NAME (NAME)
+);
+
+SELECT DISTINCT
+    t2.id AS key_link_id,
+    t2.name AS link
+FROM t1
+LEFT JOIN t2 ON t1.link_id=t2.id
+GROUP BY t1.id
+ORDER BY link;
+drop table t1,t2;
+
+#
+# Problem with table dependencies
+#
+
+create table t1 (
+    id		int not null,
+    name	tinytext not null,
+    unique	(id)
+);
+create table t2 (
+    id		int not null,
+    idx		int not null,
+    unique	(id, idx)
+);
+create table t3 (
+    id		int not null,
+    idx		int not null,
+    unique	(id, idx)
+);
+insert into t1 values (1,'yes'), (2,'no');
+insert into t2 values (1,1);
+insert into t3 values (1,1);
+EXPLAIN
+SELECT DISTINCT
+    t1.id
+from
+    t1
+    straight_join
+    t2
+    straight_join
+    t3
+    straight_join
+    t1 as j_lj_t2 left join t2 as t2_lj
+        on j_lj_t2.id=t2_lj.id
+    straight_join
+    t1 as j_lj_t3 left join t3 as t3_lj
+        on j_lj_t3.id=t3_lj.id
+WHERE
+    ((t1.id=j_lj_t2.id AND t2_lj.id IS NULL) OR (t1.id=t2.id AND t2.idx=2))
+    AND ((t1.id=j_lj_t3.id AND t3_lj.id IS NULL) OR (t1.id=t3.id AND t3.idx=2));
+SELECT DISTINCT
+    t1.id
+from
+    t1
+    straight_join
+    t2
+    straight_join
+    t3
+    straight_join
+    t1 as j_lj_t2 left join t2 as t2_lj
+        on j_lj_t2.id=t2_lj.id
+    straight_join
+    t1 as j_lj_t3 left join t3 as t3_lj
+        on j_lj_t3.id=t3_lj.id
+WHERE
+    ((t1.id=j_lj_t2.id AND t2_lj.id IS NULL) OR (t1.id=t2.id AND t2.idx=2))
+    AND ((t1.id=j_lj_t3.id AND t3_lj.id IS NULL) OR (t1.id=t3.id AND t3.idx=2));
+drop table t1,t2,t3;