path: root/mysql-test/suite/vcol/t/vcol_misc.test

--source include/have_ucs2.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
#

CREATE TABLE t1 (
 ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
 tsv TIMESTAMP AS (ADDDATE(ts, INTERVAL 1 DAY)) VIRTUAL
) ENGINE=MyISAM;

INSERT INTO t1 (tsv) VALUES (DEFAULT);

INSERT DELAYED INTO t1 (tsv) VALUES (DEFAULT);

FLUSH TABLES;

SELECT COUNT(*) FROM t1;

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;
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);
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 #