mysql-server/mysql-test/t/default_as_expr.test

--echo #
--echo # CREATE TABLE - column with func as DEFAULT then test DROP column.
--echo #
CREATE TABLE t1 (i int, b JSON DEFAULT (JSON_OBJECT("key", i)));
INSERT INTO t1(i) VALUES (1);
INSERT INTO t1 SET i = 10;
INSERT INTO t1(i, b) VALUES (2, DEFAULT);
INSERT INTO t1 SET i = 20, b = DEFAULT;
INSERT INTO t1(i, b) VALUES (3, JSON_OBJECT("key", 3));
INSERT INTO t1 SET i = 30, b = JSON_OBJECT("key", 30);
SELECT * FROM t1;
ALTER TABLE t1 DROP COLUMN b;
DROP TABLE t1;

--echo #
--echo # CREATE TABLE - column with INDEX and func as DEFAULT
--echo #
CREATE TABLE t1 (i int, b char(255) DEFAULT (sha2(i, 0)), INDEX (b(10)));
INSERT INTO t1(i) VALUES (1);
INSERT INTO t1(i, b) VALUES (2, DEFAULT);
INSERT INTO t1(i, b) VALUES (3, "some string");
SELECT * FROM t1;
DROP TABLE t1;

--echo #
--echo # Test ALTER table ADD column with DEFAULT
--echo #
CREATE TABLE t1 (i int);
INSERT INTO t1(i) VALUES (1),(2);
ALTER TABLE t1 ADD COLUMN b JSON DEFAULT (JSON_OBJECT("key",i));
INSERT INTO t1(i) VALUES (3);
INSERT INTO t1(i, b) VALUES (4, DEFAULT);
INSERT INTO t1(i, b) VALUES (5, JSON_OBJECT("key", 5));
SELECT * FROM t1;
DROP TABLE t1;

--echo #
--echo # Test ALTER table ADD column with DEFAULT then ALTER TABLE SET DEFAULT func
--echo #
CREATE TABLE t1 (i int);
INSERT INTO t1(i) VALUES (1),(2);
ALTER TABLE t1 ADD COLUMN b JSON;
ALTER TABLE t1 ALTER COLUMN b SET DEFAULT (JSON_OBJECT("key",i));
INSERT INTO t1(i) VALUES (3);
INSERT INTO t1(i, b) VALUES (4, DEFAULT);
INSERT INTO t1(i, b) VALUES (5, JSON_OBJECT("key", 5));
SELECT * FROM t1;
DROP TABLE t1;


--echo #
--echo # Test ALTER table CHANGE column
--echo #
CREATE TABLE t1 (i int);
INSERT INTO t1(i) VALUES (1),(2);
ALTER TABLE t1 ADD COLUMN b JSON DEFAULT (JSON_ARRAY());
INSERT INTO t1(i) VALUES (4);
ALTER TABLE t1 CHANGE COLUMN b new_b JSON DEFAULT (JSON_OBJECT("key",i));
INSERT INTO t1(i) VALUES (5);
INSERT INTO t1(i, new_b) VALUES (6, DEFAULT);
INSERT INTO t1(i, new_b) VALUES (7, JSON_OBJECT("key", 7));
SELECT * FROM t1;
DROP TABLE t1;

CREATE TABLE t1 (i int, b int DEFAULT (123 * 1));
--error ER_DEFAULT_VAL_GENERATED_NON_PRIOR
ALTER TABLE t1 CHANGE COLUMN i new_i JSON DEFAULT (JSON_ARRAY(b));
DROP TABLE t1;

--echo #
--echo # Test ALTER table MODIFY column
--echo #
CREATE TABLE t1 (i int);
INSERT INTO t1(i) VALUES (1),(2);
ALTER TABLE t1 ADD COLUMN b JSON DEFAULT (JSON_ARRAY());
INSERT INTO t1(i) VALUES (4);
ALTER TABLE t1 MODIFY COLUMN b JSON DEFAULT (JSON_OBJECT("key",i)) FIRST;
INSERT INTO t1(i) VALUES (5);
INSERT INTO t1(i, b) VALUES (6, DEFAULT);
INSERT INTO t1(i, b) VALUES (7, JSON_OBJECT("key", 7));
SELECT * FROM t1;
DROP TABLE t1;

CREATE TABLE t1 (i int, b int DEFAULT (123 * 1));
--error ER_DEFAULT_VAL_GENERATED_NON_PRIOR
ALTER TABLE t1 MODIFY COLUMN i JSON DEFAULT (JSON_ARRAY(b)) FIRST;
DROP TABLE t1;

--echo #
--echo # Column order with expression or literal as default value
--echo #
CREATE TABLE t1 (i JSON DEFAULT (JSON_ARRAY(b)), b int DEFAULT 123);
DROP TABLE t1;
CREATE TABLE t1 (b int DEFAULT 123, i JSON DEFAULT (JSON_ARRAY(b)));
DROP TABLE t1;
--error ER_DEFAULT_VAL_GENERATED_NON_PRIOR
CREATE TABLE t1 (i JSON DEFAULT (JSON_ARRAY(b)), b int DEFAULT (123 * 1));
CREATE TABLE t1 (b int DEFAULT (123 * 1), i JSON DEFAULT (JSON_ARRAY(b)));
DROP TABLE t1;

--echo #
--echo # Test CREATE TABLE with column without default then SET a DEFAULT.
--echo # ALSO TEST DROP DEFAULT
--echo #
CREATE TABLE t1 (i int, b JSON);
INSERT INTO t1(i) VALUES (1),(2);
ALTER TABLE t1 ALTER COLUMN b SET DEFAULT (JSON_OBJECT("key",i));
INSERT INTO t1(i) VALUES (3);
INSERT INTO t1(i, b) VALUES (4, DEFAULT);
INSERT INTO t1(i, b) VALUES (5, JSON_OBJECT("key", 5));
ALTER TABLE t1 ALTER COLUMN b DROP DEFAULT;
INSERT INTO t1(i, b) VALUES (6, NULL);
SELECT * FROM t1;
DROP TABLE t1;

--echo #
--echo # Test func as DEFAULT with auto_increment
--echo #
--error ER_DEFAULT_VAL_GENERATED_REF_AUTO_INC
CREATE TABLE t1 (id int(11) PRIMARY KEY auto_increment,
                 f1 JSON DEFAULT (JSON_OBJECT("key", id)));

--echo #
--echo # Test with not-allowed function
--echo #
--error ER_DEFAULT_VAL_GENERATED_NAMED_FUNCTION_IS_NOT_ALLOWED
CREATE TABLE t1 (a varchar(64), b varchar(1024) DEFAULT (load_file(a)));

--echo #
--echo # Test func as DEFAULT with different order
--echo #
CREATE TABLE t1 (f1 JSON DEFAULT (JSON_OBJECT("key", id)), id int(11));
INSERT INTO t1(id) VALUES(1), (2), (3);
SELECT * FROM t1;
DROP TABLE t1;

--echo #
--echo # Test function set as DEFAULT value which exceeds size
--echo #
CREATE TABLE t1 (id char(2) DEFAULT (uuid()));
--error ER_DATA_TOO_LONG
INSERT INTO t1 VALUES (),(),();
DROP TABLE t1;


--echo #
--echo # Test generated funcs with default value.
--echo #
-- error ER_WRONG_USAGE
CREATE TABLE t3 (a INT PRIMARY KEY,
                 b INT GENERATED ALWAYS AS (-a) VIRTUAL UNIQUE DEFAULT (-1 * 128));
-- error ER_WRONG_USAGE
CREATE TABLE t3 (a INT PRIMARY KEY,
                 c INT GENERATED ALWAYS AS (-a) STORED DEFAULT (-1 * 128));

--echo #
--echo # Test SHOW CREATE TABLE, DESCRIBE TABLE and CREATE as SELECT
--echo #
CREATE TABLE t1 (id char(36) DEFAULT (uuid()));
INSERT INTO t1 VALUES (),(),();
CREATE TABLE t2 as SELECT * from t1;
SHOW CREATE TABLE t2;
CREATE TABLE t3 LIKE t1;
SHOW CREATE TABLE t3;
# select just length as results are different for each run
SELECT LENGTH(id) FROM t1;
SHOW CREATE TABLE t1;
SHOW COLUMNS FROM t1;
DESCRIBE t1;
DROP TABLE t1;
DROP TABLE t2;
DROP TABLE t3;


--echo #
--echo # Create table with DEFAULT()
--echo #
--error ER_DEFAULT_AS_VAL_GENERATED
CREATE TABLE t3 (a INT PRIMARY KEY,
d INT DEFAULT (-a + 1),
c INT DEFAULT (DEFAULT(d))
);

--echo #
--echo # DROP column that it depends on
--echo #
CREATE TABLE t3 (a INT PRIMARY KEY,
d INT DEFAULT (-a + 1),
c INT DEFAULT (-d)
);

--error ER_DEFAULT_AS_VAL_GENERATED
SELECT DEFAULT(d) from t3;
--error ER_DEFAULT_AS_VAL_GENERATED
SELECT DEFAULT(c) from t3;
--error ER_DEPENDENT_BY_DEFAULT_GENERATED_VALUE
ALTER TABLE t3 DROP COLUMN d;
DROP TABLE t3;

--echo #
--echo # Test utf8mb3
--echo #
CREATE TABLE `t1` (i varchar(200) DEFAULT (_utf8mb4"\U+1F9DB♀"));
SELECT COLUMN_NAME, COLUMN_DEFAULT, DATA_TYPE, EXTRA, GENERATION_EXPRESSION
FROM information_schema.columns WHERE table_name= "t1";
INSERT INTO t1 values (),();
SELECT * from t1;
DESCRIBE t1;
DROP TABLE t1;

--echo #
--echo # Test with REPLACE INTO ..
--echo #

CREATE TABLE test (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  data VARCHAR(64) DEFAULT NULL,
  something VARCHAR(64) NOT NULL DEFAULT (CONCAT ('[', data, ']')),
  PRIMARY KEY (id)
);

REPLACE INTO test VALUES (1, 'Old', DEFAULT);
SELECT * FROM test;
REPLACE INTO test VALUES (1, 'New', DEFAULT);
SELECT * FROM test;
DROP TABLE test;

--echo #
--echo # TINYBLOB  tests
--echo #

CREATE TABLE t(i INT, b TINYBLOB  DEFAULT (repeat('b', i)));
SHOW CREATE TABLE t;
INSERT INTO t values(254, DEFAULT);
INSERT INTO t values(255, DEFAULT);
-- error ER_DATA_TOO_LONG
INSERT INTO t values(256, DEFAULT);
SELECT i, length(b) FROM t;
DROP TABLE t;

CREATE TABLE t(i INT);
INSERT INTO t values(254), (255), (256);
-- error ER_DATA_TOO_LONG
ALTER TABLE t ADD COLUMN b TINYBLOB DEFAULT (repeat('b', i));
SELECT * FROM t;
DELETE FROM t where i = 256;
ALTER TABLE t ADD COLUMN b TINYBLOB;
SELECT i, length(b) FROM t;
ALTER TABLE t ALTER COLUMN b SET DEFAULT (repeat('b', i));
SELECT i, length(b) FROM t;
INSERT INTO t values(254, DEFAULT);
INSERT INTO t values(255, DEFAULT);
-- error ER_DATA_TOO_LONG
INSERT INTO t values(256, DEFAULT);
ALTER TABLE t ALTER COLUMN b DROP DEFAULT;
-- error ER_NO_DEFAULT_FOR_FIELD
INSERT INTO t(i) values(128);
SELECT i, length(b) FROM t;
DROP TABLE t;

--echo #
--echo # MEDIUMBLOB  tests
--echo #

SET GLOBAL max_allowed_packet = 1073741824;

CREATE TABLE t(i BIGINT, b MEDIUMBLOB  DEFAULT (repeat('b', i)));
SHOW CREATE TABLE t;
INSERT INTO t values(16777214, DEFAULT);
INSERT INTO t values(16777215, DEFAULT);
-- error ER_DATA_TOO_LONG
INSERT INTO t values(16777216, DEFAULT);
SELECT i, length(b) FROM t;
DROP TABLE t;

CREATE TABLE t(i BIGINT);
INSERT INTO t values(16777214), (16777215), (16777216);
-- error ER_DATA_TOO_LONG
ALTER TABLE t ADD COLUMN b MEDIUMBLOB DEFAULT (repeat('b', i));
SELECT * FROM t;
DELETE FROM t where i = 16777216;
ALTER TABLE t ADD COLUMN b MEDIUMBLOB;
SELECT i, length(b) FROM t;
ALTER TABLE t ALTER COLUMN b SET DEFAULT (repeat('b', i));
SELECT i, length(b) FROM t;
INSERT INTO t values(16777214, DEFAULT);
INSERT INTO t values(16777215, DEFAULT);
-- error ER_DATA_TOO_LONG
INSERT INTO t values(16777216, DEFAULT);
ALTER TABLE t ALTER COLUMN b DROP DEFAULT;
-- error ER_NO_DEFAULT_FOR_FIELD
INSERT INTO t(i) values(128);
SELECT i, length(b) FROM t;
DROP TABLE t;

SET GLOBAL max_allowed_packet=default;

--echo #
--echo # tests with BLOB
--echo #

CREATE TABLE t(i INT, b BLOB DEFAULT (repeat('b', i)));
SHOW CREATE TABLE t;
INSERT INTO t values(65534, DEFAULT);
INSERT INTO t values(65535, DEFAULT);
-- error ER_DATA_TOO_LONG
INSERT INTO t values(65536, DEFAULT);
SELECT i, length(b) FROM t;
DROP TABLE t;

CREATE TABLE t(i INT);
INSERT INTO t values(65534), (65535), (65536);
-- error ER_DATA_TOO_LONG
ALTER TABLE t ADD COLUMN b BLOB DEFAULT (repeat('b', i));
SELECT * FROM t;
DELETE FROM t where i = 65536;
ALTER TABLE t ADD COLUMN b BLOB;
SELECT i, length(b) FROM t;
ALTER TABLE t ALTER COLUMN b SET DEFAULT (repeat('b', i));
SELECT i, length(b) FROM t;
INSERT INTO t values(65534, DEFAULT);
INSERT INTO t values(65535, DEFAULT);
-- error ER_DATA_TOO_LONG
INSERT INTO t values(65536, DEFAULT);
ALTER TABLE t ALTER COLUMN b DROP DEFAULT;
-- error ER_NO_DEFAULT_FOR_FIELD
INSERT INTO t(i) values(128);
SELECT i, length(b) FROM t;
DROP TABLE t;

--echo #
--echo # MEDIUMBLOB  tests
--echo # max value for max_allowed_packet is 1073741824 which is < 4294967295
--echo #

CREATE TABLE t(i BIGINT, b LONGBLOB  DEFAULT (repeat('b', i)));
SHOW CREATE TABLE t;
-- error ER_WARN_ALLOWED_PACKET_OVERFLOWED
INSERT INTO t values(4294967295, DEFAULT);
DROP TABLE t;

CREATE TABLE t(i INT, b BLOB DEFAULT (repeat('b', i)));
INSERT INTO t values(65534, DEFAULT);
INSERT INTO t values(65535, DEFAULT);
-- error ER_DATA_TOO_LONG
INSERT INTO t values(65536, DEFAULT);
SELECT i, length(b) FROM t;
DROP TABLE t;

CREATE TABLE t(i INT);
INSERT INTO t values(65534), (65535), (65536);
-- error ER_DATA_TOO_LONG
ALTER TABLE t ADD COLUMN b BLOB DEFAULT (repeat('b', i));
SELECT * FROM t;
DELETE FROM t where i = 65536;
ALTER TABLE t ADD COLUMN b BLOB;
SELECT i, length(b) FROM t;
ALTER TABLE t ALTER COLUMN b SET DEFAULT (repeat('b', i));
SELECT i, length(b) FROM t;
INSERT INTO t values(65534, DEFAULT);
INSERT INTO t values(65535, DEFAULT);
-- error ER_DATA_TOO_LONG
INSERT INTO t values(65536, DEFAULT);
ALTER TABLE t ALTER COLUMN b DROP DEFAULT;
-- error ER_NO_DEFAULT_FOR_FIELD
INSERT INTO t(i) values(128);
SELECT i, length(b) FROM t;
DROP TABLE t;


--echo #
--echo # Test other functions
--echo #
CREATE TABLE t(i INT, b VARCHAR(20) DEFAULT (repeat('b', i)));
INSERT INTO t values(14, DEFAULT);
INSERT INTO t values(16, DEFAULT);
SELECT * FROM t;
DROP TABLE t;

CREATE TABLE t1(a INT PRIMARY KEY, b GEOMETRY NOT NULL DEFAULT
                (ST_GEOMFROMTEXT('LINESTRING(0 0,9.299720368548e18 0,0 0,0 0)')));
INSERT INTO t1 VALUES(1, DEFAULT);
INSERT INTO t1 VALUES(2, DEFAULT);
INSERT INTO t1 VALUES(3, (ST_GEOMFROMTEXT('LINESTRING(0 0,9.2234818 0,0 0,0 0)')));
SELECT a, ST_AsText(b) FROM t1;
DROP TABLE t1;


--echo #
--echo # Test with TIMESTAMP with and without ON UPDATE
--echo #
CREATE TABLE t1 (a INT, b TIMESTAMP DEFAULT (TIMESTAMPADD(MINUTE, 1,'2003-01-02'))
                 ON UPDATE NOW());
SHOW CREATE TABLE t1;
INSERT INTO t1(a) VALUES (5);
SELECT * FROM t1;
SET timestamp= 1038401397;
UPDATE t1 SET a = 10 WHERE a = 5;
SELECT * FROM t1;
DROP TABLE t1;


CREATE TABLE t1 (a INT, b TIMESTAMP NOT NULL DEFAULT (TIMESTAMPADD(MINUTE, 1,'2003-01-02')));
SHOW CREATE TABLE t1;
insert into t1(a) values (1);
DROP TABLE t1;

CREATE TABLE t1 (a INT, c TIMESTAMP DEFAULT (TIMESTAMPADD(MINUTE, 5,'2003-01-02')));
SHOW CREATE TABLE t1;
INSERT INTO t1(a) VALUES (5),(6);
ALTER TABLE t1 ADD COLUMN d TIMESTAMP DEFAULT (TIMESTAMPADD(MINUTE, 10,'2003-01-03')) ON UPDATE CURRENT_TIMESTAMP;
SHOW CREATE TABLE t1;
SELECT * from t1;
UPDATE t1 SET a = 10 WHERE a = 5;
SELECT * from t1;
ALTER TABLE t1 DROP COLUMN d;
ALTER TABLE t1 ADD COLUMN d TIMESTAMP DEFAULT (TIMESTAMPADD(MINUTE, 15,'2003-01-04'));
SELECT * from t1;
DROP TABLE t1;

--echo #
--echo # Check default expressions together with Generated Columns
--echo #
CREATE TABLE t1 (i INT, b char(255) DEFAULT (sha2(i, 0)));
SHOW COLUMNS FROM t1;
INSERT INTO t1(i) VALUES (1);
INSERT INTO t1(i, b) VALUES (2, DEFAULT);
INSERT INTO t1(i, b) VALUES (3, "some string");
INSERT INTO t1(i, b) VALUES (NULL, DEFAULT);
SELECT * FROM t1;
DROP TABLE t1;

CREATE TABLE t1 (i INT, b char(255) DEFAULT (sha2(i, 0)), j INT GENERATED ALWAYS AS (i*2));
SHOW COLUMNS FROM t1;
INSERT INTO t1(i) VALUES (1);
INSERT INTO t1(i, b) VALUES (2, DEFAULT);
INSERT INTO t1(i, b) VALUES (3, "some string");
INSERT INTO t1(i, b) VALUES (NULL, DEFAULT);
SELECT * FROM t1;
DROP TABLE t1;

--echo #
--echo # Make sure there's no memory leaking after failed ALTER TABLE.
--echo # (coverage for err tag in open_table_from_share)
--echo #
CREATE TABLE t(a int);
ALTER TABLE t ADD COLUMN b int DEFAULT(
date_sub(a, INTERVAL A MONTH));
--error ER_INVALID_GROUP_FUNC_USE
ALTER TABLE t ADD COLUMN c int DEFAULT (SUM(a));
DROP TABLE t;

--echo # Function that are now allowed.
--echo # result is not shown as the data is non-deterministic

CREATE TABLE t1 (b double DEFAULT (rand()));
INSERT INTO t1 VALUES ();
--disable_result_log
SELECT * FROM t1;
--enable_result_log
DROP TABLE t1;

CREATE TABLE t1 (a varchar(30), b VARCHAR(100) DEFAULT (statement_digest(a)));
INSERT INTO t1 (a) VALUES ("SELECT 1;");
--disable_result_log
SELECT * FROM t1;
--enable_result_log
DROP TABLE t1;

CREATE TABLE t1 (a varchar(30), b varchar(100) DEFAULT (statement_digest_text(a)));
INSERT INTO t1 (a) VALUES ("SELECT 2;");
--disable_result_log
SELECT * FROM t1;
--enable_result_log
DROP TABLE t1;

CREATE TABLE t1 (a datetime DEFAULT (curdate()));
INSERT INTO t1 VALUES ();
--disable_result_log
SELECT * FROM t1;
--enable_result_log
DROP TABLE t1;

CREATE TABLE t1 (a datetime DEFAULT (current_date()));
INSERT INTO t1 VALUES ();
--disable_result_log
SELECT * FROM t1;
--enable_result_log
DROP TABLE t1;

CREATE TABLE t1 (a datetime DEFAULT (current_date));
INSERT INTO t1 VALUES ();
--disable_result_log
SELECT * FROM t1;
--enable_result_log
DROP TABLE t1;

CREATE TABLE t1 (a datetime DEFAULT (current_time()));
INSERT INTO t1 VALUES ();
--disable_result_log
SELECT * FROM t1;
--enable_result_log
DROP TABLE t1;

CREATE TABLE t1 (a datetime DEFAULT (current_time));
INSERT INTO t1 VALUES ();
--disable_result_log
SELECT * FROM t1;
--enable_result_log
DROP TABLE t1;

CREATE TABLE t1 (a datetime DEFAULT (current_timestamp()));
INSERT INTO t1 VALUES ();
--disable_result_log
SELECT * FROM t1;
--enable_result_log
DROP TABLE t1;

CREATE TABLE t1 (a datetime DEFAULT (current_timestamp));
INSERT INTO t1 VALUES ();
--disable_result_log
SELECT * FROM t1;
--enable_result_log
DROP TABLE t1;

CREATE TABLE t1 (a datetime DEFAULT (localtime()));
INSERT INTO t1 VALUES ();
--disable_result_log
SELECT * FROM t1;
--enable_result_log
DROP TABLE t1;

CREATE TABLE t1 (a datetime DEFAULT (localtime));
INSERT INTO t1 VALUES ();
--disable_result_log
SELECT * FROM t1;
--enable_result_log
DROP TABLE t1;

CREATE TABLE t1 (a datetime DEFAULT (curtime()));
INSERT INTO t1 VALUES ();
--disable_result_log
SELECT * FROM t1;
--enable_result_log
DROP TABLE t1;

CREATE TABLE t1 (a datetime, b varchar(20) DEFAULT (localtimestamp()));
INSERT INTO t1(a) VALUES (now());
--disable_result_log
SELECT * FROM t1;
--enable_result_log
DROP TABLE t1;

CREATE TABLE t1 (a datetime, b varchar(20) DEFAULT (localtimestamp));
INSERT INTO t1(a) VALUES (now());
--disable_result_log
SELECT * FROM t1;
--enable_result_log
DROP TABLE t1;

CREATE TABLE t1 (a datetime, b varchar(20) DEFAULT (now()));
INSERT INTO t1(a) VALUES (now());
--disable_result_log
SELECT * FROM t1;
--enable_result_log
DROP TABLE t1;

CREATE TABLE t1 (b varchar(100) DEFAULT (sysdate()));
INSERT INTO t1() VALUES ();
--disable_result_log
SELECT * FROM t1;
--enable_result_log
DROP TABLE t1;

CREATE TABLE t1 (a datetime, b INT DEFAULT (unix_timestamp()));
INSERT INTO t1() VALUES ();
--disable_result_log
SELECT * FROM t1;
--enable_result_log
DROP TABLE t1;

CREATE TABLE t1 (a datetime, b datetime DEFAULT (utc_date()));
INSERT INTO t1(a) VALUES (now());
--disable_result_log
SELECT * FROM t1;
--enable_result_log
DROP TABLE t1;

CREATE TABLE t1 (a datetime, b datetime DEFAULT (utc_time()));
INSERT INTO t1(a) VALUES (now());
--disable_result_log
SELECT * FROM t1;
--enable_result_log
DROP TABLE t1;

CREATE TABLE t1 (a datetime, b datetime DEFAULT (utc_timestamp()));
INSERT INTO t1(a) VALUES (now());
--disable_result_log
SELECT * FROM t1;
--enable_result_log
DROP TABLE t1;

CREATE TABLE t1 (a int DEFAULT (connection_id()));
INSERT INTO t1 VALUES ();
--disable_result_log
SELECT * FROM t1;
--enable_result_log
DROP TABLE t1;

CREATE TABLE t1 (a varchar(1024), b varchar(1024) DEFAULT (database()));
INSERT INTO t1 VALUES ();
--disable_result_log
SELECT * FROM t1;
--enable_result_log
DROP TABLE t1;

CREATE TABLE t1 (a varchar(288) DEFAULT (CURRENT_USER()));
INSERT INTO t1 VALUES ();
--disable_result_log
SELECT * FROM t1;
--enable_result_log
DROP TABLE t1;

CREATE TABLE t1 (a varchar(288) DEFAULT (SESSION_USER()));
INSERT INTO t1 VALUES ();
--disable_result_log
SELECT * FROM t1;
--enable_result_log
DROP TABLE t1;

CREATE TABLE t1 (a varchar(288) DEFAULT (USER()));
INSERT INTO t1 VALUES ();
--disable_result_log
SELECT * FROM t1;
--enable_result_log
DROP TABLE t1;

--echo # Function that are still not allowed
-- error ER_DEFAULT_VAL_GENERATED_NAMED_FUNCTION_IS_NOT_ALLOWED
CREATE TABLE t1 (a varchar(64), b varchar(1024) DEFAULT (load_file(a)));
-- error ER_DEFAULT_VAL_GENERATED_NAMED_FUNCTION_IS_NOT_ALLOWED
CREATE TABLE t1 (a varchar(1024), b varchar(1024) DEFAULT (found_rows()));
-- error ER_DEFAULT_VAL_GENERATED_NAMED_FUNCTION_IS_NOT_ALLOWED
CREATE TABLE t1 (a varchar(1024), b varchar(1024) DEFAULT (get_lock(a,10)));
-- error ER_DEFAULT_VAL_GENERATED_NAMED_FUNCTION_IS_NOT_ALLOWED
CREATE TABLE t1 (a varchar(1024), b varchar(1024) DEFAULT (is_free_lock(a)));
-- error ER_DEFAULT_VAL_GENERATED_NAMED_FUNCTION_IS_NOT_ALLOWED
CREATE TABLE t1 (a varchar(1024), b varchar(1024) DEFAULT (is_used_lock(a)));
-- error ER_DEFAULT_VAL_GENERATED_NAMED_FUNCTION_IS_NOT_ALLOWED
CREATE TABLE t1 (a int DEFAULT (last_insert_id()));
-- error ER_DEFAULT_VAL_GENERATED_NAMED_FUNCTION_IS_NOT_ALLOWED
CREATE TABLE t1 (a int DEFAULT (row_count()));
-- error ER_DEFAULT_VAL_GENERATED_NAMED_FUNCTION_IS_NOT_ALLOWED
CREATE TABLE t1 (a int, b int DEFAULT (sleep(a)));
-- error ER_DEFAULT_VAL_GENERATED_NAMED_FUNCTION_IS_NOT_ALLOWED
CREATE TABLE t1 (a varchar(1024), b varchar(1024) DEFAULT (version()));

-- error ER_INVALID_DEFAULT
CREATE TABLE t1 (id char(40) DEFAULT (uuid()) DEFAULT 4);
-- error ER_INVALID_DEFAULT
CREATE TABLE t1 (id char(40) DEFAULT 4 DEFAULT (uuid()));
-- error ER_INVALID_DEFAULT
CREATE TABLE t1 (id char(40) DEFAULT 4 DEFAULT (uuid()) DEFAULT 10);
CREATE TABLE t1 (id char(40) DEFAULT 4 DEFAULT 5);
DROP TABLE t1;

DELIMITER |;
CREATE FUNCTION CURRENT_TIMESTAMPfoo() RETURNS INT BEGIN RETURN 1; END|
DELIMITER ;|
-- error ER_DEFAULT_VAL_GENERATED_NAMED_FUNCTION_IS_NOT_ALLOWED
CREATE TABLE t1 (a INT DEFAULT (CURRENT_TIMESTAMPfoo()));
DROP FUNCTION CURRENT_TIMESTAMPfoo;

-- error ER_DEFAULT_VAL_GENERATED_FUNCTION_IS_NOT_ALLOWED
CREATE TABLE t1 (a VARCHAR(32) DEFAULT (NAME_CONST('test',1)));

-- error ER_DEFAULT_VAL_GENERATED_ROW_VALUE
CREATE TABLE t1 (a VARCHAR(32) DEFAULT ((1,1)));

-- error ER_DEFAULT_VAL_GENERATED_NAMED_FUNCTION_IS_NOT_ALLOWED
CREATE TABLE t1 (a VARCHAR(1024), b VARCHAR(1024) DEFAULT (VALUES(a)));

--echo #
--echo # Stored procedures/functions
--echo #
DELIMITER //;
CREATE PROCEDURE p1()
BEGIN
  SELECT 42;
END //

CREATE FUNCTION f1()
RETURNS INT
BEGIN
 RETURN 42;
END //

DELIMITER ;//

-- error ER_DEFAULT_VAL_GENERATED_NAMED_FUNCTION_IS_NOT_ALLOWED
CREATE TABLE t1 (a INT DEFAULT (p1()));
-- error ER_DEFAULT_VAL_GENERATED_NAMED_FUNCTION_IS_NOT_ALLOWED
CREATE TABLE t1 (a INT DEFAULT (f1()));

-- error ER_DEFAULT_VAL_GENERATED_NAMED_FUNCTION_IS_NOT_ALLOWED
CREATE TABLE t1 (a INT DEFAULT (1 + f1()));

CREATE TABLE t1 (a INT);
-- error ER_DEFAULT_VAL_GENERATED_NAMED_FUNCTION_IS_NOT_ALLOWED
ALTER TABLE t1 ADD COLUMN b INT DEFAULT (1 + f1());
-- error ER_DEFAULT_VAL_GENERATED_NAMED_FUNCTION_IS_NOT_ALLOWED
ALTER TABLE t1 ALTER COLUMN a SET DEFAULT (1 + f1());
DROP TABLE t1;

DROP PROCEDURE p1;
DROP FUNCTION f1;


-- echo #
-- echo # UDFs
-- echo #

--source include/have_udf.inc
#
# To run this test, "sql/udf_example.cc" need to be compiled into
# udf_example.so and LD_LIBRARY_PATH should be setup to point out where
# the library are. The regular CMake build system takes care of this
# automatically.
#

DROP FUNCTION IF EXISTS metaphon;
--replace_result $UDF_EXAMPLE_LIB UDF_EXAMPLE_LIB
eval CREATE FUNCTION metaphon RETURNS STRING SONAME "$UDF_EXAMPLE_LIB";
-- error ER_DEFAULT_VAL_GENERATED_NAMED_FUNCTION_IS_NOT_ALLOWED
CREATE TABLE t1 (a VARCHAR(128) DEFAULT (metaphon("testval")));
-- error ER_DEFAULT_VAL_GENERATED_NAMED_FUNCTION_IS_NOT_ALLOWED
CREATE TABLE t1 (a VARCHAR(128) DEFAULT (concat("1", metaphon("testval"))));
CREATE TABLE t1 (a VARCHAR(100));
-- error ER_DEFAULT_VAL_GENERATED_NAMED_FUNCTION_IS_NOT_ALLOWED
ALTER TABLE t1 ADD COLUMN b VARCHAR(256) DEFAULT (concat("1", metaphon("testval")));
-- error ER_DEFAULT_VAL_GENERATED_NAMED_FUNCTION_IS_NOT_ALLOWED
ALTER TABLE t1 ALTER COLUMN a SET DEFAULT (concat("1", metaphon("testval")));
DROP TABLE t1;

DROP FUNCTION metaphon;

CREATE TABLE t1 (a INT);
-- error 1064
CREATE TABLE t2 (a INT, b INT DEFAULT (select count(*) from t1));
-- error 1064
CREATE TABLE t2 (a INT, b INT DEFAULT (select * from t1));
-- error 1064
CREATE TABLE t2 (a INT, b INT DEFAULT (select 1));
DROP TABLE t1;

SET @my_var= "something";

-- error ER_DEFAULT_VAL_GENERATED_VARIABLES
CREATE TABLE t1 (i INT, b VARCHAR(256) DEFAULT (@my_var));
-- error ER_DEFAULT_VAL_GENERATED_VARIABLES
CREATE TABLE t1 (i INT, b VARCHAR(256) DEFAULT (@my_var + 1));
-- error ER_DEFAULT_VAL_GENERATED_VARIABLES
CREATE TABLE t1 (i INT, b VARCHAR(256) DEFAULT (@@global.sort_buffer_size));
-- error ER_DEFAULT_VAL_GENERATED_VARIABLES
CREATE TABLE t1 (i INT, b VARCHAR(256) DEFAULT (@@session.sort_buffer_size));

CREATE TABLE t1(i INT);

-- error ER_DEFAULT_VAL_GENERATED_VARIABLES
ALTER TABLE t1 ADD COLUMN b VARCHAR(256) DEFAULT (@@session.sort_buffer_size);
-- error ER_DEFAULT_VAL_GENERATED_VARIABLES
ALTER TABLE t1 ADD COLUMN b VARCHAR(256) DEFAULT (@@global.sort_buffer_size);
-- error ER_DEFAULT_VAL_GENERATED_VARIABLES
ALTER TABLE t1 ADD COLUMN b VARCHAR(256) DEFAULT (@my_var);
-- error ER_DEFAULT_VAL_GENERATED_VARIABLES
ALTER TABLE t1 ADD COLUMN b VARCHAR(256) DEFAULT (@my_var + 1);

-- error ER_DEFAULT_VAL_GENERATED_VARIABLES
ALTER TABLE t1 ALTER COLUMN i SET DEFAULT (@@session.sort_buffer_size);
-- error ER_DEFAULT_VAL_GENERATED_VARIABLES
ALTER TABLE t1 ALTER COLUMN i SET DEFAULT (@@global.sort_buffer_size);
-- error ER_DEFAULT_VAL_GENERATED_VARIABLES
ALTER TABLE t1 ALTER COLUMN i SET DEFAULT (@my_var);
-- error ER_DEFAULT_VAL_GENERATED_VARIABLES
ALTER TABLE t1 ALTER COLUMN i SET DEFAULT (@my_var + 1);

DROP TABLE t1;

CREATE TABLE t2 (i INT, j DOUBLE DEFAULT (i)) PARTITION BY KEY(j) PARTITIONS 4;
INSERT INTO t2(i) VALUES (1),(2);
DROP TABLE t2;

--echo #
--echo # Bug#28075950 	WL#9418: INCORRECT ERROR THROWN WHEN DEFAULT() IS USED WITHIN DEFAULT CLAUSE
--echo #
--error ER_DEFAULT_AS_VAL_GENERATED
CREATE TABLE t2 (i INT, j DOUBLE DEFAULT (SQRT(i)),
                 k DOUBLE DEFAULT (DEFAULT(j)));


--echo #
--echo # Bug#28075770 	WL#9418: INCORRECT ERROR THROWN WHEN DEFAULT COLUMN IS MADE PRIMARY KEY
--echo #
create table t1 ( i int, j int default ( i * i ), primary key(j));
insert into t1 (i) values (4), (5);
--error ER_DUP_ENTRY
insert into t1 values (4, DEFAULT), (5, DEFAULT);
insert into t1 values (6, DEFAULT), (7, DEFAULT);
select * from t1;
DROP TABLE t1;

--echo #
--echo # Bug#28016783 	WL#9418: SIG6 IN MY_EVAL_GCOLUMN_EXPR_HELPER() AT SQL/HANDLER.CC
--echo #
CREATE TABLE IF NOT EXISTS D1 (i2 INT DEFAULT 568447044, d1 DOUBLE,
                               d2 DOUBLE DEFAULT 0.0, c2 CHAR(255),
                               def2 DOUBLE DEFAULT( i2 DIV d2 ),
                               v1 INT AS ( d2 = c2 % ASIN( d1 ) > i2 ) VIRTUAL);
INSERT ignore INTO D1 (  i2, d1, d2, c2, def2)
VALUES ( 1548385958 , d1 ,128158532 , 0.0 , DEFAULT);
ALTER TABLE D1 ADD UNIQUE KEY uidx ( def2 , v1 , d2 );
DROP TABLE D1;

CREATE TABLE IF NOT EXISTS D1 (def2 DOUBLE DEFAULT( 100 DIV 0 ),
                               v1 INT AS (1) VIRTUAL);
INSERT IGNORE INTO D1 (def2) VALUES (1);
ALTER TABLE D1 ADD UNIQUE KEY uidx ( def2 , v1 );
DROP TABLE D1;

--echo #
--echo # Bug#28046624 	WL#9418: INCORRECT BEHAVIOR OF DEFAULT WITH MODIFY COLUMN
--echo #
CREATE TABLE t1 ( i int, j int DEFAULT (i) ) ;
INSERT INTO t1(i) VALUES (4),(5),(6) ;
SELECT * FROM t1 ;
ALTER TABLE t1 MODIFY COLUMN j DOUBLE DEFAULT(i*i);
SELECT * FROM t1 ;
INSERT INTO t1(i) VALUES (7);
SELECT * FROM t1 ;
DROP TABLE t1;

CREATE TABLE t2 ( i int, j int DEFAULT (i) ) ;
INSERT INTO t2(i) VALUES (4),(5),(6) ;
SELECT * FROM t2 ;
ALTER TABLE t2 MODIFY COLUMN j INT DEFAULT(i*i);
SELECT * FROM t2 ;
INSERT INTO t2(i) VALUES (7);
ALTER TABLE t2 MODIFY COLUMN j DOUBLE DEFAULT(i*100);
INSERT INTO t2(i) VALUES (8);
SELECT * FROM t2 ;
DROP TABLE t2;

--echo #
--echo # BUG#28040739 - WL#9418: INCORRECT ERROR THROWN WITH RENAME COLUMN
--echo #
CREATE TABLE t1 ( i INT, j INT DEFAULT( i * i) ) ;
--error ER_DEPENDENT_BY_DEFAULT_GENERATED_VALUE
ALTER TABLE t1 RENAME COLUMN i to i1 ;
DROP TABLE t1;

CREATE TABLE t1 ( i INT, j INT DEFAULT (i * i) ) ;
--error ER_DEPENDENT_BY_DEFAULT_GENERATED_VALUE
ALTER TABLE t1 CHANGE COLUMN i i1 DOUBLE DEFAULT ( 4 * 4 ) ;
DROP TABLE t1;

create table t1 (i int, j double DEFAULT (i * i) ) ;
--echo # "Unknown column 'z' in 'default value expression'"
--error 1054
alter table t1 add column k double DEFAULT (SQRT(z)) ; #column z does not exist
DROP TABLE t1;


CREATE TABLE t1 (
  i1 INTEGER,
  i2 INTEGER DEFAULT (i1 + i1)
);
INSERT INTO t1 (i1, i2) SELECT 5, 6;
INSERT INTO t1 (i1) SELECT 5;
INSERT INTO t1 (i1) SELECT 5 ON DUPLICATE KEY UPDATE i2= 4;
INSERT INTO t1 (i1) SELECT 5 ON DUPLICATE KEY UPDATE i2= DEFAULT;
SELECT * FROM t1;

DROP TABLE t1;

CREATE TABLE t1 (
  i1 INTEGER,
  i2 INTEGER DEFAULT (i1 + RAND())
);
INSERT INTO t1 (i1, i2) SELECT 5, 6;
INSERT INTO t1 (i1) SELECT 5;
INSERT INTO t1 (i1) SELECT 5 ON DUPLICATE KEY UPDATE i2= 4;
INSERT INTO t1 (i1) SELECT 5 ON DUPLICATE KEY UPDATE i2= DEFAULT;
SELECT count(*) FROM t1;

DROP TABLE t1;

CREATE TABLE t1 (a INT UNIQUE DEFAULT (PI() + 3), b INT DEFAULT (-a));
INSERT INTO t1 (a) VALUES (1), (2);
--sorted_result
SELECT * FROM t1;
INSERT INTO t1(a) VALUES (1) ON DUPLICATE KEY UPDATE a=DEFAULT;
--sorted_result
SELECT * FROM t1;
DELETE FROM t1 WHERE a = 6;
UPDATE t1 SET a=DEFAULT WHERE a=2;
--sorted_result
SELECT * FROM t1;
DROP TABLE t1;

CREATE TABLE t1 (a datetime, b varchar(10) DEFAULT (localtimestamp()));
--error ER_DATA_TOO_LONG
INSERT INTO t1(a) VALUES (now());
SELECT * FROM t1;
DROP TABLE t1;

--echo #
--echo # Bug#29480711   DEBUG ASSERTION IN CREATE TABLE WITH ENUM OR SET
--echo # COLUMNS HAVING EXPLICIT DEFAULT
--echo #

CREATE TABLE t1 (truth ENUM('y','n') DEFAULT('y'));
CREATE TABLE t2 (truths SET('y','n') DEFAULT('y'));
INSERT INTO t1 values (DEFAULT);
INSERT INTO t1 values ();
INSERT INTO t2 values (DEFAULT);
INSERT INTO t2 values ();
SELECT * from t1;
SELECT * from t2;
DROP TABLE t1;
DROP TABLE t2;

CREATE TABLE t1 (truth ENUM('y','n') DEFAULT('s'));
CREATE TABLE t2 (truths SET('y','n') DEFAULT('p'));

--error 1265
INSERT INTO t1 values (DEFAULT);
--error 1265
INSERT INTO t1 values ();
--error 1265
INSERT INTO t2 values (DEFAULT);
--error 1265
INSERT INTO t2 values ();
SELECT * from t1;
SELECT * from t2;
DROP TABLE t1;
DROP TABLE t2;

--echo #
--echo # Bug#30271792 "SERVER CRASH DURING ALTER TABLER ADD COLUMN IN
--echo #               DIAGNOSTICS_AREA::SET_OK_STATUS".
--echo #
CREATE TEMPORARY TABLE t1 (pk INT PRIMARY KEY);
--echo # The below ALTER TABLE should not fail on assert.
--echo # Since the table is empty no erroneous default value is
--echo # evaluated so there is no error either.
ALTER TABLE t1 ADD COLUMN i INT DEFAULT ( "foobar" ), ALGORITHM=COPY;
DROP TEMPORARY TABLE t1;

--echo #
--echo # Bug#29596969: DEFAULT EXPRESSION IS IGNORED IN CREATE TABLE QUERY,
--echo # IF THE FIELD IS MARKED AS PK
--echo #
CREATE TABLE bug(`id` binary(16) NOT NULL DEFAULT (uuid_to_bin(uuid())),
                 PRIMARY KEY (`id`));
SHOW CREATE TABLE bug;
DROP TABLE bug;

--echo # more tests with PRIMARY KEY
CREATE TABLE pk_t1(i INT NOT NULL);
INSERT INTO pk_t1 VALUES (1),(2),(3),(4);
--error ER_BINLOG_UNSAFE_SYSTEM_FUNCTION
ALTER TABLE pk_t1 ADD COLUMN
    (`id` BINARY(16) DEFAULT (uuid_to_bin(uuid())) PRIMARY KEY NOT NULL);
ALTER TABLE pk_t1 ADD COLUMN
    (`id` INT DEFAULT (10 + i) PRIMARY KEY NOT NULL);
SHOW CREATE TABLE pk_t1;
INSERT INTO pk_t1(i) VALUES (5);
INSERT INTO pk_t1(i, id) VALUES (6, DEFAULT);
SELECT * FROM pk_t1;
ALTER TABLE  pk_t1 DROP COLUMN  id;
DROP TABLE pk_t1;

--echo #
--echo # Bug#30331053: CREATE TABLE WITH AUTO_INCREMENT AND DEFAULT EXPRESSION
--echo # SHOULD BE REJECTED
--echo #
--error ER_INVALID_DEFAULT
CREATE TABLE t2 (a INT AUTO_INCREMENT DEFAULT(0));

--echo #
--echo # Bug#31331454: ALTER TABLE WITH AUTO_INCREMENT AND DEFAULT EXPRESSION
--echo # SHOULD BE REJECTED
--echo #
CREATE TABLE t1 (i INT);
--error ER_INVALID_DEFAULT
ALTER TABLE t1 MODIFY COLUMN i INT AUTO_INCREMENT PRIMARY KEY DEFAULT(GROUPING(r));
ALTER TABLE t1 MODIFY COLUMN i INT AUTO_INCREMENT PRIMARY KEY;
--error ER_INVALID_DEFAULT
ALTER TABLE t1 ALTER COLUMN i SET DEFAULT(GROUPING(r));
DROP TABLE t1;

--echo #
--echo # Bug#30302907 "ADDITION OF COLUMNS WITH GENERATED DEFAULTS IS
--echo #               BROKEN IN ALTER TABLE COPY".
--echo #

--echo #
--echo # 1) Simple case, when generated column value depends on generated
--echo #    default value.
CREATE TABLE t1 (a INT PRIMARY KEY);
CREATE TABLE t2 LIKE t1;
CREATE TABLE t3 LIKE t1;
INSERT INTO t1 VALUES (1), (2);
INSERT INTO t2 SELECT * FROM t1;
INSERT INTO t3 SELECT * FROM t1;
ALTER TABLE t1 ADD COLUMN b INT DEFAULT (a), ADD COLUMN c INT GENERATED ALWAYS AS (-b) STORED;
--echo # Repeat the test with explicit ALGORITHM=COPY
ALTER TABLE t2 ADD COLUMN b INT DEFAULT (a), ADD COLUMN c INT GENERATED ALWAYS AS (-b) STORED, ALGORITHM=COPY;
--echo # INPLACE addition of generated columns and defaults is not supported.
--error ER_ALTER_OPERATION_NOT_SUPPORTED
ALTER TABLE t3 ADD COLUMN b INT DEFAULT (a), ADD COLUMN c INT GENERATED ALWAYS AS (-b) STORED, ALGORITHM=INPLACE;
SELECT * FROM t1;
SELECT * FROM t2;
SELECT * FROM t3;
DROP TABLES t1, t2, t3;

--echo #
--echo # 2) Column with generated default "replaces" old column.
CREATE TABLE t1 (a INT, b INT);
CREATE TABLE t2 LIKE t1;
CREATE TABLE t3 LIKE t1;
INSERT INTO t1 VALUES (1, 1), (2, 2);
INSERT INTO t2 SELECT * FROM t1;
INSERT INTO t3 SELECT * FROM t1;
ALTER TABLE t1 DROP COLUMN b, ADD COLUMN c INT DEFAULT (a);
ALTER TABLE t2 DROP COLUMN b, ADD COLUMN c INT DEFAULT (a), ALGORITHM=COPY;
--echo # INPLACE addition of generated defaults is not supported.
--error ER_ALTER_OPERATION_NOT_SUPPORTED
ALTER TABLE t3 DROP COLUMN b, ADD COLUMN c INT DEFAULT (a), ALGORITHM=INPLACE;
SELECT * FROM t1;
SELECT * FROM t2;
SELECT * FROM t3;
DROP TABLES t1, t2, t3;

--echo #
--echo # 3) Column with generated default which is shifted to new poisition.
CREATE TABLE t1 (a INT, b INT DEFAULT (1+1));
CREATE TABLE t2 LIKE t1;
CREATE TABLE t3 LIKE t1;
INSERT INTO t1 VALUES (1, 0), (2, 0);
INSERT INTO t2 SELECT * FROM t1;
INSERT INTO t3 SELECT * FROM t1;
ALTER TABLE t1 ADD COLUMN c INT AFTER a;
ALTER TABLE t2 ADD COLUMN c INT AFTER a, ALGORITHM=COPY;
ALTER TABLE t3 ADD COLUMN c INT AFTER a, ALGORITHM=INPLACE;
SELECT * FROM t1;
SELECT * FROM t2;
SELECT * FROM t3;
DROP TABLES t1, t2, t3;

--echo #
--echo # 4) Complex case. Addition of generated column which depends on
--echo #    generated default, which depends on generated column, which
--echo #    which depends on generated default.
CREATE TABLE t1 (a INT PRIMARY KEY);
CREATE TABLE t2 LIKE t1;
CREATE TABLE t3 LIKE t1;
INSERT INTO t1 VALUES (1), (2), (3);
INSERT INTO t2 SELECT * FROM t1;
INSERT INTO t3 SELECT * FROM t1;
ALTER TABLE t1 ADD COLUMN b INT DEFAULT (a),
               ADD COLUMN c INT GENERATED ALWAYS AS (-b) STORED,
               ADD COLUMN d INT DEFAULT (c),
               ADD COLUMN e INT GENERATED ALWAYS AS (-d) STORED;
ALTER TABLE t2 ADD COLUMN b INT DEFAULT (a),
               ADD COLUMN c INT GENERATED ALWAYS AS (-b) STORED,
               ADD COLUMN d INT DEFAULT (c),
               ADD COLUMN e INT GENERATED ALWAYS AS (-d) STORED,
               ALGORITHM=COPY;
--echo # INPLACE addition of generated columns and defaults is not supported.
--error ER_ALTER_OPERATION_NOT_SUPPORTED
ALTER TABLE t3 ADD COLUMN b INT DEFAULT (a),
               ADD COLUMN c INT GENERATED ALWAYS AS (-b) STORED,
               ADD COLUMN d INT DEFAULT (c),
               ADD COLUMN e INT GENERATED ALWAYS AS (-d) STORED,
               ALGORITHM=INPLACE;
SELECT * FROM t1;
SELECT * FROM t2;
SELECT * FROM t3;
DROP TABLES t1, t2, t3;

--echo #
--echo # Bug#29661106 DROP COLUMN FAILS WHEN DEFAULT EXPRESSION RETURNS
--echo # INCORRECT VALUE
--echo #

CREATE TABLE t1(i INT, j INT DEFAULT("foobar"));
ALTER TABLE t1 DROP COLUMN i ;
DROP TABLE  t1;

CREATE TABLE t2(i INT, j DATETIME DEFAULT(0) );
ALTER TABLE t2 DROP COLUMN i ;
DROP TABLE  t2;


#
# Bug#31856459 MISSING COLUMN DEFAULT VALUES FROM INFORMATION_SCHEMA.COLUMNS
#
# Verify if BLOB and TEXT field display default expression using
# I_S.COLUNMNS and SHOW COLUMNS command.
#

CREATE TABLE t1 (
   pk_col      INT NOT NULL AUTO_INCREMENT,
   json_col    JSON,
   blob_col    BLOB,
   text_col    TEXT,
   tblob_col   TINYBLOB,
   ttext_col   TINYTEXT,
   json_col_d  JSON DEFAULT (JSON_OBJECT('key','val')),
   blob_col_d  BLOB DEFAULT ('asda'),
   text_col_d  TEXT DEFAULT (CONCAT('adasdada','ds')),
   tblob_col_d TINYBLOB DEFAULT ('asda'),
   ttext_col_d TINYTEXT DEFAULT (CONCAT('adasdada','ds')),
   PRIMARY KEY (`pk_col`));

SELECT COLUMN_NAME, COLUMN_DEFAULT, DATA_TYPE, EXTRA
  FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='t1'
  ORDER BY COLUMN_NAME;

SHOW COLUMNS FROM t1;

SHOW CREATE TABLE t1;

DROP TABLE t1;

--echo #
--echo # Bug#32121425: ERROR WHEN UTC_TIMESTAMP SET AS DEFAULT VALUE
--echo #

--echo # Verify that it is possible to add columns to tables which have a
--echo # column with a non-constant default expression.

--echo # This already worked because the flag Field::DEFAULT_NOW is used
CREATE TABLE t1 (created DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP);
ALTER TABLE t1 ADD COLUMN c1 VARCHAR(45) NULL;
DROP TABLE t1;

--echo # The new syntax DEFAULT (F()) did not work, because it uses the new
--echo # Field::GENERATED_FROM_EXPRESSION flag which was not handled in ALTER.
CREATE TABLE t1 (created DATETIME NOT NULL DEFAULT (UTC_TIMESTAMP()));
ALTER TABLE t1 ADD COLUMN c1 VARCHAR(45) NULL;
DROP TABLE t1;

CREATE TABLE t1 (created DATETIME NOT NULL DEFAULT (CURTIME()));
ALTER TABLE t1 ADD COLUMN c1 VARCHAR(45) NULL;
DROP TABLE t1;

CREATE TABLE t1 (created DATETIME NOT NULL DEFAULT (CURRENT_TIME()));
ALTER TABLE t1 ADD COLUMN c1 VARCHAR(45) NULL;
DROP TABLE t1;

CREATE TABLE t1 (created DATETIME NOT NULL DEFAULT (CURRENT_TIMESTAMP()));
ALTER TABLE t1 ADD COLUMN c1 VARCHAR(45) NULL;
DROP TABLE t1;

CREATE TABLE t1 (created DATETIME NOT NULL DEFAULT (LOCALTIME()));
ALTER TABLE t1 ADD COLUMN c1 VARCHAR(45) NULL;
DROP TABLE t1;

CREATE TABLE t1 (created DATETIME NOT NULL DEFAULT (LOCALTIMESTAMP()));
ALTER TABLE t1 ADD COLUMN c1 VARCHAR(45) NULL;
DROP TABLE t1;

CREATE TABLE t1 (created DATETIME NOT NULL DEFAULT (NOW()));
ALTER TABLE t1 ADD COLUMN c1 VARCHAR(45) NULL;
DROP TABLE t1;

CREATE TABLE t1 (created DATETIME NOT NULL DEFAULT (SYSDATE()));
ALTER TABLE t1 ADD COLUMN c1 VARCHAR(45) NULL;
DROP TABLE t1;

CREATE TABLE t1 (created DATETIME NOT NULL DEFAULT (UNIX_TIMESTAMP()));
ALTER TABLE t1 ADD COLUMN c1 VARCHAR(45) NULL;
DROP TABLE t1;

CREATE TABLE t1 (created DATETIME NOT NULL DEFAULT (UTC_DATE()));
ALTER TABLE t1 ADD COLUMN c1 VARCHAR(45) NULL;
DROP TABLE t1;

CREATE TABLE t1 (created DATETIME NOT NULL DEFAULT (UTC_TIME()));
ALTER TABLE t1 ADD COLUMN c1 VARCHAR(45) NULL;
DROP TABLE t1;

--echo
--echo # Bug#34463652: DEFAULT() should reject GENERATED columns
--echo

--echo # Show that we throw an error for the invalid self-reference.
--echo # We can't get DEFAULT() from a GENERATED column that has no DEFAULT.
--error ER_NO_DEFAULT_FOR_FIELD
CREATE TABLE t (a INT,
                b INT GENERATED ALWAYS AS (DEFAULT(b)));
--echo # CREATE should have failed, so consequently, so should the DROP.
DROP TABLE IF EXISTS t;
--echo

--echo # Show that we throw an error for the invalid reference.
--echo # We can't get DEFAULT() from a GENERATED column that has no DEFAULT.
--error ER_NO_DEFAULT_FOR_FIELD
CREATE TABLE t (a INT,
                b INT GENERATED ALWAYS AS (5*8),
                c INT GENERATED ALWAYS AS (DEFAULT(b)));
--echo # CREATE should have failed, so consequently, so should the DROP.
DROP TABLE IF EXISTS t;
--echo

--echo # Show that we throw an error for the self-reference.
CREATE TABLE t (a INT);
--error ER_NO_DEFAULT_FOR_FIELD
ALTER TABLE t ADD COLUMN b INT
  GENERATED ALWAYS AS (DEFAULT(b)), ALGORITHM=INPLACE;
--echo # ALTER should have failed, but there's still a table to DROP.
DROP TABLE IF EXISTS t;
--echo

--echo # Show that DEFAULT(column) with a column that uses a
--echo # DEFAULT(expression) rather than a DEFAULT literal still fails.
CREATE TABLE t (a INT,
                b INT,
                c INT DEFAULT (a*77));
DROP TABLE t;
--error ER_DEFAULT_AS_VAL_GENERATED
CREATE TABLE t (a INT,
                b INT,
                c INT DEFAULT (a*77),
                d INT GENERATED ALWAYS AS (DEFAULT(c)));
--echo

--echo # Show that correct references to DEFAULT still work.
CREATE TABLE t (a INT,
                b INT,
                c INT DEFAULT 77,
                d INT GENERATED ALWAYS AS (DEFAULT(c)));
INSERT INTO t VALUES(1,2,3,DEFAULT);
SELECT * FROM t;
DROP TABLE IF EXISTS t;
--echo

--echo # Show that correct references to columns still work.
CREATE TABLE t (a INT,
                b INT GENERATED ALWAYS AS (a*5));
INSERT INTO t VALUES(7,DEFAULT);
SELECT * FROM t;
DROP TABLE IF EXISTS t;