Merge tulin@bk-internal.mysql.com:/home/bk/mysql-4.1

into build.mysql.com:/users/tulin/mysql-4.1-ndb-merge

19 files changed, 350 insertions, 0 deletions

diff --git a/mysql-test/r/cast.result b/mysql-test/r/cast.result
index 6564a3e392b..ccf75f68e88 100644
--- a/mysql-test/r/cast.result
+++ b/mysql-test/r/cast.result
@@ -155,3 +155,26 @@ NULL
 select cast(NULL as BINARY);
 cast(NULL as BINARY)
 NULL
+CREATE TABLE t1 (a enum ('aac','aab','aaa') not null);
+INSERT INTO t1 VALUES ('aaa'),('aab'),('aac');
+SELECT a, CAST(a AS CHAR) FROM t1 ORDER BY CAST(a AS UNSIGNED) ;
+a	CAST(a AS CHAR)
+aac	aac
+aab	aab
+aaa	aaa
+SELECT a, CAST(a AS CHAR(3)) FROM t1 ORDER BY CAST(a AS CHAR(2)), a;
+a	CAST(a AS CHAR(3))
+aac	aac
+aab	aab
+aaa	aaa
+SELECT a, CAST(a AS UNSIGNED) FROM t1 ORDER BY CAST(a AS CHAR) ;
+a	CAST(a AS UNSIGNED)
+aaa	3
+aab	2
+aac	1
+SELECT a, CAST(a AS CHAR(2)) FROM t1 ORDER BY CAST(a AS CHAR(3)), a;
+a	CAST(a AS CHAR(2))
+aaa	aa
+aab	aa
+aac	aa
+DROP TABLE t1;
diff --git a/mysql-test/r/ctype_uca.result b/mysql-test/r/ctype_uca.result
index 94fe15fed26..da4b5bfb663 100644
--- a/mysql-test/r/ctype_uca.result
+++ b/mysql-test/r/ctype_uca.result
@@ -1872,3 +1872,42 @@ Z,z,Ź,ź,Ż,ż,Ž,ž
 ǁ
 ǂ
 ǃ
+drop table t1;
+SET NAMES utf8;
+CREATE TABLE t1 (c varchar(255) NOT NULL COLLATE utf8_general_ci, INDEX (c));
+INSERT INTO t1 VALUES (CONVERT(_ucs2 0x039C03C903B403B11F770308 USING utf8));
+SELECT * FROM t1 WHERE c LIKE CONVERT(_ucs2 0x039C0025 USING utf8)
+COLLATE utf8_general_ci;
+c
+Μωδαί̈
+INSERT INTO t1 VALUES (CONVERT(_ucs2 0x039C03C903B4 USING utf8));
+SELECT * FROM t1 WHERE c LIKE CONVERT(_ucs2 0x039C0025 USING utf8)
+COLLATE utf8_general_ci ORDER BY c;
+c
+Μωδ
+Μωδαί̈
+DROP TABLE t1;
+CREATE TABLE t1 (c varchar(255) NOT NULL COLLATE ucs2_unicode_ci, INDEX (c));
+INSERT INTO t1 VALUES (_ucs2 0x039C03C903B403B11F770308);
+SELECT * FROM t1 WHERE c LIKE _ucs2 0x039C0025 COLLATE ucs2_unicode_ci;
+c
+Μωδαί̈
+INSERT INTO t1 VALUES (_ucs2 0x039C03C903B4);
+SELECT * FROM t1 WHERE c LIKE _ucs2 0x039C0025
+COLLATE ucs2_unicode_ci ORDER BY c;
+c
+Μωδ
+Μωδαί̈
+DROP TABLE t1;
+CREATE TABLE t1 (c varchar(255) NOT NULL COLLATE utf8_unicode_ci, INDEX (c));
+INSERT INTO t1 VALUES (CONVERT(_ucs2 0x039C03C903B403B11F770308 USING utf8));
+SELECT * FROM t1 WHERE c LIKE CONVERT(_ucs2 0x039C0025 USING utf8) COLLATE utf8_unicode_ci;
+c
+Μωδαί̈
+INSERT INTO t1 VALUES (CONVERT(_ucs2 0x039C03C903B4 USING utf8));
+SELECT * FROM t1 WHERE c LIKE CONVERT(_ucs2 0x039C0025 USING utf8)
+COLLATE utf8_unicode_ci ORDER BY c;
+c
+Μωδ
+Μωδαί̈
+DROP TABLE t1;
diff --git a/mysql-test/r/ctype_utf8.result b/mysql-test/r/ctype_utf8.result
index f3be539251a..c7d015da9dc 100644
--- a/mysql-test/r/ctype_utf8.result
+++ b/mysql-test/r/ctype_utf8.result
@@ -639,3 +639,8 @@ select * from t1 where str='str';
 str
 str
 drop table t1;
+CREATE TABLE t1 (a varchar(32) BINARY) CHARACTER SET utf8;
+INSERT INTO t1 VALUES ('test');
+SELECT a FROM t1 WHERE a LIKE '%te';
+a
+DROP TABLE t1;
diff --git a/mysql-test/r/func_group.result b/mysql-test/r/func_group.result
index 06259ff4931..011a47874c2 100644
--- a/mysql-test/r/func_group.result
+++ b/mysql-test/r/func_group.result
@@ -684,3 +684,12 @@ max(a)
 2
 deallocate prepare stmt1;
 drop table t1;
+CREATE TABLE t1 (a int primary key);
+INSERT INTO t1 VALUES (1),(2),(3),(4);
+SELECT MAX(a) FROM t1 WHERE a > 5;
+MAX(a)
+NULL
+SELECT MIN(a) FROM t1 WHERE a < 0;
+MIN(a)
+NULL
+DROP TABLE t1;
diff --git a/mysql-test/r/group_by.result b/mysql-test/r/group_by.result
index 9af7304c167..022b8eff7e8 100644
--- a/mysql-test/r/group_by.result
+++ b/mysql-test/r/group_by.result
@@ -629,3 +629,12 @@ explain SELECT i, COUNT(DISTINCT(i)) FROM t1 GROUP BY j ORDER BY NULL;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	6	Using filesort
 DROP TABLE t1;
+create table t1 ( col1 int, col2 int );
+insert into t1 values (1,1),(1,2),(1,3),(2,1),(2,2);
+select group_concat( distinct col1 ) as alias from t1
+group by col2 having alias like '%';
+alias
+1,2
+1,2
+1
+drop table t1;
diff --git a/mysql-test/r/ndb_insert.result b/mysql-test/r/ndb_insert.result
index 93f46c85499..87f27518ea3 100644
--- a/mysql-test/r/ndb_insert.result
+++ b/mysql-test/r/ndb_insert.result
@@ -416,4 +416,16 @@ INSERT INTO t1 VALUES
 SELECT COUNT(*) FROM t1;
 COUNT(*)
 2000
+INSERT INTO t1 VALUES 
+(1,1,1),(2,2,2),(3,3,3),(4,4,4),(5,5,5),
+(6,6,6),(7,7,7),(8,8,8),(9,9,9),(10,10,10);
+ERROR 23000: Duplicate entry '10' for key 1
+begin;
+INSERT INTO t1 VALUES 
+(1,1,1),(2,2,2),(3,3,3),(4,4,4),(5,5,5),
+(6,6,6),(7,7,7),(8,8,8),(9,9,9),(10,10,10);
+ERROR 23000: Duplicate entry '10' for key 1
+commit;
+insert into t1 select * from t1 where b < 10 order by pk1;
+ERROR 23000: Duplicate entry '9' for key 1
 DROP TABLE t1;
diff --git a/mysql-test/r/rpl_set_charset.result b/mysql-test/r/rpl_set_charset.result
new file mode 100644
index 00000000000..480d926fbba
--- /dev/null
+++ b/mysql-test/r/rpl_set_charset.result
@@ -0,0 +1,48 @@
+stop slave;
+drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
+reset master;
+reset slave;
+drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
+start slave;
+drop database if exists mysqltest1;
+create database mysqltest1 /*!40100 character set latin2 */;
+use mysqltest1;
+drop table if exists t1;
+create table t1 (a varchar(255) character set latin2, b varchar(4));
+SET CHARACTER SET cp1250_latin2;
+INSERT INTO t1 VALUES ('�����','80');
+INSERT INTO t1 VALUES ('�����','90');
+INSERT INTO t1 VALUES ('����','A0');
+INSERT INTO t1 VALUES ('������','B0');
+INSERT INTO t1 VALUES ('����������������','C0');
+INSERT INTO t1 VALUES ('����������������','D0');
+INSERT INTO t1 VALUES ('����������������','E0');
+INSERT INTO t1 VALUES ('����������������','F0');
+select "--- on master ---";
+--- on master ---
+--- on master ---
+select hex(a),b from t1 order by b;
+hex(a)	b
+A9A6ABAEAC	80
+B9B6BBBEBC	90
+A3A1AAAF	A0
+B3B1BAA5B5BF	B0
+C0C1C2C3C4C5C6C7C8C9CACBCCCDCECF	C0
+D0D1D2D3D4D5D6D7D8D9DADBDCDDDEDF	D0
+E0E1E2E3E4E5E6E7E8E9EAEBECEDEEEF	E0
+F0F1F2F3F4F5F6F7F8F9FAFBFCFDFEFF	F0
+use mysqltest1;
+select "--- on slave ---";
+--- on slave ---
+--- on slave ---
+select hex(a),b from t1 order by b;
+hex(a)	b
+A9A6ABAEAC	80
+B9B6BBBEBC	90
+A3A1AAAF	A0
+B3B1BAA5B5BF	B0
+C0C1C2C3C4C5C6C7C8C9CACBCCCDCECF	C0
+D0D1D2D3D4D5D6D7D8D9DADBDCDDDEDF	D0
+E0E1E2E3E4E5E6E7E8E9EAEBECEDEEEF	E0
+F0F1F2F3F4F5F6F7F8F9FAFBFCFDFEFF	F0
+drop database mysqltest1;
diff --git a/mysql-test/r/select.result b/mysql-test/r/select.result
index 8da1660a109..f0618c0f153 100644
--- a/mysql-test/r/select.result
+++ b/mysql-test/r/select.result
@@ -2348,3 +2348,19 @@ select * from t2,t3 where t2.s = t3.s;
 s	s
 two		two	
 drop table t1, t2, t3;
+CREATE TABLE t1 (
+i int(11) NOT NULL default '0',
+c char(10) NOT NULL default '',
+PRIMARY KEY  (i),
+UNIQUE KEY c (c)
+) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (1,'a');
+INSERT INTO t1 VALUES (2,'b');
+INSERT INTO t1 VALUES (3,'c');
+EXPLAIN SELECT i FROM t1 WHERE i=1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	const	PRIMARY	PRIMARY	4	const	1	Using index
+EXPLAIN SELECT i FROM t1 WHERE i=1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	const	PRIMARY	PRIMARY	4	const	1	Using index
+DROP TABLE t1;
diff --git a/mysql-test/r/subselect_innodb.result b/mysql-test/r/subselect_innodb.result
index bbbc607b6f8..e8f6426f51b 100644
--- a/mysql-test/r/subselect_innodb.result
+++ b/mysql-test/r/subselect_innodb.result
@@ -106,3 +106,11 @@ a	b
 2	12
 4	105
 drop table t1, t2;
+CREATE TABLE `t1` ( `unit` varchar(50) NOT NULL default '', `ingredient` varchar(50) NOT NULL default '') ENGINE=InnoDB DEFAULT CHARSET=latin1;
+CREATE TABLE `t2` ( `ingredient` varchar(50) NOT NULL default '', `unit` varchar(50) NOT NULL default '', PRIMARY KEY (ingredient, unit)) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+INSERT INTO `t1` VALUES ('xx','yy');
+INSERT INTO `t2` VALUES ('yy','xx');
+SELECT R.unit, R.ingredient FROM t1 R WHERE R.ingredient IN (SELECT N.ingredient FROM t2 N WHERE N.unit = R.unit);
+unit	ingredient
+xx	yy
+drop table t1, t2;
diff --git a/mysql-test/r/type_float.result b/mysql-test/r/type_float.result
index 843bdc2bdc5..75f0298797a 100644
--- a/mysql-test/r/type_float.result
+++ b/mysql-test/r/type_float.result
@@ -137,6 +137,8 @@ t1	CREATE TABLE `t1` (
 drop table t1;
 create table t1 (c20 char);
 insert into t1 values (5000.0);
+Warnings:
+Warning	1265	Data truncated for column 'c20' at row 1
 drop table t1;
 create table t1 (f float(54));
 ERROR 42000: Incorrect column specifier for column 'f'
diff --git a/mysql-test/t/cast.test b/mysql-test/t/cast.test
index e2deb792d47..e5681dedbac 100644
--- a/mysql-test/t/cast.test
+++ b/mysql-test/t/cast.test
@@ -95,3 +95,16 @@ select cast("2001-1-1" as datetime) = "2001-01-01 00:00:00";
 select cast("1:2:3" as TIME) = "1:02:03";
 select cast(NULL as DATE);
 select cast(NULL as BINARY);
+
+#
+# Bug #5228 ORDER BY CAST(enumcol) sorts incorrectly under certain conditions
+#
+CREATE TABLE t1 (a enum ('aac','aab','aaa') not null);
+INSERT INTO t1 VALUES ('aaa'),('aab'),('aac');
+# these two should be in enum order
+SELECT a, CAST(a AS CHAR) FROM t1 ORDER BY CAST(a AS UNSIGNED) ;
+SELECT a, CAST(a AS CHAR(3)) FROM t1 ORDER BY CAST(a AS CHAR(2)), a;
+# these two should be in alphabetic order
+SELECT a, CAST(a AS UNSIGNED) FROM t1 ORDER BY CAST(a AS CHAR) ;
+SELECT a, CAST(a AS CHAR(2)) FROM t1 ORDER BY CAST(a AS CHAR(3)), a;
+DROP TABLE t1;
diff --git a/mysql-test/t/ctype_uca.test b/mysql-test/t/ctype_uca.test
index 187d21f9ab7..d9181b19992 100644
--- a/mysql-test/t/ctype_uca.test
+++ b/mysql-test/t/ctype_uca.test
@@ -180,3 +180,40 @@ select group_concat(c1 order by c1) from t1 group by c1 collate utf8_slovak_ci;
 select group_concat(c1 order by c1) from t1 group by c1 collate utf8_spanish2_ci;
 select group_concat(c1 order by c1) from t1 group by c1 collate utf8_roman_ci;
 
+drop table t1;
+
+#
+# Bug#5324
+#
+SET NAMES utf8;
+#test1
+CREATE TABLE t1 (c varchar(255) NOT NULL COLLATE utf8_general_ci, INDEX (c));
+INSERT INTO t1 VALUES (CONVERT(_ucs2 0x039C03C903B403B11F770308 USING utf8));
+#Check one row
+SELECT * FROM t1 WHERE c LIKE CONVERT(_ucs2 0x039C0025 USING utf8)
+COLLATE utf8_general_ci;
+INSERT INTO t1 VALUES (CONVERT(_ucs2 0x039C03C903B4 USING utf8));
+#Check two rows
+SELECT * FROM t1 WHERE c LIKE CONVERT(_ucs2 0x039C0025 USING utf8)
+COLLATE utf8_general_ci ORDER BY c;
+DROP TABLE t1;
+#test2
+CREATE TABLE t1 (c varchar(255) NOT NULL COLLATE ucs2_unicode_ci, INDEX (c));
+INSERT INTO t1 VALUES (_ucs2 0x039C03C903B403B11F770308);
+#Check one row
+SELECT * FROM t1 WHERE c LIKE _ucs2 0x039C0025 COLLATE ucs2_unicode_ci;
+INSERT INTO t1 VALUES (_ucs2 0x039C03C903B4);
+#Check two rows
+SELECT * FROM t1 WHERE c LIKE _ucs2 0x039C0025
+COLLATE ucs2_unicode_ci ORDER BY c;
+DROP TABLE t1;
+#test 3
+CREATE TABLE t1 (c varchar(255) NOT NULL COLLATE utf8_unicode_ci, INDEX (c));
+INSERT INTO t1 VALUES (CONVERT(_ucs2 0x039C03C903B403B11F770308 USING utf8));
+#Check one row row
+SELECT * FROM t1 WHERE c LIKE CONVERT(_ucs2 0x039C0025 USING utf8) COLLATE utf8_unicode_ci;
+INSERT INTO t1 VALUES (CONVERT(_ucs2 0x039C03C903B4 USING utf8));
+#Check two rows
+SELECT * FROM t1 WHERE c LIKE CONVERT(_ucs2 0x039C0025 USING utf8)
+COLLATE utf8_unicode_ci ORDER BY c;
+DROP TABLE t1;
diff --git a/mysql-test/t/ctype_utf8.test b/mysql-test/t/ctype_utf8.test
index 2c531d4e5d2..f5bd9ede673 100644
--- a/mysql-test/t/ctype_utf8.test
+++ b/mysql-test/t/ctype_utf8.test
@@ -492,3 +492,10 @@ INSERT INTO t1 VALUES ('str2');
 select * from t1 where str='str';
 drop table t1;
 
+#
+# Bug #5397: Crash with varchar binary and LIKE
+#
+CREATE TABLE t1 (a varchar(32) BINARY) CHARACTER SET utf8;
+INSERT INTO t1 VALUES ('test');
+SELECT a FROM t1 WHERE a LIKE '%te';
+DROP TABLE t1;
diff --git a/mysql-test/t/func_group.test b/mysql-test/t/func_group.test
index 74f4c1bad44..7f48f2b92bd 100644
--- a/mysql-test/t/func_group.test
+++ b/mysql-test/t/func_group.test
@@ -418,3 +418,19 @@ execute stmt1;
 execute stmt1;
 deallocate prepare stmt1;
 drop table t1;
+
+#
+# Bug #5406 min/max optimization for empty set
+#
+
+CREATE TABLE t1 (a int primary key);
+INSERT INTO t1 VALUES (1),(2),(3),(4);
+
+SELECT MAX(a) FROM t1 WHERE a > 5;
+SELECT MIN(a) FROM t1 WHERE a < 0;
+
+DROP TABLE t1;
+
+
+
+
diff --git a/mysql-test/t/group_by.test b/mysql-test/t/group_by.test
index d6d1922c10f..59983594c32 100644
--- a/mysql-test/t/group_by.test
+++ b/mysql-test/t/group_by.test
@@ -456,3 +456,12 @@ INSERT INTO t1 VALUES (1,2),(2,3),(4,5),(3,5),(1,5),(23,5);
 SELECT i, COUNT(DISTINCT(i)) FROM t1 GROUP BY j ORDER BY NULL;
 explain SELECT i, COUNT(DISTINCT(i)) FROM t1 GROUP BY j ORDER BY NULL;
 DROP TABLE t1;
+
+# Test for BUG#5400: GROUP_CONCAT returns everything twice.
+create table t1 ( col1 int, col2 int );
+insert into t1 values (1,1),(1,2),(1,3),(2,1),(2,2);
+select group_concat( distinct col1 ) as alias from t1
+  group by col2 having alias like '%';
+
+drop table t1;
+
diff --git a/mysql-test/t/ndb_insert.test b/mysql-test/t/ndb_insert.test
index c55a925dca2..a448e413f1d 100644
--- a/mysql-test/t/ndb_insert.test
+++ b/mysql-test/t/ndb_insert.test
@@ -429,5 +429,34 @@ INSERT INTO t1 VALUES
 SELECT COUNT(*) FROM t1;
 
 
+#
+# Insert duplicate rows
+# 
+--error 1062
+INSERT INTO t1 VALUES 
+(1,1,1),(2,2,2),(3,3,3),(4,4,4),(5,5,5),
+(6,6,6),(7,7,7),(8,8,8),(9,9,9),(10,10,10);
+
+
+
+begin;
+
+#
+# Insert duplicate rows, inside transaction
+# 
+--error 1062
+INSERT INTO t1 VALUES 
+(1,1,1),(2,2,2),(3,3,3),(4,4,4),(5,5,5),
+(6,6,6),(7,7,7),(8,8,8),(9,9,9),(10,10,10);
+
+commit;
+
+#
+# Insert duplicate rows using "insert .. select" 
+
+#
+--error 1062
+insert into t1 select * from t1 where b < 10 order by pk1;
+
 
 DROP TABLE t1;
diff --git a/mysql-test/t/rpl_set_charset.test b/mysql-test/t/rpl_set_charset.test
new file mode 100644
index 00000000000..269074b1c42
--- /dev/null
+++ b/mysql-test/t/rpl_set_charset.test
@@ -0,0 +1,33 @@
+source include/master-slave.inc;
+--disable_warnings
+drop database if exists mysqltest1;
+# 4.1 bases its conversion on the db's charset,
+# while 4.0 uses the part of "SET CHARACTER SET" after "_".
+# So for 4.1 we add a clause to CREATE DATABASE.
+create database mysqltest1 /*!40100 character set latin2 */;
+use mysqltest1;
+drop table if exists t1;
+--enable_warnings
+create table t1 (a varchar(255) character set latin2, b varchar(4));
+SET CHARACTER SET cp1250_latin2;
+INSERT INTO t1 VALUES ('�����','80');
+INSERT INTO t1 VALUES ('�����','90');
+INSERT INTO t1 VALUES ('����','A0');
+INSERT INTO t1 VALUES ('������','B0');
+INSERT INTO t1 VALUES ('����������������','C0');
+INSERT INTO t1 VALUES ('����������������','D0');
+INSERT INTO t1 VALUES ('����������������','E0');
+INSERT INTO t1 VALUES ('����������������','F0');
+select "--- on master ---";
+select hex(a),b from t1 order by b;
+save_master_pos;
+connection slave;
+sync_with_master;
+use mysqltest1;
+select "--- on slave ---";
+select hex(a),b from t1 order by b;
+connection master;
+drop database mysqltest1;
+save_master_pos;
+connection slave;
+sync_with_master;
diff --git a/mysql-test/t/select.test b/mysql-test/t/select.test
index 57827f3cc7f..4490f97765b 100644
--- a/mysql-test/t/select.test
+++ b/mysql-test/t/select.test
@@ -1880,3 +1880,24 @@ select * from t3 where s = 'one';
 select * from t1,t2 where t1.s = t2.s;
 select * from t2,t3 where t2.s = t3.s;
 drop table t1, t2, t3;
+
+#
+# Covering index is mentioned in EXPLAIN output for const tables (bug #5333)
+#
+
+CREATE TABLE t1 (
+  i int(11) NOT NULL default '0',
+  c char(10) NOT NULL default '',
+  PRIMARY KEY  (i),
+  UNIQUE KEY c (c)
+) ENGINE=MyISAM;
+
+INSERT INTO t1 VALUES (1,'a');
+INSERT INTO t1 VALUES (2,'b');
+INSERT INTO t1 VALUES (3,'c');
+
+EXPLAIN SELECT i FROM t1 WHERE i=1;
+
+EXPLAIN SELECT i FROM t1 WHERE i=1;
+
+DROP TABLE t1;
diff --git a/mysql-test/t/subselect_innodb.test b/mysql-test/t/subselect_innodb.test
index 8c13171d221..5f4badb3624 100644
--- a/mysql-test/t/subselect_innodb.test
+++ b/mysql-test/t/subselect_innodb.test
@@ -111,3 +111,17 @@ create table t2 (a int) engine=innodb;
 insert into t2 values (1),(2),(3),(4);
 select a, sum(b) as b from t1 group by a having b > (select max(a) from t2);
 drop table t1, t2;
+
+#
+# bug #5220 test suite
+#
+CREATE TABLE `t1` ( `unit` varchar(50) NOT NULL default '', `ingredient` varchar(50) NOT NULL default '') ENGINE=InnoDB DEFAULT CHARSET=latin1;
+
+CREATE TABLE `t2` ( `ingredient` varchar(50) NOT NULL default '', `unit` varchar(50) NOT NULL default '', PRIMARY KEY (ingredient, unit)) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+
+INSERT INTO `t1` VALUES ('xx','yy');
+INSERT INTO `t2` VALUES ('yy','xx');
+
+SELECT R.unit, R.ingredient FROM t1 R WHERE R.ingredient IN (SELECT N.ingredient FROM t2 N WHERE N.unit = R.unit);
+
+drop table t1, t2;