1set @OLD_SQL_MODE=@@SESSION.SQL_MODE; 2create table t1 (a int, b int generated always as (a+1)); 3show create table t1; 4Table Create Table 5t1 CREATE TABLE `t1` ( 6 `a` int(11) DEFAULT NULL, 7 `b` int(11) GENERATED ALWAYS AS (`a` + 1) VIRTUAL 8) ENGINE=MyISAM DEFAULT CHARSET=latin1 9drop table t1; 10create table t1 (a int, b int as (a+1) virtual); 11show create table t1; 12Table Create Table 13t1 CREATE TABLE `t1` ( 14 `a` int(11) DEFAULT NULL, 15 `b` int(11) GENERATED ALWAYS AS (`a` + 1) VIRTUAL 16) ENGINE=MyISAM DEFAULT CHARSET=latin1 17drop table t1; 18create table t1 (a int, b int generated always as (a+1) persistent); 19show create table t1; 20Table Create Table 21t1 CREATE TABLE `t1` ( 22 `a` int(11) DEFAULT NULL, 23 `b` int(11) GENERATED ALWAYS AS (`a` + 1) STORED 24) ENGINE=MyISAM DEFAULT CHARSET=latin1 25drop table t1; 26set session sql_mode='ORACLE'; 27create table t1 (a int, b int as (a+1)); 28show create table t1; 29Table Create Table 30t1 CREATE TABLE "t1" ( 31 "a" int(11) DEFAULT NULL, 32 "b" int(11) GENERATED ALWAYS AS ("a" + 1) VIRTUAL 33) 34drop table t1; 35create table t1 (a int, b int generated always as (a+1) virtual); 36show create table t1; 37Table Create Table 38t1 CREATE TABLE "t1" ( 39 "a" int(11) DEFAULT NULL, 40 "b" int(11) GENERATED ALWAYS AS ("a" + 1) VIRTUAL 41) 42drop table t1; 43create table t1 (a int, b int as (a+1) persistent); 44show create table t1; 45Table Create Table 46t1 CREATE TABLE "t1" ( 47 "a" int(11) DEFAULT NULL, 48 "b" int(11) GENERATED ALWAYS AS ("a" + 1) STORED 49) 50drop table t1; 51set session sql_mode=@OLD_SQL_MODE; 52# 53# MDEV-25091 CREATE TABLE: field references qualified by a wrong table name succeed 54# 55create table t2 (x int); 56create table t1 (x int, y int generated always as (t2.x)); 57ERROR 42S22: Unknown column '`t2`.`x`' in 'GENERATED ALWAYS' 58create table t1 (x int, y int check (y > t2.x)); 59ERROR 42S22: Unknown column '`t2`.`x`' in 'CHECK' 60create table t1 (x int, y int default t2.x); 61ERROR 42S22: Unknown column '`t2`.`x`' in 'DEFAULT' 62create table t1 (x int, check (t2.x > 0)); 63ERROR 42S22: Unknown column '`t2`.`x`' in 'CHECK' 64create table t1 (x int); 65alter table t1 add column y int generated always as (t2.x); 66ERROR 42S22: Unknown column '`t2`.`x`' in 'GENERATED ALWAYS' 67alter table t1 add column y int check (z > t2.x); 68ERROR 42S22: Unknown column '`t2`.`x`' in 'CHECK' 69alter table t1 add column y int default t2.x; 70ERROR 42S22: Unknown column '`t2`.`x`' in 'DEFAULT' 71alter table t1 add constraint check (t2.x > 0); 72ERROR 42S22: Unknown column '`t2`.`x`' in 'CHECK' 73create or replace table t1 (x int, y int generated always as (t1.x)); 74create or replace table t1 (x int, y int check (y > t1.x)); 75create or replace table t1 (x int, y int default t1.x); 76create or replace table t1 (x int, check (t1.x > 0)); 77create or replace table t1 (x int, y int generated always as (test.t1.x)); 78create or replace table t1 (x int, y int check (y > test.t1.x)); 79create or replace table t1 (x int, y int default test.t1.x); 80create or replace table t1 (x int, check (test.t1.x > 0)); 81drop tables t1, t2; 82create table t1 (x int, y int generated always as (test2.t1.x)); 83ERROR 42S22: Unknown column '`test2`.`t1`.`x`' in 'GENERATED ALWAYS' 84create table t1 (x int, y int check (y > test2.t1.x)); 85ERROR 42S22: Unknown column '`test2`.`t1`.`x`' in 'CHECK' 86create table t1 (x int, y int default test2.t1.x); 87ERROR 42S22: Unknown column '`test2`.`t1`.`x`' in 'DEFAULT' 88create table t1 (x int, check (test2.t1.x > 0)); 89ERROR 42S22: Unknown column '`test2`.`t1`.`x`' in 'CHECK' 90# 91# MDEV-25672 table alias from previous statement interferes later commands 92# 93create table t1 (a int, v_a int generated always as (a)); 94update t1 as x set a = 1; 95alter table t1 force; 96drop table t1; 97# 98# End of 10.2 tests 99# 100