diff options
Diffstat (limited to 'mysql-test/suite/vcol/r/vcol_misc.result')
-rw-r--r-- | mysql-test/suite/vcol/r/vcol_misc.result | 100 |
1 files changed, 95 insertions, 5 deletions
diff --git a/mysql-test/suite/vcol/r/vcol_misc.result b/mysql-test/suite/vcol/r/vcol_misc.result index f1d1045db13..58bd048ec85 100644 --- a/mysql-test/suite/vcol/r/vcol_misc.result +++ b/mysql-test/suite/vcol/r/vcol_misc.result @@ -108,10 +108,10 @@ DROP TABLE t1,t2; CREATE TABLE t1 (p int, a double NOT NULL, v double AS (ROUND(a,p)) VIRTUAL); INSERT INTO t1 VALUES (0,1,0); Warnings: -Warning 1718 The value specified for computed column 'v' in table 't1' ignored +Warning 1906 The value specified for computed column 'v' in table 't1' ignored INSERT INTO t1 VALUES (NULL,0,0); Warnings: -Warning 1718 The value specified for computed column 'v' in table 't1' ignored +Warning 1906 The value specified for computed column 'v' in table 't1' ignored SELECT a, p, v, ROUND(a,p), ROUND(a,p+NULL) FROM t1; a p v ROUND(a,p) ROUND(a,p+NULL) 1 0 1 1 NULL @@ -143,12 +143,102 @@ set join_cache_level=6; explain select * from t1,t2 where t1.b=t2.c and d <= 100; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 3 -1 SIMPLE t2 ref idx idx 5 test.t1.b 2 Using where; Using join buffer +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using where +1 SIMPLE t2 ref idx idx 5 test.t1.b 2 Using where select * from t1,t2 where t1.b=t2.c and d <= 100; a b c d v +3 30 30 100 101 4 20 20 100 101 1 20 20 100 101 -3 30 30 100 101 set join_cache_level=default; drop table t1, t2; +create table t1 (a bigint, b bigint as (a > '2')); +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` bigint(20) DEFAULT NULL, + `b` bigint(20) AS (a > '2') VIRTUAL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +insert into t1 (a) values (1),(3); +select * from t1; +a b +1 0 +3 1 +select * from t1; +a b +1 0 +3 1 +drop table t1; +create table t1 (a bigint, b bigint as (a between 0 and 2)); +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` bigint(20) DEFAULT NULL, + `b` bigint(20) AS (a between 0 and 2) VIRTUAL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +insert into t1 (a) values (1),(3); +select * from t1; +a b +1 1 +3 0 +select * from t1; +a b +1 1 +3 0 +drop table t1; +create table t1 (a char(10), b char(10) as (a between 0 and 2)); +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` char(10) DEFAULT NULL, + `b` char(10) AS (a between 0 and 2) VIRTUAL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +insert into t1 (a) values (1),(3); +select * from t1; +a b +1 1 +3 0 +select * from t1; +a b +1 1 +3 0 +drop table t1; +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; +Table Create Table +t1 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 DEFAULT CHARSET=latin1 +show columns from t1; +Field Type Null Key Default Extra +a int(11) NO NULL +b varchar(32) YES NULL +c int(11) YES NULL VIRTUAL +d varchar(5) YES NULL PERSISTENT +show full columns from t1; +Field Type Collation Null Key Default Extra Privileges Comment +a int(11) NULL NO NULL # +b varchar(32) latin1_swedish_ci YES NULL # +c int(11) NULL YES NULL VIRTUAL # +d varchar(5) latin1_swedish_ci YES NULL PERSISTENT # +INSERT INTO `test`.`t1`(`a`,`b`,`c`,`d`) VALUES ( '1','a',NULL,NULL); +UPDATE `test`.`t1` SET `d`='b' WHERE `a`='1' AND `b`='a' AND `c`='1' AND `d`='a'; +Warnings: +Warning 1906 The value specified for computed column 'd' in table 't1' ignored +INSERT INTO `test`.`t1`(`a`,`b`,`c`,`d`) VALUES ( '1','a',NULL,'a'); +Warnings: +Warning 1906 The value specified for computed column 'd' in table 't1' ignored +set sql_mode='strict_all_tables'; +UPDATE `test`.`t1` SET `d`='b' WHERE `a`='1' AND `b`='a' AND `c`='1' AND `d`='a'; +ERROR HY000: The value specified for computed column 'd' in table 't1' ignored +INSERT INTO `test`.`t1`(`a`,`b`,`c`,`d`) VALUES ( '1','a',NULL,'a'); +ERROR HY000: The value specified for computed column 'd' in table 't1' ignored +drop table t1; |