From 5b54427d7d879a47893885dbfa0466b4bd69aaf1 Mon Sep 17 00:00:00 2001
From: unknown <bell@sanja.is.com.ua>
Date: Sat, 7 Dec 2002 19:58:05 +0200
Subject: IN with row item without constant optimisation (SCRUM) renamed row
 item test

mysql-test/r/row.result:
  IN with row item without constant optimisation
mysql-test/t/row.test:
  IN with row item without constant optimisation
sql/item_cmpfunc.cc:
  IN with row item without constant optimisation
sql/item_cmpfunc.h:
  IN with row item without constant optimisation
---
 mysql-test/r/row.result      | 76 ++++++++++++++++++++++++++++++++++++++++++++
 mysql-test/r/row_test.result | 60 ----------------------------------
 mysql-test/t/row.test        | 42 ++++++++++++++++++++++++
 mysql-test/t/row_test.test   | 36 ---------------------
 4 files changed, 118 insertions(+), 96 deletions(-)
 create mode 100644 mysql-test/r/row.result
 delete mode 100644 mysql-test/r/row_test.result
 create mode 100644 mysql-test/t/row.test
 delete mode 100644 mysql-test/t/row_test.test

(limited to 'mysql-test')

diff --git a/mysql-test/r/row.result b/mysql-test/r/row.result
new file mode 100644
index 00000000000..e5bc5f4abe5
--- /dev/null
+++ b/mysql-test/r/row.result
@@ -0,0 +1,76 @@
+select row(1,2,3) IN (row(3,2,3), row(1,2,3), row(1,3,3));
+row(1,2,3) IN (row(3,2,3), row(1,2,3), row(1,3,3))
+1
+select row(10,2,3) IN (row(3,2,3), row(1,2,3), row(1,3,3));
+row(10,2,3) IN (row(3,2,3), row(1,2,3), row(1,3,3))
+0
+SELECT ROW(1,2,3)=ROW(1,2,3);
+ROW(1,2,3)=ROW(1,2,3)
+1
+SELECT ROW(2,2,3)=ROW(1+1,2,3);
+ROW(2,2,3)=ROW(1+1,2,3)
+1
+SELECT ROW(1,2,3)=ROW(1+1,2,3);
+ROW(1,2,3)=ROW(1+1,2,3)
+0
+SELECT ROW(1,2,3)<ROW(1+1,2,3);
+ROW(1,2,3)<ROW(1+1,2,3)
+1
+SELECT ROW(1,2,3)>ROW(1+1,2,3);
+ROW(1,2,3)>ROW(1+1,2,3)
+0
+SELECT ROW(1,2,3)<=ROW(1+1,2,3);
+ROW(1,2,3)<=ROW(1+1,2,3)
+1
+SELECT ROW(1,2,3)>=ROW(1+1,2,3);
+ROW(1,2,3)>=ROW(1+1,2,3)
+0
+SELECT ROW(1,2,3)<>ROW(1+1,2,3);
+ROW(1,2,3)<>ROW(1+1,2,3)
+1
+SELECT ROW(NULL,2,3)=ROW(NULL,2,3);
+ROW(NULL,2,3)=ROW(NULL,2,3)
+NULL
+SELECT ROW(NULL,2,3)<=>ROW(NULL,2,3);
+ROW(NULL,2,3)<=>ROW(NULL,2,3)
+1
+SELECT ROW(1,2,ROW(3,4,5))=ROW(1,2,ROW(3,4,5));
+ROW(1,2,ROW(3,4,5))=ROW(1,2,ROW(3,4,5))
+1
+SELECT ROW('test',2,3.33)=ROW('test',2,3.33);
+ROW('test',2,3.33)=ROW('test',2,3.33)
+1
+SELECT ROW('test',2,3.33)=ROW('test',2,3.33,4);
+Cardinality error (more/less than 3 columns)
+drop table if exists t1;
+create table t1 ( a int, b int, c int);
+insert into t1 values (1,2,3), (2,3,1), (3,2,1);
+select * from t1 where ROW(1,2,3)=ROW(a,b,c);
+a	b	c
+1	2	3
+select * from t1 where ROW(0,2,3)=ROW(a,b,c);
+a	b	c
+select * from t1 where ROW(1,2,3)<ROW(a,b,c);
+a	b	c
+2	3	1
+3	2	1
+select * from t1 where ROW(a,2,3) IN(row(1,b,c), row(2,3,1));
+a	b	c
+1	2	3
+select * from t1 where ROW(c,2,3) IN(row(1,b,a), row(2,3,1));
+a	b	c
+3	2	1
+select * from t1 where ROW(a,b,c) IN(row(1,2,3), row(3,2,1));
+a	b	c
+1	2	3
+3	2	1
+drop table t1;
+select ROW(1,1);
+Cardinality error (more/less than 1 columns)
+drop table if exists t1;
+create table t1 (i int);
+select 1 from t1 where ROW(1,1);
+Cardinality error (more/less than 1 columns)
+select count(*) from t1 order by ROW(1,1);
+Cardinality error (more/less than 1 columns)
+drop table t1;
diff --git a/mysql-test/r/row_test.result b/mysql-test/r/row_test.result
deleted file mode 100644
index f6e989789c7..00000000000
--- a/mysql-test/r/row_test.result
+++ /dev/null
@@ -1,60 +0,0 @@
-SELECT ROW(1,2,3)=ROW(1,2,3);
-ROW(1,2,3)=ROW(1,2,3)
-1
-SELECT ROW(2,2,3)=ROW(1+1,2,3);
-ROW(2,2,3)=ROW(1+1,2,3)
-1
-SELECT ROW(1,2,3)=ROW(1+1,2,3);
-ROW(1,2,3)=ROW(1+1,2,3)
-0
-SELECT ROW(1,2,3)<ROW(1+1,2,3);
-ROW(1,2,3)<ROW(1+1,2,3)
-1
-SELECT ROW(1,2,3)>ROW(1+1,2,3);
-ROW(1,2,3)>ROW(1+1,2,3)
-0
-SELECT ROW(1,2,3)<=ROW(1+1,2,3);
-ROW(1,2,3)<=ROW(1+1,2,3)
-1
-SELECT ROW(1,2,3)>=ROW(1+1,2,3);
-ROW(1,2,3)>=ROW(1+1,2,3)
-0
-SELECT ROW(1,2,3)<>ROW(1+1,2,3);
-ROW(1,2,3)<>ROW(1+1,2,3)
-1
-SELECT ROW(NULL,2,3)=ROW(NULL,2,3);
-ROW(NULL,2,3)=ROW(NULL,2,3)
-NULL
-SELECT ROW(NULL,2,3)<=>ROW(NULL,2,3);
-ROW(NULL,2,3)<=>ROW(NULL,2,3)
-1
-SELECT ROW(1,2,ROW(3,4,5))=ROW(1,2,ROW(3,4,5));
-ROW(1,2,ROW(3,4,5))=ROW(1,2,ROW(3,4,5))
-1
-SELECT ROW('test',2,3.33)=ROW('test',2,3.33);
-ROW('test',2,3.33)=ROW('test',2,3.33)
-1
-SELECT ROW('test',2,3.33)=ROW('test',2,3.33,4);
-Cardinality error (more/less than 3 columns)
-drop table if exists t1;
-create table t1 ( a int, b int, c int);
-insert into t1 values (1,2,3), (2,3,1), (3,2,1);
-select * from t1 where ROW(1,2,3)=ROW(a,b,c);
-a	b	c
-1	2	3
-select * from t1 where ROW(0,2,3)=ROW(a,b,c);
-a	b	c
-select * from t1 where ROW(1,2,3)<ROW(a,b,c);
-a	b	c
-2	3	1
-3	2	1
-drop table t1;
-select ROW(1,1);
-Cardinality error (more/less than 1 columns)
-drop table if exists t1;
-create table t1 (i int);
-select 1 from t1 where ROW(1,1);
-Cardinality error (more/less than 1 columns)
-select count(*) from t1 order by ROW(1,1);
-Cardinality error (more/less than 1 columns)
-drop table t1;
diff --git a/mysql-test/t/row.test b/mysql-test/t/row.test
new file mode 100644
index 00000000000..4515d6b220d
--- /dev/null
+++ b/mysql-test/t/row.test
@@ -0,0 +1,42 @@
+select row(1,2,3) IN (row(3,2,3), row(1,2,3), row(1,3,3));
+select row(10,2,3) IN (row(3,2,3), row(1,2,3), row(1,3,3));
+
+SELECT ROW(1,2,3)=ROW(1,2,3);
+SELECT ROW(2,2,3)=ROW(1+1,2,3);
+SELECT ROW(1,2,3)=ROW(1+1,2,3);
+SELECT ROW(1,2,3)<ROW(1+1,2,3);
+SELECT ROW(1,2,3)>ROW(1+1,2,3);
+SELECT ROW(1,2,3)<=ROW(1+1,2,3);
+SELECT ROW(1,2,3)>=ROW(1+1,2,3);
+SELECT ROW(1,2,3)<>ROW(1+1,2,3);
+SELECT ROW(NULL,2,3)=ROW(NULL,2,3);
+SELECT ROW(NULL,2,3)<=>ROW(NULL,2,3);
+SELECT ROW(1,2,ROW(3,4,5))=ROW(1,2,ROW(3,4,5));
+SELECT ROW('test',2,3.33)=ROW('test',2,3.33);
+-- error 1239
+SELECT ROW('test',2,3.33)=ROW('test',2,3.33,4);
+drop table if exists t1;
+create table t1 ( a int, b int, c int);
+insert into t1 values (1,2,3), (2,3,1), (3,2,1);
+select * from t1 where ROW(1,2,3)=ROW(a,b,c);
+select * from t1 where ROW(0,2,3)=ROW(a,b,c);
+select * from t1 where ROW(1,2,3)<ROW(a,b,c);
+select * from t1 where ROW(a,2,3) IN(row(1,b,c), row(2,3,1));
+select * from t1 where ROW(c,2,3) IN(row(1,b,a), row(2,3,1));
+select * from t1 where ROW(a,b,c) IN(row(1,2,3), row(3,2,1));
+drop table t1;
+
+-- error 1239
+select ROW(1,1);
+drop table if exists t1;
+create table t1 (i int);
+-- error 1239
+select 1 from t1 where ROW(1,1);
+-- error 1239
+select count(*) from t1 order by ROW(1,1);
+#TODO remove comments after parser fixing
+#-- error 1239
+#select count(*) from t1 order by i having (1,1);
+#-- error 1239
+#select 1 from t1 limit (1,1), (1,1);
+drop table t1;
diff --git a/mysql-test/t/row_test.test b/mysql-test/t/row_test.test
deleted file mode 100644
index 5daacaa1ee6..00000000000
--- a/mysql-test/t/row_test.test
+++ /dev/null
@@ -1,36 +0,0 @@
-SELECT ROW(1,2,3)=ROW(1,2,3);
-SELECT ROW(2,2,3)=ROW(1+1,2,3);
-SELECT ROW(1,2,3)=ROW(1+1,2,3);
-SELECT ROW(1,2,3)<ROW(1+1,2,3);
-SELECT ROW(1,2,3)>ROW(1+1,2,3);
-SELECT ROW(1,2,3)<=ROW(1+1,2,3);
-SELECT ROW(1,2,3)>=ROW(1+1,2,3);
-SELECT ROW(1,2,3)<>ROW(1+1,2,3);
-SELECT ROW(NULL,2,3)=ROW(NULL,2,3);
-SELECT ROW(NULL,2,3)<=>ROW(NULL,2,3);
-SELECT ROW(1,2,ROW(3,4,5))=ROW(1,2,ROW(3,4,5));
-SELECT ROW('test',2,3.33)=ROW('test',2,3.33);
--- error 1239
-SELECT ROW('test',2,3.33)=ROW('test',2,3.33,4);
-drop table if exists t1;
-create table t1 ( a int, b int, c int);
-insert into t1 values (1,2,3), (2,3,1), (3,2,1);
-select * from t1 where ROW(1,2,3)=ROW(a,b,c);
-select * from t1 where ROW(0,2,3)=ROW(a,b,c);
-select * from t1 where ROW(1,2,3)<ROW(a,b,c);
-drop table t1;
-
--- error 1239
-select ROW(1,1);
-drop table if exists t1;
-create table t1 (i int);
--- error 1239
-select 1 from t1 where ROW(1,1);
--- error 1239
-select count(*) from t1 order by ROW(1,1);
-#TODO remove comments after parser fixing
-#-- error 1239
-#select count(*) from t1 order by i having (1,1);
-#-- error 1239
-#select 1 from t1 limit (1,1), (1,1);
-drop table t1;
-- 
cgit v1.2.1


From 230f5f3b0f65f0c02c49ed6456450f31caa779ea Mon Sep 17 00:00:00 2001
From: unknown <bell@sanja.is.com.ua>
Date: Sun, 8 Dec 2002 03:19:03 +0200
Subject: optimized IN with Rows  (SCRUM) NULL with row (IN) cardinality error
 of row inside row new[] fixed layout

mysql-test/r/row.result:
  test of optimized IN with Rows
  test of NULL with row
  test of cardinality error of row inside row
mysql-test/t/row.test:
  test of optimized IN with Rows
  test of NULL with row
  test of cardinality error of row inside row
sql/item_cmpfunc.cc:
  optimized IN with Rows
  NULL with row (IN)
  cardinality error of row inside row
sql/item_cmpfunc.h:
  optimized IN with Rows
  NULL with row (IN)
  cardinality error of row inside row
  fixed layout
sql/sql_list.h:
  fixed layout
  new[]
---
 mysql-test/r/row.result | 76 ++++++++++++++++++++++++++++++++++++++++++-------
 mysql-test/t/row.test   | 25 +++++++++++++---
 2 files changed, 86 insertions(+), 15 deletions(-)

(limited to 'mysql-test')

diff --git a/mysql-test/r/row.result b/mysql-test/r/row.result
index e5bc5f4abe5..e0b524d9bb2 100644
--- a/mysql-test/r/row.result
+++ b/mysql-test/r/row.result
@@ -4,6 +4,35 @@ row(1,2,3) IN (row(3,2,3), row(1,2,3), row(1,3,3))
 select row(10,2,3) IN (row(3,2,3), row(1,2,3), row(1,3,3));
 row(10,2,3) IN (row(3,2,3), row(1,2,3), row(1,3,3))
 0
+select row(1,2,3) IN (row(3,NULL,3), row(1,2,3), row(1,3,3));
+row(1,2,3) IN (row(3,NULL,3), row(1,2,3), row(1,3,3))
+1
+select row(10,2,3) IN (row(3,NULL,3), row(1,2,3), row(1,3,3));
+row(10,2,3) IN (row(3,NULL,3), row(1,2,3), row(1,3,3))
+NULL
+select row('a',1.5,3) IN (row(1,2,3), row('a',1.5,3), row('a','a','a'));
+row('a',1.5,3) IN (row(1,2,3), row('a',1.5,3), row('a','a','a'))
+1
+select row('a',0,3) IN (row(3,2,3), row('a','0','3'), row(1,3,3));
+row('a',0,3) IN (row(3,2,3), row('a','0','3'), row(1,3,3))
+1
+select row('a',0,3) IN (row(3,2,3), row('a','a','3'), row(1,3,3));
+row('a',0,3) IN (row(3,2,3), row('a','a','3'), row(1,3,3))
+1
+select row('a',1.5,3) IN (row(3,NULL,3), row('a',1.5,3), row(1,3,3));
+row('a',1.5,3) IN (row(3,NULL,3), row('a',1.5,3), row(1,3,3))
+1
+select row('b',1.5,3) IN (row(3,NULL,3), row('a',1.5,3), row(1,3,3));
+row('b',1.5,3) IN (row(3,NULL,3), row('a',1.5,3), row(1,3,3))
+NULL
+select row(1,2,row(3,4)) IN (row(3,2,row(3,4)), row(1,2,row(3,4)));
+row(1,2,row(3,4)) IN (row(3,2,row(3,4)), row(1,2,row(3,4)))
+1
+select row(1,2,row(3,4)) IN (row(3,2,row(3,4)), row(1,2,4));
+Cardinality error (more/less than 2 columns)
+select row(1,2,row(3,4)) IN (row(3,2,row(3,4)), row(1,2,row(3,NULL)));
+row(1,2,row(3,4)) IN (row(3,2,row(3,4)), row(1,2,row(3,NULL)))
+NULL
 SELECT ROW(1,2,3)=ROW(1,2,3);
 ROW(1,2,3)=ROW(1,2,3)
 1
@@ -42,9 +71,20 @@ ROW('test',2,3.33)=ROW('test',2,3.33)
 1
 SELECT ROW('test',2,3.33)=ROW('test',2,3.33,4);
 Cardinality error (more/less than 3 columns)
+SELECT ROW('test',2,ROW(3,33))=ROW('test',2,ROW(3,33));
+ROW('test',2,ROW(3,33))=ROW('test',2,ROW(3,33))
+1
+SELECT ROW('test',2,ROW(3,33))=ROW('test',2,ROW(3,3));
+ROW('test',2,ROW(3,33))=ROW('test',2,ROW(3,3))
+0
+SELECT ROW('test',2,ROW(3,33))=ROW('test',2,ROW(3,NULL));
+ROW('test',2,ROW(3,33))=ROW('test',2,ROW(3,NULL))
+NULL
+SELECT ROW('test',2,ROW(3,33))=ROW('test',2,4);
+Cardinality error (more/less than 2 columns)
 drop table if exists t1;
 create table t1 ( a int, b int, c int);
-insert into t1 values (1,2,3), (2,3,1), (3,2,1);
+insert into t1 values (1,2,3), (2,3,1), (3,2,1), (1,2,NULL);
 select * from t1 where ROW(1,2,3)=ROW(a,b,c);
 a	b	c
 1	2	3
@@ -54,16 +94,30 @@ select * from t1 where ROW(1,2,3)<ROW(a,b,c);
 a	b	c
 2	3	1
 3	2	1
-select * from t1 where ROW(a,2,3) IN(row(1,b,c), row(2,3,1));
-a	b	c
-1	2	3
-select * from t1 where ROW(c,2,3) IN(row(1,b,a), row(2,3,1));
-a	b	c
-3	2	1
-select * from t1 where ROW(a,b,c) IN(row(1,2,3), row(3,2,1));
-a	b	c
-1	2	3
-3	2	1
+select ROW(a,2,3) IN(row(1,b,c), row(2,3,1)) from t1;
+ROW(a,2,3) IN(row(1,b,c), row(2,3,1))
+1
+0
+0
+NULL
+select ROW(c,2,3) IN(row(1,b,a), row(2,3,1)) from t1;
+ROW(c,2,3) IN(row(1,b,a), row(2,3,1))
+0
+0
+1
+NULL
+select ROW(a,b,c) IN(row(1,2,3), row(3,2,1)) from t1;
+ROW(a,b,c) IN(row(1,2,3), row(3,2,1))
+1
+0
+1
+NULL
+select ROW(1,2,3) IN(row(a,b,c), row(1,2,3)) from t1;
+ROW(1,2,3) IN(row(a,b,c), row(1,2,3))
+1
+1
+1
+1
 drop table t1;
 select ROW(1,1);
 Cardinality error (more/less than 1 columns)
diff --git a/mysql-test/t/row.test b/mysql-test/t/row.test
index 4515d6b220d..04f31fcbfe1 100644
--- a/mysql-test/t/row.test
+++ b/mysql-test/t/row.test
@@ -1,5 +1,16 @@
 select row(1,2,3) IN (row(3,2,3), row(1,2,3), row(1,3,3));
 select row(10,2,3) IN (row(3,2,3), row(1,2,3), row(1,3,3));
+select row(1,2,3) IN (row(3,NULL,3), row(1,2,3), row(1,3,3));
+select row(10,2,3) IN (row(3,NULL,3), row(1,2,3), row(1,3,3));
+select row('a',1.5,3) IN (row(1,2,3), row('a',1.5,3), row('a','a','a'));
+select row('a',0,3) IN (row(3,2,3), row('a','0','3'), row(1,3,3));
+select row('a',0,3) IN (row(3,2,3), row('a','a','3'), row(1,3,3));
+select row('a',1.5,3) IN (row(3,NULL,3), row('a',1.5,3), row(1,3,3));
+select row('b',1.5,3) IN (row(3,NULL,3), row('a',1.5,3), row(1,3,3));
+select row(1,2,row(3,4)) IN (row(3,2,row(3,4)), row(1,2,row(3,4)));
+-- error 1239
+select row(1,2,row(3,4)) IN (row(3,2,row(3,4)), row(1,2,4));
+select row(1,2,row(3,4)) IN (row(3,2,row(3,4)), row(1,2,row(3,NULL)));
 
 SELECT ROW(1,2,3)=ROW(1,2,3);
 SELECT ROW(2,2,3)=ROW(1+1,2,3);
@@ -15,15 +26,21 @@ SELECT ROW(1,2,ROW(3,4,5))=ROW(1,2,ROW(3,4,5));
 SELECT ROW('test',2,3.33)=ROW('test',2,3.33);
 -- error 1239
 SELECT ROW('test',2,3.33)=ROW('test',2,3.33,4);
+SELECT ROW('test',2,ROW(3,33))=ROW('test',2,ROW(3,33));
+SELECT ROW('test',2,ROW(3,33))=ROW('test',2,ROW(3,3));
+SELECT ROW('test',2,ROW(3,33))=ROW('test',2,ROW(3,NULL));
+-- error 1239
+SELECT ROW('test',2,ROW(3,33))=ROW('test',2,4);
 drop table if exists t1;
 create table t1 ( a int, b int, c int);
-insert into t1 values (1,2,3), (2,3,1), (3,2,1);
+insert into t1 values (1,2,3), (2,3,1), (3,2,1), (1,2,NULL);
 select * from t1 where ROW(1,2,3)=ROW(a,b,c);
 select * from t1 where ROW(0,2,3)=ROW(a,b,c);
 select * from t1 where ROW(1,2,3)<ROW(a,b,c);
-select * from t1 where ROW(a,2,3) IN(row(1,b,c), row(2,3,1));
-select * from t1 where ROW(c,2,3) IN(row(1,b,a), row(2,3,1));
-select * from t1 where ROW(a,b,c) IN(row(1,2,3), row(3,2,1));
+select ROW(a,2,3) IN(row(1,b,c), row(2,3,1)) from t1;
+select ROW(c,2,3) IN(row(1,b,a), row(2,3,1)) from t1;
+select ROW(a,b,c) IN(row(1,2,3), row(3,2,1)) from t1;
+select ROW(1,2,3) IN(row(a,b,c), row(1,2,3)) from t1;
 drop table t1;
 
 -- error 1239
-- 
cgit v1.2.1