new test files for checking datatypesbb-10.4-rucha

4 files changed, 273 insertions, 0 deletions

diff --git a/mysql-test/main/insert_returning_datatypes.result b/mysql-test/main/insert_returning_datatypes.result
new file mode 100644
index 00000000000..73c2c65d2c6
--- /dev/null
+++ b/mysql-test/main/insert_returning_datatypes.result
@@ -0,0 +1,72 @@
+CREATE TABLE t1(num_int1 INT(2) PRIMARY KEY, 
+num_bit1 BIT(8),
+num_float1 FLOAT(5,2),
+num_double1 DOUBLE(5,2),
+char_enum1 ENUM('A','B','C','D'),
+char_set1 SET('a','b','c','d','e'),
+str_varchar1 VARCHAR(2),
+d1 DATE,
+dt1 DATETIME,
+ts1 TIMESTAMP,
+y1 YEAR,
+b1 BOOL);
+CREATE TABLE t2(num_int2 INT(2) PRIMARY KEY, 
+num_bit2 BIT(8),
+num_float2 FLOAT(5,2),
+num_double2 DOUBLE(5,2),
+char_enum2 ENUM('A','B','C','D'),
+char_set2 SET('a','b','c','d','e'),
+str_varchar2 VARCHAR(2),
+d2 DATE,
+dt2 DATETIME,
+ts2 TIMESTAMP,
+y2 YEAR,
+b2 BOOL);
+#
+# SIMLPE INSERT STATEMENT
+#
+INSERT INTO t1(num_int1,num_bit1,num_float1,num_double1,char_enum1,char_set1,
+str_varchar1, d1,dt1,ts1,y1,b1) VALUES(1,b'1000011', 123.45, 123.55,
+'A','b,e', 'V','120314',"2012-04-19 13:08:22", '2001-07-22 12:12:12',
+2012,0) RETURNING *;
+num_int1	num_bit1	num_float1	num_double1	char_enum1	char_set1	str_varchar1	d1	dt1	ts1	y1	b1
+1	C	123.45	123.55	A	b,e	V	2012-03-14	2012-04-19 13:08:22	2001-07-22 12:12:12	2012	0
+#
+# MULTIPLE ROWS IN SINGLE STATEMENT 
+#
+INSERT INTO t1 VALUES(2,b'1000011', 123.45, 123.55, 'A','b,e', 'V',
+'120314',"2012-04-19 13:08:22", '2001-07-22 12:12:12',2012,0),(3,b'1000011', 
+123.45, 123.55, 'A','b,e', 'V','120314',"2012-04-19 13:08:22", 
+'2001-07-22 12:12:12',2012,1) RETURNING *;
+num_int1	num_bit1	num_float1	num_double1	char_enum1	char_set1	str_varchar1	d1	dt1	ts1	y1	b1
+2	C	123.45	123.55	A	b,e	V	2012-03-14	2012-04-19 13:08:22	2001-07-22 12:12:12	2012	0
+3	C	123.45	123.55	A	b,e	V	2012-03-14	2012-04-19 13:08:22	2001-07-22 12:12:12	2012	1
+#
+# INSERT...SET...RETURNING
+#
+INSERT INTO t1 SET num_int1=4,num_bit1=b'1000011',num_float1=124.67,
+num_double1=231.12,char_enum1='B',char_set1='a,d,e',
+str_varchar1='AB',d1='120314',dt1="2012-04-19 13:08:22",
+ts1='2001-07-22 12:12:1',y1=2014,b1=1 RETURNING *;
+num_int1	num_bit1	num_float1	num_double1	char_enum1	char_set1	str_varchar1	d1	dt1	ts1	y1	b1
+4	C	124.67	231.12	B	a,d,e	AB	2012-03-14	2012-04-19 13:08:22	2001-07-22 12:12:01	2014	1
+#
+# INSERT...ON DUPLICATE KEY UPDATE
+# 
+INSERT INTO t1 VALUES (5,b'1000011', 123.45, 123.55,'C','b,e', 'V',
+'120314',"2012-04-19 13:08:22", '2001-07-22 12:12:12',2012,0) 
+ON DUPLICATE KEY UPDATE num_float1=111.111 RETURNING *;
+num_int1	num_bit1	num_float1	num_double1	char_enum1	char_set1	str_varchar1	d1	dt1	ts1	y1	b1
+5	C	123.45	123.55	C	b,e	V	2012-03-14	2012-04-19 13:08:22	2001-07-22 12:12:12	2012	0
+#
+# INSERT...SELECT...RETURNING
+#
+INSERT INTO t2 SELECT * FROM t1 RETURNING *;
+num_int2	num_bit2	num_float2	num_double2	char_enum2	char_set2	str_varchar2	d2	dt2	ts2	y2	b2
+1	C	123.45	123.55	A	b,e	V	2012-03-14	2012-04-19 13:08:22	2001-07-22 12:12:12	2012	0
+2	C	123.45	123.55	A	b,e	V	2012-03-14	2012-04-19 13:08:22	2001-07-22 12:12:12	2012	0
+3	C	123.45	123.55	A	b,e	V	2012-03-14	2012-04-19 13:08:22	2001-07-22 12:12:12	2012	1
+4	C	124.67	231.12	B	a,d,e	AB	2012-03-14	2012-04-19 13:08:22	2001-07-22 12:12:01	2014	1
+5	C	123.45	123.55	C	b,e	V	2012-03-14	2012-04-19 13:08:22	2001-07-22 12:12:12	2012	0
+DROP TABLE t1;
+DROP TABLE t2;
diff --git a/mysql-test/main/insert_returning_datatypes.test b/mysql-test/main/insert_returning_datatypes.test
new file mode 100644
index 00000000000..725d849177a
--- /dev/null
+++ b/mysql-test/main/insert_returning_datatypes.test
@@ -0,0 +1,74 @@
+#Test for checking all dataypes are returned 
+
+CREATE TABLE t1(num_int1 INT(2) PRIMARY KEY, 
+num_bit1 BIT(8),
+num_float1 FLOAT(5,2),
+num_double1 DOUBLE(5,2),
+char_enum1 ENUM('A','B','C','D'),
+char_set1 SET('a','b','c','d','e'),
+str_varchar1 VARCHAR(2),
+d1 DATE,
+dt1 DATETIME,
+ts1 TIMESTAMP,
+y1 YEAR,
+b1 BOOL);
+
+CREATE TABLE t2(num_int2 INT(2) PRIMARY KEY, 
+num_bit2 BIT(8),
+num_float2 FLOAT(5,2),
+num_double2 DOUBLE(5,2),
+char_enum2 ENUM('A','B','C','D'),
+char_set2 SET('a','b','c','d','e'),
+str_varchar2 VARCHAR(2),
+d2 DATE,
+dt2 DATETIME,
+ts2 TIMESTAMP,
+y2 YEAR,
+b2 BOOL);
+
+
+--echo #
+--echo # SIMLPE INSERT STATEMENT
+--echo #
+INSERT INTO t1(num_int1,num_bit1,num_float1,num_double1,char_enum1,char_set1,
+str_varchar1, d1,dt1,ts1,y1,b1) VALUES(1,b'1000011', 123.45, 123.55,
+ 'A','b,e', 'V','120314',"2012-04-19 13:08:22", '2001-07-22 12:12:12',
+2012,0) RETURNING *;
+
+
+--echo #
+--echo # MULTIPLE ROWS IN SINGLE STATEMENT 
+--echo #
+INSERT INTO t1 VALUES(2,b'1000011', 123.45, 123.55, 'A','b,e', 'V',
+'120314',"2012-04-19 13:08:22", '2001-07-22 12:12:12',2012,0),(3,b'1000011', 
+123.45, 123.55, 'A','b,e', 'V','120314',"2012-04-19 13:08:22", 
+'2001-07-22 12:12:12',2012,1) RETURNING *;
+
+
+
+--echo #
+--echo # INSERT...SET...RETURNING
+--echo #
+INSERT INTO t1 SET num_int1=4,num_bit1=b'1000011',num_float1=124.67,
+num_double1=231.12,char_enum1='B',char_set1='a,d,e',
+str_varchar1='AB',d1='120314',dt1="2012-04-19 13:08:22",
+ts1='2001-07-22 12:12:1',y1=2014,b1=1 RETURNING *;
+
+
+
+--echo #
+--echo # INSERT...ON DUPLICATE KEY UPDATE
+--echo # 
+INSERT INTO t1 VALUES (5,b'1000011', 123.45, 123.55,'C','b,e', 'V',
+'120314',"2012-04-19 13:08:22", '2001-07-22 12:12:12',2012,0) 
+ON DUPLICATE KEY UPDATE num_float1=111.111 RETURNING *;
+
+
+--echo #
+--echo # INSERT...SELECT...RETURNING
+--echo #
+INSERT INTO t2 SELECT * FROM t1 RETURNING *;
+
+
+DROP TABLE t1;
+DROP TABLE t2;
diff --git a/mysql-test/main/replace_returning_datatypes.result b/mysql-test/main/replace_returning_datatypes.result
new file mode 100644
index 00000000000..f698e69521d
--- /dev/null
+++ b/mysql-test/main/replace_returning_datatypes.result
@@ -0,0 +1,61 @@
+CREATE TABLE t1(num_int1 INT(2) PRIMARY KEY, 
+num_bit1 BIT(8),
+num_float1 FLOAT(5,2),
+num_double1 DOUBLE(5,2),
+char_enum1 ENUM('A','B','C','D'),
+char_set1 SET('a','b','c','d','e'),
+str_varchar1 VARCHAR(2),
+d1 DATE,
+dt1 DATETIME,
+ts1 TIMESTAMP,
+y1 YEAR,
+b1 BOOL);
+CREATE TABLE t2(num_int2 INT(2) PRIMARY KEY, 
+num_bit2 BIT(8),
+num_float2 FLOAT(5,2),
+num_double2 DOUBLE(5,2),
+char_enum2 ENUM('A','B','C','D'),
+char_set2 SET('a','b','c','d','e'),
+str_varchar2 VARCHAR(2),
+d2 DATE,
+dt2 DATETIME,
+ts2 TIMESTAMP,
+y2 YEAR,
+b2 BOOL);
+#
+# SIMLPE REPLACE STATEMENT
+#
+REPLACE INTO t1(num_int1,num_bit1,num_float1,num_double1,char_enum1,char_set1,
+str_varchar1, d1,dt1,ts1,y1,b1) VALUES(1,b'1000011', 123.45, 123.55,
+'A','b,e', 'V','120314',"2012-04-19 13:08:22", '2001-07-22 12:12:12',
+2012,0) RETURNING *;
+num_int1	num_bit1	num_float1	num_double1	char_enum1	char_set1	str_varchar1	d1	dt1	ts1	y1	b1
+1	C	123.45	123.55	A	b,e	V	2012-03-14	2012-04-19 13:08:22	2001-07-22 12:12:12	2012	0
+#
+# MULTIPLE ROWS IN SINGLE STATEMENT 
+#
+REPLACE INTO t1 VALUES(1,b'1000011', 123.45, 123.55, 'A','b,e', 'V',
+'120314',"2012-04-19 13:08:22", '2001-07-22 12:12:12',2012,0),(3,b'1000011', 
+123.45, 123.55, 'A','b,e', 'V','120314',"2012-04-19 13:08:22", 
+'2001-07-22 12:12:12',2012,1) RETURNING *;
+num_int1	num_bit1	num_float1	num_double1	char_enum1	char_set1	str_varchar1	d1	dt1	ts1	y1	b1
+1	C	123.45	123.55	A	b,e	V	2012-03-14	2012-04-19 13:08:22	2001-07-22 12:12:12	2012	0
+3	C	123.45	123.55	A	b,e	V	2012-03-14	2012-04-19 13:08:22	2001-07-22 12:12:12	2012	1
+#
+# REPLACE...SET...RETURNING
+#
+REPLACE INTO t1 SET num_int1=1,num_bit1=b'1000011',num_float1=124.67,
+num_double1=231.12,char_enum1='B',char_set1='a,d,e',
+str_varchar1='AB',d1='120314',dt1="2012-04-19 13:08:22",
+ts1='2001-07-22 12:12:1',y1=2014,b1=1 RETURNING *;
+num_int1	num_bit1	num_float1	num_double1	char_enum1	char_set1	str_varchar1	d1	dt1	ts1	y1	b1
+1	C	124.67	231.12	B	a,d,e	AB	2012-03-14	2012-04-19 13:08:22	2001-07-22 12:12:01	2014	1
+#
+# REPLACE...SELECT...RETURNING
+#
+REPLACE INTO t2 SELECT * FROM t1 RETURNING *;
+num_int2	num_bit2	num_float2	num_double2	char_enum2	char_set2	str_varchar2	d2	dt2	ts2	y2	b2
+1	C	124.67	231.12	B	a,d,e	AB	2012-03-14	2012-04-19 13:08:22	2001-07-22 12:12:01	2014	1
+3	C	123.45	123.55	A	b,e	V	2012-03-14	2012-04-19 13:08:22	2001-07-22 12:12:12	2012	1
+DROP TABLE t1;
+DROP TABLE t2;
diff --git a/mysql-test/main/replace_returning_datatypes.test b/mysql-test/main/replace_returning_datatypes.test
new file mode 100644
index 00000000000..7e732b6f7b7
--- /dev/null
+++ b/mysql-test/main/replace_returning_datatypes.test
@@ -0,0 +1,66 @@
+#Test for checking all dataypes are returned 
+
+CREATE TABLE t1(num_int1 INT(2) PRIMARY KEY, 
+num_bit1 BIT(8),
+num_float1 FLOAT(5,2),
+num_double1 DOUBLE(5,2),
+char_enum1 ENUM('A','B','C','D'),
+char_set1 SET('a','b','c','d','e'),
+str_varchar1 VARCHAR(2),
+d1 DATE,
+dt1 DATETIME,
+ts1 TIMESTAMP,
+y1 YEAR,
+b1 BOOL);
+
+CREATE TABLE t2(num_int2 INT(2) PRIMARY KEY, 
+num_bit2 BIT(8),
+num_float2 FLOAT(5,2),
+num_double2 DOUBLE(5,2),
+char_enum2 ENUM('A','B','C','D'),
+char_set2 SET('a','b','c','d','e'),
+str_varchar2 VARCHAR(2),
+d2 DATE,
+dt2 DATETIME,
+ts2 TIMESTAMP,
+y2 YEAR,
+b2 BOOL);
+
+
+--echo #
+--echo # SIMLPE REPLACE STATEMENT
+--echo #
+REPLACE INTO t1(num_int1,num_bit1,num_float1,num_double1,char_enum1,char_set1,
+str_varchar1, d1,dt1,ts1,y1,b1) VALUES(1,b'1000011', 123.45, 123.55,
+'A','b,e', 'V','120314',"2012-04-19 13:08:22", '2001-07-22 12:12:12',
+2012,0) RETURNING *;
+
+
+--echo #
+--echo # MULTIPLE ROWS IN SINGLE STATEMENT 
+--echo #
+REPLACE INTO t1 VALUES(1,b'1000011', 123.45, 123.55, 'A','b,e', 'V',
+'120314',"2012-04-19 13:08:22", '2001-07-22 12:12:12',2012,0),(3,b'1000011', 
+123.45, 123.55, 'A','b,e', 'V','120314',"2012-04-19 13:08:22", 
+'2001-07-22 12:12:12',2012,1) RETURNING *;
+
+
+
+--echo #
+--echo # REPLACE...SET...RETURNING
+--echo #
+REPLACE INTO t1 SET num_int1=1,num_bit1=b'1000011',num_float1=124.67,
+num_double1=231.12,char_enum1='B',char_set1='a,d,e',
+str_varchar1='AB',d1='120314',dt1="2012-04-19 13:08:22",
+ts1='2001-07-22 12:12:1',y1=2014,b1=1 RETURNING *;
+
+
+
+--echo #
+--echo # REPLACE...SELECT...RETURNING
+--echo #
+REPLACE INTO t2 SELECT * FROM t1 RETURNING *;
+
+
+DROP TABLE t1;
+DROP TABLE t2;