Adding more tests for "MDEV-7563 Support CHECK constraint":

- real functions
- temporal functions
- hybrid functions

2 files changed, 289 insertions, 0 deletions

diff --git a/mysql-test/t/default.test b/mysql-test/t/default.test
index 5e1805f8a54..8620b7768e7 100644
--- a/mysql-test/t/default.test
+++ b/mysql-test/t/default.test
@@ -860,3 +860,260 @@ select * from t1;
 select default(a),b from t1;
 select a,default(b) from t1;
 drop table t1;
+
+
+--echo #
+--echo # Real functions
+--echo #
+
+CREATE TABLE t1 (a DECIMAL(10,1), b DOUBLE DEFAULT CAST(a AS DOUBLE));
+INSERT INTO t1 VALUES (10.1, DEFAULT);
+SELECT * FROM t1;
+DROP TABLE t1;
+
+CREATE TABLE t1 (a DOUBLE, b DOUBLE DEFAULT EXP(a), c DOUBLE DEFAULT LOG(b), d DOUBLE DEFAULT LOG(4, b));
+INSERT INTO t1 VALUES (2, DEFAULT, DEFAULT, DEFAULT);
+SELECT * FROM t1;
+DROP TABLE t1;
+
+CREATE TABLE t1 (a INT, b DOUBLE DEFAULT LOG2(a), c DOUBLE DEFAULT LOG10(a));
+INSERT INTO t1 VALUES (4, DEFAULT, DEFAULT);
+INSERT INTO t1 VALUES (100, DEFAULT, DEFAULT);
+SELECT * FROM t1;
+DROP TABLE t1;
+
+
+CREATE TABLE t1 (a DOUBLE, b DOUBLE DEFAULT SQRT(a), c DOUBLE DEFAULT POW(a,3));
+INSERT INTO t1 VALUES (4, DEFAULT, DEFAULT);
+SELECT * FROM t1;
+DROP TABLE t1;
+
+CREATE TABLE t1 (a DOUBLE, b DOUBLE DEFAULT ACOS(a), c DOUBLE DEFAULT ASIN(a), d DOUBLE DEFAULT ATAN(a));
+INSERT INTO t1 VALUES (1, DEFAULT, DEFAULT, DEFAULT);
+SELECT a, b/PI(), c/PI(), d/PI() FROM t1;
+DROP TABLE t1;
+
+CREATE TABLE t1 (a DOUBLE, b DOUBLE DEFAULT COS(a), c DOUBLE DEFAULT SIN(a), d DOUBLE DEFAULT TAN(a), e DOUBLE DEFAULT COT(a));
+INSERT INTO t1 (a) VALUES (PI()/3);
+SELECT ROUND(a,3), ROUND(b,3), ROUND(c,3), ROUND(d,3), ROUND(e,3) FROM t1;
+DROP TABLE t1;
+
+CREATE TABLE t1 (a DOUBLE DEFAULT RAND());
+INSERT INTO t1 VALUES (DEFAULT);
+DROP TABLE t1;
+
+CREATE TABLE t1 (a DOUBLE, b DOUBLE DEFAULT DEGREES(a), c DOUBLE DEFAULT RADIANS(b));
+INSERT INTO t1 VALUES (PI(), DEFAULT, DEFAULT);
+SELECT * FROM t1;
+DROP TABLE t1;
+
+# QQ: this sets "b" to (-1), which looks wrong
+#CREATE TABLE t1 (a VARCHAR(30), b DOUBLE DEFAULT MATCH (a) AGAINST('bbbb' IN BOOLEAN MODE));
+#INSERT INTO t1 VALUES ('aaaa bbbb cccc dddd', DEFAULT);
+#SELECT * FROM t1;
+#DROP TABLE t1;
+
+
+--echo #
+--echo # Temporal functions
+--echo #
+
+--echo # Item_temporal_hybrid_func
+
+CREATE TABLE t1 (a DATE, b INT, c DATE DEFAULT DATE_ADD(a, INTERVAL b DAY));
+INSERT INTO t1 VALUES ('2001-01-01', 30, DEFAULT);
+SELECT * FROM t1;
+DROP TABLE t1;
+
+CREATE TABLE t1 (a DATE, b TIME, c DATETIME DEFAULT ADDTIME(a, b));
+INSERT INTO t1 VALUES ('2001-01-01', '10:20:30', DEFAULT);
+SELECT * FROM t1;
+DROP TABLE t1;
+
+CREATE TABLE t1 (a VARCHAR(32), b VARCHAR(32), c DATE DEFAULT STR_TO_DATE(a,b));
+INSERT INTO t1 VALUES ('01,5,2013','%d,%m,%Y', DEFAULT);
+SELECT * FROM t1;
+DROP TABLE t1;
+
+
+--echo # Item_datefunc
+
+SET time_zone='-10:00';
+SET timestamp=UNIX_TIMESTAMP('2001-01-01 23:59:59');
+CREATE TABLE t1 (a DATE DEFAULT CURDATE(), b DATE DEFAULT UTC_DATE());
+INSERT INTO t1 VALUES ();
+SELECT * FROM t1;
+DROP TABLE t1;
+SET time_zone=DEFAULT, timestamp= DEFAULT;
+
+CREATE TABLE t1 (a INT, b DATE DEFAULT FROM_DAYS(a));
+INSERT INTO t1 VALUES (730669, DEFAULT);
+SELECT * FROM t1;
+DROP TABLE t1;
+
+CREATE TABLE t1 (a DATE, b DATE DEFAULT LAST_DAY(a));
+INSERT INTO t1 VALUES ('2003-02-05', DEFAULT);
+SELECT * FROM t1;
+DROP TABLE t1;
+
+CREATE TABLE t1 (yy INT, yd INT, d DATE DEFAULT MAKEDATE(yy, yd));
+INSERT INTO t1 VALUES (2011,32,DEFAULT);
+SELECT * FROM t1;
+DROP TABLE t1;
+
+--echo # Item_timefunc
+
+SET time_zone='-10:00';
+SET timestamp=UNIX_TIMESTAMP('2001-01-01 23:59:59');
+CREATE TABLE t1 (a TIME DEFAULT CURTIME(), b TIME DEFAULT UTC_TIME());
+INSERT INTO t1 VALUES ();
+SELECT * FROM t1;
+DROP TABLE t1;
+SET time_zone=DEFAULT, timestamp= DEFAULT;
+
+CREATE TABLE t1 (a INT, b TIME DEFAULT SEC_TO_TIME(a));
+INSERT INTO t1 VALUES (2378, DEFAULT);
+SELECT * FROM t1;
+DROP TABLE t1;
+
+CREATE TABLE t1 (a DATETIME, b DATETIME, c TIME DEFAULT TIMEDIFF(a,b));
+INSERT INTO t1 VALUES ('2000:01:01 00:00:00', '2000:01:02 10:20:30', DEFAULT);
+SELECT * FROM t1;
+DROP TABLE t1;
+
+CREATE TABLE t1 (hh INT, mm INT, ss INT, t TIME DEFAULT MAKETIME(hh,mm,ss));
+INSERT INTO t1 VALUES (10,20,30,DEFAULT);
+SELECT * FROM t1;
+DROP TABLE t1;
+
+
+--echo # Item_datetimefunc
+
+SET time_zone='-10:00';
+SET timestamp=UNIX_TIMESTAMP('2001-01-01 23:59:59');
+CREATE TABLE t1 (a TIMESTAMP DEFAULT NOW(), b TIMESTAMP DEFAULT UTC_TIMESTAMP());
+INSERT INTO t1 VALUES ();
+SELECT * FROM t1;
+DROP TABLE t1;
+SET time_zone=DEFAULT, timestamp= DEFAULT;
+
+# SYSDATE is evaluated during get_date() rather than fix_fields.
+CREATE TABLE t1 (a TIMESTAMP(6) DEFAULT SYSDATE(6), s INT, b TIMESTAMP(6) DEFAULT SYSDATE(6));
+INSERT INTO t1 VALUES (DEFAULT, SLEEP(0.1), DEFAULT);
+SELECT b>a FROM t1;
+DROP TABLE t1;
+
+SET time_zone='+00:00';
+CREATE TABLE t1 (a INT, b TIMESTAMP DEFAULT FROM_UNIXTIME(a));
+INSERT INTO t1 VALUES (1447430881, DEFAULT);
+SELECT * FROM t1;
+DROP TABLE t1;
+SET time_zone=DEFAULT;
+
+CREATE TABLE t1 (a TIMESTAMP, b TIMESTAMP DEFAULT CONVERT_TZ(a, '-10:00', '+10:00'));
+INSERT INTO t1 VALUES ('2001-01-01 10:20:30', DEFAULT);
+SELECT * FROM t1;
+DROP TABLE t1;
+
+--echo # Item_temporal_typecast
+CREATE TABLE t1 (a INT, b DATE DEFAULT CAST(a AS DATE));
+INSERT INTO t1 VALUES (20010203, DEFAULT);
+SELECT * FROM t1;
+DROP TABLE t1;
+
+CREATE TABLE t1 (a INT, b TIME DEFAULT CAST(a AS TIME));
+INSERT INTO t1 VALUES (102030, DEFAULT);
+SELECT * FROM t1;
+DROP TABLE t1;
+
+CREATE TABLE t1 (a BIGINT, b DATETIME DEFAULT CAST(a AS DATETIME));
+INSERT INTO t1 VALUES (20010203102030, DEFAULT);
+SELECT * FROM t1;
+DROP TABLE t1;
+
+
+
+--echo #
+--echo # Hybrid type functions
+--echo #
+
+CREATE TABLE t1 (a INT, b INT, c INT DEFAULT COALESCE(a,b));
+INSERT INTO t1 VALUES (NULL, 1, DEFAULT);
+SELECT * FROM t1;
+DROP TABLE t1;
+
+
+CREATE TABLE t1 (a INT, b INT, c INT DEFAULT IFNULL(a,b));
+INSERT INTO t1 VALUES (NULL, 2, DEFAULT);
+INSERT INTO t1 VALUES (1, 2, DEFAULT);
+SELECT * FROM t1;
+DROP TABLE t1;
+
+
+CREATE TABLE t1 (a INT, b INT, c INT DEFAULT NULLIF(a,b));
+INSERT INTO t1 VALUES (1, 1, DEFAULT);
+INSERT INTO t1 VALUES (1, 2, DEFAULT);
+SELECT * FROM t1;
+DROP TABLE t1;
+
+CREATE TABLE t1 (a INT, b INT, c INT DEFAULT IF(a,b,2));
+INSERT INTO t1 VALUES (0, 1, DEFAULT);
+INSERT INTO t1 VALUES (1, 1, DEFAULT);
+SELECT * FROM t1;
+DROP TABLE t1;
+
+CREATE TABLE t1 (a INT, b INT, c INT DEFAULT CASE WHEN a THEN b ELSE 2 END);
+INSERT INTO t1 VALUES (0, 1, DEFAULT);
+INSERT INTO t1 VALUES (1, 1, DEFAULT);
+SELECT * FROM t1;
+DROP TABLE t1;
+
+
+CREATE TABLE t1 (a INT, b INT DEFAULT -a);
+INSERT INTO t1 VALUES (10, DEFAULT);
+SELECT * FROM t1;
+DROP TABLE t1;
+
+CREATE TABLE t1 (a INT, b INT DEFAULT ABS(a));
+INSERT INTO t1 VALUES (-10, DEFAULT);
+SELECT * FROM t1;
+DROP TABLE t1;
+
+CREATE TABLE t1 (a DOUBLE, b INT DEFAULT CEILING(a), c INT DEFAULT FLOOR(a), d INT DEFAULT ROUND(a));
+INSERT INTO t1 VALUES (1.5, DEFAULT, DEFAULT, DEFAULT);
+INSERT INTO t1 VALUES (-1.5, DEFAULT, DEFAULT, DEFAULT);
+SELECT * FROM t1;
+DROP TABLE t1;
+
+CREATE TABLE t1 (a INT, b INT, c INT DEFAULT a+b, d INT DEFAULT a-b);
+INSERT INTO t1 VALUES (2, 1, DEFAULT, DEFAULT);
+SELECT * FROM t1;
+DROP TABLE t1;
+
+CREATE TABLE t1 (a INT, b INT, c INT DEFAULT a*b, d INT DEFAULT a/b, e INT DEFAULT a MOD b);
+INSERT INTO t1 VALUES (7, 3, DEFAULT, DEFAULT, DEFAULT);
+SELECT * FROM t1;
+DROP TABLE t1;
+
+SET time_zone='+00:00';
+CREATE TABLE t1 (a DATETIME, b INT DEFAULT UNIX_TIMESTAMP(a));
+INSERT INTO t1 VALUES ('2001-01-01 10:20:30', DEFAULT);
+SELECT * FROM t1;
+DROP TABLE t1;
+SET time_zone=DEFAULT;
+
+CREATE TABLE t1 (a TIME, b INT DEFAULT TIME_TO_SEC(a));
+INSERT INTO t1 VALUES ('22:23:00', DEFAULT);
+SELECT * FROM t1;
+DROP TABLE t1;
+
+CREATE TABLE t1 (a INT, b INT, c INT DEFAULT LEAST(a,b), d INT DEFAULT GREATEST(a,b));
+INSERT INTO t1 VALUES (0, 1, DEFAULT, DEFAULT);
+INSERT INTO t1 VALUES (1, 1, DEFAULT, DEFAULT);
+SELECT * FROM t1;
+DROP TABLE t1;
+
+CREATE TABLE t1 (a INT, b INT, c INT DEFAULT LAST_VALUE(a,b));
+INSERT INTO t1 VALUES (1, 2, DEFAULT);
+SELECT * FROM t1;
+DROP TABLE t1;
diff --git a/mysql-test/t/gis.test b/mysql-test/t/gis.test
index 18cfe95b749..1c7169f33d6 100644
--- a/mysql-test/t/gis.test
+++ b/mysql-test/t/gis.test
@@ -1525,3 +1525,35 @@ CREATE TABLE t1 (g1 GEOMETRY NOT NULL,g2 GEOMETRY NULL);
 CREATE TABLE t2 AS SELECT WITHIN(g1,g1) as w1,WITHIN(g2,g2) AS w2 FROM t1;
 SHOW CREATE TABLE t2;
 DROP TABLE t1,t2;
+
+--echo #
+--echo # Start of 10.2 tests
+--echo #
+
+--echo #
+--echo # MDEV-7563 Support CHECK constraint
+--echo #
+
+CREATE TABLE t1 (a POINT, x DOUBLE DEFAULT x(a), y DOUBLE DEFAULT y(a));
+INSERT INTO t1 (a) VALUES (Point(1,2));
+SELECT x,y FROM t1;
+DROP TABLE t1;
+
+CREATE TABLE t1 (g GEOMETRY, area DOUBLE DEFAULT ST_AREA(g));
+INSERT INTO t1 (g) VALUES (GeomFromText('POLYGON((0 0,20 0,20 20,0 20,0 0))'));
+SELECT area FROM t1;
+DROP TABLE t1;
+
+CREATE TABLE t1 (g GEOMETRY, length DOUBLE DEFAULT ST_LENGTH(g));
+INSERT INTO t1 (g) VALUES (GeomFromText('LINESTRING(0 0,20 0,20 20,0 20,0 0)'));
+SELECT length FROM t1;
+DROP TABLE t1;
+
+CREATE TABLE t1 (g POINT, distance DOUBLE DEFAULT ST_DISTANCE(g, POINT(0,0)));
+INSERT INTO t1 (g) VALUES (Point(1,0));
+SELECT distance FROM t1;
+DROP TABLE t1;
+
+--echo #
+--echo # End of 10.2 tests
+--echo #