Merge sanja.is.com.ua:/home/bell/mysql/mysql-4.1

into sanja.is.com.ua:/home/bell/mysql/work-row-4.1


sql/sql_yacc.yy:
  Auto merged

2 files changed, 145 insertions, 4 deletions

diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result
index f12e0396694..502fe0fad60 100644
--- a/mysql-test/r/subselect.result
+++ b/mysql-test/r/subselect.result
@@ -52,6 +52,54 @@ a
 SELECT (SELECT 1) as a FROM (SELECT 1) b WHERE (SELECT a) IS NOT NULL;
 a
 1
+SELECT (SELECT 1,2,3) = ROW(1,2,3);
+(SELECT 1,2,3) = ROW(1,2,3)
+1
+SELECT (SELECT 1,2,3) = ROW(1,2,1);
+(SELECT 1,2,3) = ROW(1,2,1)
+0
+SELECT (SELECT 1,2,3) < ROW(1,2,1);
+(SELECT 1,2,3) < ROW(1,2,1)
+0
+SELECT (SELECT 1,2,3) > ROW(1,2,1);
+(SELECT 1,2,3) > ROW(1,2,1)
+1
+SELECT (SELECT 1,2,3) = ROW(1,2,NULL);
+(SELECT 1,2,3) = ROW(1,2,NULL)
+NULL
+SELECT ROW(1,2,3) = (SELECT 1,2,3);
+ROW(1,2,3) = (SELECT 1,2,3)
+1
+SELECT ROW(1,2,3) = (SELECT 1,2,1);
+ROW(1,2,3) = (SELECT 1,2,1)
+0
+SELECT ROW(1,2,3) < (SELECT 1,2,1);
+ROW(1,2,3) < (SELECT 1,2,1)
+0
+SELECT ROW(1,2,3) > (SELECT 1,2,1);
+ROW(1,2,3) > (SELECT 1,2,1)
+1
+SELECT ROW(1,2,3) = (SELECT 1,2,NULL);
+ROW(1,2,3) = (SELECT 1,2,NULL)
+NULL
+SELECT (SELECT 1.5,2,'a') = ROW(1.5,2,'a');
+(SELECT 1.5,2,'a') = ROW(1.5,2,'a')
+1
+SELECT (SELECT 1.5,2,'a') = ROW(1.5,2,'b');
+(SELECT 1.5,2,'a') = ROW(1.5,2,'b')
+0
+SELECT (SELECT 1.5,2,'a') = ROW('b',2,'b');
+(SELECT 1.5,2,'a') = ROW('b',2,'b')
+0
+SELECT (SELECT 'b',2,'a') = ROW(1.5,2,'a');
+(SELECT 'b',2,'a') = ROW(1.5,2,'a')
+0
+SELECT (SELECT 1.5,2,'a') = ROW(1.5,'c','a');
+(SELECT 1.5,2,'a') = ROW(1.5,'c','a')
+0
+SELECT (SELECT 1.5,'c','a') = ROW(1.5,2,'a');
+(SELECT 1.5,'c','a') = ROW(1.5,2,'a')
+0
 drop table if exists t1,t2,t3,t4,t5,t6,t7,t8;
 create table t1 (a int);
 create table t2 (a int, b int);
@@ -586,7 +634,7 @@ EXPLAIN SELECT * FROM t WHERE id IN (SELECT 1 UNION SELECT 3);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t	index	NULL	id	5	NULL	2	Using where; Using index
 2	DEPENDENT SUBSELECT	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
-3	UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+3	DEPENDENT UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
 SELECT * FROM t WHERE id IN (SELECT 5 UNION SELECT 3);
 id
 SELECT * FROM t WHERE id IN (SELECT 5 UNION SELECT 2);
@@ -602,7 +650,7 @@ CREATE TABLE t1 (id int(11) default NULL, KEY id (id)) TYPE=MyISAM CHARSET=latin
 INSERT INTO t1 values (1),(1);
 UPDATE t SET id=(SELECT * FROM t1);
 Subselect returns more than 1 record
-drop table t;
+drop table t, t1;
 create table t (a int);
 insert into t values (1),(2),(3);
 select 1 IN (SELECT * from t);
@@ -711,3 +759,56 @@ This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'
 select 10.5 IN (SELECT * from t LIMIT 1 UNION SELECT 1.5);
 This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'
 drop table t;
+create table t1 (a int, b int, c varchar(10));
+create table t2 (a int);
+insert into t1 values (1,2,'a'),(2,3,'b'),(3,4,'c');
+insert into t2 values (1),(2),(NULL);
+select a, (select a,b,c from t1 where t1.a=t2.a) = ROW(a,2,'a'),(select c from t1 where a=t2.a)  from t2;
+a	(select a,b,c from t1 where t1.a=t2.a) = ROW(a,2,'a')	(select c from t1 where a=t2.a)
+1	1	a
+2	0	b
+NULL	NULL	NULL
+select a, (select a,b,c from t1 where t1.a=t2.a) = ROW(a,3,'b'),(select c from t1 where a=t2.a) from t2;
+a	(select a,b,c from t1 where t1.a=t2.a) = ROW(a,3,'b')	(select c from t1 where a=t2.a)
+1	0	a
+2	1	b
+NULL	NULL	NULL
+select a, (select a,b,c from t1 where t1.a=t2.a) = ROW(a,4,'c'),(select c from t1 where a=t2.a) from t2;
+a	(select a,b,c from t1 where t1.a=t2.a) = ROW(a,4,'c')	(select c from t1 where a=t2.a)
+1	0	a
+2	0	b
+NULL	NULL	NULL
+drop table t1,t2;
+drop table if exists t;
+create table t (a int, b real, c varchar(10));
+insert into t values (1, 1, 'a'), (2,2,'b'), (NULL, 2, 'b');
+select ROW(1, 1, 'a') IN (select a,b,c from t);
+ROW(1, 1, 'a') IN (select a,b,c from t)
+1
+select ROW(1, 2, 'a') IN (select a,b,c from t);
+ROW(1, 2, 'a') IN (select a,b,c from t)
+NULL
+select ROW(1, 1, 'a') IN (select b,a,c from t);
+ROW(1, 1, 'a') IN (select b,a,c from t)
+1
+select ROW(1, 1, 'a') IN (select a,b,c from t where a is not null);
+ROW(1, 1, 'a') IN (select a,b,c from t where a is not null)
+1
+select ROW(1, 2, 'a') IN (select a,b,c from t where a is not null);
+ROW(1, 2, 'a') IN (select a,b,c from t where a is not null)
+0
+select ROW(1, 1, 'a') IN (select b,a,c from t where a is not null);
+ROW(1, 1, 'a') IN (select b,a,c from t where a is not null)
+1
+select ROW(1, 1, 'a') IN (select a,b,c from t where c='b' or c='a');
+ROW(1, 1, 'a') IN (select a,b,c from t where c='b' or c='a')
+1
+select ROW(1, 2, 'a') IN (select a,b,c from t where c='b' or c='a');
+ROW(1, 2, 'a') IN (select a,b,c from t where c='b' or c='a')
+NULL
+select ROW(1, 1, 'a') IN (select b,a,c from t where c='b' or c='a');
+ROW(1, 1, 'a') IN (select b,a,c from t where c='b' or c='a')
+1
+select ROW(1, 1, 'a') IN (select b,a,c from t limit 2);
+This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'
+drop table if exists t;
diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test
index 347d6276280..c77fc33f0ea 100644
--- a/mysql-test/t/subselect.test
+++ b/mysql-test/t/subselect.test
@@ -26,6 +26,22 @@ select (SELECT 1 FROM (SELECT 1) a PROCEDURE ANALYSE(1));
 SELECT 1 FROM (SELECT 1) a PROCEDURE ANALYSE((SELECT 1));
 SELECT (SELECT 1) as a FROM (SELECT 1) b WHERE (SELECT a) IS NULL;
 SELECT (SELECT 1) as a FROM (SELECT 1) b WHERE (SELECT a) IS NOT NULL;
+SELECT (SELECT 1,2,3) = ROW(1,2,3);
+SELECT (SELECT 1,2,3) = ROW(1,2,1);
+SELECT (SELECT 1,2,3) < ROW(1,2,1);
+SELECT (SELECT 1,2,3) > ROW(1,2,1);
+SELECT (SELECT 1,2,3) = ROW(1,2,NULL);
+SELECT ROW(1,2,3) = (SELECT 1,2,3);
+SELECT ROW(1,2,3) = (SELECT 1,2,1);
+SELECT ROW(1,2,3) < (SELECT 1,2,1);
+SELECT ROW(1,2,3) > (SELECT 1,2,1);
+SELECT ROW(1,2,3) = (SELECT 1,2,NULL);
+SELECT (SELECT 1.5,2,'a') = ROW(1.5,2,'a');
+SELECT (SELECT 1.5,2,'a') = ROW(1.5,2,'b');
+SELECT (SELECT 1.5,2,'a') = ROW('b',2,'b');
+SELECT (SELECT 'b',2,'a') = ROW(1.5,2,'a');
+SELECT (SELECT 1.5,2,'a') = ROW(1.5,'c','a');
+SELECT (SELECT 1.5,'c','a') = ROW(1.5,2,'a');
 
 drop table if exists t1,t2,t3,t4,t5,t6,t7,t8;
 create table t1 (a int);
@@ -363,7 +379,7 @@ CREATE TABLE t1 (id int(11) default NULL, KEY id (id)) TYPE=MyISAM CHARSET=latin
 INSERT INTO t1 values (1),(1);
 -- error 1240
 UPDATE t SET id=(SELECT * FROM t1);
-drop table t;
+drop table t, t1;
 
 
 #NULL test
@@ -416,4 +432,28 @@ create table t (a float);
 select 10.5 IN (SELECT * from t LIMIT 1);
 -- error 1235
 select 10.5 IN (SELECT * from t LIMIT 1 UNION SELECT 1.5);
-drop table t;
\ No newline at end of file
+drop table t;
+create table t1 (a int, b int, c varchar(10));
+create table t2 (a int);
+insert into t1 values (1,2,'a'),(2,3,'b'),(3,4,'c');
+insert into t2 values (1),(2),(NULL);
+select a, (select a,b,c from t1 where t1.a=t2.a) = ROW(a,2,'a'),(select c from t1 where a=t2.a)  from t2;
+select a, (select a,b,c from t1 where t1.a=t2.a) = ROW(a,3,'b'),(select c from t1 where a=t2.a) from t2;
+select a, (select a,b,c from t1 where t1.a=t2.a) = ROW(a,4,'c'),(select c from t1 where a=t2.a) from t2;
+drop table t1,t2;
+
+drop table if exists t;
+create table t (a int, b real, c varchar(10));
+insert into t values (1, 1, 'a'), (2,2,'b'), (NULL, 2, 'b');
+select ROW(1, 1, 'a') IN (select a,b,c from t);
+select ROW(1, 2, 'a') IN (select a,b,c from t);
+select ROW(1, 1, 'a') IN (select b,a,c from t);
+select ROW(1, 1, 'a') IN (select a,b,c from t where a is not null);
+select ROW(1, 2, 'a') IN (select a,b,c from t where a is not null);
+select ROW(1, 1, 'a') IN (select b,a,c from t where a is not null);
+select ROW(1, 1, 'a') IN (select a,b,c from t where c='b' or c='a');
+select ROW(1, 2, 'a') IN (select a,b,c from t where c='b' or c='a');
+select ROW(1, 1, 'a') IN (select b,a,c from t where c='b' or c='a');
+-- error 1235
+select ROW(1, 1, 'a') IN (select b,a,c from t limit 2);
+drop table if exists t;