diff options
Diffstat (limited to 'mysql-test/suite/vcol/t/vcol_misc.test')
| -rw-r--r-- | mysql-test/suite/vcol/t/vcol_misc.test | 510 |
1 files changed, 510 insertions, 0 deletions
diff --git a/mysql-test/suite/vcol/t/vcol_misc.test b/mysql-test/suite/vcol/t/vcol_misc.test new file mode 100644 index 00000000..6f770190 --- /dev/null +++ b/mysql-test/suite/vcol/t/vcol_misc.test @@ -0,0 +1,510 @@ +--source include/have_ucs2.inc +--source include/have_debug.inc + +let $MYSQLD_DATADIR= `select @@datadir`; + +--disable_warnings +drop table if exists t1,t2; +--enable_warnings + +# +# Bug#601164: DELETE/UPDATE with ORDER BY index and LIMIT +# + +create table t1 (a int, b int, v int as (a+1), index idx(b)); +insert into t1(a, b) values + (4, 40), (3, 30), (5, 50), (7, 70), (8, 80), (2, 20), (1, 10); + +select * from t1 order by b; + +delete from t1 where v > 6 order by b limit 1; +select * from t1 order by b; + +update t1 set a=v order by b limit 1; +select * from t1 order by b; + +drop table t1; + +# +# Bug#604549: Expression for virtual column returns row +# + +-- error ER_OPERAND_COLUMNS +CREATE TABLE t1 ( + a int NOT NULL DEFAULT '0', + v double AS ((1, a)) VIRTUAL +); + +# +# Bug#603654: Virtual column in ORDER BY, no other references of table columns +# + +CREATE TABLE t1 ( + a CHAR(255) BINARY NOT NULL DEFAULT 0, + b CHAR(255) BINARY NOT NULL DEFAULT 0, + v CHAR(255) BINARY AS (CONCAT(a,b)) VIRTUAL ); +INSERT INTO t1(a,b) VALUES ('4','7'), ('4','6'); +SELECT 1 AS C FROM t1 ORDER BY v; + +DROP TABLE t1; + +# +# Bug#603186: Insert into a table with stored vurtual columns +# + +CREATE TABLE t1(a int, b int DEFAULT 0, v INT AS (b+10) PERSISTENT); +INSERT INTO t1(a) VALUES (1); +SELECT b, v FROM t1; + +DROP TABLE t1; + +CREATE TABLE t1(a int DEFAULT 100, v int AS (a+1) PERSISTENT); +INSERT INTO t1 () VALUES (); +CREATE TABLE t2(a int DEFAULT 100 , v int AS (a+1)); +INSERT INTO t2 () VALUES (); + +SELECT a, v FROM t1; +SELECT a, v FROM t2; + +DROP TABLE t1,t2; + +# +# Bug#604503: Virtual column expression with datetime comparison +# + +CREATE TABLE t1 ( + a datetime NOT NULL DEFAULT '2000-01-01', + v boolean AS (a < '2001-01-01') +); +INSERT INTO t1(a) VALUES ('2002-02-15'); +INSERT INTO t1(a) VALUES ('2000-10-15'); + +SELECT a, v FROM t1; +SELECT a, v FROM t1; + +CREATE TABLE t2 ( + a datetime NOT NULL DEFAULT '2000-01-01', + v boolean AS (a < '2001-01-01') PERSISTENT +); +INSERT INTO t2(a) VALUES ('2002-02-15'); +INSERT INTO t2(a) VALUES ('2000-10-15'); + +SELECT * FROM t2; + +DROP TABLE t1, t2; + +# +# Bug#607566: Virtual column in the select list of SELECT with ORDER BY +# + +CREATE TABLE t1 ( + a char(255), b char(255), c char(255), d char(255), + v char(255) AS (CONCAT(c,d) ) VIRTUAL +); + +INSERT INTO t1(a,b,c,d) VALUES ('w','x','y','z'), ('W','X','Y','Z'); + +SELECT v FROM t1 ORDER BY CONCAT(a,b); + +DROP TABLE t1; + +# +# Bug#607168: CREATE TABLE AS SELECT that returns virtual columns +# + +CREATE TABLE t1 (f1 INTEGER, v1 INTEGER AS (f1) VIRTUAL); +CREATE TABLE t2 AS SELECT v1 FROM t1; +SHOW CREATE TABLE t2; + +DROP TABLE t1,t2; + +# +# Bug#607177: ROUND function in the expression for a virtual function +# + +CREATE TABLE t1 (p int, a double NOT NULL, v double AS (ROUND(a,p)) VIRTUAL); +INSERT IGNORE INTO t1 VALUES (0,1,0); +INSERT IGNORE INTO t1 VALUES (NULL,0,0); +SELECT a, p, v, ROUND(a,p), ROUND(a,p+NULL) FROM t1; +DROP TABLE t1; + +CREATE TABLE t1 (p int, a double NOT NULL); +INSERT INTO t1(p,a) VALUES (0,1); +INSERT INTO t1(p,a) VALUES (NULL,0); +SELECT a, p, ROUND(a,p), ROUND(a,p+NULL) FROM t1; +DROP TABLE t1; + +# +# Bug#610890: SHOW CREATE TABLE with a virtual column +# + +CREATE TABLE t1 (a char(32), v char(32) CHARACTER SET ucs2 AS (a) VIRTUAL); + +SHOW CREATE TABLE t1; + +DROP TABLE t1; + +# +# Bug#930814: no info in information schema for tables with virtual columns +# + +CREATE TABLE t1 (a int, b int); +CREATE TABLE t2 (a int, b int as (a+1) VIRTUAL); + +SELECT table_schema, table_name, column_name, column_type, extra + FROM information_schema.columns WHERE table_name = 't1'; +SELECT table_schema, table_name, column_name, column_type, extra + FROM information_schema.columns WHERE table_name = 't2'; + +DROP TABLE t1,t2; + +# +# Bug mdev-354: virtual columns of ENUM and SET types +# + +create table t1 ( + a int not null, b char(2) not null, + c enum('Y','N') as (case when b = 'aa' then 'Y' else 'N' end) persistent +); +show create table t1; +insert into t1(a,b) values (1,'bb'), (2,'aa'), (3,'cc'); +select * from t1; + +create table t2 ( + a int, b int, + c set("y","n") + as (if(a=0,if(b=0,('n,n'),('n,y')),if(b=0,('y,n'),('y,y')))) persistent +); +show create table t2; +insert into t2(a,b) values (7,0), (2,3), (0,1); +select * from t2; + +drop table t1,t2; + +# +# Bug mdev-3938: INSERT DELAYED for a table with virtual columns +# + +SET @old_debug= @@global.debug; +SET @old_debug= @@global.debug; +SET GLOBAL debug_dbug= "+d,write_delay_wakeup"; +CREATE TABLE t1 (a int, + ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, + tsv TIMESTAMP AS (ADDDATE(ts, INTERVAL 1 DAY)) VIRTUAL +) ENGINE=MyISAM; + +--echo # First test FLUSH TABLES +INSERT INTO t1 (a,tsv) VALUES (1,DEFAULT); +INSERT DELAYED INTO t1 (a,tsv) VALUES (2,DEFAULT); +FLUSH TABLES; +# Count may be 1 or 2, depending on FLUSH happened before or after delayed +SELECT COUNT(*) > 0 FROM t1; + +--echo # Then test FLUSH TABLES t1; +INSERT INTO t1 (a,tsv) VALUES (3,DEFAULT); +INSERT DELAYED INTO t1 (a,tsv) VALUES (4,DEFAULT); +FLUSH TABLES t1; +SELECT COUNT(*) FROM t1; + +--echo # Then test FLUSH TABLES WITH READ LOCK; + +INSERT INTO t1 (a,tsv) VALUES (5,DEFAULT); +INSERT DELAYED INTO t1 (a,tsv) VALUES (6,DEFAULT); +FLUSH TABLES WITH READ LOCK; +SELECT COUNT(*) FROM t1; +set GLOBAL debug_dbug= @old_debug; +unlock tables; +DROP TABLE t1; + +--echo # +--echo # MDEV-4823 Server crashes in Item_func_not::fix_fields on +--echo # creating a table with a virtual column using NOT +--echo # +CREATE TABLE t1 ( f1 INT, v4 INT AS ( NOT f1 ) VIRTUAL ); +drop table t1; + +--echo # end of 5.2 tests + +# +# SELECT that uses a virtual column and executed with BKA +# + +create table t1 (a int, b int); +insert into t1 values (3, 30), (4, 20), (1, 20); +create table t2 (c int, d int, v int as (d+1), index idx(c)); +insert into t2(c,d) values + (20, 100), (20, 300), (30, 100), (30, 200), (40, 500), + (70, 100), (40, 300), (60, 100), (40, 100), (70, 100); +insert into t2(c,d) values + (120, 100), (150, 300), (130, 100), (130, 200), (140, 500), + (170, 100), (180, 300), (160, 100), (40, 100), (170, 100); + +set join_cache_level=6; +explain +select * from t1,t2 where t1.b=t2.c and d <= 100; + +select * from t1,t2 where t1.b=t2.c and d <= 100; +set join_cache_level=default; + +drop table t1, t2; + +# +# Test crashes when using convert_const_item() +# +create table t1 (a bigint, b bigint as (a > '2')); +show create table t1; +insert into t1 (a) values (1),(3); +select * from t1; +select * from t1; +drop table t1; +create table t1 (a bigint, b bigint as (a between 0 and 2)); +show create table t1; +insert into t1 (a) values (1),(3); +select * from t1; +select * from t1; +drop table t1; +create table t1 (a char(10), b char(10) as (a between 0 and 2)); +show create table t1; +insert into t1 (a) values (1),(3); +select * from t1; +select * from t1; +drop table t1; + +# +# Test output of show columns +# + +CREATE TABLE `t1` ( + `a` int(11) NOT NULL, + `b` varchar(32) DEFAULT NULL, + `c` int(11) AS (a MOD 10) VIRTUAL, + `d` varchar(5) AS (LEFT(b,5)) PERSISTENT +) ENGINE=MyISAM; +show create table t1; +show columns from t1; +--replace_column 8 # +show full columns from t1; +INSERT INTO `test`.`t1`(`a`,`b`,`c`,`d`) VALUES ( '1','a',NULL,NULL); +UPDATE IGNORE `test`.`t1` SET `d`='b' WHERE `a`='1' AND `b`='a' AND `c`='1' AND `d`='a'; +INSERT IGNORE INTO `test`.`t1`(`a`,`b`,`c`,`d`) VALUES ( '1','a',NULL,'a'); +set sql_mode='strict_all_tables'; +--error ER_WARNING_NON_DEFAULT_VALUE_FOR_GENERATED_COLUMN +UPDATE `test`.`t1` SET `d`='b' WHERE `a`='1' AND `b`='a' AND `c`='1' AND `d`='a'; +--error ER_WARNING_NON_DEFAULT_VALUE_FOR_GENERATED_COLUMN +INSERT INTO `test`.`t1`(`a`,`b`,`c`,`d`) VALUES ( '1','a',NULL,'a'); +drop table t1; + +--echo # +--echo # MDEV-5611: self-referencing virtual column +--echo # + +--error ER_EXPRESSION_REFERS_TO_UNINIT_FIELD +create table t1 (a int, b int as (b is null) virtual); + +create table t1 (a int as (1+1), b int as (a is null) virtual); +drop table t1; + +--echo # end of 5.3 tests + +# +# MDEV-7655 SHOW CREATE TABLE returns invalid DDL when using virtual columns along with a table collation +# +create table t1 (v1 varchar(255) as (c1) persistent, c1 varchar(50)) collate=latin1_general_ci; +show create table t1; +drop table t1; + +# +# MDEV-11527 Virtual columns do not get along well with NO_ZERO_DATE +# +set sql_mode='no_zero_date'; +create table t1 ( + ts timestamp not null default current_timestamp, + tsv timestamp as (adddate(ts, interval 1 day)) virtual +); +drop table t1; +set sql_mode=default; + +--echo # +--echo # MDEV-11819 NO_ZERO_IN_DATE: Incorrect generated column value +--echo # + +SET sql_mode='NO_ZERO_IN_DATE'; +CREATE TABLE t1 +( + a datetime DEFAULT NULL, + b datetime DEFAULT NULL, + c time GENERATED ALWAYS AS (timediff(`a`,`b`)) VIRTUAL +); +INSERT INTO t1 VALUES ('2008-12-31 23:59:59.000001','2008-12-30 01:01:01.000002',DEFAULT); +SELECT * FROM t1; +DROP TABLE t1; +SET sql_mode=DEFAULT; + + +--echo # +--echo # MDEV-15834 The code in TABLE_SHARE::init_from_binary_frm_image() is not safe +--echo # + +--copy_file std_data/frm/t1.frm $MYSQLD_DATADIR/test/t1.frm +SHOW TABLES; +--replace_result $MYSQLD_DATADIR ./ +--error ER_NOT_FORM_FILE +SHOW CREATE TABLE t1; +--replace_result $MYSQLD_DATADIR ./ +--error ER_NOT_FORM_FILE +ALTER TABLE t1; +--remove_file $MYSQLD_DATADIR/test/t1.frm + + +--echo # +--echo # End of 5.5 tests +--echo # + +--echo # +--echo # End of 10.0 tests +--echo # + +--echo # +--echo # MDEV-8441 Bad SHOW CREATE TABLE output for a table with a virtual column +--echo # +CREATE TABLE t1 (a DATETIME, b TIMESTAMP AS (TIMESTAMP(a))); +SHOW CREATE TABLE t1; +DROP TABLE t1; +# Make sure that if the first TIMESTAMP column appears to be virtual, +# then no further promotion is done, so the next TIMESTAMP column "c" does not +# get the "DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP" attributes. +CREATE TABLE t1 (a DATETIME, b TIMESTAMP AS (TIMESTAMP(a)),c TIMESTAMP); +SHOW CREATE TABLE t1; +DROP TABLE t1; + +--echo # +--echo # MDEV-8839 COLUMN_GET() produces warnings with no data +--echo # +SET @aaa= COLUMN_CREATE('price', _binary 0xF0F1F2F3F4F5F6F7); +SELECT COLUMN_GET(@aaa, 'price' AS DECIMAL) aaa; +SELECT COLUMN_GET(@aaa, 'price' AS INT) aaa; +SELECT COLUMN_GET(@aaa, 'price' AS DOUBLE) aaa; + + +--echo # +--echo # MDEV-22579 No error when inserting DEFAULT(non_virtual_column) into a virtual column +--echo # + +SET sql_mode=STRICT_ALL_TABLES; +CREATE OR REPLACE TABLE t1 ( + a INT NOT NULL DEFAULT 10, + b INT AS (a+1) VIRTUAL +) ENGINE=MyISAM; + +# Testing with a column list + +--error ER_WARNING_NON_DEFAULT_VALUE_FOR_GENERATED_COLUMN +INSERT INTO t1 (b) VALUES (10); +--error ER_WARNING_NON_DEFAULT_VALUE_FOR_GENERATED_COLUMN +INSERT INTO t1 (b) VALUES (DEFAULT(a)); +INSERT INTO t1 (b) VALUES (DEFAULT); + +# Testing without a column list +--error ER_WARNING_NON_DEFAULT_VALUE_FOR_GENERATED_COLUMN +INSERT INTO t1 VALUES (10,10); +--error ER_WARNING_NON_DEFAULT_VALUE_FOR_GENERATED_COLUMN +INSERT INTO t1 VALUES (10,DEFAULT(a)); +INSERT INTO t1 VALUES (10, DEFAULT); + +DROP TABLE t1; +SET sql_mode=DEFAULT; + + +--echo # +--echo # End of 10.1 tests +--echo # + + +--echo # +--echo # Start of 10.2 tests +--echo # + +--echo # +--echo # MDEV-16518 MYSQL57_GENERATED_FIELD: The code in TABLE_SHARE::init_from_binary_frm_image() is not safe +--echo # + +--copy_file std_data/frm/mdev16518.frm $MYSQLD_DATADIR/test/t1.frm +SHOW TABLES; +--replace_result $MYSQLD_DATADIR ./ +--error ER_NOT_FORM_FILE +SHOW CREATE TABLE t1; +--replace_result $MYSQLD_DATADIR ./ +--error ER_NOT_FORM_FILE +ALTER TABLE t1; +--remove_file $MYSQLD_DATADIR/test/t1.frm + +--echo # +--echo # MDEV-19771 REPLACE on table with virtual_field can cause crash in set_ok_status() +--echo + +create or replace table t1 (pk int primary key, col_bit bit(15) default null, + vcol_bit bit(10) GENERATED ALWAYS AS (`col_bit`) VIRTUAL); +replace INTO `t1` (`pk`,col_bit) VALUES (99,1000); +select pk, col_bit+0, vcol_bit+0 from t1; +replace INTO `t1` (`pk`,col_bit) VALUES (99,10000); +select pk, col_bit+0, vcol_bit+0 from t1; +--error ER_DATA_TOO_LONG +REPLACE LOW_PRIORITY INTO `t1` (`pk`) VALUES (99); +drop table t1; + +--echo # +--echo # MDEV-17837 REPLACE on table with virtual_field can cause crash in set_ok_status() +--echo # + +SET @old_sql_mode=@@sql_mode; +SET sql_mode= STRICT_ALL_TABLES; + +CREATE TABLE t1 ( + pk INT, + i TINYINT, + vi TINYINT AS (i+1) PERSISTENT, + PRIMARY KEY(pk) +); + +INSERT INTO t1 (pk,i) VALUES (1,1); +TRUNCATE TABLE t1; +INSERT IGNORE INTO t1 (pk,i) VALUES (1,127); +--error ER_WARN_DATA_OUT_OF_RANGE +REPLACE INTO t1 (pk,i) VALUES (1,2); +DROP TABLE t1; +SET @sql_mode=@old_sql_mode; + +--echo # +--echo # MDEV-22579 No error when inserting DEFAULT(non_virtual_column) into a virtual column +--echo # 10.2+ specific part +--echo # + +SET sql_mode=STRICT_ALL_TABLES; +CREATE OR REPLACE TABLE t1 ( + a INT NOT NULL DEFAULT 10, + b INT AS (a+1) VIRTUAL +) ENGINE=MyISAM; + +# Testing with column list + +EXECUTE IMMEDIATE 'INSERT INTO t1 (b) VALUES(?)' USING DEFAULT; +EXECUTE IMMEDIATE 'INSERT INTO t1 (b) VALUES(?)' USING IGNORE; +INSERT INTO t1 (b) VALUES (DEFAULT); +INSERT INTO t1 (b) VALUES (IGNORE); +SELECT * FROM t1; +DELETE FROM t1; + +# Testing without column list + +EXECUTE IMMEDIATE 'INSERT INTO t1 VALUES(10,?)' USING DEFAULT; +EXECUTE IMMEDIATE 'INSERT INTO t1 VALUES(11,?)' USING IGNORE; +INSERT INTO t1 VALUES (12,DEFAULT); +INSERT INTO t1 VALUES (13,IGNORE); +SELECT * FROM t1; + +DROP TABLE t1; +SET sql_mode=DEFAULT; + +--echo # +--echo # End of 10.2 tests +--echo # |