1create table t1 (a int check (a>0)); 2show create table t1; 3Table Create Table 4t1 CREATE TABLE `t1` ( 5 `a` int(11) DEFAULT NULL CHECK (`a` > 0) 6) ENGINE=MyISAM DEFAULT CHARSET=latin1 7insert into t1 values (1); 8insert into t1 values (0); 9ERROR 23000: CONSTRAINT `t1.a` failed for `test`.`t1` 10drop table t1; 11create table t1 (a int, b int, check (a>b)); 12show create table t1; 13Table Create Table 14t1 CREATE TABLE `t1` ( 15 `a` int(11) DEFAULT NULL, 16 `b` int(11) DEFAULT NULL, 17 CONSTRAINT `CONSTRAINT_1` CHECK (`a` > `b`) 18) ENGINE=MyISAM DEFAULT CHARSET=latin1 19insert into t1 values (1,0); 20insert into t1 values (0,1); 21ERROR 23000: CONSTRAINT `CONSTRAINT_1` failed for `test`.`t1` 22drop table t1; 23create table t1 (a int ,b int, constraint abc check (a>b)); 24show create table t1; 25Table Create Table 26t1 CREATE TABLE `t1` ( 27 `a` int(11) DEFAULT NULL, 28 `b` int(11) DEFAULT NULL, 29 CONSTRAINT `abc` CHECK (`a` > `b`) 30) ENGINE=MyISAM DEFAULT CHARSET=latin1 31insert into t1 values (1,0); 32insert into t1 values (0,1); 33ERROR 23000: CONSTRAINT `abc` failed for `test`.`t1` 34drop table t1; 35create table t1 (a int null); 36show create table t1; 37Table Create Table 38t1 CREATE TABLE `t1` ( 39 `a` int(11) DEFAULT NULL 40) ENGINE=MyISAM DEFAULT CHARSET=latin1 41insert into t1 values (1),(NULL); 42drop table t1; 43create table t1 (a int null); 44alter table t1 add constraint constraint_1 unique (a); 45alter table t1 add constraint unique key_1(a); 46Warnings: 47Note 1831 Duplicate index `key_1`. This is deprecated and will be disallowed in a future release 48alter table t1 add constraint constraint_2 unique key_2(a); 49Warnings: 50Note 1831 Duplicate index `key_2`. This is deprecated and will be disallowed in a future release 51show create table t1; 52Table Create Table 53t1 CREATE TABLE `t1` ( 54 `a` int(11) DEFAULT NULL, 55 UNIQUE KEY `constraint_1` (`a`), 56 UNIQUE KEY `key_1` (`a`), 57 UNIQUE KEY `key_2` (`a`) 58) ENGINE=MyISAM DEFAULT CHARSET=latin1 59drop table t1; 60drop table if exists t_illegal; 61create table t_illegal (a int, b int, check a>b); 62ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'a>b)' at line 1 63create table t_illegal (a int, b int, constraint abc check a>b); 64ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'a>b)' at line 1 65create table t_illegal (a int, b int, constraint abc); 66ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ')' at line 1 67drop table if exists t_11714; 68create table t_11714(a int, b int); 69alter table t_11714 add constraint cons1; 70ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 1 71drop table t_11714; 72CREATE TABLE t_illegal (col_1 INT CHECK something (whatever)); 73ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'something (whatever))' at line 1 74CREATE TABLE t_illegal (col_1 INT CHECK something); 75ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'something)' at line 1 76CREATE TABLE long_enough_name ( 77pk int(11) NOT NULL, 78f1 int(11) DEFAULT NULL, 79f2 int(11) NOT NULL, 80f3 int(11) DEFAULT NULL, 81f4 timestamp NOT NULL DEFAULT current_timestamp(), 82f5 varchar(32) COLLATE utf8_bin NOT NULL DEFAULT 'foo', 83f6 smallint(6) NOT NULL DEFAULT 1, 84f7 int(11) DEFAULT NULL, 85PRIMARY KEY (pk), 86KEY idx1 (f7), 87KEY idx2 (f1), 88KEY idx3 (f2), 89KEY idx4 (f3), 90CONSTRAINT constr CHECK (f6 >= 0) 91); 92SELECT * FROM long_enough_name AS tbl; 93pk f1 f2 f3 f4 f5 f6 f7 94SHOW CREATE TABLE long_enough_name; 95Table Create Table 96long_enough_name CREATE TABLE `long_enough_name` ( 97 `pk` int(11) NOT NULL, 98 `f1` int(11) DEFAULT NULL, 99 `f2` int(11) NOT NULL, 100 `f3` int(11) DEFAULT NULL, 101 `f4` timestamp NOT NULL DEFAULT current_timestamp(), 102 `f5` varchar(32) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT 'foo', 103 `f6` smallint(6) NOT NULL DEFAULT 1, 104 `f7` int(11) DEFAULT NULL, 105 PRIMARY KEY (`pk`), 106 KEY `idx1` (`f7`), 107 KEY `idx2` (`f1`), 108 KEY `idx3` (`f2`), 109 KEY `idx4` (`f3`), 110 CONSTRAINT `constr` CHECK (`f6` >= 0) 111) ENGINE=MyISAM DEFAULT CHARSET=latin1 112DROP TABLE long_enough_name; 113CREATE TABLE test.t(t int COMMENT 't_comment' CHECK(t>0)); 114SHOW CREATE TABLE test.t; 115Table Create Table 116t CREATE TABLE `t` ( 117 `t` int(11) DEFAULT NULL COMMENT 't_comment' CHECK (`t` > 0) 118) ENGINE=MyISAM DEFAULT CHARSET=latin1 119DROP table test.t; 120SET @OLD_SQL_MODE=@@SQL_MODE; 121SET SQL_MODE='IGNORE_BAD_TABLE_OPTIONS'; 122CREATE TABLE test.t (f int foo=bar check(f>0)); 123Warnings: 124Warning 1911 Unknown option 'foo' 125SHOW CREATE TABLE t; 126Table Create Table 127t CREATE TABLE `t` ( 128 `f` int(11) DEFAULT NULL `foo`=bar CHECK (`f` > 0) 129) ENGINE=MyISAM DEFAULT CHARSET=latin1 130DROP table test.t; 131SET @@SQL_MODE=@OLD_SQL_MODE; 132# 133# MDEV-16932 - ASAN heap-use-after-free in my_charlen_utf8 / 134# my_well_formed_char_length_utf8 on 2nd execution of SP with 135# ALTER trying to add bad CHECK 136# 137CREATE TABLE t1 (a INT); 138CREATE PROCEDURE sp() ALTER TABLE t1 ADD CONSTRAINT CHECK (b > 0); 139CALL sp; 140ERROR 42S22: Unknown column 'b' in 'CHECK' 141CALL sp; 142ERROR 42S22: Unknown column 'b' in 'CHECK' 143CALL sp; 144ERROR 42S22: Unknown column 'b' in 'CHECK' 145show create table t1; 146Table Create Table 147t1 CREATE TABLE `t1` ( 148 `a` int(11) DEFAULT NULL 149) ENGINE=MyISAM DEFAULT CHARSET=latin1 150alter table t1 add column b int; 151CALL sp; 152show create table t1; 153Table Create Table 154t1 CREATE TABLE `t1` ( 155 `a` int(11) DEFAULT NULL, 156 `b` int(11) DEFAULT NULL, 157 CONSTRAINT `CONSTRAINT_1` CHECK (`b` > 0) 158) ENGINE=MyISAM DEFAULT CHARSET=latin1 159CALL sp; 160show create table t1; 161Table Create Table 162t1 CREATE TABLE `t1` ( 163 `a` int(11) DEFAULT NULL, 164 `b` int(11) DEFAULT NULL, 165 CONSTRAINT `CONSTRAINT_1` CHECK (`b` > 0), 166 CONSTRAINT `CONSTRAINT_2` CHECK (`b` > 0) 167) ENGINE=MyISAM DEFAULT CHARSET=latin1 168DROP PROCEDURE sp; 169DROP TABLE t1; 170CREATE TABLE t1 (a INT); 171CREATE PROCEDURE sp() ALTER TABLE t1 ADD CONSTRAINT CHECK (b > 0); 172CALL sp; 173ERROR 42S22: Unknown column 'b' in 'CHECK' 174alter table t1 add column b int, add constraint check (b < 10); 175CALL sp; 176show create table t1; 177Table Create Table 178t1 CREATE TABLE `t1` ( 179 `a` int(11) DEFAULT NULL, 180 `b` int(11) DEFAULT NULL, 181 CONSTRAINT `CONSTRAINT_1` CHECK (`b` < 10), 182 CONSTRAINT `CONSTRAINT_2` CHECK (`b` > 0) 183) ENGINE=MyISAM DEFAULT CHARSET=latin1 184DROP PROCEDURE sp; 185DROP TABLE t1; 186# End of 10.2 tests 187create table t1 (a int check (a>10)) select 100 as 'a'; 188show create table t1; 189Table Create Table 190t1 CREATE TABLE `t1` ( 191 `a` int(11) DEFAULT NULL CHECK (`a` > 10) 192) ENGINE=MyISAM DEFAULT CHARSET=latin1 193drop table t1; 194create table t1 (a text default(length(now())) check (length(a) > 1)); 195insert into t1 values (); 196insert into t1 values ("ccc"); 197insert into t1 values (""); 198ERROR 23000: CONSTRAINT `t1.a` failed for `test`.`t1` 199select * from t1; 200a 20119 202ccc 203drop table t1; 204