Cleaner implementation if INSERT ... SELECT with same tables

Tests cleanup (put drop database first in tests)


client/mysql.cc:
  Cleanup of code in last pull
include/config-win.h:
  Remove HAVE_CHSIZE on windows as it's not 64 bit clean
include/my_global.h:
  Portability fix
mysql-test/r/drop.result:
  Clean up results
mysql-test/r/flush.result:
  Clean up results
mysql-test/r/grant_cache.result:
  Clean up results
mysql-test/r/innodb.result:
  Clean up results
mysql-test/r/insert_select.result:
  Clean up results
mysql-test/r/merge.result:
  Clean up results
mysql-test/r/query_cache.result:
  Clean up results
mysql-test/t/drop.test:
  Clean up tests
mysql-test/t/flush.test:
  Clean up tests
mysql-test/t/grant_cache.test:
  Clean up tests
mysql-test/t/innodb.test:
  Clean up tests
mysql-test/t/insert_select.test:
  Added more tests
mysql-test/t/merge.test:
  Test of bug 515
mysql-test/t/query_cache.test:
  Clean up tests
mysql-test/t/symlink.test:
  Clean up tests
sql/mysql_priv.h:
  Cleaner implementation if INSERT ... SELECT with same tables
sql/sql_lex.h:
  Cleaner implementation if INSERT ... SELECT with same tables
sql/sql_list.h:
  Indentation cleanup
sql/sql_parse.cc:
  Cleaner implementation if INSERT ... SELECT with same tables
sql/sql_yacc.yy:
  Cleaner implementation if INSERT ... SELECT with same tables

18 files changed, 138 insertions, 22 deletions

diff --git a/mysql-test/r/ctype_cp1251.result b/mysql-test/r/ctype_cp1251.result
new file mode 100644
index 00000000000..a1f860e1f83
--- /dev/null
+++ b/mysql-test/r/ctype_cp1251.result
@@ -0,0 +1,24 @@
+drop table if exists t1;
+create table t1 (a varchar(10) not null);
+insert into t1 values ("a"),("ab"),("abc");
+select * from t1;
+a
+a
+ab
+abc
+select a, left(a,1) as b from t1;
+a	b
+a	a
+ab	a
+abc	a
+select a, left(a,1) as b from t1 group by a;
+a	b
+a	a
+ab	a
+abc	a
+SELECT DISTINCT RIGHT(a,1) from t1;
+RIGHT(a,1)
+a
+b
+c
+drop table t1;
diff --git a/mysql-test/r/drop.result b/mysql-test/r/drop.result
index 178c3a8cb4f..5c732aebbcc 100644
--- a/mysql-test/r/drop.result
+++ b/mysql-test/r/drop.result
@@ -1,5 +1,4 @@
 drop table if exists t1;
-drop table if exists t1;
 drop table t1;
 Unknown table 't1'
 create table t1(n int);
@@ -24,7 +23,6 @@ n
 drop database if exists mysqltest;
 create database mysqltest;
 drop database mysqltest;
-drop database if exists mysqltest;
 flush tables with read lock;
 create database mysqltest;
 Got one of the listed errors
diff --git a/mysql-test/r/flush.result b/mysql-test/r/flush.result
index 7080f2b6145..8dc73e446c7 100644
--- a/mysql-test/r/flush.result
+++ b/mysql-test/r/flush.result
@@ -1,4 +1,5 @@
 drop table if exists t1;
+drop database if exists mysqltest;
 create temporary table t1(n int not null primary key);
 drop table if exists t2;
 create table t2(n int);
@@ -11,7 +12,6 @@ drop table t2;
 Table 't2' was locked with a READ lock and can't be updated
  drop table t2;
 unlock tables;
-drop database if exists mysqltest;
 create database mysqltest;
 create table mysqltest.t1(n int);
 insert into mysqltest.t1 values (23);
diff --git a/mysql-test/r/grant_cache.result b/mysql-test/r/grant_cache.result
index 3892765f587..fca6864cc63 100644
--- a/mysql-test/r/grant_cache.result
+++ b/mysql-test/r/grant_cache.result
@@ -1,4 +1,5 @@
 drop table if exists test.t1,mysqltest.t1,mysqltest.t2;
+drop database if exists mysqltest;
 reset query cache;
 flush status;
 create database if not exists mysqltest;
diff --git a/mysql-test/r/innodb.result b/mysql-test/r/innodb.result
index 465d4faf0bc..cb6c8397c5a 100644
--- a/mysql-test/r/innodb.result
+++ b/mysql-test/r/innodb.result
@@ -1,4 +1,5 @@
 drop table if exists t1,t2,t3;
+drop database if exists mysqltest;
 create table t1 (id int unsigned not null auto_increment, code tinyint unsigned not null, name char(20) not null, primary key (id), key (code), unique (name)) type=innodb;
 insert into t1 (code, name) values (1, 'Tim'), (1, 'Monty'), (2, 'David'), (2, 'Erik'), (3, 'Sasha'), (3, 'Jeremy'), (4, 'Matt');
 select id, code, name from t1 order by id;
diff --git a/mysql-test/r/insert_select.result b/mysql-test/r/insert_select.result
index dff5d0ff3ca..c007191b562 100644
--- a/mysql-test/r/insert_select.result
+++ b/mysql-test/r/insert_select.result
@@ -566,4 +566,24 @@ a
 5
 5
 5
+insert into t1 select * from t1,t1;
+Not unique table/alias: 't1'
+drop table t1,t2;
+create table t1 (a int not null primary key, b char(10));
+create table t2 (a int not null, b char(10));
+insert into t1 values (1,"t1:1"),(3,"t1:3");
+insert into t2 values (2,"t2:2"), (3,"t2:3");
+insert into t1 select * from t2;
+Duplicate entry '3' for key 1
+select * from t1;
+a	b
+1	t1:1
+3	t1:3
+2	t2:2
+replace into t1 select * from t2;
+select * from t1;
+a	b
+1	t1:1
+3	t2:3
+2	t2:2
 drop table t1,t2;
diff --git a/mysql-test/r/merge.result b/mysql-test/r/merge.result
index 0bffc93c284..cf39faea41b 100644
--- a/mysql-test/r/merge.result
+++ b/mysql-test/r/merge.result
@@ -596,3 +596,15 @@ AND file_code = '0000000115' LIMIT 1;
 table	type	possible_keys	key	key_len	ref	rows	Extra
 t2	const	PRIMARY,files	PRIMARY	33	const,const	1	
 DROP TABLE IF EXISTS t1, t2;
+create table t1 (x int, y int, index xy(x, y));
+create table t2 (x int, y int, index xy(x, y));
+create table t3 (x int, y int, index xy(x, y)) type=merge union=(t1,t2);
+insert into t1 values(1, 2);
+insert into t2 values(1, 3);
+select * from t3 where x = 1 and y < 5 order by y;
+x	y
+1	2
+1	3
+select * from t3 where x = 1 and y < 5 order by y desc;
+x	y
+drop table t1,t2,t3;
diff --git a/mysql-test/r/query_cache.result b/mysql-test/r/query_cache.result
index 6abd572b3d9..0ef0cab423b 100644
--- a/mysql-test/r/query_cache.result
+++ b/mysql-test/r/query_cache.result
@@ -3,6 +3,7 @@ flush query cache;
 reset query cache;
 flush status;
 drop table if exists t1,t2,t3,t11,t21, mysqltest.t1;
+drop database if exists mysqltest;
 create table t1 (a int not null);
 insert into t1 values (1),(2),(3);
 select * from t1;
@@ -357,7 +358,7 @@ show status like "Qcache_queries_in_cache";
 Variable_name	Value
 Qcache_queries_in_cache	0
 drop table t1,t2;
-create database if not exists mysqltest;
+create database mysqltest;
 create table mysqltest.t1 (i int not null auto_increment, a int, primary key (i));
 insert into mysqltest.t1 (a) values (1);
 select * from mysqltest.t1 where i is null;
diff --git a/mysql-test/t/ctype_cp1251-master.opt b/mysql-test/t/ctype_cp1251-master.opt
new file mode 100644
index 00000000000..af089d9f176
--- /dev/null
+++ b/mysql-test/t/ctype_cp1251-master.opt
@@ -0,0 +1,2 @@
+--default-character-set=cp1251 --new
+
diff --git a/mysql-test/t/ctype_cp1251.test b/mysql-test/t/ctype_cp1251.test
new file mode 100644
index 00000000000..fffade35389
--- /dev/null
+++ b/mysql-test/t/ctype_cp1251.test
@@ -0,0 +1,17 @@
+# Test of charset cp1251
+
+--disable_warnings
+drop table if exists t1;
+--enable_warnings
+
+#
+# Test problem with LEFT() (Bug #514)
+#
+
+create table t1 (a varchar(10) not null);
+insert into t1 values ("a"),("ab"),("abc");
+select * from t1;
+select a, left(a,1) as b from t1;
+select a, left(a,1) as b from t1 group by a;
+SELECT DISTINCT RIGHT(a,1) from t1;
+drop table t1;
diff --git a/mysql-test/t/drop.test b/mysql-test/t/drop.test
index c92f2b1f3b9..2f3fa99bac0 100644
--- a/mysql-test/t/drop.test
+++ b/mysql-test/t/drop.test
@@ -1,5 +1,4 @@
 drop table if exists t1;
-drop table if exists t1;
 --error 1051;
 drop table t1;
 create table t1(n int);
@@ -26,7 +25,6 @@ create database mysqltest;
 drop database mysqltest;
 
 # test drop/create database and FLUSH TABLES WITH READ LOCK
-drop database if exists mysqltest;
 flush tables with read lock;
 --error 1209,1223;
 create database mysqltest;
diff --git a/mysql-test/t/flush.test b/mysql-test/t/flush.test
index 540aa4235cc..7754fc2e197 100644
--- a/mysql-test/t/flush.test
+++ b/mysql-test/t/flush.test
@@ -10,6 +10,8 @@ connect (con1,localhost,root,,);
 connect (con2,localhost,root,,);
 connection con1;
 drop table if exists t1;
+drop database if exists mysqltest;
+
 create temporary table t1(n int not null primary key);
 drop table if exists t2;
 create table t2(n int);
@@ -44,7 +46,6 @@ reap;
 
 #test if drop database will wait until we release the global read lock
 connection con1;
-drop database if exists mysqltest;
 create database mysqltest;
 create table mysqltest.t1(n int);
 insert into mysqltest.t1 values (23);
@@ -66,4 +67,3 @@ connection con2;
 insert into t1 values (345);
 select * from t1;
 drop table t1;
-
diff --git a/mysql-test/t/grant_cache.test b/mysql-test/t/grant_cache.test
index 7dfbb3b8d83..95989d1ee99 100644
--- a/mysql-test/t/grant_cache.test
+++ b/mysql-test/t/grant_cache.test
@@ -1,9 +1,12 @@
 -- source include/have_query_cache.inc
 
+drop table if exists test.t1,mysqltest.t1,mysqltest.t2;
+drop database if exists mysqltest;
+
 #
 # Test grants with query cache
 #
-drop table if exists test.t1,mysqltest.t1,mysqltest.t2;
+
 reset query cache;
 flush status;
 connect (root,localhost,root,,test,$MASTER_MYPORT,master.sock);
diff --git a/mysql-test/t/innodb.test b/mysql-test/t/innodb.test
index cf203d87c8b..509cae9c1dc 100644
--- a/mysql-test/t/innodb.test
+++ b/mysql-test/t/innodb.test
@@ -5,6 +5,8 @@
 #
 
 drop table if exists t1,t2,t3;
+drop database if exists mysqltest;
+
 create table t1 (id int unsigned not null auto_increment, code tinyint unsigned not null, name char(20) not null, primary key (id), key (code), unique (name)) type=innodb;
 
 insert into t1 (code, name) values (1, 'Tim'), (1, 'Monty'), (2, 'David'), (2, 'Erik'), (3, 'Sasha'), (3, 'Jeremy'), (4, 'Matt');
diff --git a/mysql-test/t/insert_select.test b/mysql-test/t/insert_select.test
index 5c7fc57262b..d115e9c1af6 100644
--- a/mysql-test/t/insert_select.test
+++ b/mysql-test/t/insert_select.test
@@ -85,6 +85,11 @@ let $VERSION=`select version()`;
 show binlog events;
 drop table t1, t2;
 drop table if exists t1, t2;
+
+#
+# Test of insert ... select from same table
+#
+
 create table t1 (a int not null);
 create table t2 (a int not null);
 insert into t1 values (1);
@@ -99,4 +104,21 @@ insert into t2 select * from t1 as t2;
 select * from t1;
 insert into t1 select t2.a from t1,t2;
 select * from t1;
+--error 1066
+insert into t1 select * from t1,t1;
+drop table t1,t2;
+
+#
+# test replace ... select
+#
+
+create table t1 (a int not null primary key, b char(10));
+create table t2 (a int not null, b char(10));
+insert into t1 values (1,"t1:1"),(3,"t1:3");
+insert into t2 values (2,"t2:2"), (3,"t2:3");
+--error 1062
+insert into t1 select * from t2;
+select * from t1;
+replace into t1 select * from t2;
+select * from t1;
 drop table t1,t2;
diff --git a/mysql-test/t/merge.test b/mysql-test/t/merge.test
index e2cd8b57b7e..b625e780f1f 100644
--- a/mysql-test/t/merge.test
+++ b/mysql-test/t/merge.test
@@ -237,3 +237,16 @@ EXPLAIN SELECT * FROM t2 WHERE fileset_id = 2
 AND file_code = '0000000115' LIMIT 1;
 DROP TABLE IF EXISTS t1, t2;
 
+#
+# Test of ORDER BY DESC on key (Bug #515)
+#
+
+create table t1 (x int, y int, index xy(x, y));
+create table t2 (x int, y int, index xy(x, y));
+create table t3 (x int, y int, index xy(x, y)) type=merge union=(t1,t2);
+insert into t1 values(1, 2);
+insert into t2 values(1, 3);
+select * from t3 where x = 1 and y < 5 order by y;
+# Bug is that followng query returns empty set while it must be same as above
+select * from t3 where x = 1 and y < 5 order by y desc;
+drop table t1,t2,t3;
diff --git a/mysql-test/t/query_cache.test b/mysql-test/t/query_cache.test
index f0f3063c00d..fe258b2c7e1 100644
--- a/mysql-test/t/query_cache.test
+++ b/mysql-test/t/query_cache.test
@@ -11,6 +11,7 @@ flush query cache; # This crashed in some versions
 reset query cache;
 flush status;
 drop table if exists t1,t2,t3,t11,t21, mysqltest.t1;
+drop database if exists mysqltest;
 
 #
 # First simple test
@@ -241,7 +242,7 @@ drop table t1,t2;
 #
 # noncachable ODBC work around (and prepare cache for drop database)
 #
-create database if not exists mysqltest;
+create database mysqltest;
 create table mysqltest.t1 (i int not null auto_increment, a int, primary key (i));
 insert into mysqltest.t1 (a) values (1);
 select * from mysqltest.t1 where i is null;
diff --git a/mysql-test/t/symlink.test b/mysql-test/t/symlink.test
index 586761921b8..d8bba12b645 100644
--- a/mysql-test/t/symlink.test
+++ b/mysql-test/t/symlink.test
@@ -3,10 +3,12 @@ disable_query_log;
 show variables like "have_symlink";
 enable_query_log;
 
+drop table if exists t1,t2,t7,t8,t9;
+drop database if exists mysqltest;
+
 #
 # First create little data to play with
 #
-drop table if exists t1,t2,t7,t8,t9;
 create table t1 (a int not null auto_increment, b char(16) not null, primary key (a));
 create table t2 (a int not null auto_increment, b char(16) not null, primary key (a));
 insert into t1 (b) values ("test"),("test1"),("test2"),("test3");
@@ -64,26 +66,25 @@ create table t1 (a int not null auto_increment, b char(16) not null, primary key
 
 # Check that we cannot link over a table from another database.
 
-drop database if exists test_mysqltest;
-create database test_mysqltest;
+create database mysqltest;
 
 --error 1,1
-create table test_mysqltest.t9 (a int not null auto_increment, b char(16) not null, primary key (a)) type=myisam index directory="/this-dir-does-not-exist";
+create table mysqltest.t9 (a int not null auto_increment, b char(16) not null, primary key (a)) type=myisam index directory="/this-dir-does-not-exist";
 
 --error 1103,1103
-create table test_mysqltest.t9 (a int not null auto_increment, b char(16) not null, primary key (a)) type=myisam index directory="not-hard-path";
+create table mysqltest.t9 (a int not null auto_increment, b char(16) not null, primary key (a)) type=myisam index directory="not-hard-path";
 
 --error 1,1
-eval create table test_mysqltest.t9 (a int not null auto_increment, b char(16) not null, primary key (a)) type=myisam index directory="$MYSQL_TEST_DIR/var/run";
+eval create table mysqltest.t9 (a int not null auto_increment, b char(16) not null, primary key (a)) type=myisam index directory="$MYSQL_TEST_DIR/var/run";
 
 --error 1,1
-eval create table test_mysqltest.t9 (a int not null auto_increment, b char(16) not null, primary key (a)) type=myisam data directory="$MYSQL_TEST_DIR/var/tmp";
+eval create table mysqltest.t9 (a int not null auto_increment, b char(16) not null, primary key (a)) type=myisam data directory="$MYSQL_TEST_DIR/var/tmp";
 enable_query_log;
 
-# Check moving table t9 from default database to test_mysqltest;
+# Check moving table t9 from default database to mysqltest;
 # In this case the symlinks should be removed.
 
-alter table t9 rename test_mysqltest.t9;
-select count(*) from test_mysqltest.t9;
-show create table test_mysqltest.t9;
-drop database test_mysqltest;
+alter table t9 rename mysqltest.t9;
+select count(*) from mysqltest.t9;
+show create table mysqltest.t9;
+drop database mysqltest;