diff options
| author | Alexander Barkov <bar@mariadb.org> | 2016-06-29 21:10:35 +0200 |
|---|---|---|
| committer | Sergei Golubchik <serg@mariadb.org> | 2016-06-30 11:43:02 +0200 |
| commit | 4dcbb775aea9afc79d550661b384e0528c47eda4 (patch) | |
| tree | b42f0cdd90589a97f20a21089790af727995f4ab | |
| parent | b3e11d33db5ab866c710ff8dd8653c314578f545 (diff) | |
| download | mariadb-git-4dcbb775aea9afc79d550661b384e0528c47eda4.tar.gz | |
parentheses in default
- Adding SHOW CREATE TABLE into all DEFAULT tests,
to cover need_parentheses_in_default() for all items
- Fixing a few items not to print parentheses in DEFAULT:
spatial function-alike predicates, IS_IPV4 and IS_IPV6 functions,
COLUMN_CHECK() and COLUMN_EXISTS().
27 files changed, 1415 insertions, 2 deletions
diff --git a/mysql-test/r/ctype_like_range.result b/mysql-test/r/ctype_like_range.result index db452406e5c..033dc214335 100644 --- a/mysql-test/r/ctype_like_range.result +++ b/mysql-test/r/ctype_like_range.result @@ -3309,6 +3309,13 @@ a VARCHAR(10), mn VARCHAR(10) DEFAULT LIKE_RANGE_MIN(a,10), mx VARCHAR(10) DEFAULT LIKE_RANGE_MAX(a,10) ); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` varchar(10) DEFAULT NULL, + `mn` varchar(10) DEFAULT LIKE_RANGE_MIN(a,10), + `mx` varchar(10) DEFAULT LIKE_RANGE_MAX(a,10) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 INSERT INTO t1 (a) VALUES ('a'),('a_'),('a%'); SELECT a, HEX(mn), HEX(mx) FROM t1; a HEX(mn) HEX(mx) diff --git a/mysql-test/r/default.result b/mysql-test/r/default.result index 26d04cc2656..d3fbe3df892 100644 --- a/mysql-test/r/default.result +++ b/mysql-test/r/default.result @@ -473,6 +473,8 @@ CREATE TABLE t1 (a INT DEFAULT (1=ANY (SELECT 1))); ERROR HY000: Function or expression 'subselect' is not allowed for 'DEFAULT' of column/constraint 'a' CREATE TABLE t1 (a INT DEFAULT ROW(1,1)); ERROR 21000: Operand should contain 1 column(s) +CREATE TABLE t1 (a INT DEFAULT (1,1)); +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '1))' at line 1 CREATE TABLE t1 (a INT DEFAULT ((1,1))); ERROR 21000: Operand should contain 1 column(s) CREATE TABLE t1 (a INT DEFAULT ?); @@ -740,26 +742,53 @@ DROP TABLE t1; CREATE TABLE t1 (a INT DEFAULT 'x'); ERROR 42000: Invalid default value for 'a' CREATE TABLE t1 (a INT DEFAULT CONCAT('x')); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT CONCAT('x') +) ENGINE=MyISAM DEFAULT CHARSET=latin1 insert into t1 values(); Warnings: Warning 1366 Incorrect integer value: 'x' for column 'a' at row 1 DROP TABLE t1; CREATE TABLE t1 (a INT DEFAULT COALESCE('x')); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT COALESCE('x') +) ENGINE=MyISAM DEFAULT CHARSET=latin1 insert into t1 values(); Warnings: Warning 1366 Incorrect integer value: 'x' for column 'a' at row 1 DROP TABLE t1; +CREATE TABLE t1 (a INT DEFAULT (((((COALESCE('x'))))))); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT ((((COALESCE('x'))))) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t1; # # INT: string expressions with numbers + garbage # CREATE TABLE t1 (a INT DEFAULT '1x'); ERROR 42000: Invalid default value for 'a' CREATE TABLE t1 (a INT DEFAULT COALESCE('1x')); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT COALESCE('1x') +) ENGINE=MyISAM DEFAULT CHARSET=latin1 insert into t1 values(); Warnings: Warning 1265 Data truncated for column 'a' at row 1 DROP TABLE t1; CREATE TABLE t1 (a INT DEFAULT CONCAT('1x')); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT CONCAT('1x') +) ENGINE=MyISAM DEFAULT CHARSET=latin1 insert into t1 values(); Warnings: Warning 1265 Data truncated for column 'a' at row 1 @@ -891,6 +920,11 @@ DROP TABLE t1; CREATE TABLE t1 (a VARCHAR(2) DEFAULT 'xxx' NOT NULL); ERROR 42000: Invalid default value for 'a' CREATE TABLE t1 (a VARCHAR(2) DEFAULT CONCAT('xxx') NOT NULL); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` varchar(2) NOT NULL DEFAULT CONCAT('xxx') +) ENGINE=MyISAM DEFAULT CHARSET=latin1 insert into t1 values(); Warnings: Warning 1265 Data truncated for column 'a' at row 1 @@ -901,6 +935,11 @@ DROP TABLE t1; CREATE TABLE t1 (a VARCHAR(2) DEFAULT 'xx ' NOT NULL); ERROR 42000: Invalid default value for 'a' CREATE TABLE t1 (a VARCHAR(2) DEFAULT CONCAT('xx ') NOT NULL); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` varchar(2) NOT NULL DEFAULT CONCAT('xx ') +) ENGINE=MyISAM DEFAULT CHARSET=latin1 insert into t1 values(); Warnings: Note 1265 Data truncated for column 'a' at row 1 @@ -911,11 +950,21 @@ DROP TABLE t1; CREATE TABLE t1 (a VARCHAR(2) CHARACTER SET latin1 DEFAULT _utf8 X'D18F' NOT NULL); ERROR 42000: Invalid default value for 'a' CREATE TABLE t1 (a VARCHAR(2) CHARACTER SET latin1 DEFAULT CONCAT(_utf8 X'D18F') NOT NULL); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` varchar(2) NOT NULL DEFAULT CONCAT(_utf8 X'D18F') +) ENGINE=MyISAM DEFAULT CHARSET=latin1 insert into t1 values(); Warnings: Warning 1366 Incorrect string value: '\xD1\x8F' for column 'a' at row 1 DROP TABLE t1; CREATE TABLE t1 (a VARCHAR(2) CHARACTER SET latin1 DEFAULT CONCAT(_utf8 0xD18F) NOT NULL); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` varchar(2) NOT NULL DEFAULT CONCAT(_utf8 0xD18F) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 insert into t1 values(); Warnings: Warning 1366 Incorrect string value: '\xD1\x8F' for column 'a' at row 1 @@ -1129,6 +1178,11 @@ ERROR HY000: Function or expression 'current_role()' is not allowed for 'DEFAULT # Other Item_func_sysconst derived functions # CREATE TABLE t1 (a VARCHAR(30) DEFAULT DATABASE()); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` varchar(30) DEFAULT DATABASE() +) ENGINE=MyISAM DEFAULT CHARSET=latin1 INSERT INTO t1 VALUES (); USE INFORMATION_SCHEMA; INSERT INTO test.t1 VALUES (); @@ -1144,6 +1198,12 @@ DROP TABLE t1; # Check DEFAULT() function # CREATE TABLE `t1` (`a` int(11) DEFAULT (3+3),`b` int(11) DEFAULT '1000'); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT (3+3), + `b` int(11) DEFAULT '1000' +) ENGINE=MyISAM DEFAULT CHARSET=latin1 insert into t1 values (1,1),(2,2); insert into t1 values (default,default); select * from t1; @@ -1163,18 +1223,39 @@ drop table t1; # Real functions # CREATE TABLE t1 (a DECIMAL(10,1), b DOUBLE DEFAULT CAST(a AS DOUBLE)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` decimal(10,1) DEFAULT NULL, + `b` double DEFAULT (CAST(a AS DOUBLE)) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 INSERT INTO t1 VALUES (10.1, DEFAULT); SELECT * FROM t1; a b 10.1 10.1 DROP TABLE t1; CREATE TABLE t1 (a DOUBLE, b DOUBLE DEFAULT EXP(a), c DOUBLE DEFAULT LOG(b), d DOUBLE DEFAULT LOG(4, b)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` double DEFAULT NULL, + `b` double DEFAULT EXP(a), + `c` double DEFAULT LOG(b), + `d` double DEFAULT LOG(4, b) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 INSERT INTO t1 VALUES (2, DEFAULT, DEFAULT, DEFAULT); SELECT * FROM t1; a b c d 2 7.38905609893065 2 1.4426950408889634 DROP TABLE t1; CREATE TABLE t1 (a INT, b DOUBLE DEFAULT LOG2(a), c DOUBLE DEFAULT LOG10(a)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL, + `b` double DEFAULT LOG2(a), + `c` double DEFAULT LOG10(a) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 INSERT INTO t1 VALUES (4, DEFAULT, DEFAULT); INSERT INTO t1 VALUES (100, DEFAULT, DEFAULT); SELECT * FROM t1; @@ -1183,27 +1264,63 @@ a b c 100 6.643856189774725 2 DROP TABLE t1; CREATE TABLE t1 (a DOUBLE, b DOUBLE DEFAULT SQRT(a), c DOUBLE DEFAULT POW(a,3)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` double DEFAULT NULL, + `b` double DEFAULT SQRT(a), + `c` double DEFAULT POW(a,3) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 INSERT INTO t1 VALUES (4, DEFAULT, DEFAULT); SELECT * FROM t1; a b c 4 2 64 DROP TABLE t1; CREATE TABLE t1 (a DOUBLE, b DOUBLE DEFAULT ACOS(a), c DOUBLE DEFAULT ASIN(a), d DOUBLE DEFAULT ATAN(a)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` double DEFAULT NULL, + `b` double DEFAULT ACOS(a), + `c` double DEFAULT ASIN(a), + `d` double DEFAULT ATAN(a) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 INSERT INTO t1 VALUES (1, DEFAULT, DEFAULT, DEFAULT); SELECT a, b/PI(), c/PI(), d/PI() FROM t1; a b/PI() c/PI() d/PI() 1 0 0.5 0.25 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)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` double DEFAULT NULL, + `b` double DEFAULT COS(a), + `c` double DEFAULT SIN(a), + `d` double DEFAULT TAN(a), + `e` double DEFAULT COT(a) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 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; ROUND(a,3) ROUND(b,3) ROUND(c,3) ROUND(d,3) ROUND(e,3) 1.047 0.500 0.866 1.732 0.577 DROP TABLE t1; CREATE TABLE t1 (a DOUBLE DEFAULT RAND()); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` double DEFAULT RAND() +) ENGINE=MyISAM DEFAULT CHARSET=latin1 INSERT INTO t1 VALUES (DEFAULT); DROP TABLE t1; CREATE TABLE t1 (a DOUBLE, b DOUBLE DEFAULT DEGREES(a), c DOUBLE DEFAULT RADIANS(b)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` double DEFAULT NULL, + `b` double DEFAULT DEGREES(a), + `c` double DEFAULT RADIANS(b) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 INSERT INTO t1 VALUES (PI(), DEFAULT, DEFAULT); SELECT * FROM t1; a b c @@ -1213,18 +1330,37 @@ DROP TABLE t1; # INT result functions # CREATE TABLE t1 (a INT, b INT DEFAULT INTERVAL(a, 10, 20, 30, 40)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL, + `b` int(11) DEFAULT INTERVAL(a, 10, 20, 30, 40) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 INSERT INTO t1 (a) VALUES (34); SELECT * FROM t1; a b 34 3 DROP TABLE t1; CREATE TABLE t1 (a INT, b INT, c INT DEFAULT (a DIV b)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL, + `b` int(11) DEFAULT NULL, + `c` int(11) DEFAULT (a DIV b) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 INSERT INTO t1 (a, b) VALUES (13, 3); SELECT * FROM t1; a b c 13 3 4 DROP TABLE t1; CREATE TABLE t1 (a INT, b INT DEFAULT SIGN(a)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL, + `b` int(11) DEFAULT SIGN(a) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 INSERT INTO t1 (a) VALUES (-10),(0), (10); SELECT * FROM t1; a b @@ -1233,24 +1369,48 @@ a b 10 1 DROP TABLE t1; CREATE TABLE t1 (a VARCHAR(30), b INT DEFAULT FIELD(a, 'Hej', 'ej', 'Heja', 'hej', 'foo')); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` varchar(30) DEFAULT NULL, + `b` int(11) DEFAULT FIELD(a, 'Hej', 'ej', 'Heja', 'hej', 'foo') +) ENGINE=MyISAM DEFAULT CHARSET=latin1 INSERT INTO t1 (a) VALUES ('ej'); SELECT * FROM t1; a b ej 2 DROP TABLE t1; CREATE TABLE t1 (a VARCHAR(30), b INT DEFAULT FIND_IN_SET(a, 'Hej,ej,Heja,hej,foo')); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` varchar(30) DEFAULT NULL, + `b` int(11) DEFAULT FIND_IN_SET(a, 'Hej,ej,Heja,hej,foo') +) ENGINE=MyISAM DEFAULT CHARSET=latin1 INSERT INTO t1 (a) VALUES ('ej'); SELECT * FROM t1; a b ej 2 DROP TABLE t1; CREATE TABLE t1 (a VARCHAR(30), b INT DEFAULT ASCII(a), c INT DEFAULT ORD(a)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` varchar(30) DEFAULT NULL, + `b` int(11) DEFAULT ASCII(a), + `c` int(11) DEFAULT ORD(a) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 INSERT INTO t1 (a) VALUES ('a'); SELECT * FROM t1; a b c a 97 97 DROP TABLE t1; CREATE TABLE t1 (a TEXT DEFAULT UUID_SHORT()); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` text DEFAULT UUID_SHORT() +) ENGINE=MyISAM DEFAULT CHARSET=latin1 INSERT INTO t1 VALUES (); SELECT a>0 FROM t1; a>0 @@ -1283,18 +1443,39 @@ ERROR HY000: Function or expression 'match' is not allowed for 'DEFAULT' of colu # # Item_temporal_hybrid_func CREATE TABLE t1 (a DATE, b INT, c DATE DEFAULT DATE_ADD(a, INTERVAL b DAY)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` date DEFAULT NULL, + `b` int(11) DEFAULT NULL, + `c` date DEFAULT (DATE_ADD(a, INTERVAL b DAY)) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 INSERT INTO t1 VALUES ('2001-01-01', 30, DEFAULT); SELECT * FROM t1; a b c 2001-01-01 30 2001-01-31 DROP TABLE t1; CREATE TABLE t1 (a DATE, b TIME, c DATETIME DEFAULT ADDTIME(a, b)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` date DEFAULT NULL, + `b` time DEFAULT NULL, + `c` datetime DEFAULT ADDTIME(a, b) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 INSERT INTO t1 VALUES ('2001-01-01', '10:20:30', DEFAULT); SELECT * FROM t1; a b c 2001-01-01 10:20:30 2001-01-01 10:20:30 DROP TABLE t1; CREATE TABLE t1 (a VARCHAR(32), b VARCHAR(32), c DATE DEFAULT STR_TO_DATE(a,b)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` varchar(32) DEFAULT NULL, + `b` varchar(32) DEFAULT NULL, + `c` date DEFAULT STR_TO_DATE(a,b) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 INSERT INTO t1 VALUES ('01,5,2013','%d,%m,%Y', DEFAULT); SELECT * FROM t1; a b c @@ -1306,6 +1487,12 @@ ERROR HY000: Function or expression 'date_format' is not allowed for 'DEFAULT' o 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()); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` date DEFAULT CURDATE(), + `b` date DEFAULT UTC_DATE() +) ENGINE=MyISAM DEFAULT CHARSET=latin1 INSERT INTO t1 VALUES (); SELECT * FROM t1; a b @@ -1313,18 +1500,37 @@ a b DROP TABLE t1; SET time_zone=DEFAULT, timestamp= DEFAULT; CREATE TABLE t1 (a INT, b DATE DEFAULT FROM_DAYS(a)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL, + `b` date DEFAULT FROM_DAYS(a) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 INSERT INTO t1 VALUES (730669, DEFAULT); SELECT * FROM t1; a b 730669 2000-07-03 DROP TABLE t1; CREATE TABLE t1 (a DATE, b DATE DEFAULT LAST_DAY(a)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` date DEFAULT NULL, + `b` date DEFAULT LAST_DAY(a) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 INSERT INTO t1 VALUES ('2003-02-05', DEFAULT); SELECT * FROM t1; a b 2003-02-05 2003-02-28 DROP TABLE t1; CREATE TABLE t1 (yy INT, yd INT, d DATE DEFAULT MAKEDATE(yy, yd)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `yy` int(11) DEFAULT NULL, + `yd` int(11) DEFAULT NULL, + `d` date DEFAULT MAKEDATE(yy, yd) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 INSERT INTO t1 VALUES (2011,32,DEFAULT); SELECT * FROM t1; yy yd d @@ -1334,6 +1540,12 @@ DROP TABLE t1; 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()); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` time DEFAULT CURTIME(), + `b` time DEFAULT UTC_TIME() +) ENGINE=MyISAM DEFAULT CHARSET=latin1 INSERT INTO t1 VALUES (); SELECT * FROM t1; a b @@ -1341,18 +1553,39 @@ a b DROP TABLE t1; SET time_zone=DEFAULT, timestamp= DEFAULT; CREATE TABLE t1 (a INT, b TIME DEFAULT SEC_TO_TIME(a)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL, + `b` time DEFAULT SEC_TO_TIME(a) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 INSERT INTO t1 VALUES (2378, DEFAULT); SELECT * FROM t1; a b 2378 00:39:38 DROP TABLE t1; CREATE TABLE t1 (a DATETIME, b DATETIME, c TIME DEFAULT TIMEDIFF(a,b)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` datetime DEFAULT NULL, + `b` datetime DEFAULT NULL, + `c` time DEFAULT TIMEDIFF(a,b) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 INSERT INTO t1 VALUES ('2000:01:01 00:00:00', '2000:01:02 10:20:30', DEFAULT); SELECT * FROM t1; a b c 2000-01-01 00:00:00 2000-01-02 10:20:30 -34:20:30 DROP TABLE t1; CREATE TABLE t1 (hh INT, mm INT, ss INT, t TIME DEFAULT MAKETIME(hh,mm,ss)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `hh` int(11) DEFAULT NULL, + `mm` int(11) DEFAULT NULL, + `ss` int(11) DEFAULT NULL, + `t` time DEFAULT MAKETIME(hh,mm,ss) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 INSERT INTO t1 VALUES (10,20,30,DEFAULT); SELECT * FROM t1; hh mm ss t @@ -1362,6 +1595,12 @@ DROP TABLE t1; 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()); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, + `b` timestamp NOT NULL DEFAULT UTC_TIMESTAMP() +) ENGINE=MyISAM DEFAULT CHARSET=latin1 INSERT INTO t1 VALUES (); SELECT * FROM t1; a b @@ -1369,6 +1608,13 @@ a b DROP TABLE t1; SET time_zone=DEFAULT, timestamp= DEFAULT; CREATE TABLE t1 (a TIMESTAMP(6) DEFAULT SYSDATE(6), s INT, b TIMESTAMP(6) DEFAULT SYSDATE(6)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` timestamp(6) NOT NULL DEFAULT SYSDATE(6), + `s` int(11) DEFAULT NULL, + `b` timestamp(6) NOT NULL DEFAULT SYSDATE(6) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 INSERT INTO t1 VALUES (DEFAULT, SLEEP(0.1), DEFAULT); SELECT b>a FROM t1; b>a @@ -1376,6 +1622,12 @@ b>a DROP TABLE t1; SET time_zone='+00:00'; CREATE TABLE t1 (a INT, b TIMESTAMP DEFAULT FROM_UNIXTIME(a)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL, + `b` timestamp NOT NULL DEFAULT FROM_UNIXTIME(a) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 INSERT INTO t1 VALUES (1447430881, DEFAULT); SELECT * FROM t1; a b @@ -1383,6 +1635,12 @@ a b DROP TABLE t1; SET time_zone=DEFAULT; CREATE TABLE t1 (a TIMESTAMP, b TIMESTAMP DEFAULT CONVERT_TZ(a, '-10:00', '+10:00')); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, + `b` timestamp NOT NULL DEFAULT CONVERT_TZ(a, '-10:00', '+10:00') +) ENGINE=MyISAM DEFAULT CHARSET=latin1 INSERT INTO t1 VALUES ('2001-01-01 10:20:30', DEFAULT); SELECT * FROM t1; a b @@ -1390,18 +1648,36 @@ a b DROP TABLE t1; # Item_temporal_typecast CREATE TABLE t1 (a INT, b DATE DEFAULT CAST(a AS DATE)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL, + `b` date DEFAULT CAST(a AS DATE) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 INSERT INTO t1 VALUES (20010203, DEFAULT); SELECT * FROM t1; a b 20010203 2001-02-03 DROP TABLE t1; CREATE TABLE t1 (a INT, b TIME DEFAULT CAST(a AS TIME)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL, + `b` time DEFAULT CAST(a AS TIME) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 INSERT INTO t1 VALUES (102030, DEFAULT); SELECT * FROM t1; a b 102030 10:20:30 DROP TABLE t1; CREATE TABLE t1 (a BIGINT, b DATETIME DEFAULT CAST(a AS DATETIME)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` bigint(20) DEFAULT NULL, + `b` datetime DEFAULT CAST(a AS DATETIME) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 INSERT INTO t1 VALUES (20010203102030, DEFAULT); SELECT * FROM t1; a b @@ -1411,114 +1687,230 @@ DROP TABLE t1; # Functions with temporal input # CREATE TABLE t1 (a INT, b INT, c INT DEFAULT PERIOD_ADD(a,b)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL, + `b` int(11) DEFAULT NULL, + `c` int(11) DEFAULT PERIOD_ADD(a,b) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 INSERT INTO t1 (a,b) VALUES (200801, 2); SELECT * FROM t1; a b c 200801 2 200803 DROP TABLE t1; CREATE TABLE t1 (a INT, b INT, c INT DEFAULT PERIOD_DIFF(a,b)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL, + `b` int(11) DEFAULT NULL, + `c` int(11) DEFAULT PERIOD_DIFF(a,b) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 INSERT INTO t1 (a,b) VALUES (200802, 200703); SELECT * FROM t1; a b c 200802 200703 11 DROP TABLE t1; CREATE TABLE t1 (a INT, b INT DEFAULT TO_DAYS(a)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL, + `b` int(11) DEFAULT TO_DAYS(a) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 INSERT INTO t1 (a) VALUES (950501); SELECT * FROM t1; a b 950501 728779 DROP TABLE t1; CREATE TABLE t1 (a DATE, b INT DEFAULT TO_DAYS(a)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` date DEFAULT NULL, + `b` int(11) DEFAULT TO_DAYS(a) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 INSERT INTO t1 (a) VALUES ('2007-10-07'); SELECT * FROM t1; a b 2007-10-07 733321 DROP TABLE t1; CREATE TABLE t1 (a INT, b BIGINT DEFAULT TO_SECONDS(a)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL, + `b` bigint(20) DEFAULT TO_SECONDS(a) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 INSERT INTO t1 (a) VALUES (950501); SELECT * FROM t1; a b 950501 62966505600 DROP TABLE t1; CREATE TABLE t1 (a DATE, b BIGINT DEFAULT TO_SECONDS(a)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` date DEFAULT NULL, + `b` bigint(20) DEFAULT TO_SECONDS(a) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 INSERT INTO t1 (a) VALUES ('2009-11-29'); SELECT * FROM t1; a b 2009-11-29 63426672000 DROP TABLE t1; CREATE TABLE t1 (a DATETIME, b BIGINT DEFAULT TO_SECONDS(a)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` datetime DEFAULT NULL, + `b` bigint(20) DEFAULT TO_SECONDS(a) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 INSERT INTO t1 (a) VALUES ('2009-11-29 13:43:32'); SELECT * FROM t1; a b 2009-11-29 13:43:32 63426721412 DROP TABLE t1; CREATE TABLE t1 (a DATE, b BIGINT DEFAULT DAYOFMONTH(a)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` date DEFAULT NULL, + `b` bigint(20) DEFAULT DAYOFMONTH(a) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 INSERT INTO t1 (a) VALUES ('2007-02-03'); SELECT * FROM t1; a b 2007-02-03 3 DROP TABLE t1; CREATE TABLE t1 (a DATE, b BIGINT DEFAULT DAYOFWEEK(a)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` date DEFAULT NULL, + `b` bigint(20) DEFAULT DAYOFWEEK(a) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 INSERT INTO t1 (a) VALUES ('2007-02-03'); SELECT * FROM t1; a b 2007-02-03 7 DROP TABLE t1; CREATE TABLE t1 (a DATE, b BIGINT DEFAULT DAYOFYEAR(a)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` date DEFAULT NULL, + `b` bigint(20) DEFAULT DAYOFYEAR(a) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 INSERT INTO t1 (a) VALUES ('2007-02-03'); SELECT * FROM t1; a b 2007-02-03 34 DROP TABLE t1; CREATE TABLE t1 (a TIME, b INT DEFAULT HOUR(a)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` time DEFAULT NULL, + `b` int(11) DEFAULT HOUR(a) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 INSERT INTO t1 (a) VALUES ('10:05:03'); SELECT * FROM t1; a b 10:05:03 10 DROP TABLE t1; CREATE TABLE t1 (a TIME, b INT DEFAULT MINUTE(a)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` time DEFAULT NULL, + `b` int(11) DEFAULT MINUTE(a) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 INSERT INTO t1 (a) VALUES ('10:05:03'); SELECT * FROM t1; a b 10:05:03 5 DROP TABLE t1; CREATE TABLE t1 (a TIME, b INT DEFAULT SECOND(a)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` time DEFAULT NULL, + `b` int(11) DEFAULT SECOND(a) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 INSERT INTO t1 (a) VALUES ('10:05:03'); SELECT * FROM t1; a b 10:05:03 3 DROP TABLE t1; CREATE TABLE t1 (a DATETIME(6), b INT DEFAULT MICROSECOND(a)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` datetime(6) DEFAULT NULL, + `b` int(11) DEFAULT MICROSECOND(a) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 INSERT INTO t1 (a) VALUES ('2009-12-31 23:59:59.000010'); SELECT * FROM t1; a b 2009-12-31 23:59:59.000010 10 DROP TABLE t1; CREATE TABLE t1 (a DATE, b INT DEFAULT YEAR(a)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` date DEFAULT NULL, + `b` int(11) DEFAULT YEAR(a) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 INSERT INTO t1 (a) VALUES ('1987-01-01'); SELECT * FROM t1; a b 1987-01-01 1987 DROP TABLE t1; CREATE TABLE t1 (a DATE, b INT DEFAULT MONTH(a)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` date DEFAULT NULL, + `b` int(11) DEFAULT MONTH(a) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 INSERT INTO t1 (a) VALUES ('1987-01-01'); SELECT * FROM t1; a b 1987-01-01 1 DROP TABLE t1; CREATE TABLE t1 (a DATE, b INT DEFAULT WEEK(a)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` date DEFAULT NULL, + `b` int(11) DEFAULT WEEK(a) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 INSERT INTO t1 (a) VALUES ('1987-02-01'); SELECT * FROM t1; a b 1987-02-01 5 DROP TABLE t1; CREATE TABLE t1 (a DATE, b INT DEFAULT YEARWEEK(a)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` date DEFAULT NULL, + `b` int(11) DEFAULT YEARWEEK(a) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 INSERT INTO t1 (a) VALUES ('2000-01-01'); SELECT * FROM t1; a b 2000-01-01 199952 DROP TABLE t1; CREATE TABLE t1 (a DATE, b INT DEFAULT QUARTER(a)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` date DEFAULT NULL, + `b` int(11) DEFAULT QUARTER(a) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 INSERT INTO t1 (a) VALUES ('2008-04-01'); SELECT * FROM t1; a b @@ -1529,42 +1921,87 @@ ERROR HY000: Function or expression 'dayname' is not allowed for 'DEFAULT' of co CREATE TABLE t1 (a DATE, b VARCHAR(30) DEFAULT MONTHNAME(a)); ERROR HY000: Function or expression 'monthname' is not allowed for 'DEFAULT' of column/constraint 'b' CREATE TABLE t1 (a DATE, b INT DEFAULT EXTRACT(YEAR FROM a)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` date DEFAULT NULL, + `b` int(11) DEFAULT EXTRACT(YEAR FROM a) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 INSERT INTO t1 (a) VALUES ('2009-07-02'); SELECT * FROM t1; a b 2009-07-02 2009 DROP TABLE t1; CREATE TABLE t1 (a DATETIME, b INT DEFAULT EXTRACT(YEAR_MONTH FROM a)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` datetime DEFAULT NULL, + `b` int(11) DEFAULT EXTRACT(YEAR_MONTH FROM a) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 INSERT INTO t1 (a) VALUES ('2009-07-02 01:02:03'); SELECT * FROM t1; a b 2009-07-02 01:02:03 200907 DROP TABLE t1; CREATE TABLE t1 (a DATETIME, b INT DEFAULT EXTRACT(DAY_MINUTE FROM a)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` datetime DEFAULT NULL, + `b` int(11) DEFAULT EXTRACT(DAY_MINUTE FROM a) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 INSERT INTO t1 (a) VALUES ('2009-07-02 01:02:03'); SELECT * FROM t1; a b 2009-07-02 01:02:03 20102 DROP TABLE t1; CREATE TABLE t1 (a DATETIME(6), b INT DEFAULT EXTRACT(MICROSECOND FROM a)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` datetime(6) DEFAULT NULL, + `b` int(11) DEFAULT EXTRACT(MICROSECOND FROM a) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 INSERT INTO t1 (a) VALUES ('2009-07-02 01:02:03.000123'); SELECT * FROM t1; a b 2009-07-02 01:02:03.000123 123 DROP TABLE t1; CREATE TABLE t1 (a DATE, b DATE, c INT DEFAULT TIMESTAMPDIFF(MONTH,a,b)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` date DEFAULT NULL, + `b` date DEFAULT NULL, + `c` int(11) DEFAULT TIMESTAMPDIFF(MONTH,a,b) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 INSERT INTO t1 (a,b) VALUES ('2003-02-01','2003-05-01'); SELECT * FROM t1; a b c 2003-02-01 2003-05-01 3 DROP TABLE t1; CREATE TABLE t1 (a DATE, b DATE, c INT DEFAULT TIMESTAMPDIFF(YEAR,a,b)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` date DEFAULT NULL, + `b` date DEFAULT NULL, + `c` int(11) DEFAULT TIMESTAMPDIFF(YEAR,a,b) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 INSERT INTO t1 (a,b) VALUES ('2002-05-01','2001-01-01'); SELECT * FROM t1; a b c 2002-05-01 2001-01-01 -1 DROP TABLE t1; CREATE TABLE t1 (a DATE, b DATETIME, c INT DEFAULT TIMESTAMPDIFF(MINUTE,a,b)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` date DEFAULT NULL, + `b` datetime DEFAULT NULL, + `c` int(11) DEFAULT TIMESTAMPDIFF(MINUTE,a,b) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 INSERT INTO t1 (a,b) VALUES ('2003-02-01','2003-05-01 12:05:55'); SELECT * FROM t1; a b c @@ -1574,12 +2011,26 @@ DROP TABLE t1; # Hybrid type functions # CREATE TABLE t1 (a INT, b INT, c INT DEFAULT COALESCE(a,b)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL, + `b` int(11) DEFAULT NULL, + `c` int(11) DEFAULT COALESCE(a,b) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 INSERT INTO t1 VALUES (NULL, 1, DEFAULT); SELECT * FROM t1; a b c NULL 1 1 DROP TABLE t1; CREATE TABLE t1 (a INT, b INT, c INT DEFAULT IFNULL(a,b)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL, + `b` int(11) DEFAULT NULL, + `c` int(11) DEFAULT IFNULL(a,b) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 INSERT INTO t1 VALUES (NULL, 2, DEFAULT); INSERT INTO t1 VALUES (1, 2, DEFAULT); SELECT * FROM t1; @@ -1588,6 +2039,13 @@ NULL 2 2 1 2 1 DROP TABLE t1; CREATE TABLE t1 (a INT, b INT, c INT DEFAULT NULLIF(a,b)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL, + `b` int(11) DEFAULT NULL, + `c` int(11) DEFAULT NULLIF(a,b) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 INSERT INTO t1 VALUES (1, 1, DEFAULT); INSERT INTO t1 VALUES (1, 2, DEFAULT); SELECT * FROM t1; @@ -1596,6 +2054,13 @@ a b c 1 2 1 DROP TABLE t1; CREATE TABLE t1 (a INT, b INT, c INT DEFAULT IF(a,b,2)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL, + `b` int(11) DEFAULT NULL, + `c` int(11) DEFAULT IF(a,b,2) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 INSERT INTO t1 VALUES (0, 1, DEFAULT); INSERT INTO t1 VALUES (1, 1, DEFAULT); SELECT * FROM t1; @@ -1604,6 +2069,13 @@ a b c 1 1 1 DROP TABLE t1; CREATE TABLE t1 (a INT, b INT, c INT DEFAULT CASE WHEN a THEN b ELSE 2 END); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL, + `b` int(11) DEFAULT NULL, + `c` int(11) DEFAULT (CASE WHEN a THEN b ELSE 2 END) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 INSERT INTO t1 VALUES (0, 1, DEFAULT); INSERT INTO t1 VALUES (1, 1, DEFAULT); SELECT * FROM t1; @@ -1618,18 +2090,38 @@ t1 CREATE TABLE `t1` ( `a` int(11) DEFAULT NULL, `b` int(11) DEFAULT (-a) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL, + `b` int(11) DEFAULT (-a) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 INSERT INTO t1 VALUES (10, DEFAULT); SELECT * FROM t1; a b 10 -10 DROP TABLE t1; CREATE TABLE t1 (a INT, b INT DEFAULT ABS(a)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL, + `b` int(11) DEFAULT ABS(a) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 INSERT INTO t1 VALUES (-10, DEFAULT); SELECT * FROM t1; a b -10 10 DROP TABLE t1; CREATE TABLE t1 (a DOUBLE, b INT DEFAULT CEILING(a), c INT DEFAULT FLOOR(a), d INT DEFAULT ROUND(a)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` double DEFAULT NULL, + `b` int(11) DEFAULT CEILING(a), + `c` int(11) DEFAULT FLOOR(a), + `d` int(11) DEFAULT ROUND(a) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 INSERT INTO t1 VALUES (1.5, DEFAULT, DEFAULT, DEFAULT); INSERT INTO t1 VALUES (-1.5, DEFAULT, DEFAULT, DEFAULT); SELECT * FROM t1; @@ -1638,6 +2130,14 @@ a b c d -1.5 -1 -2 -2 DROP TABLE t1; CREATE TABLE t1 (a INT, b INT, c INT DEFAULT (a+b), d INT DEFAULT (a-b)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL, + `b` int(11) DEFAULT NULL, + `c` int(11) DEFAULT (a+b), + `d` int(11) DEFAULT (a-b) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 INSERT INTO t1 VALUES (2, 1, DEFAULT, DEFAULT); SELECT * FROM t1; a b c d @@ -1653,6 +2153,15 @@ t1 CREATE TABLE `t1` ( `d` int(11) DEFAULT (a/b), `e` int(11) DEFAULT (a MOD b) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL, + `b` int(11) DEFAULT NULL, + `c` int(11) DEFAULT (a*b), + `d` int(11) DEFAULT (a/b), + `e` int(11) DEFAULT (a MOD b) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 INSERT INTO t1 VALUES (7, 3, DEFAULT, DEFAULT, DEFAULT); SELECT * FROM t1; a b c d e @@ -1660,6 +2169,12 @@ a b c d e DROP TABLE t1; SET time_zone='+00:00'; CREATE TABLE t1 (a DATETIME, b INT DEFAULT UNIX_TIMESTAMP(a)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` datetime DEFAULT NULL, + `b` int(11) DEFAULT UNIX_TIMESTAMP(a) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 INSERT INTO t1 VALUES ('2001-01-01 10:20:30', DEFAULT); SELECT * FROM t1; a b @@ -1667,12 +2182,26 @@ a b DROP TABLE t1; SET time_zone=DEFAULT; CREATE TABLE t1 (a TIME, b INT DEFAULT TIME_TO_SEC(a)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` time DEFAULT NULL, + `b` int(11) DEFAULT TIME_TO_SEC(a) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 INSERT INTO t1 VALUES ('22:23:00', DEFAULT); SELECT * FROM t1; a b 22:23:00 80580 DROP TABLE t1; CREATE TABLE t1 (a INT, b INT, c INT DEFAULT LEAST(a,b), d INT DEFAULT GREATEST(a,b)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL, + `b` int(11) DEFAULT NULL, + `c` int(11) DEFAULT LEAST(a,b), + `d` int(11) DEFAULT GREATEST(a,b) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 INSERT INTO t1 VALUES (0, 1, DEFAULT, DEFAULT); INSERT INTO t1 VALUES (1, 1, DEFAULT, DEFAULT); SELECT * FROM t1; @@ -1681,6 +2210,13 @@ a b c d 1 1 1 1 DROP TABLE t1; CREATE TABLE t1 (a INT, b INT, c INT DEFAULT LAST_VALUE(a,b)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL, + `b` int(11) DEFAULT NULL, + `c` int(11) DEFAULT LAST_VALUE(a,b) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 INSERT INTO t1 VALUES (1, 2, DEFAULT); SELECT * FROM t1; a b c @@ -1690,6 +2226,12 @@ DROP TABLE t1; # CAST # CREATE TABLE t1 (a VARCHAR(30), b DECIMAL(10,6) DEFAULT CAST(a AS DECIMAL(10,1))); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` varchar(30) DEFAULT NULL, + `b` decimal(10,6) DEFAULT (CAST(a AS DECIMAL(10,1))) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 INSERT INTO t1 (a) VALUES ('123.456'); SELECT * FROM t1; a b @@ -1698,6 +2240,13 @@ DROP TABLE t1; CREATE TABLE t1 (a DECIMAL(10,3), b VARCHAR(10) DEFAULT CAST(a AS CHAR(10)), c VARCHAR(10) DEFAULT CAST(a AS CHAR(4))); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` decimal(10,3) DEFAULT NULL, + `b` varchar(10) DEFAULT (CAST(a AS CHAR(10))), + `c` varchar(10) DEFAULT (CAST(a AS CHAR(4))) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 INSERT INTO t1 (a) VALUES (123.456); Warnings: Warning 1292 Truncated incorrect CHAR(4) value: '123.456' @@ -1706,6 +2255,12 @@ a b c 123.456 123.456 123. DROP TABLE t1; CREATE TABLE t1 (a INT, b INT UNSIGNED DEFAULT CAST(a AS UNSIGNED)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL, + `b` int(10) unsigned DEFAULT (CAST(a AS UNSIGNED)) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 INSERT INTO t1 (a) VALUES (-1); Warnings: Note 1105 Cast to unsigned converted negative integer to it's positive complement @@ -1715,6 +2270,12 @@ a b -1 4294967295 DROP TABLE t1; CREATE TABLE t1 (a BIGINT UNSIGNED, b BIGINT SIGNED DEFAULT CAST(a AS SIGNED)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` bigint(20) unsigned DEFAULT NULL, + `b` bigint(20) DEFAULT (CAST(a AS SIGNED)) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 INSERT INTO t1 (a) VALUES (0xFFFFFFFFFFFFFFFF); SELECT * FROM t1; a b @@ -1743,30 +2304,64 @@ DROP TABLE t1; # Bit functions # CREATE TABLE t1 (a INT, b INT DEFAULT BIT_COUNT(a)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL, + `b` int(11) DEFAULT BIT_COUNT(a) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 INSERT INTO t1 (a) VALUES (7); SELECT * FROM t1; a b 7 3 DROP TABLE t1; CREATE TABLE t1 (a INT, b INT, c INT DEFAULT (a|b)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL, + `b` int(11) DEFAULT NULL, + `c` int(11) DEFAULT (a|b) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 INSERT INTO t1 (a,b) VALUES (1,2); SELECT * FROM t1; a b c 1 2 3 DROP TABLE t1; CREATE TABLE t1 (a INT, b INT, c INT DEFAULT (a&b)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL, + `b` int(11) DEFAULT NULL, + `c` int(11) DEFAULT (a&b) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 INSERT INTO t1 (a,b) VALUES (5,4); SELECT * FROM t1; a b c 5 4 4 DROP TABLE t1; CREATE TABLE t1 (a INT, b INT, c INT DEFAULT (a^b)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL, + `b` int(11) DEFAULT NULL, + `c` int(11) DEFAULT (a^b) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 INSERT INTO t1 (a,b) VALUES (11,3); SELECT * FROM t1; a b c 11 3 8 DROP TABLE t1; CREATE TABLE t1 (a INT, b INT, c INT DEFAULT (a&~b)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL, + `b` int(11) DEFAULT NULL, + `c` int(11) DEFAULT (a&~b) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 INSERT INTO t1 (a,b) VALUES (5,1); SELECT * FROM t1; a b c @@ -1790,138 +2385,290 @@ DROP TABLE t1; # String functions # CREATE TABLE t1 (a VARCHAR(10), b VARCHAR(20) DEFAULT REVERSE(a)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` varchar(10) DEFAULT NULL, + `b` varchar(20) DEFAULT REVERSE(a) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 INSERT INTO t1 (a) VALUES ('abcd'); SELECT * FROM t1; a b abcd dcba DROP TABLE t1; CREATE TABLE t1 (a VARCHAR(10), b VARCHAR(10) DEFAULT UPPER(a), c VARCHAR(10) DEFAULT LOWER(a)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` varchar(10) DEFAULT NULL, + `b` varchar(10) DEFAULT UPPER(a), + `c` varchar(10) DEFAULT LOWER(a) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 INSERT INTO t1 (a) VALUES ('ABcd'); SELECT * FROM t1; a b c ABcd ABCD abcd DROP TABLE t1; CREATE TABLE t1 (a VARCHAR(10), b VARCHAR(10) DEFAULT LEFT(a,1), c VARCHAR(10) DEFAULT RIGHT(a,1), d VARCHAR(10) DEFAULT SUBSTR(a,2,2)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` varchar(10) DEFAULT NULL, + `b` varchar(10) DEFAULT LEFT(a,1), + `c` varchar(10) DEFAULT RIGHT(a,1), + `d` varchar(10) DEFAULT SUBSTR(a,2,2) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 INSERT INTO t1 (a) VALUES ('abcd'); SELECT * FROM t1; a b c d abcd a d bc DROP TABLE t1; CREATE TABLE t1 (a VARCHAR(20), b VARCHAR(20) DEFAULT SUBSTRING_INDEX(a,'.',2)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` varchar(20) DEFAULT NULL, + `b` varchar(20) DEFAULT SUBSTRING_INDEX(a,'.',2) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 INSERT INTO t1 (a) VALUES ('www.mariadb.org'); SELECT * FROM t1; a b www.mariadb.org www.mariadb DROP TABLE t1; CREATE TABLE t1 (a VARCHAR(10), b VARCHAR(10), c VARCHAR(20) DEFAULT CONCAT(a,b)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` varchar(10) DEFAULT NULL, + `b` varchar(10) DEFAULT NULL, + `c` varchar(20) DEFAULT CONCAT(a,b) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 INSERT INTO t1 (a,b) VALUES ('a','b'); SELECT * FROM t1; a b c a b ab DROP TABLE t1; CREATE TABLE t1 (a VARCHAR(10), b VARCHAR(10), c VARCHAR(20) DEFAULT CONCAT_WS(',',a,b)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` varchar(10) DEFAULT NULL, + `b` varchar(10) DEFAULT NULL, + `c` varchar(20) DEFAULT CONCAT_WS(',',a,b) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 INSERT INTO t1 (a,b) VALUES ('a','b'); SELECT * FROM t1; a b c a b a,b DROP TABLE t1; CREATE TABLE t1 (a VARCHAR(10), b VARCHAR(10) DEFAULT REPLACE(a,'a','A')); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` varchar(10) DEFAULT NULL, + `b` varchar(10) DEFAULT REPLACE(a,'a','A') +) ENGINE=MyISAM DEFAULT CHARSET=latin1 INSERT INTO t1 (a) VALUES ('abc'); SELECT * FROM t1; a b abc Abc DROP TABLE t1; CREATE TABLE t1 (a VARCHAR(10), b VARCHAR(10) DEFAULT REGEXP_REPLACE(a,'[0-9]','.')); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` varchar(10) DEFAULT NULL, + `b` varchar(10) DEFAULT REGEXP_REPLACE(a,'[0-9]','.') +) ENGINE=MyISAM DEFAULT CHARSET=latin1 INSERT INTO t1 (a) VALUES ('a1b2c'); SELECT * FROM t1; a b a1b2c a.b.c DROP TABLE t1; CREATE TABLE t1 (a VARCHAR(10), b VARCHAR(10) DEFAULT REGEXP_SUBSTR(a,'[0-9]+')); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` varchar(10) DEFAULT NULL, + `b` varchar(10) DEFAULT REGEXP_SUBSTR(a,'[0-9]+') +) ENGINE=MyISAM DEFAULT CHARSET=latin1 INSERT INTO t1 (a) VALUES ('ab12cd'); SELECT * FROM t1; a b ab12cd 12 DROP TABLE t1; CREATE TABLE t1 (a VARCHAR(20), b VARCHAR(20) DEFAULT SOUNDEX(a)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` varchar(20) DEFAULT NULL, + `b` varchar(20) DEFAULT SOUNDEX(a) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 INSERT INTO t1 (a) VALUES ('tester'); SELECT * FROM t1; a b tester T236 DROP TABLE t1; CREATE TABLE t1 (a VARCHAR(20), b VARCHAR(20) DEFAULT QUOTE(a)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` varchar(20) DEFAULT NULL, + `b` varchar(20) DEFAULT QUOTE(a) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 INSERT INTO t1 (a) VALUES ('a\'b'); SELECT * FROM t1; a b a'b 'a\'b' DROP TABLE t1; CREATE TABLE t1 (a VARCHAR(10), b VARCHAR(10) DEFAULT LPAD(a,10,'.'), c VARCHAR(10) DEFAULT RPAD(a,10,'.')); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` varchar(10) DEFAULT NULL, + `b` varchar(10) DEFAULT LPAD(a,10,'.'), + `c` varchar(10) DEFAULT RPAD(a,10,'.') +) ENGINE=MyISAM DEFAULT CHARSET=latin1 INSERT INTO t1 (a) VALUES ('ab'); SELECT * FROM t1; a b c ab ........ab ab........ DROP TABLE t1; CREATE TABLE t1 (a VARCHAR(10), b VARCHAR(10) DEFAULT LTRIM(a), c VARCHAR(10) DEFAULT RTRIM(a)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` varchar(10) DEFAULT NULL, + `b` varchar(10) DEFAULT LTRIM(a), + `c` varchar(10) DEFAULT RTRIM(a) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 INSERT INTO t1 (a) VALUES (' ab '); SELECT a, HEX(b), HEX(c) FROM t1; a HEX(b) HEX(c) ab 616220 206162 DROP TABLE t1; CREATE TABLE t1 (a VARCHAR(10), b VARCHAR(10) DEFAULT TRIM(BOTH 'a' FROM a)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` varchar(10) DEFAULT NULL, + `b` varchar(10) DEFAULT TRIM(BOTH 'a' FROM a) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 INSERT INTO t1 (a) VALUES ('abba'); SELECT a, b FROM t1; a b abba bb DROP TABLE t1; CREATE TABLE t1 (a INT, b VARCHAR(10) DEFAULT SPACE(a)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL, + `b` varchar(10) DEFAULT SPACE(a) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 INSERT INTO t1 (a) VALUES (3); SELECT a, HEX(b) FROM t1; a HEX(b) 3 202020 DROP TABLE t1; CREATE TABLE t1 (a INT, b VARCHAR(10), c VARCHAR(10) DEFAULT REPEAT(b,a)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL, + `b` varchar(10) DEFAULT NULL, + `c` varchar(10) DEFAULT REPEAT(b,a) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 INSERT INTO t1 (a,b) VALUES (3,'x'); SELECT a, b, c FROM t1; a b c 3 x xxx DROP TABLE t1; CREATE TABLE t1 (str VARCHAR(10), pos INT, len INT, newstr VARCHAR(10), result VARCHAR(10) DEFAULT INSERT(str,pos,len,newstr)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `str` varchar(10) DEFAULT NULL, + `pos` int(11) DEFAULT NULL, + `len` int(11) DEFAULT NULL, + `newstr` varchar(10) DEFAULT NULL, + `result` varchar(10) DEFAULT INSERT(str,pos,len,newstr) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 INSERT INTO t1 (str,pos,len,newstr) VALUES ('Quadratic', 3, 4, 'What'); SELECT * FROM t1; str pos len newstr result Quadratic 3 4 What QuWhattic DROP TABLE t1; CREATE TABLE t1 (n INT, res VARCHAR(10) DEFAULT ELT(n,'ej', 'Heja', 'hej', 'foo')); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `n` int(11) DEFAULT NULL, + `res` varchar(10) DEFAULT ELT(n,'ej', 'Heja', 'hej', 'foo') +) ENGINE=MyISAM DEFAULT CHARSET=latin1 INSERT INTO t1 (n) VALUES (1); SELECT * FROM t1; n res 1 ej DROP TABLE t1; CREATE TABLE t1 (bits INT, res VARCHAR(10) DEFAULT MAKE_SET(bits,'a','b','c','d')); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `bits` int(11) DEFAULT NULL, + `res` varchar(10) DEFAULT MAKE_SET(bits,'a','b','c','d') +) ENGINE=MyISAM DEFAULT CHARSET=latin1 INSERT INTO t1 (bits) VALUES (1|4); SELECT * FROM t1; bits res 5 a,c DROP TABLE t1; CREATE TABLE t1 (a INT, b VARCHAR(10) DEFAULT CHAR(a)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL, + `b` varchar(10) DEFAULT CHAR(a) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 INSERT INTO t1 (a) VALUES (77); SELECT * FROM t1; a b 77 M DROP TABLE t1; CREATE TABLE t1 (a INT, b VARCHAR(10) DEFAULT CONV(a,10,16)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL, + `b` varchar(10) DEFAULT CONV(a,10,16) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 INSERT INTO t1 (a) VALUES (64); SELECT * FROM t1; a b 64 40 DROP TABLE t1; CREATE TABLE t1 (a INT, b INT, c VARCHAR(30) DEFAULT FORMAT(a,b)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL, + `b` int(11) DEFAULT NULL, + `c` varchar(30) DEFAULT FORMAT(a,b) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 INSERT INTO t1 (a,b) VALUES (10000,3); SELECT * FROM t1; a b c 10000 3 10,000.000 DROP TABLE t1; CREATE TABLE t1 (a INT, b INT, l VARCHAR(10), c VARCHAR(30) DEFAULT FORMAT(a,b,l)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL, + `b` int(11) DEFAULT NULL, + `l` varchar(10) DEFAULT NULL, + `c` varchar(30) DEFAULT FORMAT(a,b,l) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 INSERT INTO t1 (a,b,l) VALUES (10000,2,'no_NO'),(10000,2,'ru_RU'),(10000,2,'ar_BH'); SELECT * FROM t1; a b l c @@ -1930,6 +2677,12 @@ a b l c 10000 2 ar_BH 10,000.00 DROP TABLE t1; CREATE TABLE t1 (a VARCHAR(10), b VARCHAR(20) DEFAULT GET_FORMAT(DATE,a)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` varchar(10) DEFAULT NULL, + `b` varchar(20) DEFAULT GET_FORMAT(DATE,a) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 INSERT INTO t1 (a) VALUES ('EUR'),('USA'),('JIS'),('ISO'),('INTERNAL'); SELECT * FROM t1; a b @@ -1947,6 +2700,16 @@ v_separator VARCHAR(10), number_of_bits INT, x VARCHAR(30) DEFAULT EXPORT_SET(bits, v_on, v_off, v_separator, number_of_bits) ); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `bits` int(11) DEFAULT NULL, + `v_on` varchar(10) DEFAULT NULL, + `v_off` varchar(10) DEFAULT NULL, + `v_separator` varchar(10) DEFAULT NULL, + `number_of_bits` int(11) DEFAULT NULL, + `x` varchar(30) DEFAULT EXPORT_SET(bits, v_on, v_off, v_separator, number_of_bits) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 INSERT INTO t1 VALUES (0x50006,'Y','N','',64,DEFAULT); Warnings: Warning 1265 Data truncated for column 'x' at row 1 @@ -1974,6 +2737,13 @@ NULL NULL 1 0 DROP TABLE t1; CREATE TABLE t1 (a INT, b INT, x INT DEFAULT (a XOR b)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL, + `b` int(11) DEFAULT NULL, + `x` int(11) DEFAULT (a XOR b) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 INSERT INTO t1 (a,b) VALUES (0,0),(0,1),(1,0),(1,1); SELECT * FROM t1; a b x @@ -1998,6 +2768,13 @@ NULL 0 1 1 1 0 DROP TABLE t1; CREATE TABLE t1 (a INT, b INT DEFAULT (a IS FALSE), c INT DEFAULT (a IS NOT FALSE)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL, + `b` int(11) DEFAULT (a IS FALSE), + `c` int(11) DEFAULT (a IS NOT FALSE) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 INSERT INTO t1 (a) VALUES (NULL),(0),(1); SELECT * FROM t1; a b c @@ -2006,6 +2783,13 @@ NULL 0 1 1 0 1 DROP TABLE t1; CREATE TABLE t1 (a INT, b INT DEFAULT (a IS NULL), c INT DEFAULT (a IS NOT NULL)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL, + `b` int(11) DEFAULT (a IS NULL), + `c` int(11) DEFAULT (a IS NOT NULL) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 INSERT INTO t1 (a) VALUES (NULL),(0),(1); SELECT * FROM t1; a b c @@ -2014,6 +2798,13 @@ NULL 1 0 1 0 1 DROP TABLE t1; CREATE TABLE t1 (a INT, b INT DEFAULT (a IS UNKNOWN), c INT DEFAULT (a IS NOT UNKNOWN)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL, + `b` int(11) DEFAULT (a IS UNKNOWN), + `c` int(11) DEFAULT (a IS NOT UNKNOWN) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 INSERT INTO t1 (a) VALUES (NULL),(0),(1); SELECT * FROM t1; a b c @@ -2026,6 +2817,18 @@ eq INT DEFAULT (a=0), equal INT DEFAULT (a<=>0), ne INT DEFAULT (a<>0), lt INT DEFAULT (a<0), le INT DEFAULT (a<=0), gt INT DEFAULT (a>0), ge INT DEFAULT (a>=0)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL, + `eq` int(11) DEFAULT (a=0), + `equal` int(11) DEFAULT (a<=>0), + `ne` int(11) DEFAULT (a<>0), + `lt` int(11) DEFAULT (a<0), + `le` int(11) DEFAULT (a<=0), + `gt` int(11) DEFAULT (a>0), + `ge` int(11) DEFAULT (a>=0) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 INSERT INTO t1 (a) VALUES (NULL),(-1),(0),(1); SELECT * FROM t1; a eq equal ne lt le gt ge @@ -2035,6 +2838,12 @@ NULL NULL 0 NULL NULL NULL NULL NULL 1 0 0 1 0 0 1 1 DROP TABLE t1; CREATE TABLE t1 (a VARCHAR(10), b INT DEFAULT (a LIKE 'a%')); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` varchar(10) DEFAULT NULL, + `b` int(11) DEFAULT (a LIKE 'a%') +) ENGINE=MyISAM DEFAULT CHARSET=latin1 INSERT INTO t1 (a) VALUES ('AAA'),('aaa'),('bbb'); SELECT * FROM t1; a b @@ -2043,6 +2852,12 @@ aaa 1 bbb 0 DROP TABLE t1; CREATE TABLE t1 (a VARCHAR(10), b INT DEFAULT (a RLIKE 'a$')); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` varchar(10) DEFAULT NULL, + `b` int(11) DEFAULT (a RLIKE 'a$') +) ENGINE=MyISAM DEFAULT CHARSET=latin1 INSERT INTO t1 (a) VALUES ('AAA'),('aaa'),('bbb'); SELECT * FROM t1; a b @@ -2051,6 +2866,12 @@ aaa 1 bbb 0 DROP TABLE t1; CREATE TABLE t1 (a VARCHAR(10), b INT DEFAULT (a IN ('aaa','bbb'))); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` varchar(10) DEFAULT NULL, + `b` int(11) DEFAULT (a IN ('aaa','bbb')) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 INSERT INTO t1 (a) VALUES ('AAA'),('aaa'),('bbb'),('ccc'); SELECT * FROM t1; a b @@ -2060,6 +2881,12 @@ bbb 1 ccc 0 DROP TABLE t1; CREATE TABLE t1 (a VARCHAR(10), b INT DEFAULT (a NOT IN ('aaa','bbb'))); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` varchar(10) DEFAULT NULL, + `b` int(11) DEFAULT (a NOT IN ('aaa','bbb')) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 INSERT INTO t1 (a) VALUES ('AAA'),('aaa'),('bbb'),('ccc'); SELECT * FROM t1; a b @@ -2069,6 +2896,12 @@ bbb 0 ccc 1 DROP TABLE t1; CREATE TABLE t1 (a VARCHAR(10), b INT DEFAULT (a BETWEEN 'aaa' AND 'bbb')); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` varchar(10) DEFAULT NULL, + `b` int(11) DEFAULT (a BETWEEN 'aaa' AND 'bbb') +) ENGINE=MyISAM DEFAULT CHARSET=latin1 INSERT INTO t1 (a) VALUES ('AAA'),('aaa'),('bbb'),('ccc'); SELECT * FROM t1; a b @@ -2078,6 +2911,12 @@ bbb 1 ccc 0 DROP TABLE t1; CREATE TABLE t1 (a VARCHAR(10), b INT DEFAULT (a NOT BETWEEN 'aaa' AND 'bbb')); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` varchar(10) DEFAULT NULL, + `b` int(11) DEFAULT (a NOT BETWEEN 'aaa' AND 'bbb') +) ENGINE=MyISAM DEFAULT CHARSET=latin1 INSERT INTO t1 (a) VALUES ('AAA'),('aaa'),('bbb'),('ccc'); SELECT * FROM t1; a b @@ -2087,6 +2926,11 @@ bbb 0 ccc 1 DROP TABLE t1; CREATE TABLE t1 (a TEXT DEFAULT UUID()); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` text DEFAULT UUID() +) ENGINE=MyISAM DEFAULT CHARSET=latin1 INSERT INTO t1 VALUES (); SELECT LENGTH(a)>0 FROM t1; LENGTH(a)>0 @@ -2096,6 +2940,12 @@ DROP TABLE t1; # Numeric result functions with string input # CREATE TABLE t1 (a VARCHAR(10), b INT DEFAULT STRCMP(a,'b')); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` varchar(10) DEFAULT NULL, + `b` int(11) DEFAULT STRCMP(a,'b') +) ENGINE=MyISAM DEFAULT CHARSET=latin1 INSERT INTO t1 (a) VALUES ('A'),('a'),('B'),('b'),('C'),('c'); SELECT * FROM t1; a b @@ -2107,6 +2957,14 @@ C 1 c 1 DROP TABLE t1; CREATE TABLE t1 (a VARCHAR(10), b INT DEFAULT LENGTH(a), c INT DEFAULT CHAR_LENGTH(a), d INT DEFAULT BIT_LENGTH(a)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` varchar(10) DEFAULT NULL, + `b` int(11) DEFAULT LENGTH(a), + `c` int(11) DEFAULT CHAR_LENGTH(a), + `d` int(11) DEFAULT BIT_LENGTH(a) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 INSERT INTO t1 (a) VALUES ('a'),('aa'),('aaa'); SELECT * FROM t1; a b c d @@ -2115,6 +2973,12 @@ aa 2 2 16 aaa 3 3 24 DROP TABLE t1; CREATE TABLE t1 (a VARCHAR(10), b INT DEFAULT LOCATE('a',a)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` varchar(10) DEFAULT NULL, + `b` int(11) DEFAULT LOCATE('a',a) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 INSERT INTO t1 (a) VALUES ('xa'),('xxa'),('xxxa'); SELECT * FROM t1; a b @@ -2123,6 +2987,12 @@ xxa 3 xxxa 4 DROP TABLE t1; CREATE TABLE t1 (a VARCHAR(10), b INT DEFAULT REGEXP_INSTR(a, 'a')); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` varchar(10) DEFAULT NULL, + `b` int(11) DEFAULT REGEXP_INSTR(a, 'a') +) ENGINE=MyISAM DEFAULT CHARSET=latin1 INSERT INTO t1 (a) VALUES ('xa'),('xxa'),('xxxa'); SELECT * FROM t1; a b @@ -2140,12 +3010,24 @@ b INT DEFAULT LAST_INSERT_ID() ); ERROR HY000: Function or expression 'last_insert_id' is not allowed for 'DEFAULT' of column/constraint 'b' CREATE TABLE t1 (a INT DEFAULT CONNECTION_ID()); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT CONNECTION_ID() +) ENGINE=MyISAM DEFAULT CHARSET=latin1 INSERT INTO t1 VALUES(); SELECT a>0 FROM t1; a>0 1 DROP TABLE t1; CREATE TABLE t1 (a VARCHAR(10), b INT DEFAULT COERCIBILITY(a), c INT DEFAULT COERCIBILITY(b)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` varchar(10) DEFAULT NULL, + `b` int(11) DEFAULT COERCIBILITY(a), + `c` int(11) DEFAULT COERCIBILITY(b) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 INSERT INTO t1 (a) VALUES ('test'); SELECT * FROM t1; a b c @@ -2159,6 +3041,13 @@ a VARCHAR(10) CHARACTER SET latin1, b VARCHAR(20) DEFAULT CHARSET(a), c VARCHAR(20) DEFAULT COLLATION(a) ); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` varchar(10) DEFAULT NULL, + `b` varchar(20) DEFAULT CHARSET(a), + `c` varchar(20) DEFAULT COLLATION(a) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 INSERT INTO t1 (a) VALUES ('test'); SELECT * FROM t1; a b c @@ -2168,36 +3057,76 @@ DROP TABLE t1; # Hash, compression, encode/decode # CREATE TABLE t1 (a VARCHAR(10), b BIGINT DEFAULT CRC32(a), c TEXT DEFAULT MD5(a)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` varchar(10) DEFAULT NULL, + `b` bigint(20) DEFAULT CRC32(a), + `c` text DEFAULT MD5(a) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 INSERT INTO t1 (a) VALUES ('a'); SELECT * FROM t1; a b c a 3904355907 0cc175b9c0f1b6a831c399e269772661 DROP TABLE t1; CREATE TABLE t1 (a VARCHAR(10), b TEXT DEFAULT TO_BASE64(a), c TEXT DEFAULT FROM_BASE64(b)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` varchar(10) DEFAULT NULL, + `b` text DEFAULT TO_BASE64(a), + `c` text DEFAULT FROM_BASE64(b) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 INSERT INTO t1 (a) VALUES ('aaaabbbb'); SELECT * FROM t1; a b c aaaabbbb YWFhYWJiYmI= aaaabbbb DROP TABLE t1; CREATE TABLE t1 (a VARCHAR(10), b TEXT DEFAULT HEX(a), c TEXT DEFAULT UNHEX(b)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` varchar(10) DEFAULT NULL, + `b` text DEFAULT HEX(a), + `c` text DEFAULT UNHEX(b) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 INSERT INTO t1 (a) VALUES ('aaaabbbb'); SELECT * FROM t1; a b c aaaabbbb 6161616162626262 aaaabbbb DROP TABLE t1; CREATE TABLE t1 (a VARCHAR(10), b TEXT DEFAULT ENCODE(a,'test'), c TEXT DEFAULT DECODE(b,'test')); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` varchar(10) DEFAULT NULL, + `b` text DEFAULT ENCODE(a,'test'), + `c` text DEFAULT DECODE(b,'test') +) ENGINE=MyISAM DEFAULT CHARSET=latin1 INSERT INTO t1 (a) VALUES ('aaaabbbb'); SELECT a, HEX(b), c FROM t1; a HEX(b) c aaaabbbb 059AEEE33E4AF848 aaaabbbb DROP TABLE t1; CREATE TABLE t1 (a VARCHAR(30), b TEXT DEFAULT PASSWORD(a)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` varchar(30) DEFAULT NULL, + `b` text DEFAULT PASSWORD(a) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 INSERT INTO t1 (a) VALUES ('notagoodpwd'); SELECT * FROM t1; a b notagoodpwd *3A70EE9FC6594F88CE9E959CD51C5A1C002DC937 DROP TABLE t1; CREATE TABLE t1 (a VARCHAR(10), b VARCHAR(30) DEFAULT ENCRYPT(a,123)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` varchar(10) DEFAULT NULL, + `b` varchar(30) DEFAULT ENCRYPT(a,123) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 INSERT INTO t1 (a) VALUES ('hello'); SELECT * FROM t1; a b @@ -2208,6 +3137,13 @@ a VARCHAR(30), b BLOB DEFAULT AES_ENCRYPT(a, 'passwd'), c TEXT DEFAULT AES_DECRYPT(b, 'passwd') ); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` varchar(30) DEFAULT NULL, + `b` blob DEFAULT AES_ENCRYPT(a, 'passwd'), + `c` text DEFAULT AES_DECRYPT(b, 'passwd') +) ENGINE=MyISAM DEFAULT CHARSET=latin1 INSERT INTO t1 (a) VALUES ('test'); SELECT c FROM t1; c @@ -2219,4 +3155,9 @@ DROP TABLE t1; CREATE TABLE t1 (a VARCHAR(20) CHARACTER SET latin1 DEFAULT CONCAT('ö')) CHARACTER SET koi8r COLLATE koi8r_bin; ERROR 22007: Encountered illegal value 'ö' when converting to koi8r CREATE OR REPLACE TABLE t1 (a char(2) default concat('A') COLLATE utf8mb4_unicode_ci); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` char(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT concat('A') +) ENGINE=MyISAM DEFAULT CHARSET=latin1 DROP TABLE t1; diff --git a/mysql-test/r/dyncol.result b/mysql-test/r/dyncol.result index 3a0abfdc733..88b2afb2d70 100644 --- a/mysql-test/r/dyncol.result +++ b/mysql-test/r/dyncol.result @@ -1895,6 +1895,22 @@ dyncol2_check INT DEFAULT COLUMN_CHECK(dyncol2), dyncol1_list TEXT DEFAULT COLUMN_LIST(dyncol1), dyncol1_json TEXT DEFAULT COLUMN_JSON(dyncol1) ); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `name` varchar(10) DEFAULT NULL, + `value` varchar(10) DEFAULT NULL, + `dyncol0` blob DEFAULT COLUMN_CREATE(name, value), + `value_dyncol0_name0` varchar(10) DEFAULT (COLUMN_GET(dyncol0, 'name0' AS CHAR)), + `dyncol1` blob DEFAULT COLUMN_ADD(dyncol0, 'name1', 'value1'), + `value_dyncol1_name1` varchar(10) DEFAULT (COLUMN_GET(dyncol1, 'name1' AS CHAR)), + `dyncol2` blob DEFAULT COLUMN_DELETE(dyncol1, 'name1'), + `dyncol2_exists_name0` int(11) DEFAULT COLUMN_EXISTS(dyncol2, 'name0'), + `dyncol2_exists_name1` int(11) DEFAULT COLUMN_EXISTS(dyncol2, 'name1'), + `dyncol2_check` int(11) DEFAULT COLUMN_CHECK(dyncol2), + `dyncol1_list` text DEFAULT COLUMN_LIST(dyncol1), + `dyncol1_json` text DEFAULT COLUMN_JSON(dyncol1) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 INSERT INTO t1 (name,value) VALUES ('name0', 'value0'); SELECT value_dyncol0_name0, value_dyncol1_name1 FROM t1; value_dyncol0_name0 value_dyncol1_name1 diff --git a/mysql-test/r/func_compress.result b/mysql-test/r/func_compress.result index 7f5208c67f6..9ef7f13487f 100644 --- a/mysql-test/r/func_compress.result +++ b/mysql-test/r/func_compress.result @@ -174,6 +174,14 @@ set global max_allowed_packet=default; # MDEV-10134 Add full support for DEFAULT # CREATE TABLE t1 (a TEXT, b BLOB DEFAULT COMPRESS(a), bl INT DEFAULT UNCOMPRESSED_LENGTH(b), a1 TEXT DEFAULT UNCOMPRESS(b)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` text DEFAULT NULL, + `b` blob DEFAULT COMPRESS(a), + `bl` int(11) DEFAULT UNCOMPRESSED_LENGTH(b), + `a1` text DEFAULT UNCOMPRESS(b) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 INSERT INTO t1 (a) VALUES (REPEAT('a',100)); SELECT bl, a1 FROM t1; bl a1 diff --git a/mysql-test/r/func_digest.result b/mysql-test/r/func_digest.result index 6d1fe17c0da..31a32da72ed 100644 --- a/mysql-test/r/func_digest.result +++ b/mysql-test/r/func_digest.result @@ -1437,6 +1437,13 @@ a VARCHAR(30), b TEXT DEFAULT SHA(a), c TEXT DEFAULT SHA2(a,224) ); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` varchar(30) DEFAULT NULL, + `b` text DEFAULT SHA(a), + `c` text DEFAULT SHA2(a,224) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 INSERT INTO t1 (a) VALUES ('abc'); SELECT * FROM t1; a b c diff --git a/mysql-test/r/func_encrypt.result b/mysql-test/r/func_encrypt.result index f3a268d8e5f..34dff598452 100644 --- a/mysql-test/r/func_encrypt.result +++ b/mysql-test/r/func_encrypt.result @@ -219,6 +219,13 @@ a VARCHAR(30), b BLOB DEFAULT DES_ENCRYPT(a, 'passwd'), c TEXT DEFAULT DES_DECRYPT(b, 'passwd') ); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` varchar(30) DEFAULT NULL, + `b` blob DEFAULT DES_ENCRYPT(a, 'passwd'), + `c` text DEFAULT DES_DECRYPT(b, 'passwd') +) ENGINE=MyISAM DEFAULT CHARSET=latin1 INSERT INTO t1 (a) VALUES ('test'); SELECT c FROM t1; c diff --git a/mysql-test/r/func_misc.result b/mysql-test/r/func_misc.result index 25e9c869793..4e38754788c 100644 --- a/mysql-test/r/func_misc.result +++ b/mysql-test/r/func_misc.result @@ -1446,6 +1446,15 @@ a1 VARCHAR(30) DEFAULT INET_NTOA(b), c INT DEFAULT IS_IPV4(a), d INT DEFAULT IS_IPV6(a) ); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` varchar(30) DEFAULT NULL, + `b` bigint(20) DEFAULT INET_ATON(a), + `a1` varchar(30) DEFAULT INET_NTOA(b), + `c` int(11) DEFAULT IS_IPV4(a), + `d` int(11) DEFAULT IS_IPV6(a) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 INSERT INTO t1 (a) VALUES ('192.168.001.001'),('::1'),('xxx'); SELECT * FROM t1; a b a1 c d @@ -1460,6 +1469,15 @@ str1 VARCHAR(128) DEFAULT INET6_NTOA(addr), b INT DEFAULT IS_IPV4_COMPAT(addr), c INT DEFAULT IS_IPV4_MAPPED(addr) ); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `str` varchar(128) DEFAULT NULL, + `addr` varbinary(16) DEFAULT INET6_ATON(str), + `str1` varchar(128) DEFAULT INET6_NTOA(addr), + `b` int(11) DEFAULT IS_IPV4_COMPAT(addr), + `c` int(11) DEFAULT IS_IPV4_MAPPED(addr) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 INSERT INTO t1 (str) VALUES ('::FFFF:192.168.0.1'),('::10.0.5.9'); SELECT str, str1, b,c FROM t1; str str1 b c diff --git a/mysql-test/r/func_weight_string.result b/mysql-test/r/func_weight_string.result index 585c5d7cf8d..0f52e793843 100644 --- a/mysql-test/r/func_weight_string.result +++ b/mysql-test/r/func_weight_string.result @@ -151,6 +151,12 @@ DROP TABLE t1; # MDEV-10134 Add full support for DEFAULT # CREATE TABLE t1 (a VARCHAR(10) CHARACTER SET latin1, b VARBINARY(10) DEFAULT WEIGHT_STRING(a AS CHAR(10))); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` varchar(10) DEFAULT NULL, + `b` varbinary(10) DEFAULT WEIGHT_STRING(a AS CHAR(10)) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 INSERT INTO t1 (a) VALUES ('a'); SELECT a, HEX(b) FROM t1; a HEX(b) diff --git a/mysql-test/r/gis.result b/mysql-test/r/gis.result index 09c844e1a21..ee65077fbc2 100644 --- a/mysql-test/r/gis.result +++ b/mysql-test/r/gis.result @@ -1833,177 +1833,358 @@ DROP TABLE t1,t2; # Start of 10.2 tests # # -# MDEV-7563 Support CHECK constraint +# MDEV-10134 Add full support for DEFAULT # CREATE TABLE t1 (a POINT, x DOUBLE DEFAULT x(a), y DOUBLE DEFAULT y(a)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` point DEFAULT NULL, + `x` double DEFAULT x(a), + `y` double DEFAULT y(a) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 INSERT INTO t1 (a) VALUES (Point(1,2)); SELECT x,y FROM t1; x y 1 2 DROP TABLE t1; CREATE TABLE t1 (g GEOMETRY, area DOUBLE DEFAULT ST_AREA(g)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `g` geometry DEFAULT NULL, + `area` double DEFAULT ST_AREA(g) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 INSERT INTO t1 (g) VALUES (GeomFromText('POLYGON((0 0,20 0,20 20,0 20,0 0))')); SELECT area FROM t1; area 400 DROP TABLE t1; CREATE TABLE t1 (g GEOMETRY, length DOUBLE DEFAULT ST_LENGTH(g)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `g` geometry DEFAULT NULL, + `length` double DEFAULT ST_LENGTH(g) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 INSERT INTO t1 (g) VALUES (GeomFromText('LINESTRING(0 0,20 0,20 20,0 20,0 0)')); SELECT length FROM t1; length 80 DROP TABLE t1; CREATE TABLE t1 (g POINT, distance DOUBLE DEFAULT ST_DISTANCE(g, POINT(0,0))); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `g` point DEFAULT NULL, + `distance` double DEFAULT ST_DISTANCE(g, POINT(0,0)) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 INSERT INTO t1 (g) VALUES (Point(1,0)); SELECT distance FROM t1; distance 1 DROP TABLE t1; CREATE TABLE t1 (a TEXT, g GEOMETRY DEFAULT GeomFromText(a)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` text DEFAULT NULL, + `g` geometry DEFAULT GeomFromText(a) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 INSERT INTO t1 (a) VALUES ('point(1 1)'); SELECT AsText(g) FROM t1; AsText(g) POINT(1 1) DROP TABLE t1; CREATE TABLE t1 (x INT, y INT, g GEOMETRY DEFAULT POINT(x,y)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `x` int(11) DEFAULT NULL, + `y` int(11) DEFAULT NULL, + `g` geometry DEFAULT POINT(x,y) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 INSERT INTO t1 (x,y) VALUES (10,20); SELECT AsText(g) FROM t1; AsText(g) POINT(10 20) DROP TABLE t1; CREATE TABLE t1 (a GEOMETRY, b GEOMETRY DEFAULT PointN(a,2)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` geometry DEFAULT NULL, + `b` geometry DEFAULT PointN(a,2) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 INSERT INTO t1 (a) VALUES (GeomFromText('LineString(1 1,2 2,3 3)')); SELECT AsText(b) FROM t1; AsText(b) POINT(2 2) DROP TABLE t1; CREATE TABLE t1 (a GEOMETRY, b GEOMETRY DEFAULT StartPoint(a)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` geometry DEFAULT NULL, + `b` geometry DEFAULT StartPoint(a) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 INSERT INTO t1 (a) VALUES (GeomFromText('LineString(1 1,2 2,3 3)')); SELECT AsText(b) FROM t1; AsText(b) POINT(1 1) DROP TABLE t1; CREATE TABLE t1 (a GEOMETRY, b GEOMETRY, c GEOMETRY DEFAULT GeometryCollection(a,b)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` geometry DEFAULT NULL, + `b` geometry DEFAULT NULL, + `c` geometry DEFAULT GeometryCollection(a,b) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 INSERT INTO t1 (a,b) VALUES (Point(1,1), Point(2,2)); SELECT AsText(c) FROM t1; AsText(c) GEOMETRYCOLLECTION(POINT(1 1),POINT(2 2)) DROP TABLE t1; CREATE TABLE t1 (a GEOMETRY, b GEOMETRY DEFAULT GeomFromWKB(AsBinary(a),20)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` geometry DEFAULT NULL, + `b` geometry DEFAULT GeomFromWKB(AsBinary(a),20) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 INSERT INTO t1 (a) VALUES (GeomFromText('POINT(1 1)', 10)); SELECT AsText(a), SRID(a), AsText(b), SRID(b) FROM t1; AsText(a) SRID(a) AsText(b) SRID(b) POINT(1 1) 10 POINT(1 1) 20 DROP TABLE t1; CREATE TABLE t1 (a GEOMETRY, b GEOMETRY DEFAULT BOUNDARY(a)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` geometry DEFAULT NULL, + `b` geometry DEFAULT BOUNDARY(a) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 INSERT INTO t1 (a) VALUES (GeomFromText('POLYGON((10 10, 10 20, 20 20, 20 10, 10 10))')); SELECT AsText(b) FROM t1; AsText(b) LINESTRING(10 10,10 20,20 20,20 10,10 10) DROP TABLE t1; CREATE TABLE t1 (a GEOMETRY, b GEOMETRY DEFAULT BUFFER(a,10)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` geometry DEFAULT NULL, + `b` geometry DEFAULT BUFFER(a,10) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 INSERT INTO t1 (a) VALUES (GeomFromText('POLYGON((10 10, 10 20, 20 20, 20 10, 10 10))')); SELECT GeometryType(b) FROM t1; GeometryType(b) POLYGON DROP TABLE t1; CREATE TABLE t1 (a GEOMETRY, b GEOMETRY DEFAULT CENTROID(a)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` geometry DEFAULT NULL, + `b` geometry DEFAULT CENTROID(a) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 INSERT INTO t1 (a) VALUES (GeomFromText('POLYGON((10 10, 10 20, 20 20, 20 10, 10 10))')); SELECT AsText(b) FROM t1; AsText(b) POINT(15 15) DROP TABLE t1; CREATE TABLE t1 (a GEOMETRY, b GEOMETRY DEFAULT ENVELOPE(a)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` geometry DEFAULT NULL, + `b` geometry DEFAULT ENVELOPE(a) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 INSERT INTO t1 (a) VALUES (GeomFromText('LineString(1 1,4 4)')); SELECT AsText(b) FROM t1; AsText(b) POLYGON((1 1,4 1,4 4,1 4,1 1)) DROP TABLE t1; CREATE TABLE t1 (a GEOMETRY, b GEOMETRY DEFAULT PointOnSurface(a)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` geometry DEFAULT NULL, + `b` geometry DEFAULT PointOnSurface(a) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 INSERT INTO t1 (a) VALUES (GeomFromText('POLYGON((10 10, 10 20, 20 20, 20 10, 10 10))')); SELECT GeometryType(b) FROM t1; GeometryType(b) POINT DROP TABLE t1; CREATE TABLE t1 (a GEOMETRY, b GEOMETRY DEFAULT Point(1,1), c GEOMETRY DEFAULT ST_UNION(a,b)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` geometry DEFAULT NULL, + `b` geometry DEFAULT Point(1,1), + `c` geometry DEFAULT ST_UNION(a,b) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 INSERT INTO t1 (a) VALUES (Point(0,0)); SELECT AsText(c) FROM t1; AsText(c) MULTIPOINT(0 0,1 1) DROP TABLE t1; CREATE TABLE t1 (a GEOMETRY, b VARCHAR(20) DEFAULT GeometryType(a)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` geometry DEFAULT NULL, + `b` varchar(20) DEFAULT GeometryType(a) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 INSERT INTO t1 (a) VALUES (Point(0, 0)); SELECT b FROM t1; b POINT DROP TABLE t1; CREATE TABLE t1 (a GEOMETRY, b INT DEFAULT IsSimple(a)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` geometry DEFAULT NULL, + `b` int(11) DEFAULT IsSimple(a) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 INSERT INTO t1 (a) VALUES (Point(0, 0)); SELECT b FROM t1; b 1 DROP TABLE t1; CREATE TABLE t1 (a GEOMETRY, b INT DEFAULT IsEmpty(a)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` geometry DEFAULT NULL, + `b` int(11) DEFAULT IsEmpty(a) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 INSERT INTO t1 (a) VALUES (Point(0, 0)); SELECT b FROM t1; b 0 DROP TABLE t1; CREATE TABLE t1 (a GEOMETRY, b INT DEFAULT IsRing(a)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` geometry DEFAULT NULL, + `b` int(11) DEFAULT IsRing(a) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 INSERT INTO t1 (a) VALUES (GeomFromText('LineString(0 0,0 1,1 1,1 0,0 0)')); SELECT b FROM t1; b 1 DROP TABLE t1; CREATE TABLE t1 (a GEOMETRY, b INT DEFAULT IsClosed(a)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` geometry DEFAULT NULL, + `b` int(11) DEFAULT IsClosed(a) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 INSERT INTO t1 (a) VALUES (GeomFromText('LineString(0 0,0 1,1 1,1 0,0 0)')); SELECT b FROM t1; b 1 DROP TABLE t1; CREATE TABLE t1 (a GEOMETRY, b INT DEFAULT Dimension(a)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` geometry DEFAULT NULL, + `b` int(11) DEFAULT Dimension(a) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 INSERT INTO t1 (a) VALUES (Buffer(Point(1,1),1)); SELECT b FROM t1; b 2 DROP TABLE t1; CREATE TABLE t1 (a GEOMETRY, b INT DEFAULT NumGeometries(a)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` geometry DEFAULT NULL, + `b` int(11) DEFAULT NumGeometries(a) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 INSERT INTO t1 (a) VALUES (ST_UNION(Point(1,1),Point(0,0))); SELECT b FROM t1; b 2 DROP TABLE t1; CREATE TABLE t1 (a GEOMETRY, b INT DEFAULT NumInteriorRings(a)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` geometry DEFAULT NULL, + `b` int(11) DEFAULT NumInteriorRings(a) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 INSERT INTO t1 (a) VALUES (GeomFromText('Polygon((0 0,0 3,3 3,3 0,0 0),(1 1,1 2,2 2,2 1,1 1))')); SELECT b FROM t1; b 1 DROP TABLE t1; CREATE TABLE t1 (a GEOMETRY, b INT DEFAULT NumPoints(a)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` geometry DEFAULT NULL, + `b` int(11) DEFAULT NumPoints(a) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 INSERT INTO t1 (a) VALUES (LineString(Point(1,1),Point(0,0))); SELECT b FROM t1; b 2 DROP TABLE t1; CREATE TABLE t1 (a GEOMETRY, b INT DEFAULT SRID(a)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` geometry DEFAULT NULL, + `b` int(11) DEFAULT SRID(a) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 INSERT INTO t1 (a) VALUES (GeomFromText('Point(1 1)', 100)); SELECT b FROM t1; b 100 DROP TABLE t1; CREATE TABLE t1 (a GEOMETRY, b GEOMETRY, c INT DEFAULT MBRDisjoint(a,b)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` geometry DEFAULT NULL, + `b` geometry DEFAULT NULL, + `c` int(11) DEFAULT MBRDisjoint(a,b) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 INSERT INTO t1 (a,b) VALUES (Point(1,1),Point(1,1)); SELECT c FROM t1; c 0 DROP TABLE t1; CREATE TABLE t1 (a GEOMETRY, b GEOMETRY, c INT DEFAULT ST_Disjoint(a,b)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` geometry DEFAULT NULL, + `b` geometry DEFAULT NULL, + `c` int(11) DEFAULT ST_Disjoint(a,b) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 INSERT INTO t1 (a,b) VALUES (Point(1,1),Point(1,1)); SELECT c FROM t1; c 0 DROP TABLE t1; CREATE TABLE t1 (a GEOMETRY, b GEOMETRY, c INT DEFAULT ST_Relate(a,b,'T*F**FFF*')); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` geometry DEFAULT NULL, + `b` geometry DEFAULT NULL, + `c` int(11) DEFAULT ST_Relate(a,b,'T*F**FFF*') +) ENGINE=MyISAM DEFAULT CHARSET=latin1 INSERT INTO t1 (a,b) VALUES (Point(1,1),Point(1,1)); SELECT c FROM t1; c diff --git a/mysql-test/r/grant.result b/mysql-test/r/grant.result index b219d5e7409..8442f7fc401 100644 --- a/mysql-test/r/grant.result +++ b/mysql-test/r/grant.result @@ -2672,6 +2672,11 @@ set GLOBAL sql_mode=default; # MDEV-10134 Add full support for DEFAULT # CREATE TABLE t1 (a VARCHAR(30) DEFAULT USER()); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` varchar(30) DEFAULT USER() +) ENGINE=MyISAM DEFAULT CHARSET=latin1 INSERT INTO t1 VALUES (); GRANT ALL PRIVILEGES ON test.* TO dummy@localhost IDENTIFIED BY 'pwd'; connect conn1,localhost,dummy,pwd,test; diff --git a/mysql-test/r/statistics.result b/mysql-test/r/statistics.result index fba00fe084d..52986fa9a6b 100644 --- a/mysql-test/r/statistics.result +++ b/mysql-test/r/statistics.result @@ -1684,6 +1684,12 @@ set use_stat_tables=@save_use_stat_tables; # MDEV-10134 Add full support for DEFAULT # CREATE TABLE t1 (a BLOB, b TEXT DEFAULT DECODE_HISTOGRAM('SINGLE_PREC_HB',a)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` blob DEFAULT NULL, + `b` text DEFAULT DECODE_HISTOGRAM('SINGLE_PREC_HB',a) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 INSERT INTO t1 (a) VALUES (0x0000000000000000000000000101010101010101010202020303030304040404050505050606070707080809090A0A0B0C0D0D0E0E0F10111213131415161718191B1C1E202224292A2E33373B4850575F6A76818C9AA7B9C4CFDADFE5EBF0F4F8FAFCFF); SELECT b FROM t1; b diff --git a/mysql-test/r/udf.result b/mysql-test/r/udf.result index 120b4d22b03..dc9806bb7d1 100644 --- a/mysql-test/r/udf.result +++ b/mysql-test/r/udf.result @@ -453,6 +453,12 @@ SELECT METAPHON('Hello'); METAPHON('Hello') HL CREATE TABLE t1 (a VARCHAR(10), b VARCHAR(10) DEFAULT METAPHON(a)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` varchar(10) DEFAULT NULL, + `b` varchar(10) DEFAULT METAPHON(a) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 INSERT INTO t1 (a) VALUES ('Hello'); SELECT * FROM t1; a b diff --git a/mysql-test/t/ctype_like_range.test b/mysql-test/t/ctype_like_range.test index 76180a64569..c663584ccf0 100644 --- a/mysql-test/t/ctype_like_range.test +++ b/mysql-test/t/ctype_like_range.test @@ -130,6 +130,7 @@ CREATE TABLE t1 ( mn VARCHAR(10) DEFAULT LIKE_RANGE_MIN(a,10), mx VARCHAR(10) DEFAULT LIKE_RANGE_MAX(a,10) ); +SHOW CREATE TABLE t1; INSERT INTO t1 (a) VALUES ('a'),('a_'),('a%'); SELECT a, HEX(mn), HEX(mx) FROM t1; DROP TABLE t1; diff --git a/mysql-test/t/default.test b/mysql-test/t/default.test index f56b559991a..88a4b703aaa 100644 --- a/mysql-test/t/default.test +++ b/mysql-test/t/default.test @@ -355,6 +355,9 @@ CREATE TABLE t1 (a INT DEFAULT (1=ANY (SELECT 1))); --error ER_OPERAND_COLUMNS CREATE TABLE t1 (a INT DEFAULT ROW(1,1)); +--error ER_PARSE_ERROR +CREATE TABLE t1 (a INT DEFAULT (1,1)); + --error ER_OPERAND_COLUMNS CREATE TABLE t1 (a INT DEFAULT ((1,1))); @@ -583,13 +586,22 @@ DROP TABLE t1; CREATE TABLE t1 (a INT DEFAULT 'x'); CREATE TABLE t1 (a INT DEFAULT CONCAT('x')); +SHOW CREATE TABLE t1; insert into t1 values(); DROP TABLE t1; CREATE TABLE t1 (a INT DEFAULT COALESCE('x')); +SHOW CREATE TABLE t1; insert into t1 values(); DROP TABLE t1; +# QQ: shouldn't extra parentheses be removed: +# so SHOW CREATE TABLE reports DEFAULT COALESCE('x') +# rather than DEFAULT ((((COALESCE('x'))))? +CREATE TABLE t1 (a INT DEFAULT (((((COALESCE('x'))))))); +SHOW CREATE TABLE t1; +DROP TABLE t1; + --echo # --echo # INT: string expressions with numbers + garbage @@ -599,10 +611,12 @@ DROP TABLE t1; CREATE TABLE t1 (a INT DEFAULT '1x'); CREATE TABLE t1 (a INT DEFAULT COALESCE('1x')); +SHOW CREATE TABLE t1; insert into t1 values(); DROP TABLE t1; CREATE TABLE t1 (a INT DEFAULT CONCAT('1x')); +SHOW CREATE TABLE t1; insert into t1 values(); DROP TABLE t1; @@ -688,6 +702,7 @@ DROP TABLE t1; CREATE TABLE t1 (a VARCHAR(2) DEFAULT 'xxx' NOT NULL); CREATE TABLE t1 (a VARCHAR(2) DEFAULT CONCAT('xxx') NOT NULL); +SHOW CREATE TABLE t1; insert into t1 values(); DROP TABLE t1; @@ -697,6 +712,7 @@ DROP TABLE t1; --error ER_INVALID_DEFAULT CREATE TABLE t1 (a VARCHAR(2) DEFAULT 'xx ' NOT NULL); CREATE TABLE t1 (a VARCHAR(2) DEFAULT CONCAT('xx ') NOT NULL); +SHOW CREATE TABLE t1; insert into t1 values(); DROP TABLE t1; @@ -709,10 +725,12 @@ DROP TABLE t1; CREATE TABLE t1 (a VARCHAR(2) CHARACTER SET latin1 DEFAULT _utf8 X'D18F' NOT NULL); CREATE TABLE t1 (a VARCHAR(2) CHARACTER SET latin1 DEFAULT CONCAT(_utf8 X'D18F') NOT NULL); +SHOW CREATE TABLE t1; insert into t1 values(); DROP TABLE t1; CREATE TABLE t1 (a VARCHAR(2) CHARACTER SET latin1 DEFAULT CONCAT(_utf8 0xD18F) NOT NULL); +SHOW CREATE TABLE t1; insert into t1 values(); DROP TABLE t1; @@ -857,6 +875,7 @@ CREATE TABLE t1 (a VARCHAR(30) DEFAULT CURRENT_ROLE); --echo # Other Item_func_sysconst derived functions --echo # CREATE TABLE t1 (a VARCHAR(30) DEFAULT DATABASE()); +SHOW CREATE TABLE t1; INSERT INTO t1 VALUES (); USE INFORMATION_SCHEMA; INSERT INTO test.t1 VALUES (); @@ -870,6 +889,7 @@ DROP TABLE t1; --echo # CREATE TABLE `t1` (`a` int(11) DEFAULT (3+3),`b` int(11) DEFAULT '1000'); +SHOW CREATE TABLE t1; insert into t1 values (1,1),(2,2); insert into t1 values (default,default); select * from t1; @@ -884,16 +904,19 @@ drop table t1; --echo # CREATE TABLE t1 (a DECIMAL(10,1), b DOUBLE DEFAULT CAST(a AS DOUBLE)); +SHOW CREATE TABLE t1; 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)); +SHOW CREATE TABLE t1; 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)); +SHOW CREATE TABLE t1; INSERT INTO t1 VALUES (4, DEFAULT, DEFAULT); INSERT INTO t1 VALUES (100, DEFAULT, DEFAULT); SELECT * FROM t1; @@ -901,25 +924,30 @@ DROP TABLE t1; CREATE TABLE t1 (a DOUBLE, b DOUBLE DEFAULT SQRT(a), c DOUBLE DEFAULT POW(a,3)); +SHOW CREATE TABLE t1; 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)); +SHOW CREATE TABLE t1; 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)); +SHOW CREATE TABLE t1; 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()); +SHOW CREATE TABLE t1; INSERT INTO t1 VALUES (DEFAULT); DROP TABLE t1; CREATE TABLE t1 (a DOUBLE, b DOUBLE DEFAULT DEGREES(a), c DOUBLE DEFAULT RADIANS(b)); +SHOW CREATE TABLE t1; INSERT INTO t1 VALUES (PI(), DEFAULT, DEFAULT); SELECT * FROM t1; DROP TABLE t1; @@ -929,36 +957,43 @@ DROP TABLE t1; --echo # CREATE TABLE t1 (a INT, b INT DEFAULT INTERVAL(a, 10, 20, 30, 40)); +SHOW CREATE TABLE t1; INSERT INTO t1 (a) VALUES (34); SELECT * FROM t1; DROP TABLE t1; CREATE TABLE t1 (a INT, b INT, c INT DEFAULT (a DIV b)); +SHOW CREATE TABLE t1; INSERT INTO t1 (a, b) VALUES (13, 3); SELECT * FROM t1; DROP TABLE t1; CREATE TABLE t1 (a INT, b INT DEFAULT SIGN(a)); +SHOW CREATE TABLE t1; INSERT INTO t1 (a) VALUES (-10),(0), (10); SELECT * FROM t1; DROP TABLE t1; CREATE TABLE t1 (a VARCHAR(30), b INT DEFAULT FIELD(a, 'Hej', 'ej', 'Heja', 'hej', 'foo')); +SHOW CREATE TABLE t1; INSERT INTO t1 (a) VALUES ('ej'); SELECT * FROM t1; DROP TABLE t1; CREATE TABLE t1 (a VARCHAR(30), b INT DEFAULT FIND_IN_SET(a, 'Hej,ej,Heja,hej,foo')); +SHOW CREATE TABLE t1; INSERT INTO t1 (a) VALUES ('ej'); SELECT * FROM t1; DROP TABLE t1; CREATE TABLE t1 (a VARCHAR(30), b INT DEFAULT ASCII(a), c INT DEFAULT ORD(a)); +SHOW CREATE TABLE t1; INSERT INTO t1 (a) VALUES ('a'); SELECT * FROM t1; DROP TABLE t1; CREATE TABLE t1 (a TEXT DEFAULT UUID_SHORT()); +SHOW CREATE TABLE t1; INSERT INTO t1 VALUES (); SELECT a>0 FROM t1; DROP TABLE t1; @@ -1003,16 +1038,19 @@ CREATE TABLE t1 (a VARCHAR(30), b DOUBLE DEFAULT MATCH (a) AGAINST('bbbb' IN BOO --echo # Item_temporal_hybrid_func CREATE TABLE t1 (a DATE, b INT, c DATE DEFAULT DATE_ADD(a, INTERVAL b DAY)); +SHOW CREATE TABLE t1; 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)); +SHOW CREATE TABLE t1; 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)); +SHOW CREATE TABLE t1; INSERT INTO t1 VALUES ('01,5,2013','%d,%m,%Y', DEFAULT); SELECT * FROM t1; DROP TABLE t1; @@ -1025,22 +1063,26 @@ CREATE TABLE t1 (a VARCHAR(30), b VARCHAR(30) DEFAULT DATE_FORMAT(a,'%W %M %Y')) 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()); +SHOW CREATE TABLE t1; 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)); +SHOW CREATE TABLE t1; INSERT INTO t1 VALUES (730669, DEFAULT); SELECT * FROM t1; DROP TABLE t1; CREATE TABLE t1 (a DATE, b DATE DEFAULT LAST_DAY(a)); +SHOW CREATE TABLE t1; 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)); +SHOW CREATE TABLE t1; INSERT INTO t1 VALUES (2011,32,DEFAULT); SELECT * FROM t1; DROP TABLE t1; @@ -1050,22 +1092,26 @@ DROP TABLE t1; 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()); +SHOW CREATE TABLE t1; 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)); +SHOW CREATE TABLE t1; 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)); +SHOW CREATE TABLE t1; 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)); +SHOW CREATE TABLE t1; INSERT INTO t1 VALUES (10,20,30,DEFAULT); SELECT * FROM t1; DROP TABLE t1; @@ -1076,6 +1122,7 @@ DROP TABLE t1; 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()); +SHOW CREATE TABLE t1; INSERT INTO t1 VALUES (); SELECT * FROM t1; DROP TABLE t1; @@ -1083,34 +1130,40 @@ 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)); +SHOW CREATE TABLE t1; 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)); +SHOW CREATE TABLE t1; 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')); +SHOW CREATE TABLE t1; 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)); +SHOW CREATE TABLE t1; INSERT INTO t1 VALUES (20010203, DEFAULT); SELECT * FROM t1; DROP TABLE t1; CREATE TABLE t1 (a INT, b TIME DEFAULT CAST(a AS TIME)); +SHOW CREATE TABLE t1; INSERT INTO t1 VALUES (102030, DEFAULT); SELECT * FROM t1; DROP TABLE t1; CREATE TABLE t1 (a BIGINT, b DATETIME DEFAULT CAST(a AS DATETIME)); +SHOW CREATE TABLE t1; INSERT INTO t1 VALUES (20010203102030, DEFAULT); SELECT * FROM t1; DROP TABLE t1; @@ -1121,94 +1174,113 @@ DROP TABLE t1; --echo # CREATE TABLE t1 (a INT, b INT, c INT DEFAULT PERIOD_ADD(a,b)); +SHOW CREATE TABLE t1; INSERT INTO t1 (a,b) VALUES (200801, 2); SELECT * FROM t1; DROP TABLE t1; CREATE TABLE t1 (a INT, b INT, c INT DEFAULT PERIOD_DIFF(a,b)); +SHOW CREATE TABLE t1; INSERT INTO t1 (a,b) VALUES (200802, 200703); SELECT * FROM t1; DROP TABLE t1; CREATE TABLE t1 (a INT, b INT DEFAULT TO_DAYS(a)); +SHOW CREATE TABLE t1; INSERT INTO t1 (a) VALUES (950501); SELECT * FROM t1; DROP TABLE t1; CREATE TABLE t1 (a DATE, b INT DEFAULT TO_DAYS(a)); +SHOW CREATE TABLE t1; INSERT INTO t1 (a) VALUES ('2007-10-07'); SELECT * FROM t1; DROP TABLE t1; CREATE TABLE t1 (a INT, b BIGINT DEFAULT TO_SECONDS(a)); +SHOW CREATE TABLE t1; INSERT INTO t1 (a) VALUES (950501); SELECT * FROM t1; DROP TABLE t1; CREATE TABLE t1 (a DATE, b BIGINT DEFAULT TO_SECONDS(a)); +SHOW CREATE TABLE t1; INSERT INTO t1 (a) VALUES ('2009-11-29'); SELECT * FROM t1; DROP TABLE t1; CREATE TABLE t1 (a DATETIME, b BIGINT DEFAULT TO_SECONDS(a)); +SHOW CREATE TABLE t1; INSERT INTO t1 (a) VALUES ('2009-11-29 13:43:32'); SELECT * FROM t1; DROP TABLE t1; CREATE TABLE t1 (a DATE, b BIGINT DEFAULT DAYOFMONTH(a)); +SHOW CREATE TABLE t1; INSERT INTO t1 (a) VALUES ('2007-02-03'); SELECT * FROM t1; DROP TABLE t1; CREATE TABLE t1 (a DATE, b BIGINT DEFAULT DAYOFWEEK(a)); +SHOW CREATE TABLE t1; INSERT INTO t1 (a) VALUES ('2007-02-03'); SELECT * FROM t1; DROP TABLE t1; CREATE TABLE t1 (a DATE, b BIGINT DEFAULT DAYOFYEAR(a)); +SHOW CREATE TABLE t1; INSERT INTO t1 (a) VALUES ('2007-02-03'); SELECT * FROM t1; DROP TABLE t1; CREATE TABLE t1 (a TIME, b INT DEFAULT HOUR(a)); +SHOW CREATE TABLE t1; INSERT INTO t1 (a) VALUES ('10:05:03'); SELECT * FROM t1; DROP TABLE t1; CREATE TABLE t1 (a TIME, b INT DEFAULT MINUTE(a)); +SHOW CREATE TABLE t1; INSERT INTO t1 (a) VALUES ('10:05:03'); SELECT * FROM t1; DROP TABLE t1; CREATE TABLE t1 (a TIME, b INT DEFAULT SECOND(a)); +SHOW CREATE TABLE t1; INSERT INTO t1 (a) VALUES ('10:05:03'); SELECT * FROM t1; DROP TABLE t1; CREATE TABLE t1 (a DATETIME(6), b INT DEFAULT MICROSECOND(a)); +SHOW CREATE TABLE t1; INSERT INTO t1 (a) VALUES ('2009-12-31 23:59:59.000010'); SELECT * FROM t1; DROP TABLE t1; CREATE TABLE t1 (a DATE, b INT DEFAULT YEAR(a)); +SHOW CREATE TABLE t1; INSERT INTO t1 (a) VALUES ('1987-01-01'); SELECT * FROM t1; DROP TABLE t1; CREATE TABLE t1 (a DATE, b INT DEFAULT MONTH(a)); +SHOW CREATE TABLE t1; INSERT INTO t1 (a) VALUES ('1987-01-01'); SELECT * FROM t1; DROP TABLE t1; # QQ: this depends on @@default_week_format. CREATE TABLE t1 (a DATE, b INT DEFAULT WEEK(a)); +SHOW CREATE TABLE t1; INSERT INTO t1 (a) VALUES ('1987-02-01'); SELECT * FROM t1; DROP TABLE t1; CREATE TABLE t1 (a DATE, b INT DEFAULT YEARWEEK(a)); +SHOW CREATE TABLE t1; INSERT INTO t1 (a) VALUES ('2000-01-01'); SELECT * FROM t1; DROP TABLE t1; CREATE TABLE t1 (a DATE, b INT DEFAULT QUARTER(a)); +SHOW CREATE TABLE t1; INSERT INTO t1 (a) VALUES ('2008-04-01'); SELECT * FROM t1; DROP TABLE t1; @@ -1220,31 +1292,38 @@ CREATE TABLE t1 (a DATE, b VARCHAR(30) DEFAULT DAYNAME(a)); CREATE TABLE t1 (a DATE, b VARCHAR(30) DEFAULT MONTHNAME(a)); CREATE TABLE t1 (a DATE, b INT DEFAULT EXTRACT(YEAR FROM a)); +SHOW CREATE TABLE t1; INSERT INTO t1 (a) VALUES ('2009-07-02'); SELECT * FROM t1; DROP TABLE t1; CREATE TABLE t1 (a DATETIME, b INT DEFAULT EXTRACT(YEAR_MONTH FROM a)); +SHOW CREATE TABLE t1; INSERT INTO t1 (a) VALUES ('2009-07-02 01:02:03'); SELECT * FROM t1; DROP TABLE t1; CREATE TABLE t1 (a DATETIME, b INT DEFAULT EXTRACT(DAY_MINUTE FROM a)); +SHOW CREATE TABLE t1; INSERT INTO t1 (a) VALUES ('2009-07-02 01:02:03'); SELECT * FROM t1; DROP TABLE t1; CREATE TABLE t1 (a DATETIME(6), b INT DEFAULT EXTRACT(MICROSECOND FROM a)); +SHOW CREATE TABLE t1; INSERT INTO t1 (a) VALUES ('2009-07-02 01:02:03.000123'); SELECT * FROM t1; DROP TABLE t1; CREATE TABLE t1 (a DATE, b DATE, c INT DEFAULT TIMESTAMPDIFF(MONTH,a,b)); +SHOW CREATE TABLE t1; INSERT INTO t1 (a,b) VALUES ('2003-02-01','2003-05-01'); SELECT * FROM t1; DROP TABLE t1; CREATE TABLE t1 (a DATE, b DATE, c INT DEFAULT TIMESTAMPDIFF(YEAR,a,b)); +SHOW CREATE TABLE t1; INSERT INTO t1 (a,b) VALUES ('2002-05-01','2001-01-01'); SELECT * FROM t1; DROP TABLE t1; CREATE TABLE t1 (a DATE, b DATETIME, c INT DEFAULT TIMESTAMPDIFF(MINUTE,a,b)); +SHOW CREATE TABLE t1; INSERT INTO t1 (a,b) VALUES ('2003-02-01','2003-05-01 12:05:55'); SELECT * FROM t1; DROP TABLE t1; @@ -1255,12 +1334,14 @@ DROP TABLE t1; --echo # CREATE TABLE t1 (a INT, b INT, c INT DEFAULT COALESCE(a,b)); +SHOW CREATE TABLE t1; 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)); +SHOW CREATE TABLE t1; INSERT INTO t1 VALUES (NULL, 2, DEFAULT); INSERT INTO t1 VALUES (1, 2, DEFAULT); SELECT * FROM t1; @@ -1268,18 +1349,21 @@ DROP TABLE t1; CREATE TABLE t1 (a INT, b INT, c INT DEFAULT NULLIF(a,b)); +SHOW CREATE TABLE t1; 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)); +SHOW CREATE TABLE t1; 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); +SHOW CREATE TABLE t1; INSERT INTO t1 VALUES (0, 1, DEFAULT); INSERT INTO t1 VALUES (1, 1, DEFAULT); SELECT * FROM t1; @@ -1287,51 +1371,60 @@ DROP TABLE t1; CREATE TABLE t1 (a INT, b INT DEFAULT (-a)); SHOW CREATE TABLE t1; +SHOW CREATE TABLE t1; INSERT INTO t1 VALUES (10, DEFAULT); SELECT * FROM t1; DROP TABLE t1; CREATE TABLE t1 (a INT, b INT DEFAULT ABS(a)); +SHOW CREATE TABLE t1; 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)); +SHOW CREATE TABLE t1; 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)); +SHOW CREATE TABLE t1; 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)); SHOW CREATE TABLE t1; +SHOW CREATE TABLE t1; 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)); +SHOW CREATE TABLE t1; 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)); +SHOW CREATE TABLE t1; 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)); +SHOW CREATE TABLE t1; 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)); +SHOW CREATE TABLE t1; INSERT INTO t1 VALUES (1, 2, DEFAULT); SELECT * FROM t1; DROP TABLE t1; @@ -1340,6 +1433,7 @@ DROP TABLE t1; --echo # CAST --echo # CREATE TABLE t1 (a VARCHAR(30), b DECIMAL(10,6) DEFAULT CAST(a AS DECIMAL(10,1))); +SHOW CREATE TABLE t1; INSERT INTO t1 (a) VALUES ('123.456'); SELECT * FROM t1; DROP TABLE t1; @@ -1347,16 +1441,19 @@ DROP TABLE t1; CREATE TABLE t1 (a DECIMAL(10,3), b VARCHAR(10) DEFAULT CAST(a AS CHAR(10)), c VARCHAR(10) DEFAULT CAST(a AS CHAR(4))); +SHOW CREATE TABLE t1; INSERT INTO t1 (a) VALUES (123.456); SELECT * FROM t1; DROP TABLE t1; CREATE TABLE t1 (a INT, b INT UNSIGNED DEFAULT CAST(a AS UNSIGNED)); +SHOW CREATE TABLE t1; INSERT INTO t1 (a) VALUES (-1); SELECT * FROM t1; DROP TABLE t1; CREATE TABLE t1 (a BIGINT UNSIGNED, b BIGINT SIGNED DEFAULT CAST(a AS SIGNED)); +SHOW CREATE TABLE t1; INSERT INTO t1 (a) VALUES (0xFFFFFFFFFFFFFFFF); SELECT * FROM t1; DROP TABLE t1; @@ -1378,25 +1475,35 @@ DROP TABLE t1; --echo # CREATE TABLE t1 (a INT, b INT DEFAULT BIT_COUNT(a)); +SHOW CREATE TABLE t1; INSERT INTO t1 (a) VALUES (7); SELECT * FROM t1; DROP TABLE t1; + CREATE TABLE t1 (a INT, b INT, c INT DEFAULT (a|b)); +SHOW CREATE TABLE t1; INSERT INTO t1 (a,b) VALUES (1,2); SELECT * FROM t1; DROP TABLE t1; + CREATE TABLE t1 (a INT, b INT, c INT DEFAULT (a&b)); +SHOW CREATE TABLE t1; INSERT INTO t1 (a,b) VALUES (5,4); SELECT * FROM t1; DROP TABLE t1; + CREATE TABLE t1 (a INT, b INT, c INT DEFAULT (a^b)); +SHOW CREATE TABLE t1; INSERT INTO t1 (a,b) VALUES (11,3); SELECT * FROM t1; DROP TABLE t1; + CREATE TABLE t1 (a INT, b INT, c INT DEFAULT (a&~b)); +SHOW CREATE TABLE t1; INSERT INTO t1 (a,b) VALUES (5,1); SELECT * FROM t1; DROP TABLE t1; + CREATE TABLE t1 (a INT, b INT, c INT DEFAULT (a<<b), d INT DEFAULT (a>>b)); SHOW CREATE TABLE t1; INSERT INTO t1 (a,b) VALUES (5,1); @@ -1409,122 +1516,146 @@ DROP TABLE t1; --echo # CREATE TABLE t1 (a VARCHAR(10), b VARCHAR(20) DEFAULT REVERSE(a)); +SHOW CREATE TABLE t1; INSERT INTO t1 (a) VALUES ('abcd'); SELECT * FROM t1; DROP TABLE t1; CREATE TABLE t1 (a VARCHAR(10), b VARCHAR(10) DEFAULT UPPER(a), c VARCHAR(10) DEFAULT LOWER(a)); +SHOW CREATE TABLE t1; INSERT INTO t1 (a) VALUES ('ABcd'); SELECT * FROM t1; DROP TABLE t1; CREATE TABLE t1 (a VARCHAR(10), b VARCHAR(10) DEFAULT LEFT(a,1), c VARCHAR(10) DEFAULT RIGHT(a,1), d VARCHAR(10) DEFAULT SUBSTR(a,2,2)); +SHOW CREATE TABLE t1; INSERT INTO t1 (a) VALUES ('abcd'); SELECT * FROM t1; DROP TABLE t1; CREATE TABLE t1 (a VARCHAR(20), b VARCHAR(20) DEFAULT SUBSTRING_INDEX(a,'.',2)); +SHOW CREATE TABLE t1; INSERT INTO t1 (a) VALUES ('www.mariadb.org'); SELECT * FROM t1; DROP TABLE t1; CREATE TABLE t1 (a VARCHAR(10), b VARCHAR(10), c VARCHAR(20) DEFAULT CONCAT(a,b)); +SHOW CREATE TABLE t1; INSERT INTO t1 (a,b) VALUES ('a','b'); SELECT * FROM t1; DROP TABLE t1; CREATE TABLE t1 (a VARCHAR(10), b VARCHAR(10), c VARCHAR(20) DEFAULT CONCAT_WS(',',a,b)); +SHOW CREATE TABLE t1; INSERT INTO t1 (a,b) VALUES ('a','b'); SELECT * FROM t1; DROP TABLE t1; CREATE TABLE t1 (a VARCHAR(10), b VARCHAR(10) DEFAULT REPLACE(a,'a','A')); +SHOW CREATE TABLE t1; INSERT INTO t1 (a) VALUES ('abc'); SELECT * FROM t1; DROP TABLE t1; CREATE TABLE t1 (a VARCHAR(10), b VARCHAR(10) DEFAULT REGEXP_REPLACE(a,'[0-9]','.')); +SHOW CREATE TABLE t1; INSERT INTO t1 (a) VALUES ('a1b2c'); SELECT * FROM t1; DROP TABLE t1; CREATE TABLE t1 (a VARCHAR(10), b VARCHAR(10) DEFAULT REGEXP_SUBSTR(a,'[0-9]+')); +SHOW CREATE TABLE t1; INSERT INTO t1 (a) VALUES ('ab12cd'); SELECT * FROM t1; DROP TABLE t1; CREATE TABLE t1 (a VARCHAR(20), b VARCHAR(20) DEFAULT SOUNDEX(a)); +SHOW CREATE TABLE t1; INSERT INTO t1 (a) VALUES ('tester'); SELECT * FROM t1; DROP TABLE t1; CREATE TABLE t1 (a VARCHAR(20), b VARCHAR(20) DEFAULT QUOTE(a)); +SHOW CREATE TABLE t1; INSERT INTO t1 (a) VALUES ('a\'b'); SELECT * FROM t1; DROP TABLE t1; CREATE TABLE t1 (a VARCHAR(10), b VARCHAR(10) DEFAULT LPAD(a,10,'.'), c VARCHAR(10) DEFAULT RPAD(a,10,'.')); +SHOW CREATE TABLE t1; INSERT INTO t1 (a) VALUES ('ab'); SELECT * FROM t1; DROP TABLE t1; CREATE TABLE t1 (a VARCHAR(10), b VARCHAR(10) DEFAULT LTRIM(a), c VARCHAR(10) DEFAULT RTRIM(a)); +SHOW CREATE TABLE t1; INSERT INTO t1 (a) VALUES (' ab '); SELECT a, HEX(b), HEX(c) FROM t1; DROP TABLE t1; CREATE TABLE t1 (a VARCHAR(10), b VARCHAR(10) DEFAULT TRIM(BOTH 'a' FROM a)); +SHOW CREATE TABLE t1; INSERT INTO t1 (a) VALUES ('abba'); SELECT a, b FROM t1; DROP TABLE t1; CREATE TABLE t1 (a INT, b VARCHAR(10) DEFAULT SPACE(a)); +SHOW CREATE TABLE t1; INSERT INTO t1 (a) VALUES (3); SELECT a, HEX(b) FROM t1; DROP TABLE t1; CREATE TABLE t1 (a INT, b VARCHAR(10), c VARCHAR(10) DEFAULT REPEAT(b,a)); +SHOW CREATE TABLE t1; INSERT INTO t1 (a,b) VALUES (3,'x'); SELECT a, b, c FROM t1; DROP TABLE t1; CREATE TABLE t1 (str VARCHAR(10), pos INT, len INT, newstr VARCHAR(10), result VARCHAR(10) DEFAULT INSERT(str,pos,len,newstr)); +SHOW CREATE TABLE t1; INSERT INTO t1 (str,pos,len,newstr) VALUES ('Quadratic', 3, 4, 'What'); SELECT * FROM t1; DROP TABLE t1; CREATE TABLE t1 (n INT, res VARCHAR(10) DEFAULT ELT(n,'ej', 'Heja', 'hej', 'foo')); +SHOW CREATE TABLE t1; INSERT INTO t1 (n) VALUES (1); SELECT * FROM t1; DROP TABLE t1; CREATE TABLE t1 (bits INT, res VARCHAR(10) DEFAULT MAKE_SET(bits,'a','b','c','d')); +SHOW CREATE TABLE t1; INSERT INTO t1 (bits) VALUES (1|4); SELECT * FROM t1; DROP TABLE t1; CREATE TABLE t1 (a INT, b VARCHAR(10) DEFAULT CHAR(a)); +SHOW CREATE TABLE t1; INSERT INTO t1 (a) VALUES (77); SELECT * FROM t1; DROP TABLE t1; CREATE TABLE t1 (a INT, b VARCHAR(10) DEFAULT CONV(a,10,16)); +SHOW CREATE TABLE t1; INSERT INTO t1 (a) VALUES (64); SELECT * FROM t1; DROP TABLE t1; # QQ: this depends on @@lc_time_names CREATE TABLE t1 (a INT, b INT, c VARCHAR(30) DEFAULT FORMAT(a,b)); +SHOW CREATE TABLE t1; INSERT INTO t1 (a,b) VALUES (10000,3); SELECT * FROM t1; DROP TABLE t1; CREATE TABLE t1 (a INT, b INT, l VARCHAR(10), c VARCHAR(30) DEFAULT FORMAT(a,b,l)); +SHOW CREATE TABLE t1; INSERT INTO t1 (a,b,l) VALUES (10000,2,'no_NO'),(10000,2,'ru_RU'),(10000,2,'ar_BH'); SELECT * FROM t1; DROP TABLE t1; CREATE TABLE t1 (a VARCHAR(10), b VARCHAR(20) DEFAULT GET_FORMAT(DATE,a)); +SHOW CREATE TABLE t1; INSERT INTO t1 (a) VALUES ('EUR'),('USA'),('JIS'),('ISO'),('INTERNAL'); SELECT * FROM t1; DROP TABLE t1; @@ -1537,6 +1668,7 @@ CREATE TABLE t1 ( number_of_bits INT, x VARCHAR(30) DEFAULT EXPORT_SET(bits, v_on, v_off, v_separator, number_of_bits) ); +SHOW CREATE TABLE t1; INSERT INTO t1 VALUES (0x50006,'Y','N','',64,DEFAULT); SELECT * FROM t1; DROP TABLE t1; @@ -1555,6 +1687,7 @@ SELECT * FROM t1; DROP TABLE t1; CREATE TABLE t1 (a INT, b INT, x INT DEFAULT (a XOR b)); +SHOW CREATE TABLE t1; INSERT INTO t1 (a,b) VALUES (0,0),(0,1),(1,0),(1,1); SELECT * FROM t1; DROP TABLE t1; @@ -1566,16 +1699,19 @@ SELECT * FROM t1; DROP TABLE t1; CREATE TABLE t1 (a INT, b INT DEFAULT (a IS FALSE), c INT DEFAULT (a IS NOT FALSE)); +SHOW CREATE TABLE t1; INSERT INTO t1 (a) VALUES (NULL),(0),(1); SELECT * FROM t1; DROP TABLE t1; CREATE TABLE t1 (a INT, b INT DEFAULT (a IS NULL), c INT DEFAULT (a IS NOT NULL)); +SHOW CREATE TABLE t1; INSERT INTO t1 (a) VALUES (NULL),(0),(1); SELECT * FROM t1; DROP TABLE t1; CREATE TABLE t1 (a INT, b INT DEFAULT (a IS UNKNOWN), c INT DEFAULT (a IS NOT UNKNOWN)); +SHOW CREATE TABLE t1; INSERT INTO t1 (a) VALUES (NULL),(0),(1); SELECT * FROM t1; DROP TABLE t1; @@ -1585,41 +1721,49 @@ CREATE TABLE t1 (a INT, ne INT DEFAULT (a<>0), lt INT DEFAULT (a<0), le INT DEFAULT (a<=0), gt INT DEFAULT (a>0), ge INT DEFAULT (a>=0)); +SHOW CREATE TABLE t1; INSERT INTO t1 (a) VALUES (NULL),(-1),(0),(1); SELECT * FROM t1; DROP TABLE t1; CREATE TABLE t1 (a VARCHAR(10), b INT DEFAULT (a LIKE 'a%')); +SHOW CREATE TABLE t1; INSERT INTO t1 (a) VALUES ('AAA'),('aaa'),('bbb'); SELECT * FROM t1; DROP TABLE t1; CREATE TABLE t1 (a VARCHAR(10), b INT DEFAULT (a RLIKE 'a$')); +SHOW CREATE TABLE t1; INSERT INTO t1 (a) VALUES ('AAA'),('aaa'),('bbb'); SELECT * FROM t1; DROP TABLE t1; CREATE TABLE t1 (a VARCHAR(10), b INT DEFAULT (a IN ('aaa','bbb'))); +SHOW CREATE TABLE t1; INSERT INTO t1 (a) VALUES ('AAA'),('aaa'),('bbb'),('ccc'); SELECT * FROM t1; DROP TABLE t1; CREATE TABLE t1 (a VARCHAR(10), b INT DEFAULT (a NOT IN ('aaa','bbb'))); +SHOW CREATE TABLE t1; INSERT INTO t1 (a) VALUES ('AAA'),('aaa'),('bbb'),('ccc'); SELECT * FROM t1; DROP TABLE t1; CREATE TABLE t1 (a VARCHAR(10), b INT DEFAULT (a BETWEEN 'aaa' AND 'bbb')); +SHOW CREATE TABLE t1; INSERT INTO t1 (a) VALUES ('AAA'),('aaa'),('bbb'),('ccc'); SELECT * FROM t1; DROP TABLE t1; CREATE TABLE t1 (a VARCHAR(10), b INT DEFAULT (a NOT BETWEEN 'aaa' AND 'bbb')); +SHOW CREATE TABLE t1; INSERT INTO t1 (a) VALUES ('AAA'),('aaa'),('bbb'),('ccc'); SELECT * FROM t1; DROP TABLE t1; CREATE TABLE t1 (a TEXT DEFAULT UUID()); +SHOW CREATE TABLE t1; INSERT INTO t1 VALUES (); SELECT LENGTH(a)>0 FROM t1; DROP TABLE t1; @@ -1629,21 +1773,25 @@ DROP TABLE t1; --echo # CREATE TABLE t1 (a VARCHAR(10), b INT DEFAULT STRCMP(a,'b')); +SHOW CREATE TABLE t1; INSERT INTO t1 (a) VALUES ('A'),('a'),('B'),('b'),('C'),('c'); SELECT * FROM t1; DROP TABLE t1; CREATE TABLE t1 (a VARCHAR(10), b INT DEFAULT LENGTH(a), c INT DEFAULT CHAR_LENGTH(a), d INT DEFAULT BIT_LENGTH(a)); +SHOW CREATE TABLE t1; INSERT INTO t1 (a) VALUES ('a'),('aa'),('aaa'); SELECT * FROM t1; DROP TABLE t1; CREATE TABLE t1 (a VARCHAR(10), b INT DEFAULT LOCATE('a',a)); +SHOW CREATE TABLE t1; INSERT INTO t1 (a) VALUES ('xa'),('xxa'),('xxxa'); SELECT * FROM t1; DROP TABLE t1; CREATE TABLE t1 (a VARCHAR(10), b INT DEFAULT REGEXP_INSTR(a, 'a')); +SHOW CREATE TABLE t1; INSERT INTO t1 (a) VALUES ('xa'),('xxa'),('xxxa'); SELECT * FROM t1; DROP TABLE t1; @@ -1661,11 +1809,13 @@ CREATE TABLE t1 ); CREATE TABLE t1 (a INT DEFAULT CONNECTION_ID()); +SHOW CREATE TABLE t1; INSERT INTO t1 VALUES(); SELECT a>0 FROM t1; DROP TABLE t1; CREATE TABLE t1 (a VARCHAR(10), b INT DEFAULT COERCIBILITY(a), c INT DEFAULT COERCIBILITY(b)); +SHOW CREATE TABLE t1; INSERT INTO t1 (a) VALUES ('test'); SELECT * FROM t1; DROP TABLE t1; @@ -1678,6 +1828,7 @@ CREATE TABLE t1 ( b VARCHAR(20) DEFAULT CHARSET(a), c VARCHAR(20) DEFAULT COLLATION(a) ); +SHOW CREATE TABLE t1; INSERT INTO t1 (a) VALUES ('test'); SELECT * FROM t1; DROP TABLE t1; @@ -1686,31 +1837,37 @@ DROP TABLE t1; --echo # Hash, compression, encode/decode --echo # CREATE TABLE t1 (a VARCHAR(10), b BIGINT DEFAULT CRC32(a), c TEXT DEFAULT MD5(a)); +SHOW CREATE TABLE t1; INSERT INTO t1 (a) VALUES ('a'); SELECT * FROM t1; DROP TABLE t1; CREATE TABLE t1 (a VARCHAR(10), b TEXT DEFAULT TO_BASE64(a), c TEXT DEFAULT FROM_BASE64(b)); +SHOW CREATE TABLE t1; INSERT INTO t1 (a) VALUES ('aaaabbbb'); SELECT * FROM t1; DROP TABLE t1; CREATE TABLE t1 (a VARCHAR(10), b TEXT DEFAULT HEX(a), c TEXT DEFAULT UNHEX(b)); +SHOW CREATE TABLE t1; INSERT INTO t1 (a) VALUES ('aaaabbbb'); SELECT * FROM t1; DROP TABLE t1; CREATE TABLE t1 (a VARCHAR(10), b TEXT DEFAULT ENCODE(a,'test'), c TEXT DEFAULT DECODE(b,'test')); +SHOW CREATE TABLE t1; INSERT INTO t1 (a) VALUES ('aaaabbbb'); SELECT a, HEX(b), c FROM t1; DROP TABLE t1; CREATE TABLE t1 (a VARCHAR(30), b TEXT DEFAULT PASSWORD(a)); +SHOW CREATE TABLE t1; INSERT INTO t1 (a) VALUES ('notagoodpwd'); SELECT * FROM t1; DROP TABLE t1; CREATE TABLE t1 (a VARCHAR(10), b VARCHAR(30) DEFAULT ENCRYPT(a,123)); +SHOW CREATE TABLE t1; INSERT INTO t1 (a) VALUES ('hello'); SELECT * FROM t1; DROP TABLE t1; @@ -1720,6 +1877,7 @@ CREATE TABLE t1 ( b BLOB DEFAULT AES_ENCRYPT(a, 'passwd'), c TEXT DEFAULT AES_DECRYPT(b, 'passwd') ); +SHOW CREATE TABLE t1; INSERT INTO t1 (a) VALUES ('test'); SELECT c FROM t1; DROP TABLE t1; @@ -1731,4 +1889,5 @@ DROP TABLE t1; --error ER_BAD_DATA CREATE TABLE t1 (a VARCHAR(20) CHARACTER SET latin1 DEFAULT CONCAT('ö')) CHARACTER SET koi8r COLLATE koi8r_bin; CREATE OR REPLACE TABLE t1 (a char(2) default concat('A') COLLATE utf8mb4_unicode_ci); +SHOW CREATE TABLE t1; DROP TABLE t1; diff --git a/mysql-test/t/dyncol.test b/mysql-test/t/dyncol.test index 8745781e003..49b2c5542d3 100644 --- a/mysql-test/t/dyncol.test +++ b/mysql-test/t/dyncol.test @@ -945,6 +945,7 @@ CREATE TABLE t1 ( dyncol1_list TEXT DEFAULT COLUMN_LIST(dyncol1), dyncol1_json TEXT DEFAULT COLUMN_JSON(dyncol1) ); +SHOW CREATE TABLE t1; INSERT INTO t1 (name,value) VALUES ('name0', 'value0'); SELECT value_dyncol0_name0, value_dyncol1_name1 FROM t1; SELECT dyncol2_check, dyncol2_exists_name0, dyncol2_exists_name1 FROM t1; diff --git a/mysql-test/t/func_compress.test b/mysql-test/t/func_compress.test index 5a1757c04c8..50eb14777a7 100644 --- a/mysql-test/t/func_compress.test +++ b/mysql-test/t/func_compress.test @@ -161,6 +161,7 @@ set global max_allowed_packet=default; --echo # MDEV-10134 Add full support for DEFAULT --echo # CREATE TABLE t1 (a TEXT, b BLOB DEFAULT COMPRESS(a), bl INT DEFAULT UNCOMPRESSED_LENGTH(b), a1 TEXT DEFAULT UNCOMPRESS(b)); +SHOW CREATE TABLE t1; INSERT INTO t1 (a) VALUES (REPEAT('a',100)); SELECT bl, a1 FROM t1; DROP TABLE t1; diff --git a/mysql-test/t/func_digest.test b/mysql-test/t/func_digest.test index 1db580ce717..384b238523a 100644 --- a/mysql-test/t/func_digest.test +++ b/mysql-test/t/func_digest.test @@ -507,6 +507,7 @@ CREATE TABLE t1 ( b TEXT DEFAULT SHA(a), c TEXT DEFAULT SHA2(a,224) ); +SHOW CREATE TABLE t1; INSERT INTO t1 (a) VALUES ('abc'); SELECT * FROM t1; DROP TABLE t1; diff --git a/mysql-test/t/func_encrypt.test b/mysql-test/t/func_encrypt.test index 3653e1a5702..22cf878c966 100644 --- a/mysql-test/t/func_encrypt.test +++ b/mysql-test/t/func_encrypt.test @@ -135,6 +135,7 @@ CREATE TABLE t1 ( b BLOB DEFAULT DES_ENCRYPT(a, 'passwd'), c TEXT DEFAULT DES_DECRYPT(b, 'passwd') ); +SHOW CREATE TABLE t1; INSERT INTO t1 (a) VALUES ('test'); SELECT c FROM t1; DROP TABLE t1; diff --git a/mysql-test/t/func_misc.test b/mysql-test/t/func_misc.test index 307f57ac954..f24576cc992 100644 --- a/mysql-test/t/func_misc.test +++ b/mysql-test/t/func_misc.test @@ -1097,6 +1097,7 @@ CREATE TABLE t1 ( c INT DEFAULT IS_IPV4(a), d INT DEFAULT IS_IPV6(a) ); +SHOW CREATE TABLE t1; INSERT INTO t1 (a) VALUES ('192.168.001.001'),('::1'),('xxx'); SELECT * FROM t1; DROP TABLE t1; @@ -1108,6 +1109,7 @@ CREATE TABLE t1 ( b INT DEFAULT IS_IPV4_COMPAT(addr), c INT DEFAULT IS_IPV4_MAPPED(addr) ); +SHOW CREATE TABLE t1; INSERT INTO t1 (str) VALUES ('::FFFF:192.168.0.1'),('::10.0.5.9'); SELECT str, str1, b,c FROM t1; DROP TABLE t1; diff --git a/mysql-test/t/func_weight_string.test b/mysql-test/t/func_weight_string.test index 76f5ba8f00a..ddaf14dc75d 100644 --- a/mysql-test/t/func_weight_string.test +++ b/mysql-test/t/func_weight_string.test @@ -156,6 +156,7 @@ DROP TABLE t1; --echo # MDEV-10134 Add full support for DEFAULT --echo # CREATE TABLE t1 (a VARCHAR(10) CHARACTER SET latin1, b VARBINARY(10) DEFAULT WEIGHT_STRING(a AS CHAR(10))); +SHOW CREATE TABLE t1; INSERT INTO t1 (a) VALUES ('a'); SELECT a, HEX(b) FROM t1; DROP TABLE t1; diff --git a/mysql-test/t/gis.test b/mysql-test/t/gis.test index fe4db10e982..12b3e607e96 100644 --- a/mysql-test/t/gis.test +++ b/mysql-test/t/gis.test @@ -1531,150 +1531,179 @@ DROP TABLE t1,t2; --echo # --echo # ---echo # MDEV-7563 Support CHECK constraint +--echo # MDEV-10134 Add full support for DEFAULT --echo # CREATE TABLE t1 (a POINT, x DOUBLE DEFAULT x(a), y DOUBLE DEFAULT y(a)); +SHOW CREATE TABLE t1; 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)); +SHOW CREATE TABLE t1; 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)); +SHOW CREATE TABLE t1; 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))); +SHOW CREATE TABLE t1; INSERT INTO t1 (g) VALUES (Point(1,0)); SELECT distance FROM t1; DROP TABLE t1; CREATE TABLE t1 (a TEXT, g GEOMETRY DEFAULT GeomFromText(a)); +SHOW CREATE TABLE t1; INSERT INTO t1 (a) VALUES ('point(1 1)'); SELECT AsText(g) FROM t1; DROP TABLE t1; CREATE TABLE t1 (x INT, y INT, g GEOMETRY DEFAULT POINT(x,y)); +SHOW CREATE TABLE t1; INSERT INTO t1 (x,y) VALUES (10,20); SELECT AsText(g) FROM t1; DROP TABLE t1; CREATE TABLE t1 (a GEOMETRY, b GEOMETRY DEFAULT PointN(a,2)); +SHOW CREATE TABLE t1; INSERT INTO t1 (a) VALUES (GeomFromText('LineString(1 1,2 2,3 3)')); SELECT AsText(b) FROM t1; DROP TABLE t1; CREATE TABLE t1 (a GEOMETRY, b GEOMETRY DEFAULT StartPoint(a)); +SHOW CREATE TABLE t1; INSERT INTO t1 (a) VALUES (GeomFromText('LineString(1 1,2 2,3 3)')); SELECT AsText(b) FROM t1; DROP TABLE t1; CREATE TABLE t1 (a GEOMETRY, b GEOMETRY, c GEOMETRY DEFAULT GeometryCollection(a,b)); +SHOW CREATE TABLE t1; INSERT INTO t1 (a,b) VALUES (Point(1,1), Point(2,2)); SELECT AsText(c) FROM t1; DROP TABLE t1; CREATE TABLE t1 (a GEOMETRY, b GEOMETRY DEFAULT GeomFromWKB(AsBinary(a),20)); +SHOW CREATE TABLE t1; INSERT INTO t1 (a) VALUES (GeomFromText('POINT(1 1)', 10)); SELECT AsText(a), SRID(a), AsText(b), SRID(b) FROM t1; DROP TABLE t1; CREATE TABLE t1 (a GEOMETRY, b GEOMETRY DEFAULT BOUNDARY(a)); +SHOW CREATE TABLE t1; INSERT INTO t1 (a) VALUES (GeomFromText('POLYGON((10 10, 10 20, 20 20, 20 10, 10 10))')); SELECT AsText(b) FROM t1; DROP TABLE t1; CREATE TABLE t1 (a GEOMETRY, b GEOMETRY DEFAULT BUFFER(a,10)); +SHOW CREATE TABLE t1; INSERT INTO t1 (a) VALUES (GeomFromText('POLYGON((10 10, 10 20, 20 20, 20 10, 10 10))')); SELECT GeometryType(b) FROM t1; DROP TABLE t1; CREATE TABLE t1 (a GEOMETRY, b GEOMETRY DEFAULT CENTROID(a)); +SHOW CREATE TABLE t1; INSERT INTO t1 (a) VALUES (GeomFromText('POLYGON((10 10, 10 20, 20 20, 20 10, 10 10))')); SELECT AsText(b) FROM t1; DROP TABLE t1; CREATE TABLE t1 (a GEOMETRY, b GEOMETRY DEFAULT ENVELOPE(a)); +SHOW CREATE TABLE t1; INSERT INTO t1 (a) VALUES (GeomFromText('LineString(1 1,4 4)')); SELECT AsText(b) FROM t1; DROP TABLE t1; CREATE TABLE t1 (a GEOMETRY, b GEOMETRY DEFAULT PointOnSurface(a)); +SHOW CREATE TABLE t1; INSERT INTO t1 (a) VALUES (GeomFromText('POLYGON((10 10, 10 20, 20 20, 20 10, 10 10))')); SELECT GeometryType(b) FROM t1; DROP TABLE t1; CREATE TABLE t1 (a GEOMETRY, b GEOMETRY DEFAULT Point(1,1), c GEOMETRY DEFAULT ST_UNION(a,b)); +SHOW CREATE TABLE t1; INSERT INTO t1 (a) VALUES (Point(0,0)); SELECT AsText(c) FROM t1; DROP TABLE t1; CREATE TABLE t1 (a GEOMETRY, b VARCHAR(20) DEFAULT GeometryType(a)); +SHOW CREATE TABLE t1; INSERT INTO t1 (a) VALUES (Point(0, 0)); SELECT b FROM t1; DROP TABLE t1; CREATE TABLE t1 (a GEOMETRY, b INT DEFAULT IsSimple(a)); +SHOW CREATE TABLE t1; INSERT INTO t1 (a) VALUES (Point(0, 0)); SELECT b FROM t1; DROP TABLE t1; CREATE TABLE t1 (a GEOMETRY, b INT DEFAULT IsEmpty(a)); +SHOW CREATE TABLE t1; INSERT INTO t1 (a) VALUES (Point(0, 0)); SELECT b FROM t1; DROP TABLE t1; CREATE TABLE t1 (a GEOMETRY, b INT DEFAULT IsRing(a)); +SHOW CREATE TABLE t1; INSERT INTO t1 (a) VALUES (GeomFromText('LineString(0 0,0 1,1 1,1 0,0 0)')); SELECT b FROM t1; DROP TABLE t1; CREATE TABLE t1 (a GEOMETRY, b INT DEFAULT IsClosed(a)); +SHOW CREATE TABLE t1; INSERT INTO t1 (a) VALUES (GeomFromText('LineString(0 0,0 1,1 1,1 0,0 0)')); SELECT b FROM t1; DROP TABLE t1; CREATE TABLE t1 (a GEOMETRY, b INT DEFAULT Dimension(a)); +SHOW CREATE TABLE t1; INSERT INTO t1 (a) VALUES (Buffer(Point(1,1),1)); SELECT b FROM t1; DROP TABLE t1; CREATE TABLE t1 (a GEOMETRY, b INT DEFAULT NumGeometries(a)); +SHOW CREATE TABLE t1; INSERT INTO t1 (a) VALUES (ST_UNION(Point(1,1),Point(0,0))); SELECT b FROM t1; DROP TABLE t1; CREATE TABLE t1 (a GEOMETRY, b INT DEFAULT NumInteriorRings(a)); +SHOW CREATE TABLE t1; INSERT INTO t1 (a) VALUES (GeomFromText('Polygon((0 0,0 3,3 3,3 0,0 0),(1 1,1 2,2 2,2 1,1 1))')); SELECT b FROM t1; DROP TABLE t1; CREATE TABLE t1 (a GEOMETRY, b INT DEFAULT NumPoints(a)); +SHOW CREATE TABLE t1; INSERT INTO t1 (a) VALUES (LineString(Point(1,1),Point(0,0))); SELECT b FROM t1; DROP TABLE t1; CREATE TABLE t1 (a GEOMETRY, b INT DEFAULT SRID(a)); +SHOW CREATE TABLE t1; INSERT INTO t1 (a) VALUES (GeomFromText('Point(1 1)', 100)); SELECT b FROM t1; DROP TABLE t1; CREATE TABLE t1 (a GEOMETRY, b GEOMETRY, c INT DEFAULT MBRDisjoint(a,b)); +SHOW CREATE TABLE t1; INSERT INTO t1 (a,b) VALUES (Point(1,1),Point(1,1)); SELECT c FROM t1; DROP TABLE t1; CREATE TABLE t1 (a GEOMETRY, b GEOMETRY, c INT DEFAULT ST_Disjoint(a,b)); +SHOW CREATE TABLE t1; INSERT INTO t1 (a,b) VALUES (Point(1,1),Point(1,1)); SELECT c FROM t1; DROP TABLE t1; CREATE TABLE t1 (a GEOMETRY, b GEOMETRY, c INT DEFAULT ST_Relate(a,b,'T*F**FFF*')); +SHOW CREATE TABLE t1; INSERT INTO t1 (a,b) VALUES (Point(1,1),Point(1,1)); SELECT c FROM t1; DROP TABLE t1; diff --git a/mysql-test/t/grant.test b/mysql-test/t/grant.test index 7bcc7c5a28a..b816afed851 100644 --- a/mysql-test/t/grant.test +++ b/mysql-test/t/grant.test @@ -2183,6 +2183,7 @@ set GLOBAL sql_mode=default; CREATE TABLE t1 (a VARCHAR(30) DEFAULT USER()); +SHOW CREATE TABLE t1; INSERT INTO t1 VALUES (); GRANT ALL PRIVILEGES ON test.* TO dummy@localhost IDENTIFIED BY 'pwd'; connect (conn1,localhost,dummy,pwd,test); diff --git a/mysql-test/t/statistics.test b/mysql-test/t/statistics.test index 7908fda0ced..d8b56b9c0a3 100644 --- a/mysql-test/t/statistics.test +++ b/mysql-test/t/statistics.test @@ -751,6 +751,7 @@ set use_stat_tables=@save_use_stat_tables; --echo # CREATE TABLE t1 (a BLOB, b TEXT DEFAULT DECODE_HISTOGRAM('SINGLE_PREC_HB',a)); +SHOW CREATE TABLE t1; INSERT INTO t1 (a) VALUES (0x0000000000000000000000000101010101010101010202020303030304040404050505050606070707080809090A0A0B0C0D0D0E0E0F10111213131415161718191B1C1E202224292A2E33373B4850575F6A76818C9AA7B9C4CFDADFE5EBF0F4F8FAFCFF); SELECT b FROM t1; DROP TABLE t1; diff --git a/mysql-test/t/udf.test b/mysql-test/t/udf.test index bbf08981802..42a813b0782 100644 --- a/mysql-test/t/udf.test +++ b/mysql-test/t/udf.test @@ -520,6 +520,7 @@ DROP TABLE t1; eval CREATE FUNCTION metaphon RETURNS STRING SONAME "$UDF_EXAMPLE_SO"; SELECT METAPHON('Hello'); CREATE TABLE t1 (a VARCHAR(10), b VARCHAR(10) DEFAULT METAPHON(a)); +SHOW CREATE TABLE t1; INSERT INTO t1 (a) VALUES ('Hello'); SELECT * FROM t1; DROP FUNCTION METAPHON; diff --git a/sql/item_cmpfunc.h b/sql/item_cmpfunc.h index 33e061072da..4a2948e4439 100644 --- a/sql/item_cmpfunc.h +++ b/sql/item_cmpfunc.h @@ -2422,6 +2422,7 @@ public: Item_func_dyncol_check(THD *thd, Item *str): Item_bool_func(thd, str) {} longlong val_int(); const char *func_name() const { return "column_check"; } + bool need_parentheses_in_default() { return false; } }; class Item_func_dyncol_exists :public Item_bool_func @@ -2431,6 +2432,7 @@ public: Item_bool_func(thd, str, num) {} longlong val_int(); const char *func_name() const { return "column_exists"; } + bool need_parentheses_in_default() { return false; } }; inline bool is_cond_or(Item *item) diff --git a/sql/item_geofunc.h b/sql/item_geofunc.h index cb5322a6b93..cde61bd38ca 100644 --- a/sql/item_geofunc.h +++ b/sql/item_geofunc.h @@ -299,6 +299,7 @@ public: return add_key_fields_optimize_op(join, key_fields, and_level, usable_tables, sargables, false); } + bool need_parentheses_in_default() { return false; } }; @@ -339,6 +340,7 @@ public: { } longlong val_int(); const char *func_name() const { return "st_relate"; } + bool need_parentheses_in_default() { return false; } }; @@ -429,6 +431,7 @@ public: longlong val_int(); const char *func_name() const { return "st_isempty"; } void fix_length_and_dec() { maybe_null= 1; } + bool need_parentheses_in_default() { return false; } }; class Item_func_issimple: public Item_int_func diff --git a/sql/item_inetfunc.h b/sql/item_inetfunc.h index 82a4405df1e..f5a0596d860 100644 --- a/sql/item_inetfunc.h +++ b/sql/item_inetfunc.h @@ -76,6 +76,7 @@ public: public: virtual longlong val_int(); + bool need_parentheses_in_default() { return false; } protected: virtual bool calc_value(const String *arg) = 0; |