| 1 |
create table t1 (a int, b int, v int as (a+1), index idx(b)); |
| 2 |
insert into t1(a, b) values |
| 3 |
(4, 40), (3, 30), (5, 50), (7, 70), (8, 80), (2, 20), (1, 10); |
| 4 |
select * from t1 order by b; |
| 5 |
a b v |
| 6 |
1 10 2 |
| 7 |
2 20 3 |
| 8 |
3 30 4 |
| 9 |
4 40 5 |
| 10 |
5 50 6 |
| 11 |
7 70 8 |
| 12 |
8 80 9 |
| 13 |
delete from t1 where v > 6 order by b limit 1; |
| 14 |
select * from t1 order by b; |
| 15 |
a b v |
| 16 |
1 10 2 |
| 17 |
2 20 3 |
| 18 |
3 30 4 |
| 19 |
4 40 5 |
| 20 |
5 50 6 |
| 21 |
8 80 9 |
| 22 |
update t1 set a=v order by b limit 1; |
| 23 |
select * from t1 order by b; |
| 24 |
a b v |
| 25 |
2 10 3 |
| 26 |
2 20 3 |
| 27 |
3 30 4 |
| 28 |
4 40 5 |
| 29 |
5 50 6 |
| 30 |
8 80 9 |
| 31 |
drop table t1; |
| 32 |
CREATE TABLE t1 ( |
| 33 |
a int NOT NULL DEFAULT '0', |
| 34 |
v double AS ((1, a)) VIRTUAL |
| 35 |
); |
| 36 |
ERROR 21000: Operand should contain 1 column(s) |
| 37 |
CREATE TABLE t1 ( |
| 38 |
a CHAR(255) BINARY NOT NULL DEFAULT 0, |
| 39 |
b CHAR(255) BINARY NOT NULL DEFAULT 0, |
| 40 |
v CHAR(255) BINARY AS (CONCAT(a,b)) VIRTUAL ); |
| 41 |
INSERT INTO t1(a,b) VALUES ('4','7'), ('4','6'); |
| 42 |
SELECT 1 AS C FROM t1 ORDER BY v; |
| 43 |
C |
| 44 |
1 |
| 45 |
1 |
| 46 |
DROP TABLE t1; |
| 47 |
CREATE TABLE t1(a int, b int DEFAULT 0, v INT AS (b+10) PERSISTENT); |
| 48 |
INSERT INTO t1(a) VALUES (1); |
| 49 |
SELECT b, v FROM t1; |
| 50 |
b v |
| 51 |
0 10 |
| 52 |
DROP TABLE t1; |
| 53 |
CREATE TABLE t1(a int DEFAULT 100, v int AS (a+1) PERSISTENT); |
| 54 |
INSERT INTO t1 () VALUES (); |
| 55 |
CREATE TABLE t2(a int DEFAULT 100 , v int AS (a+1)); |
| 56 |
INSERT INTO t2 () VALUES (); |
| 57 |
SELECT a, v FROM t1; |
| 58 |
a v |
| 59 |
100 101 |
| 60 |
SELECT a, v FROM t2; |
| 61 |
a v |
| 62 |
100 101 |
| 63 |
DROP TABLE t1,t2; |
| 64 |
CREATE TABLE t1 ( |
| 65 |
a datetime NOT NULL DEFAULT '2000-01-01', |
| 66 |
v boolean AS (a < '2001-01-01') |
| 67 |
); |
| 68 |
INSERT INTO t1(a) VALUES ('2002-02-15'); |
| 69 |
INSERT INTO t1(a) VALUES ('2000-10-15'); |
| 70 |
SELECT a, v FROM t1; |
| 71 |
a v |
| 72 |
2002-02-15 00:00:00 0 |
| 73 |
2000-10-15 00:00:00 1 |
| 74 |
SELECT a, v FROM t1; |
| 75 |
a v |
| 76 |
2002-02-15 00:00:00 0 |
| 77 |
2000-10-15 00:00:00 1 |
| 78 |
CREATE TABLE t2 ( |
| 79 |
a datetime NOT NULL DEFAULT '2000-01-01', |
| 80 |
v boolean AS (a < '2001-01-01') PERSISTENT |
| 81 |
); |
| 82 |
INSERT INTO t2(a) VALUES ('2002-02-15'); |
| 83 |
INSERT INTO t2(a) VALUES ('2000-10-15'); |
| 84 |
SELECT * FROM t2; |
| 85 |
a v |
| 86 |
2002-02-15 00:00:00 0 |
| 87 |
2000-10-15 00:00:00 1 |
| 88 |
DROP TABLE t1, t2; |
| 89 |
CREATE TABLE t1 ( |
| 90 |
a char(255), b char(255), c char(255), d char(255), |
| 91 |
v char(255) AS (CONCAT(c,d) ) VIRTUAL |
| 92 |
); |
| 93 |
INSERT INTO t1(a,b,c,d) VALUES ('w','x','y','z'), ('W','X','Y','Z'); |
| 94 |
SELECT v FROM t1 ORDER BY CONCAT(a,b); |
| 95 |
v |
| 96 |
yz |
| 97 |
YZ |
| 98 |
DROP TABLE t1; |
| 99 |
CREATE TABLE t1 (f1 INTEGER, v1 INTEGER AS (f1) VIRTUAL); |
| 100 |
CREATE TABLE t2 AS SELECT v1 FROM t1; |
| 101 |
SHOW CREATE TABLE t2; |
| 102 |
Table Create Table |
| 103 |
t2 CREATE TABLE `t2` ( |
| 104 |
`v1` int(11) DEFAULT NULL |
| 105 |
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci |
| 106 |
DROP TABLE t1,t2; |
| 107 |
CREATE TABLE t1 (p int, a double NOT NULL, v double AS (ROUND(a,p)) VIRTUAL); |
| 108 |
INSERT IGNORE INTO t1 VALUES (0,1,0); |
| 109 |
Warnings: |
| 110 |
Warning 1906 The value specified for generated column 'v' in table 't1' has been ignored |
| 111 |
INSERT IGNORE INTO t1 VALUES (NULL,0,0); |
| 112 |
Warnings: |
| 113 |
Warning 1906 The value specified for generated column 'v' in table 't1' has been ignored |
| 114 |
SELECT a, p, v, ROUND(a,p), ROUND(a,p+NULL) FROM t1; |
| 115 |
a p v ROUND(a,p) ROUND(a,p+NULL) |
| 116 |
1 0 1 1 NULL |
| 117 |
0 NULL NULL NULL NULL |
| 118 |
DROP TABLE t1; |
| 119 |
CREATE TABLE t1 (p int, a double NOT NULL); |
| 120 |
INSERT INTO t1(p,a) VALUES (0,1); |
| 121 |
INSERT INTO t1(p,a) VALUES (NULL,0); |
| 122 |
SELECT a, p, ROUND(a,p), ROUND(a,p+NULL) FROM t1; |
| 123 |
a p ROUND(a,p) ROUND(a,p+NULL) |
| 124 |
1 0 1 NULL |
| 125 |
0 NULL NULL NULL |
| 126 |
DROP TABLE t1; |
| 127 |
CREATE TABLE t1 (a char(32), v char(32) CHARACTER SET ucs2 AS (a) VIRTUAL); |
| 128 |
SHOW CREATE TABLE t1; |
| 129 |
Table Create Table |
| 130 |
t1 CREATE TABLE `t1` ( |
| 131 |
`a` char(32) DEFAULT NULL, |
| 132 |
`v` char(32) CHARACTER SET ucs2 COLLATE ucs2_uca1400_ai_ci GENERATED ALWAYS AS (`a`) VIRTUAL |
| 133 |
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci |
| 134 |
DROP TABLE t1; |
| 135 |
CREATE TABLE t1 (a int, b int); |
| 136 |
CREATE TABLE t2 (a int, b int as (a+1) VIRTUAL); |
| 137 |
SELECT table_schema, table_name, column_name, column_type, extra |
| 138 |
FROM information_schema.columns WHERE table_name = 't1'; |
| 139 |
table_schema table_name column_name column_type extra |
| 140 |
test t1 a int(11) |
| 141 |
test t1 b int(11) |
| 142 |
SELECT table_schema, table_name, column_name, column_type, extra |
| 143 |
FROM information_schema.columns WHERE table_name = 't2'; |
| 144 |
table_schema table_name column_name column_type extra |
| 145 |
test t2 a int(11) |
| 146 |
test t2 b int(11) VIRTUAL GENERATED |
| 147 |
DROP TABLE t1,t2; |
| 148 |
create table t1 ( |
| 149 |
a int not null, b char(2) not null, |
| 150 |
c enum('Y','N') as (case when b = 'aa' then 'Y' else 'N' end) persistent |
| 151 |
); |
| 152 |
show create table t1; |
| 153 |
Table Create Table |
| 154 |
t1 CREATE TABLE `t1` ( |
| 155 |
`a` int(11) NOT NULL, |
| 156 |
`b` char(2) NOT NULL, |
| 157 |
`c` enum('Y','N') GENERATED ALWAYS AS (case when `b` = 'aa' then 'Y' else 'N' end) STORED |
| 158 |
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci |
| 159 |
insert into t1(a,b) values (1,'bb'), (2,'aa'), (3,'cc'); |
| 160 |
select * from t1; |
| 161 |
a b c |
| 162 |
1 bb N |
| 163 |
2 aa Y |
| 164 |
3 cc N |
| 165 |
create table t2 ( |
| 166 |
a int, b int, |
| 167 |
c set("y","n") |
| 168 |
as (if(a=0,if(b=0,('n,n'),('n,y')),if(b=0,('y,n'),('y,y')))) persistent |
| 169 |
); |
| 170 |
show create table t2; |
| 171 |
Table Create Table |
| 172 |
t2 CREATE TABLE `t2` ( |
| 173 |
`a` int(11) DEFAULT NULL, |
| 174 |
`b` int(11) DEFAULT NULL, |
| 175 |
`c` set('y','n') GENERATED ALWAYS AS (if(`a` = 0,if(`b` = 0,'n,n','n,y'),if(`b` = 0,'y,n','y,y'))) STORED |
| 176 |
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci |
| 177 |
insert into t2(a,b) values (7,0), (2,3), (0,1); |
| 178 |
select * from t2; |
| 179 |
a b c |
| 180 |
7 0 y,n |
| 181 |
2 3 y |
| 182 |
0 1 y,n |
| 183 |
drop table t1,t2; |
| 184 |
SET @old_debug= @@global.debug_dbug; |
| 185 |
SET @old_debug= @@global.debug_dbug; |
| 186 |
SET GLOBAL debug_dbug= "+d,write_delay_wakeup"; |
| 187 |
CREATE TABLE t1 (a int, |
| 188 |
ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, |
| 189 |
tsv TIMESTAMP AS (ADDDATE(ts, INTERVAL 1 DAY)) VIRTUAL |
| 190 |
) ENGINE=MyISAM; |
| 191 |
# First test FLUSH TABLES |
| 192 |
INSERT INTO t1 (a,tsv) VALUES (1,DEFAULT); |
| 193 |
INSERT DELAYED INTO t1 (a,tsv) VALUES (2,DEFAULT); |
| 194 |
FLUSH TABLES; |
| 195 |
SELECT COUNT(*) > 0 FROM t1; |
| 196 |
COUNT(*) > 0 |
| 197 |
1 |
| 198 |
# Then test FLUSH TABLES t1; |
| 199 |
INSERT INTO t1 (a,tsv) VALUES (3,DEFAULT); |
| 200 |
INSERT DELAYED INTO t1 (a,tsv) VALUES (4,DEFAULT); |
| 201 |
FLUSH TABLES t1; |
| 202 |
SELECT COUNT(*) FROM t1; |
| 203 |
COUNT(*) |
| 204 |
4 |
| 205 |
# Then test FLUSH TABLES WITH READ LOCK; |
| 206 |
INSERT INTO t1 (a,tsv) VALUES (5,DEFAULT); |
| 207 |
INSERT DELAYED INTO t1 (a,tsv) VALUES (6,DEFAULT); |
| 208 |
FLUSH TABLES WITH READ LOCK; |
| 209 |
SELECT COUNT(*) FROM t1; |
| 210 |
COUNT(*) |
| 211 |
6 |
| 212 |
set GLOBAL debug_dbug= @old_debug; |
| 213 |
unlock tables; |
| 214 |
DROP TABLE t1; |
| 215 |
# |
| 216 |
# MDEV-4823 Server crashes in Item_func_not::fix_fields on |
| 217 |
# creating a table with a virtual column using NOT |
| 218 |
# |
| 219 |
CREATE TABLE t1 ( f1 INT, v4 INT AS ( NOT f1 ) VIRTUAL ); |
| 220 |
drop table t1; |
| 221 |
# end of 5.2 tests |
| 222 |
create table t1 (a int, b int); |
| 223 |
insert into t1 values (3, 30), (4, 20), (1, 20); |
| 224 |
create table t2 (c int, d int, v int as (d+1), index idx(c)); |
| 225 |
insert into t2(c,d) values |
| 226 |
(20, 100), (20, 300), (30, 100), (30, 200), (40, 500), |
| 227 |
(70, 100), (40, 300), (60, 100), (40, 100), (70, 100); |
| 228 |
insert into t2(c,d) values |
| 229 |
(120, 100), (150, 300), (130, 100), (130, 200), (140, 500), |
| 230 |
(170, 100), (180, 300), (160, 100), (40, 100), (170, 100); |
| 231 |
set join_cache_level=6; |
| 232 |
explain |
| 233 |
select * from t1,t2 where t1.b=t2.c and d <= 100; |
| 234 |
id select_type table type possible_keys key key_len ref rows Extra |
| 235 |
1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using where |
| 236 |
1 SIMPLE t2 ref idx idx 5 test.t1.b 2 Using where |
| 237 |
select * from t1,t2 where t1.b=t2.c and d <= 100; |
| 238 |
a b c d v |
| 239 |
3 30 30 100 101 |
| 240 |
4 20 20 100 101 |
| 241 |
1 20 20 100 101 |
| 242 |
set join_cache_level=default; |
| 243 |
drop table t1, t2; |
| 244 |
create table t1 (a bigint, b bigint as (a > '2')); |
| 245 |
show create table t1; |
| 246 |
Table Create Table |
| 247 |
t1 CREATE TABLE `t1` ( |
| 248 |
`a` bigint(20) DEFAULT NULL, |
| 249 |
`b` bigint(20) GENERATED ALWAYS AS (`a` > 2) VIRTUAL |
| 250 |
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci |
| 251 |
insert into t1 (a) values (1),(3); |
| 252 |
select * from t1; |
| 253 |
a b |
| 254 |
1 0 |
| 255 |
3 1 |
| 256 |
select * from t1; |
| 257 |
a b |
| 258 |
1 0 |
| 259 |
3 1 |
| 260 |
drop table t1; |
| 261 |
create table t1 (a bigint, b bigint as (a between 0 and 2)); |
| 262 |
show create table t1; |
| 263 |
Table Create Table |
| 264 |
t1 CREATE TABLE `t1` ( |
| 265 |
`a` bigint(20) DEFAULT NULL, |
| 266 |
`b` bigint(20) GENERATED ALWAYS AS (`a` between 0 and 2) VIRTUAL |
| 267 |
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci |
| 268 |
insert into t1 (a) values (1),(3); |
| 269 |
select * from t1; |
| 270 |
a b |
| 271 |
1 1 |
| 272 |
3 0 |
| 273 |
select * from t1; |
| 274 |
a b |
| 275 |
1 1 |
| 276 |
3 0 |
| 277 |
drop table t1; |
| 278 |
create table t1 (a char(10), b char(10) as (a between 0 and 2)); |
| 279 |
show create table t1; |
| 280 |
Table Create Table |
| 281 |
t1 CREATE TABLE `t1` ( |
| 282 |
`a` char(10) DEFAULT NULL, |
| 283 |
`b` char(10) GENERATED ALWAYS AS (`a` between 0 and 2) VIRTUAL |
| 284 |
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci |
| 285 |
insert into t1 (a) values (1),(3); |
| 286 |
select * from t1; |
| 287 |
a b |
| 288 |
1 1 |
| 289 |
3 0 |
| 290 |
select * from t1; |
| 291 |
a b |
| 292 |
1 1 |
| 293 |
3 0 |
| 294 |
drop table t1; |
| 295 |
CREATE TABLE `t1` ( |
| 296 |
`a` int(11) NOT NULL, |
| 297 |
`b` varchar(32) DEFAULT NULL, |
| 298 |
`c` int(11) AS (a MOD 10) VIRTUAL, |
| 299 |
`d` varchar(5) AS (LEFT(b,5)) PERSISTENT |
| 300 |
) ENGINE=MyISAM; |
| 301 |
show create table t1; |
| 302 |
Table Create Table |
| 303 |
t1 CREATE TABLE `t1` ( |
| 304 |
`a` int(11) NOT NULL, |
| 305 |
`b` varchar(32) DEFAULT NULL, |
| 306 |
`c` int(11) GENERATED ALWAYS AS (`a` MOD 10) VIRTUAL, |
| 307 |
`d` varchar(5) GENERATED ALWAYS AS (left(`b`,5)) STORED |
| 308 |
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci |
| 309 |
show columns from t1; |
| 310 |
Field Type Null Key Default Extra |
| 311 |
a int(11) NO NULL |
| 312 |
b varchar(32) YES NULL |
| 313 |
c int(11) YES NULL VIRTUAL GENERATED |
| 314 |
d varchar(5) YES NULL STORED GENERATED |
| 315 |
show full columns from t1; |
| 316 |
Field Type Collation Null Key Default Extra Privileges Comment |
| 317 |
a int(11) NULL NO NULL # |
| 318 |
b varchar(32) utf8mb4_uca1400_ai_ci YES NULL # |
| 319 |
c int(11) NULL YES NULL VIRTUAL GENERATED # |
| 320 |
d varchar(5) utf8mb4_uca1400_ai_ci YES NULL STORED GENERATED # |
| 321 |
INSERT INTO `test`.`t1`(`a`,`b`,`c`,`d`) VALUES ( '1','a',NULL,NULL); |
| 322 |
UPDATE IGNORE `test`.`t1` SET `d`='b' WHERE `a`='1' AND `b`='a' AND `c`='1' AND `d`='a'; |
| 323 |
Warnings: |
| 324 |
Warning 1906 The value specified for generated column 'd' in table 't1' has been ignored |
| 325 |
INSERT IGNORE INTO `test`.`t1`(`a`,`b`,`c`,`d`) VALUES ( '1','a',NULL,'a'); |
| 326 |
Warnings: |
| 327 |
Warning 1906 The value specified for generated column 'd' in table 't1' has been ignored |
| 328 |
set sql_mode='strict_all_tables'; |
| 329 |
UPDATE `test`.`t1` SET `d`='b' WHERE `a`='1' AND `b`='a' AND `c`='1' AND `d`='a'; |
| 330 |
ERROR HY000: The value specified for generated column 'd' in table 't1' has been ignored |
| 331 |
INSERT INTO `test`.`t1`(`a`,`b`,`c`,`d`) VALUES ( '1','a',NULL,'a'); |
| 332 |
ERROR HY000: The value specified for generated column 'd' in table 't1' has been ignored |
| 333 |
drop table t1; |
| 334 |
# |
| 335 |
# MDEV-5611: self-referencing virtual column |
| 336 |
# |
| 337 |
create table t1 (a int, b int as (b is null) virtual); |
| 338 |
ERROR 01000: Expression for field `b` is referring to uninitialized field `b` |
| 339 |
create table t1 (a int as (1+1), b int as (a is null) virtual); |
| 340 |
drop table t1; |
| 341 |
# end of 5.3 tests |
| 342 |
create table t1 (v1 varchar(255) as (c1) persistent, c1 varchar(50)) collate=latin1_general_ci; |
| 343 |
show create table t1; |
| 344 |
Table Create Table |
| 345 |
t1 CREATE TABLE `t1` ( |
| 346 |
`v1` varchar(255) GENERATED ALWAYS AS (`c1`) STORED, |
| 347 |
`c1` varchar(50) DEFAULT NULL |
| 348 |
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci |
| 349 |
drop table t1; |
| 350 |
set sql_mode='no_zero_date'; |
| 351 |
create table t1 ( |
| 352 |
ts timestamp not null default current_timestamp, |
| 353 |
tsv timestamp as (adddate(ts, interval 1 day)) virtual |
| 354 |
); |
| 355 |
drop table t1; |
| 356 |
set sql_mode=default; |
| 357 |
# |
| 358 |
# MDEV-11819 NO_ZERO_IN_DATE: Incorrect generated column value |
| 359 |
# |
| 360 |
SET sql_mode='NO_ZERO_IN_DATE'; |
| 361 |
CREATE TABLE t1 |
| 362 |
( |
| 363 |
a datetime DEFAULT NULL, |
| 364 |
b datetime DEFAULT NULL, |
| 365 |
c time GENERATED ALWAYS AS (timediff(`a`,`b`)) VIRTUAL |
| 366 |
); |
| 367 |
INSERT INTO t1 VALUES ('2008-12-31 23:59:59.000001','2008-12-30 01:01:01.000002',DEFAULT); |
| 368 |
SELECT * FROM t1; |
| 369 |
a b c |
| 370 |
2008-12-31 23:59:59 2008-12-30 01:01:01 46:58:58 |
| 371 |
DROP TABLE t1; |
| 372 |
SET sql_mode=DEFAULT; |
| 373 |
# |
| 374 |
# MDEV-15834 The code in TABLE_SHARE::init_from_binary_frm_image() is not safe |
| 375 |
# |
| 376 |
SHOW TABLES; |
| 377 |
Tables_in_test |
| 378 |
t1 |
| 379 |
SHOW CREATE TABLE t1; |
| 380 |
ERROR HY000: Incorrect information in file: './test/t1.frm' |
| 381 |
ALTER TABLE t1; |
| 382 |
ERROR HY000: Incorrect information in file: './test/t1.frm' |
| 383 |
# |
| 384 |
# End of 5.5 tests |
| 385 |
# |
| 386 |
# |
| 387 |
# End of 10.0 tests |
| 388 |
# |
| 389 |
# |
| 390 |
# MDEV-8441 Bad SHOW CREATE TABLE output for a table with a virtual column |
| 391 |
# |
| 392 |
CREATE TABLE t1 (a DATETIME, b TIMESTAMP AS (TIMESTAMP(a))); |
| 393 |
SHOW CREATE TABLE t1; |
| 394 |
Table Create Table |
| 395 |
t1 CREATE TABLE `t1` ( |
| 396 |
`a` datetime DEFAULT NULL, |
| 397 |
`b` timestamp GENERATED ALWAYS AS (cast(`a` as datetime)) VIRTUAL |
| 398 |
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci |
| 399 |
DROP TABLE t1; |
| 400 |
CREATE TABLE t1 (a DATETIME, b TIMESTAMP AS (TIMESTAMP(a)),c TIMESTAMP); |
| 401 |
SHOW CREATE TABLE t1; |
| 402 |
Table Create Table |
| 403 |
t1 CREATE TABLE `t1` ( |
| 404 |
`a` datetime DEFAULT NULL, |
| 405 |
`b` timestamp GENERATED ALWAYS AS (cast(`a` as datetime)) VIRTUAL, |
| 406 |
`c` timestamp NULL DEFAULT NULL |
| 407 |
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci |
| 408 |
DROP TABLE t1; |
| 409 |
# |
| 410 |
# MDEV-8839 COLUMN_GET() produces warnings with no data |
| 411 |
# |
| 412 |
SET @aaa= COLUMN_CREATE('price', _binary 0xF0F1F2F3F4F5F6F7); |
| 413 |
SELECT COLUMN_GET(@aaa, 'price' AS DECIMAL) aaa; |
| 414 |
aaa |
| 415 |
0 |
| 416 |
Warnings: |
| 417 |
Warning 1918 Encountered illegal value '\xF0\xF1\xF2\xF3\xF4\xF5\xF6\xF7' when converting to DECIMAL |
| 418 |
SELECT COLUMN_GET(@aaa, 'price' AS INT) aaa; |
| 419 |
aaa |
| 420 |
0 |
| 421 |
Warnings: |
| 422 |
Warning 1918 Encountered illegal value '\xF0\xF1\xF2\xF3\xF4\xF5\xF6\xF7' when converting to INT |
| 423 |
SELECT COLUMN_GET(@aaa, 'price' AS DOUBLE) aaa; |
| 424 |
aaa |
| 425 |
0 |
| 426 |
Warnings: |
| 427 |
Warning 1918 Encountered illegal value '\xF0\xF1\xF2\xF3\xF4\xF5\xF6\xF7' when converting to DOUBLE |
| 428 |
# |
| 429 |
# MDEV-22579 No error when inserting DEFAULT(non_virtual_column) into a virtual column |
| 430 |
# |
| 431 |
SET sql_mode=STRICT_ALL_TABLES; |
| 432 |
CREATE OR REPLACE TABLE t1 ( |
| 433 |
a INT NOT NULL DEFAULT 10, |
| 434 |
b INT AS (a+1) VIRTUAL |
| 435 |
) ENGINE=MyISAM; |
| 436 |
INSERT INTO t1 (b) VALUES (10); |
| 437 |
ERROR HY000: The value specified for generated column 'b' in table 't1' has been ignored |
| 438 |
INSERT INTO t1 (b) VALUES (DEFAULT(a)); |
| 439 |
ERROR HY000: The value specified for generated column 'b' in table 't1' has been ignored |
| 440 |
INSERT INTO t1 (b) VALUES (DEFAULT); |
| 441 |
INSERT INTO t1 VALUES (10,10); |
| 442 |
ERROR HY000: The value specified for generated column 'b' in table 't1' has been ignored |
| 443 |
INSERT INTO t1 VALUES (10,DEFAULT(a)); |
| 444 |
ERROR HY000: The value specified for generated column 'b' in table 't1' has been ignored |
| 445 |
INSERT INTO t1 VALUES (10, DEFAULT); |
| 446 |
DROP TABLE t1; |
| 447 |
SET sql_mode=DEFAULT; |
| 448 |
# |
| 449 |
# End of 10.1 tests |
| 450 |
# |
| 451 |
# |
| 452 |
# MDEV-16518 MYSQL57_GENERATED_FIELD: The code in TABLE_SHARE::init_from_binary_frm_image() is not safe |
| 453 |
# |
| 454 |
SHOW TABLES; |
| 455 |
Tables_in_test |
| 456 |
t1 |
| 457 |
SHOW CREATE TABLE t1; |
| 458 |
ERROR HY000: Incorrect information in file: './test/t1.frm' |
| 459 |
ALTER TABLE t1; |
| 460 |
ERROR HY000: Incorrect information in file: './test/t1.frm' |
| 461 |
# |
| 462 |
# MDEV-19771 REPLACE on table with virtual_field can cause crash in set_ok_status() |
| 463 |
|
| 464 |
create or replace table t1 (pk int primary key, col_bit bit(15) default null, |
| 465 |
vcol_bit bit(10) GENERATED ALWAYS AS (`col_bit`) VIRTUAL); |
| 466 |
replace INTO `t1` (`pk`,col_bit) VALUES (99,1000); |
| 467 |
select pk, col_bit+0, vcol_bit+0 from t1; |
| 468 |
pk col_bit+0 vcol_bit+0 |
| 469 |
99 1000 1000 |
| 470 |
replace INTO `t1` (`pk`,col_bit) VALUES (99,10000); |
| 471 |
select pk, col_bit+0, vcol_bit+0 from t1; |
| 472 |
pk col_bit+0 vcol_bit+0 |
| 473 |
99 10000 1023 |
| 474 |
REPLACE LOW_PRIORITY INTO `t1` (`pk`) VALUES (99); |
| 475 |
ERROR 22001: Data too long for column 'vcol_bit' at row 1 |
| 476 |
drop table t1; |
| 477 |
# |
| 478 |
# MDEV-17837 REPLACE on table with virtual_field can cause crash in set_ok_status() |
| 479 |
# |
| 480 |
SET @old_sql_mode=@@sql_mode; |
| 481 |
SET sql_mode= STRICT_ALL_TABLES; |
| 482 |
CREATE TABLE t1 ( |
| 483 |
pk INT, |
| 484 |
i TINYINT, |
| 485 |
vi TINYINT AS (i+1) PERSISTENT, |
| 486 |
PRIMARY KEY(pk) |
| 487 |
); |
| 488 |
INSERT INTO t1 (pk,i) VALUES (1,1); |
| 489 |
TRUNCATE TABLE t1; |
| 490 |
INSERT IGNORE INTO t1 (pk,i) VALUES (1,127); |
| 491 |
Warnings: |
| 492 |
Warning 1264 Out of range value for column 'vi' at row 1 |
| 493 |
REPLACE INTO t1 (pk,i) VALUES (1,2); |
| 494 |
ERROR 22003: Out of range value for column 'vi' at row 1 |
| 495 |
DROP TABLE t1; |
| 496 |
SET @sql_mode=@old_sql_mode; |
| 497 |
# |
| 498 |
# MDEV-22579 No error when inserting DEFAULT(non_virtual_column) into a virtual column |
| 499 |
# 10.2+ specific part |
| 500 |
# |
| 501 |
SET sql_mode=STRICT_ALL_TABLES; |
| 502 |
CREATE OR REPLACE TABLE t1 ( |
| 503 |
a INT NOT NULL DEFAULT 10, |
| 504 |
b INT AS (a+1) VIRTUAL |
| 505 |
) ENGINE=MyISAM; |
| 506 |
EXECUTE IMMEDIATE 'INSERT INTO t1 (b) VALUES(?)' USING DEFAULT; |
| 507 |
EXECUTE IMMEDIATE 'INSERT INTO t1 (b) VALUES(?)' USING IGNORE; |
| 508 |
INSERT INTO t1 (b) VALUES (DEFAULT); |
| 509 |
INSERT INTO t1 (b) VALUES (IGNORE); |
| 510 |
SELECT * FROM t1; |
| 511 |
a b |
| 512 |
10 11 |
| 513 |
10 11 |
| 514 |
10 11 |
| 515 |
10 11 |
| 516 |
DELETE FROM t1; |
| 517 |
EXECUTE IMMEDIATE 'INSERT INTO t1 VALUES(10,?)' USING DEFAULT; |
| 518 |
EXECUTE IMMEDIATE 'INSERT INTO t1 VALUES(11,?)' USING IGNORE; |
| 519 |
INSERT INTO t1 VALUES (12,DEFAULT); |
| 520 |
INSERT INTO t1 VALUES (13,IGNORE); |
| 521 |
SELECT * FROM t1; |
| 522 |
a b |
| 523 |
10 11 |
| 524 |
11 12 |
| 525 |
12 13 |
| 526 |
13 14 |
| 527 |
DROP TABLE t1; |
| 528 |
SET sql_mode=DEFAULT; |
| 529 |
# |
| 530 |
# CONTEXT_ANALYSIS_ONLY_VCOL_EXPR |
| 531 |
# |
| 532 |
call mtr.add_suppression("Charset id.*trying to replace"); |
| 533 |
create table t1 (c1 char(1) character set ucs2 collate ucs2_test_ci, |
| 534 |
v1 char(1) character set ucs2 collate ucs2_test_ci as (c1), |
| 535 |
v2 int as (c1 = 'b'), |
| 536 |
v3 int as (v1 = 'b')); |
| 537 |
insert into t1 (c1) values ('a'); |
| 538 |
select * from t1 where v1 = 'b'; |
| 539 |
c1 v1 v2 v3 |
| 540 |
a a 1 1 |
| 541 |
show create table t1; |
| 542 |
Table Create Table |
| 543 |
t1 CREATE TABLE `t1` ( |
| 544 |
`c1` char(1) CHARACTER SET ucs2 COLLATE ucs2_test_ci DEFAULT NULL, |
| 545 |
`v1` char(1) CHARACTER SET ucs2 COLLATE ucs2_test_ci GENERATED ALWAYS AS (`c1`) VIRTUAL, |
| 546 |
`v2` int(11) GENERATED ALWAYS AS (`c1` = 'b') VIRTUAL, |
| 547 |
`v3` int(11) GENERATED ALWAYS AS (`v1` = 'b') VIRTUAL |
| 548 |
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci |
| 549 |
drop table t1; |
| 550 |
# |
| 551 |
# End of 10.2 tests |
| 552 |
# |
| 553 |
# |
| 554 |
# RAND is session func |
| 555 |
# |
| 556 |
create table t1 (a int, b float default rand(1)); |
| 557 |
insert into t1 (a) values (1); |
| 558 |
insert into t1 (a) values (2); |
| 559 |
insert into t1 (a) values (3); |
| 560 |
select * from t1; |
| 561 |
a b |
| 562 |
1 0.405404 |
| 563 |
2 0.405404 |
| 564 |
3 0.405404 |
| 565 |
drop table t1; |
| 566 |
# |
| 567 |
# End of 10.3 tests |
| 568 |
# |
| 569 |
# |
| 570 |
# MDEV-31112 vcol circular references lead to stack overflow |
| 571 |
# |
| 572 |
create table t (a int, c int as (a)); |
| 573 |
alter table t alter column c drop default; |
| 574 |
alter table t modify column a int as (c) stored; |
| 575 |
ERROR 01000: Expression for field `a` is referring to uninitialized field `c` |
| 576 |
drop table t; |
| 577 |
# |
| 578 |
# End of 10.4 tests |
| 579 |
# |
| 580 |
# |
| 581 |
# MDEV-26891 Segfault in Field::register_field_in_read_map upon INSERT DELAYED with virtual columns |
| 582 |
# |
| 583 |
CREATE TABLE t ( |
| 584 |
id INT AUTO_INCREMENT, |
| 585 |
a varchar(16) NOT NULL DEFAULT '', |
| 586 |
b varchar(16) GENERATED ALWAYS AS (a) VIRTUAL, |
| 587 |
KEY `col_year` (b(8),id) |
| 588 |
) ENGINE=MyISAM; |
| 589 |
INSERT DELAYED INTO t (a) VALUES ('foo'),('bar'); |
| 590 |
DROP TABLE t; |
| 591 |
# |
| 592 |
# End of 10.5 tests |
| 593 |
# |