Debian Codegraph @

mysql-test/suite/engines/iuds/t/strings_update_delete.test

raw
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;