| 1 |
#Want to skip this test from daily Valgrind execution |
| 2 |
--source include/no_valgrind_without_big.inc |
| 3 |
--disable_warnings |
| 4 |
DROP TABLE IF EXISTS t1,t2,t3,t4,t5,t6,t7,t8,t9,t10,t11,t12,t13,t14,t15,t16,t17,t18; |
| 5 |
--enable_warnings |
| 6 |
SET sql_mode='NO_ENGINE_SUBSTITUTION'; |
| 7 |
######## Running UPDATE tests ######## |
| 8 |
|
| 9 |
# Generic tables with mixed field types and sizes |
| 10 |
CREATE TABLE t1(c1 CHAR(255), c2 TEXT); |
| 11 |
eval INSERT INTO t1 VALUES(REPEAT('abcdef',40), REPEAT('1',65535)); |
| 12 |
eval INSERT INTO t1 VALUES(REPEAT('abc',80), REPEAT('2',65533)); |
| 13 |
eval INSERT INTO t1 VALUES(REPEAT('\t',255), REPEAT('3',65534)); |
| 14 |
UPDATE t1 SET c1=REPEAT('\n',255) WHERE c1=REPEAT('\t',255); |
| 15 |
--sorted_result |
| 16 |
SELECT LENGTH(c1),LENGTH(c2),CHAR_LENGTH(c2) FROM t1; |
| 17 |
--sorted_result |
| 18 |
SELECT c1, c2 FROM t1 where c1= REPEAT('\n',255); |
| 19 |
#--warning 1265 |
| 20 |
UPDATE t1 SET c1=REPEAT('xyz',85), c2=REPEAT(c1, 100) ORDER by c1 LIMIT 2; |
| 21 |
SHOW WARNINGS; |
| 22 |
|
| 23 |
--sorted_result |
| 24 |
SELECT LENGTH(c2) FROM t1 WHERE c2 LIKE 'xyz%'; |
| 25 |
--sorted_result |
| 26 |
SELECT LENGTH(c1),LENGTH(c2),CHAR_LENGTH(c2) FROM t1; |
| 27 |
--sorted_result |
| 28 |
SELECT c1, c2 FROM t1; |
| 29 |
--disable_query_log |
| 30 |
eval INSERT INTO t1 VALUES(REPEAT('\0',255),LOAD_FILE('$MYSQLTEST_VARDIR/sample.txt')); |
| 31 |
--enable_query_log |
| 32 |
ALTER TABLE t1 MODIFY c1 TEXT; |
| 33 |
UPDATE t1 SET c1=LOAD_FILE('$MYSQLTEST_VARDIR/temp.txt') LIMIT 2; |
| 34 |
SHOW WARNINGS; |
| 35 |
UPDATE t1 SET c1=NULL WHERE c1 LIKE 'xyz%'; |
| 36 |
DELETE FROM t1 LIMIT 1; |
| 37 |
DELETE FROM t1 WHERE c1 NOT LIKE 'xyz%' LIMIT 1; |
| 38 |
--sorted_result |
| 39 |
SELECT length(c1), c1, length(c2), c2 FROM t1; |
| 40 |
CREATE TABLE t2(c1 BLOB, c2 TINYBLOB, c3 TEXT); |
| 41 |
eval INSERT INTO t2 VALUES(REPEAT('1',65535),REPEAT('a',254),REPEAT('d',65534 )); |
| 42 |
eval INSERT INTO t2 VALUES(REPEAT('2',65534),REPEAT('b',253),REPEAT('e',65535 )); |
| 43 |
eval INSERT INTO t2 VALUES(REPEAT('3',65533),REPEAT('c',255),REPEAT('f',65533)); |
| 44 |
--sorted_result |
| 45 |
SELECT c1,c2 FROM t2; |
| 46 |
let $i=100; |
| 47 |
while($i){ |
| 48 |
let $j=100; |
| 49 |
while($j){ |
| 50 |
eval INSERT INTO t2 VALUES('abcde\t \t fgh \n\n ', ' sdsdsd',NULL); |
| 51 |
dec $j; |
| 52 |
} |
| 53 |
dec $i; |
| 54 |
} |
| 55 |
--sorted_result |
| 56 |
SELECT length(c1),c1, length(c2),c2, length(c3), c3 FROM t2; |
| 57 |
UPDATE t2 SET c3='Not NULL' WHERE c3=NULL; |
| 58 |
--sorted_result |
| 59 |
SELECT length(c1),c1, length(c2),c2, length(c3), c3 FROM t2; |
| 60 |
# Multi-table updates/deletes |
| 61 |
CREATE TABLE t3 (c1 CHAR(10), c2 CHAR(100)); |
| 62 |
INSERT INTO t3 VALUES ('AS','Axel Soa'), ('AK','Axle Kora'), ('HH','Hulk Hogan'); |
| 63 |
CREATE TABLE t4 (c1 CHAR(10), c2 CHAR(100)); |
| 64 |
INSERT INTO t4 VALUES ('AS','#100, Avenue, UK'), ('AK','#101, Avenue, US'), ('HH','#103, Avenu MT'); |
| 65 |
UPDATE t4 SET c2='#102, Avennue MT' WHERE c1='HH'; |
| 66 |
--sorted_result |
| 67 |
SELECT LENGTH(c1), c1 , LENGTH(c2),c2 FROM t4; |
| 68 |
DELETE FROM t3 USING t3,t4 WHERE t3.c1='AS' AND t3.c1=t4.c1 ; |
| 69 |
--sorted_result |
| 70 |
SELECT length(c1), c1, length(c2), c2 FROM t3; |
| 71 |
DROP TABLE t3,t4; |
| 72 |
CREATE TABLE t3 (id int(11) NOT NULL default '0',name varchar(10) default NULL,PRIMARY KEY (id)) ; |
| 73 |
INSERT INTO t3 VALUES (1, 'aaa'),(2,'aaa'),(3,'aaa'); |
| 74 |
CREATE TABLE t4 (id int(11) NOT NULL default '0',name varchar(10) default NULL, PRIMARY KEY (id)) ; |
| 75 |
INSERT INTO t4 VALUES (2,'bbb'),(3,'bbb'),(4,'bbb'); |
| 76 |
CREATE TABLE t5 (id int(11) NOT NULL default '0', mydate datetime default NULL,PRIMARY KEY (id)); |
| 77 |
INSERT INTO t5 VALUES (1,'2002-02-04 00:00:00'),(3,'2002-05-12 00:00:00'),(5,'2002-05-12 00:00:00'),(6,'2002-06-22 00:00:00'),(7,'2002-07-22 00:00:00'); |
| 78 |
delete t3,t4,t5 from t3,t4,t5 where to_days(now())-to_days(t5.mydate)>=30 and t5.id=t3.id and t5.id=t4.id; |
| 79 |
select * from t5; |
| 80 |
DROP TABLE t3,t4,t5; |
| 81 |
CREATE TABLE t6 (a char(2) not null primary key, b varchar(20) not null, key (b)); |
| 82 |
CREATE TABLE t7 (a char(2) not null primary key, b varchar(20) not null, key (b)); |
| 83 |
INSERT INTO t6 values ('AB','MySQLAB'),('JA','Sun Microsystems'),('MS','Microsoft'),('IB','IBM- Inc.'),('GO','Google Inc.'); |
| 84 |
INSERT INTO t7 values ('AB','Sweden'),('JA','USA'),('MS','United States of Amercica'),('IB','North America'),('GO','South America'); |
| 85 |
update t6,t7 set t6.a=LCASE(t6.a); |
| 86 |
--sorted_result |
| 87 |
select * from t6; |
| 88 |
update t6,t7 set t6.a=UCASE(t6.a) where t6.a='AB'; |
| 89 |
--sorted_result |
| 90 |
select * from t6; |
| 91 |
update t6,t7 set t6.b=UPPER(t6.b) where t6.b LIKE 'United%'; |
| 92 |
--sorted_result |
| 93 |
select * from t7; |
| 94 |
update t6,t7 set t6.b=UPPER(t6.b),t7.b=LOWER(t7.b) where LENGTH(t6.b) between 3 and 5 and t7.a=LOWER(t6.a); |
| 95 |
--sorted_result |
| 96 |
select * from t6; |
| 97 |
--sorted_result |
| 98 |
select * from t7; |
| 99 |
drop table t6,t7; |
| 100 |
# Test for some STRING functions on TEXT columns |
| 101 |
CREATE TABLE t12(c1 TINYTEXT, c2 TEXT, c3 MEDIUMTEXT, c4 LONGTEXT); |
| 102 |
eval INSERT INTO t12 values (" This is a test ","\0 \0 for STRING","functions available \t in", " \t\t MySQL " ); |
| 103 |
eval UPDATE t12 SET c1="This is a update test!" WHERE c2 LIKE "\0%"; |
| 104 |
--sorted_result |
| 105 |
SELECT LENGTH(c1),LENGTH(c2),LENGTH(c3),LENGTH(c4) FROM t12; |
| 106 |
--replace_regex /[^[:print:]]/#/ |
| 107 |
--sorted_result |
| 108 |
SELECT LEFT(c1,5),LEFT(c2,5),LEFT(c3,5),LEFT(c4,5) FROM t12; |
| 109 |
--replace_regex /[^[:print:]]/#/ |
| 110 |
--sorted_result |
| 111 |
SELECT RTRIM(c1),RTRIM(c2),RTRIM(c3),RTRIM(c4) FROM t12; |
| 112 |
--replace_regex /[^[:print:]]/#/ |
| 113 |
--sorted_result |
| 114 |
SELECT HEX(c1),HEX(c2),HEX(c3),HEX(c4) FROM t12; |
| 115 |
--replace_regex /[^[:print:]]/#/ |
| 116 |
--sorted_result |
| 117 |
SELECT REVERSE(c1),REVERSE(c2),REVERSE(c3),REVERSE(c4) FROM t12; |
| 118 |
DELETE FROM t12 WHERE C1 LIKE "%update%"; |
| 119 |
--sorted_result |
| 120 |
SELECT HEX(c1),HEX(c2),HEX(c3),HEX(c4) FROM t12; |
| 121 |
# Test for SET datatype |
| 122 |
CREATE TABLE t16 (c1 SET('a', 'b', 'c', 'd', 'e')); |
| 123 |
eval INSERT INTO t16 VALUES (('d,a,d,d')); |
| 124 |
--sorted_result |
| 125 |
SELECT c1 FROM t16; |
| 126 |
eval INSERT INTO t16 (c1) VALUES ('a,b,d'),('d,A ,b'); |
| 127 |
--sorted_result |
| 128 |
SELECT c1 FROM t16; |
| 129 |
#--warning WARN_DATA_TRUNCATED |
| 130 |
eval INSERT INTO t16 (c1) VALUES ('f,a,b'); |
| 131 |
SHOW WARNINGS; |
| 132 |
--sorted_result |
| 133 |
SELECT c1 FROM t16; |
| 134 |
#--warning ER_DUPLICATED_VALUE_IN_TYPE |
| 135 |
CREATE TABLE t17 (c1 SET('a','b','a','b')); |
| 136 |
SHOW WARNINGS; |
| 137 |
# Tests for ENUM datatype |
| 138 |
# Invalid ENUM value insert behavior |
| 139 |
CREATE TABLE t18 (c1 CHAR(4),c2 enum('SMALL','MEDIUM','LARGE','VERY LARGE')); |
| 140 |
eval INSERT INTO t18 VALUES('SIZE', 'SMALL'); |
| 141 |
eval INSERT INTO t18 VALUES('SIZE', 'SMALL1'); |
| 142 |
--sorted_result |
| 143 |
SELECT * FROM t18 WHERE c2=0; |
| 144 |
EXPLAIN SELECT * FROM t18 WHERE c2=0; |
| 145 |
eval SET sql_mode= default; |
| 146 |
--error 1265 |
| 147 |
eval INSERT INTO t18 VALUES('SIZE','SMALL2'); |
| 148 |
--sorted_result |
| 149 |
SELECT * FROM t18 WHERE c2=0; |
| 150 |
DROP TABLE t17,t18; |
| 151 |
DROP TABLE IF EXISTS t1,t2,t3,t4,t5,t6,t7,t8,t9,t10,t11,t12,t13,t14,t15,t16,t17,t18; |