diff options
Diffstat (limited to 'mysql-test/t')
53 files changed, 2399 insertions, 66 deletions
diff --git a/mysql-test/t/alter_table.test b/mysql-test/t/alter_table.test index 3e1e7952af1..874c42ac0b6 100644 --- a/mysql-test/t/alter_table.test +++ b/mysql-test/t/alter_table.test @@ -518,4 +518,81 @@ SHOW INDEX FROM bug24219_2; DROP TABLE bug24219_2; +# +# Bug#24562 (ALTER TABLE ... ORDER BY ... with complex expression asserts) +# + +--disable_warnings +drop table if exists table_24562; +--enable_warnings + +create table table_24562( + section int, + subsection int, + title varchar(50)); + +insert into table_24562 values +(1, 0, "Introduction"), +(1, 1, "Authors"), +(1, 2, "Acknowledgements"), +(2, 0, "Basics"), +(2, 1, "Syntax"), +(2, 2, "Client"), +(2, 3, "Server"), +(3, 0, "Intermediate"), +(3, 1, "Complex queries"), +(3, 2, "Stored Procedures"), +(3, 3, "Stored Functions"), +(4, 0, "Advanced"), +(4, 1, "Replication"), +(4, 2, "Load balancing"), +(4, 3, "High availability"), +(5, 0, "Conclusion"); + +select * from table_24562; + +alter table table_24562 add column reviewer varchar(20), +order by title; + +select * from table_24562; + +update table_24562 set reviewer="Me" where section=2; +update table_24562 set reviewer="You" where section=3; + +alter table table_24562 +order by section ASC, subsection DESC; + +select * from table_24562; + +alter table table_24562 +order by table_24562.subsection ASC, table_24562.section DESC; + +select * from table_24562; + +--error 1064 +alter table table_24562 order by 12; +--error 1064 +alter table table_24562 order by (section + 12); +--error 1064 +alter table table_24562 order by length(title); +--error 1064 +alter table table_24562 order by (select 12 from dual); + +--error 1054 +alter table table_24562 order by no_such_col; + +drop table table_24562; + +# +# Bug #20710: adding unique index of column with duplicated +# long values to reproduce error message with truncated key value. +# + +CREATE TABLE t1 (c1 CHAR(255)); +INSERT INTO t1 VALUES (REPEAT("x", 255)), (REPEAT("x", 255)); +--error 1062 +ALTER TABLE t1 ADD UNIQUE (c1); +DROP TABLE t1; + # End of 4.1 tests + diff --git a/mysql-test/t/backup.test b/mysql-test/t/backup.test index b6b3ef1c060..0f1881368a9 100644 --- a/mysql-test/t/backup.test +++ b/mysql-test/t/backup.test @@ -51,5 +51,6 @@ unlock tables; connection con1; reap; drop table t5; +--system rm $MYSQLTEST_VARDIR/tmp/t?.* # End of 4.1 tests diff --git a/mysql-test/t/blackhole.test b/mysql-test/t/blackhole.test index 257770d311c..4375f1c13ce 100644 --- a/mysql-test/t/blackhole.test +++ b/mysql-test/t/blackhole.test @@ -126,4 +126,13 @@ show binlog events; drop table t1,t2,t3; +# +# BUG#27998 - mysqld crashed when executing INSERT DELAYED on a BLACKHOLE +# table +# +CREATE TABLE t1(a INT) ENGINE=BLACKHOLE; +--error 1031 +INSERT DELAYED INTO t1 VALUES(1); +DROP TABLE t1; + # End of 4.1 tests diff --git a/mysql-test/t/cast.test b/mysql-test/t/cast.test index b214cef10fa..8eef66f9e1b 100644 --- a/mysql-test/t/cast.test +++ b/mysql-test/t/cast.test @@ -167,4 +167,16 @@ INSERT INTO t1 SET f1 = +1.0e+30 ; SELECT f1 AS double_val, CAST(f1 AS SIGNED INT) AS cast_val FROM t1; DROP TABLE t1; -# End of 4.1 tests +# +# Bug #23938: cast(NULL as DATE) +# + +select isnull(date(NULL)), isnull(cast(NULL as DATE)); + +# +# Bug#23656: Wrong result of CAST from DATE to int +# +SELECT CAST(cast('01-01-01' as date) AS UNSIGNED); +SELECT CAST(cast('01-01-01' as date) AS SIGNED); + +--echo End of 4.1 tests diff --git a/mysql-test/t/ctype_hebrew.test b/mysql-test/t/ctype_hebrew.test new file mode 100644 index 00000000000..f786d05141d --- /dev/null +++ b/mysql-test/t/ctype_hebrew.test @@ -0,0 +1,16 @@ +# +# BUG #24037: Lossy Hebrew to Unicode conversion +# +# Test if LRM and RLM characters are correctly converted to UTF-8 +--disable_warnings +DROP TABLE IF EXISTS t1; +--enable_warnings + +SET NAMES hebrew; +CREATE TABLE t1 (a char(1)) DEFAULT CHARSET=hebrew; +INSERT INTO t1 VALUES (0xFD),(0xFE); +ALTER TABLE t1 CONVERT TO CHARACTER SET utf8; +SELECT HEX(a) FROM t1; +DROP TABLE t1; + +--echo End of 4.1 tests diff --git a/mysql-test/t/ctype_utf8.test b/mysql-test/t/ctype_utf8.test index af40121852f..0b3f9ed2400 100644 --- a/mysql-test/t/ctype_utf8.test +++ b/mysql-test/t/ctype_utf8.test @@ -94,6 +94,26 @@ select * from t1; drop table t1; # +# Bug#22646 LC_TIME_NAMES: Assignment to non-UTF8 target fails +# +set names utf8; +set LC_TIME_NAMES='fr_FR'; +create table t1 (s1 char(20) character set latin1); +insert into t1 values (date_format('2004-02-02','%M')); +select hex(s1) from t1; +drop table t1; +create table t1 (s1 char(20) character set koi8r); +set LC_TIME_NAMES='ru_RU'; +insert into t1 values (date_format('2004-02-02','%M')); +insert into t1 values (date_format('2004-02-02','%b')); +insert into t1 values (date_format('2004-02-02','%W')); +insert into t1 values (date_format('2004-02-02','%a')); +select hex(s1), s1 from t1; +drop table t1; +set LC_TIME_NAMES='en_US'; + + +# # Bug #2366 Wrong utf8 behaviour when data is truncated # set names koi8r; diff --git a/mysql-test/t/delayed.test b/mysql-test/t/delayed.test index a32eec536ea..961bcc946bf 100644 --- a/mysql-test/t/delayed.test +++ b/mysql-test/t/delayed.test @@ -1,3 +1,5 @@ +# delayed works differently in embedded server +--source include/not_embedded.inc # # test of DELAYED insert and timestamps # (Can't be tested with purify :( ) diff --git a/mysql-test/t/delete.test b/mysql-test/t/delete.test index 2036b59d810..301b2cdbb99 100644 --- a/mysql-test/t/delete.test +++ b/mysql-test/t/delete.test @@ -174,4 +174,14 @@ delete from t1 where a is null; select count(*) from t1; drop table t1; +# +# Bug#17711: DELETE doesn't use index when ORDER BY, LIMIT and +# non-restricting WHERE is present. +# +create table t1(f1 int primary key); +insert into t1 values (4),(3),(1),(2); +delete from t1 where (@a:= f1) order by f1 limit 1; +select @a; +drop table t1; + --echo End of 4.1 tests diff --git a/mysql-test/t/distinct.test b/mysql-test/t/distinct.test index a057eee8e37..bd46115231e 100644 --- a/mysql-test/t/distinct.test +++ b/mysql-test/t/distinct.test @@ -1,3 +1,5 @@ +# test script should specify proper directories for embedded +--source include/not_embedded.inc # # Bug with distinct and INSERT INTO # Bug with group by and not used fields diff --git a/mysql-test/t/fulltext.test b/mysql-test/t/fulltext.test index d5ce6241490..1a9a6b578dc 100644 --- a/mysql-test/t/fulltext.test +++ b/mysql-test/t/fulltext.test @@ -369,4 +369,14 @@ EXECUTE stmt; DEALLOCATE PREPARE stmt; DROP TABLE t1; +# +# BUG#25951 - ignore/use index does not work with fulltext +# +CREATE TABLE t1 (a VARCHAR(255), FULLTEXT(a)); +SELECT * FROM t1 IGNORE INDEX(a) WHERE MATCH(a) AGAINST('test'); +ALTER TABLE t1 DISABLE KEYS; +--error 1191 +SELECT * FROM t1 WHERE MATCH(a) AGAINST('test'); +DROP TABLE t1; + # End of 4.1 tests diff --git a/mysql-test/t/fulltext_left_join.test b/mysql-test/t/fulltext_left_join.test index 3bb1f0b7309..516c006b109 100644 --- a/mysql-test/t/fulltext_left_join.test +++ b/mysql-test/t/fulltext_left_join.test @@ -45,4 +45,45 @@ select * from t1 left join t2 on (venue_id = entity_id and match(name) against(' select * from t1 left join t2 on (venue_id = entity_id and match(name) against('aberdeen')) where dt = '2003-05-23 19:30:00'; drop table t1,t2; +# +# BUG#14708 +# Inconsistent treatment of NULLs in LEFT JOINed FULLTEXT matching without index +# + +create table t1 (id int not null primary key, d char(200) not null, e char(200)); +insert into t1 values (1, 'aword', null), (2, 'aword', 'bword'), (3, 'bword', null), (4, 'bword', 'aword'), (5, 'aword and bword', null); +select * from t1 where match(d, e) against ('+aword +bword' in boolean mode); +create table t2 (m_id int not null, f char(200), key (m_id)); +insert into t2 values (1, 'bword'), (3, 'aword'), (5, ''); +select * from t1 left join t2 on m_id = id where match(d, e, f) against ('+aword +bword' in boolean mode); +drop table t1,t2; + +# +# BUG#25637: LEFT JOIN with BOOLEAN FULLTEXT loses left table matches +# (this is actually the same bug as bug #14708) +# + +CREATE TABLE t1 ( + id int(10) NOT NULL auto_increment, + link int(10) default NULL, + name mediumtext default NULL, + PRIMARY KEY (id), + FULLTEXT (name) +); +INSERT INTO t1 VALUES (1, 1, 'string'); +INSERT INTO t1 VALUES (2, 0, 'string'); +CREATE TABLE t2 ( + id int(10) NOT NULL auto_increment, + name mediumtext default NULL, + PRIMARY KEY (id), + FULLTEXT (name) +); +INSERT INTO t2 VALUES (1, 'string'); + +SELECT t1.*, MATCH(t1.name) AGAINST('string') AS relevance + FROM t1 LEFT JOIN t2 ON t1.link = t2.id + WHERE MATCH(t1.name, t2.name) AGAINST('string' IN BOOLEAN MODE); + +DROP TABLE t1,t2; + # End of 4.1 tests diff --git a/mysql-test/t/func_str.test b/mysql-test/t/func_str.test index 3c855a32eed..5897674d1d4 100644 --- a/mysql-test/t/func_str.test +++ b/mysql-test/t/func_str.test @@ -713,4 +713,12 @@ EXPLAIN EXTENDED SELECT s FROM t1 WHERE TRIM(BOTH 'y' FROM s) > 'ab'; DROP TABLE t1; +# +# Bug#23409: ENCODE() and DECODE() functions aren't printed correctly +# +create table t1(f1 varchar(4)); +explain extended select encode(f1,'zxcv') as 'enc' from t1; +explain extended select decode(f1,'zxcv') as 'enc' from t1; +drop table t1; + --echo End of 4.1 tests diff --git a/mysql-test/t/func_time.test b/mysql-test/t/func_time.test index fa12c45db04..2fb5ea04a19 100644 --- a/mysql-test/t/func_time.test +++ b/mysql-test/t/func_time.test @@ -492,4 +492,11 @@ union select last_day('0000-00-00'); +# +# Bug 23616: datetime functions with double argumets +# + +select isnull(week(now() + 0)), isnull(week(now() + 0.2)), + week(20061108), week(20061108.01), week(20061108085411.000002); + --echo End of 4.1 tests diff --git a/mysql-test/t/gis-rtree.test b/mysql-test/t/gis-rtree.test index cdd8d1f3f0f..f28a718cc11 100644 --- a/mysql-test/t/gis-rtree.test +++ b/mysql-test/t/gis-rtree.test @@ -242,4 +242,560 @@ INSERT INTO t1 (foo) VALUES (PointFromWKB(POINT(0,1))); INSERT INTO t1 (foo) VALUES (PointFromWKB(POINT(0,0))); SELECT 1 FROM t1 WHERE foo != PointFromWKB(POINT(0,0)); DROP TABLE t1; + +# +# Bug#25673 - spatial index corruption, error 126 incorrect key file for table +# +CREATE TABLE t1 (id bigint(12) unsigned NOT NULL auto_increment, + c2 varchar(15) collate utf8_bin default NULL, + c1 varchar(15) collate utf8_bin default NULL, + c3 varchar(10) collate utf8_bin default NULL, + spatial_point point NOT NULL, + PRIMARY KEY(id), + SPATIAL KEY (spatial_point(32)) + )ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin; +# +INSERT INTO t1 (c2, c1, c3, spatial_point) VALUES + ('y', 's', 'j', GeomFromText('POINT(167 74)')), + ('r', 'n', 'd', GeomFromText('POINT(215 118)')), + ('g', 'n', 'e', GeomFromText('POINT(203 98)')), + ('h', 'd', 'd', GeomFromText('POINT(54 193)')), + ('r', 'x', 'y', GeomFromText('POINT(47 69)')), + ('t', 'q', 'r', GeomFromText('POINT(109 42)')), + ('a', 'z', 'd', GeomFromText('POINT(0 154)')), + ('x', 'v', 'o', GeomFromText('POINT(174 131)')), + ('b', 'r', 'a', GeomFromText('POINT(114 253)')), + ('x', 'z', 'i', GeomFromText('POINT(163 21)')), + ('w', 'p', 'i', GeomFromText('POINT(42 102)')), + ('g', 'j', 'j', GeomFromText('POINT(170 133)')), + ('m', 'g', 'n', GeomFromText('POINT(28 22)')), + ('b', 'z', 'h', GeomFromText('POINT(174 28)')), + ('q', 'k', 'f', GeomFromText('POINT(233 73)')), + ('w', 'w', 'a', GeomFromText('POINT(124 200)')), + ('t', 'j', 'w', GeomFromText('POINT(252 101)')), + ('d', 'r', 'd', GeomFromText('POINT(98 18)')), + ('w', 'o', 'y', GeomFromText('POINT(165 31)')), + ('y', 'h', 't', GeomFromText('POINT(14 220)')), + ('d', 'p', 'u', GeomFromText('POINT(223 196)')), + ('g', 'y', 'g', GeomFromText('POINT(207 96)')), + ('x', 'm', 'n', GeomFromText('POINT(214 3)')), + ('g', 'v', 'e', GeomFromText('POINT(140 205)')), + ('g', 'm', 'm', GeomFromText('POINT(10 236)')), + ('i', 'r', 'j', GeomFromText('POINT(137 228)')), + ('w', 's', 'p', GeomFromText('POINT(115 6)')), + ('o', 'n', 'k', GeomFromText('POINT(158 129)')), + ('j', 'h', 'l', GeomFromText('POINT(129 72)')), + ('f', 'x', 'l', GeomFromText('POINT(139 207)')), + ('u', 'd', 'n', GeomFromText('POINT(125 109)')), + ('b', 'a', 'z', GeomFromText('POINT(30 32)')), + ('m', 'h', 'o', GeomFromText('POINT(251 251)')), + ('f', 'r', 'd', GeomFromText('POINT(243 211)')), + ('b', 'd', 'r', GeomFromText('POINT(232 80)')), + ('g', 'k', 'v', GeomFromText('POINT(15 100)')), + ('i', 'f', 'c', GeomFromText('POINT(109 66)')), + ('r', 't', 'j', GeomFromText('POINT(178 6)')), + ('y', 'n', 'f', GeomFromText('POINT(233 211)')), + ('f', 'y', 'm', GeomFromText('POINT(99 16)')), + ('z', 'q', 'l', GeomFromText('POINT(39 49)')), + ('j', 'c', 'r', GeomFromText('POINT(75 187)')), + ('c', 'y', 'y', GeomFromText('POINT(246 253)')), + ('w', 'u', 'd', GeomFromText('POINT(56 190)')), + ('n', 'q', 'm', GeomFromText('POINT(73 149)')), + ('d', 'y', 'a', GeomFromText('POINT(134 6)')), + ('z', 's', 'w', GeomFromText('POINT(216 225)')), + ('d', 'u', 'k', GeomFromText('POINT(132 70)')), + ('f', 'v', 't', GeomFromText('POINT(187 141)')), + ('r', 'r', 'a', GeomFromText('POINT(152 39)')), + ('y', 'p', 'o', GeomFromText('POINT(45 27)')), + ('p', 'n', 'm', GeomFromText('POINT(228 148)')), + ('e', 'g', 'e', GeomFromText('POINT(88 81)')), + ('m', 'a', 'h', GeomFromText('POINT(35 29)')), + ('m', 'h', 'f', GeomFromText('POINT(30 71)')), + ('h', 'k', 'i', GeomFromText('POINT(244 78)')), + ('z', 'v', 'd', GeomFromText('POINT(241 38)')), + ('q', 'l', 'j', GeomFromText('POINT(13 71)')), + ('s', 'p', 'g', GeomFromText('POINT(108 38)')), + ('q', 's', 'j', GeomFromText('POINT(92 101)')), + ('l', 'h', 'g', GeomFromText('POINT(120 78)')), + ('w', 't', 'b', GeomFromText('POINT(193 109)')), + ('b', 's', 's', GeomFromText('POINT(223 211)')), + ('w', 'w', 'y', GeomFromText('POINT(122 42)')), + ('q', 'c', 'c', GeomFromText('POINT(104 102)')), + ('w', 'g', 'n', GeomFromText('POINT(213 120)')), + ('p', 'q', 'a', GeomFromText('POINT(247 148)')), + ('c', 'z', 'e', GeomFromText('POINT(18 106)')), + ('z', 'u', 'n', GeomFromText('POINT(70 133)')), + ('j', 'n', 'x', GeomFromText('POINT(232 13)')), + ('e', 'h', 'f', GeomFromText('POINT(22 135)')), + ('w', 'l', 'f', GeomFromText('POINT(9 180)')), + ('a', 'v', 'q', GeomFromText('POINT(163 228)')), + ('i', 'z', 'o', GeomFromText('POINT(180 100)')), + ('e', 'c', 'l', GeomFromText('POINT(182 231)')), + ('c', 'k', 'o', GeomFromText('POINT(19 60)')), + ('q', 'f', 'p', GeomFromText('POINT(79 95)')), + ('m', 'd', 'r', GeomFromText('POINT(3 127)')), + ('m', 'e', 't', GeomFromText('POINT(136 154)')), + ('w', 'w', 'w', GeomFromText('POINT(102 15)')), + ('l', 'n', 'q', GeomFromText('POINT(71 196)')), + ('p', 'k', 'c', GeomFromText('POINT(47 139)')), + ('j', 'o', 'r', GeomFromText('POINT(177 128)')), + ('j', 'q', 'a', GeomFromText('POINT(170 6)')), + ('b', 'a', 'o', GeomFromText('POINT(63 211)')), + ('g', 's', 'o', GeomFromText('POINT(144 251)')), + ('w', 'u', 'w', GeomFromText('POINT(221 214)')), + ('g', 'a', 'm', GeomFromText('POINT(14 102)')), + ('u', 'q', 'z', GeomFromText('POINT(86 200)')), + ('k', 'a', 'm', GeomFromText('POINT(144 222)')), + ('j', 'u', 'r', GeomFromText('POINT(216 142)')), + ('q', 'k', 'v', GeomFromText('POINT(121 236)')), + ('p', 'o', 'r', GeomFromText('POINT(108 102)')), + ('b', 'd', 'x', GeomFromText('POINT(127 198)')), + ('k', 's', 'a', GeomFromText('POINT(2 150)')), + ('f', 'm', 'f', GeomFromText('POINT(160 191)')), + ('q', 'y', 'x', GeomFromText('POINT(98 111)')), + ('o', 'f', 'm', GeomFromText('POINT(232 218)')), + ('c', 'w', 'j', GeomFromText('POINT(156 165)')), + ('s', 'q', 'v', GeomFromText('POINT(98 161)')); +SET @@RAND_SEED1=692635050, @@RAND_SEED2=297339954; +DELETE FROM t1 ORDER BY RAND() LIMIT 10; +SET @@RAND_SEED1=159925977, @@RAND_SEED2=942570618; +DELETE FROM t1 ORDER BY RAND() LIMIT 10; +SET @@RAND_SEED1=328169745, @@RAND_SEED2=410451954; +DELETE FROM t1 ORDER BY RAND() LIMIT 10; +SET @@RAND_SEED1=178507359, @@RAND_SEED2=332493072; +DELETE FROM t1 ORDER BY RAND() LIMIT 10; +SET @@RAND_SEED1=1034033013, @@RAND_SEED2=558966507; +DELETE FROM t1 ORDER BY RAND() LIMIT 10; +UPDATE t1 set spatial_point=GeomFromText('POINT(230 9)') where c1 like 'y%'; +UPDATE t1 set spatial_point=GeomFromText('POINT(95 35)') where c1 like 'j%'; +UPDATE t1 set spatial_point=GeomFromText('POINT(93 99)') where c1 like 'a%'; +UPDATE t1 set spatial_point=GeomFromText('POINT(19 81)') where c1 like 'r%'; +UPDATE t1 set spatial_point=GeomFromText('POINT(20 177)') where c1 like 'h%'; +UPDATE t1 set spatial_point=GeomFromText('POINT(221 193)') where c1 like 'u%'; +UPDATE t1 set spatial_point=GeomFromText('POINT(195 205)') where c1 like 'd%'; +UPDATE t1 set spatial_point=GeomFromText('POINT(15 213)') where c1 like 'u%'; +UPDATE t1 set spatial_point=GeomFromText('POINT(214 63)') where c1 like 'n%'; +UPDATE t1 set spatial_point=GeomFromText('POINT(243 171)') where c1 like 'c%'; +UPDATE t1 set spatial_point=GeomFromText('POINT(198 82)') where c1 like 'y%'; +INSERT INTO t1 (c2, c1, c3, spatial_point) VALUES + ('f', 'y', 'p', GeomFromText('POINT(109 235)')), + ('b', 'e', 'v', GeomFromText('POINT(20 48)')), + ('i', 'u', 'f', GeomFromText('POINT(15 55)')), + ('o', 'r', 'z', GeomFromText('POINT(105 64)')), + ('a', 'p', 'a', GeomFromText('POINT(142 236)')), + ('g', 'i', 'k', GeomFromText('POINT(10 49)')), + ('x', 'z', 'x', GeomFromText('POINT(192 200)')), + ('c', 'v', 'r', GeomFromText('POINT(94 168)')), + ('y', 'z', 'e', GeomFromText('POINT(141 51)')), + ('h', 'm', 'd', GeomFromText('POINT(35 251)')), + ('v', 'm', 'q', GeomFromText('POINT(44 90)')), + ('j', 'l', 'z', GeomFromText('POINT(67 237)')), + ('i', 'v', 'a', GeomFromText('POINT(75 14)')), + ('b', 'q', 't', GeomFromText('POINT(153 33)')), + ('e', 'm', 'a', GeomFromText('POINT(247 49)')), + ('l', 'y', 'g', GeomFromText('POINT(56 203)')), + ('v', 'o', 'r', GeomFromText('POINT(90 54)')), + ('r', 'n', 'd', GeomFromText('POINT(135 83)')), + ('j', 't', 'u', GeomFromText('POINT(174 239)')), + ('u', 'n', 'g', GeomFromText('POINT(104 191)')), + ('p', 'q', 'y', GeomFromText('POINT(63 171)')), + ('o', 'q', 'p', GeomFromText('POINT(192 103)')), + ('f', 'x', 'e', GeomFromText('POINT(244 30)')), + ('n', 'x', 'c', GeomFromText('POINT(92 103)')), + ('r', 'q', 'z', GeomFromText('POINT(166 20)')), + ('s', 'a', 'j', GeomFromText('POINT(137 205)')), + ('z', 't', 't', GeomFromText('POINT(99 134)')), + ('o', 'm', 'j', GeomFromText('POINT(217 3)')), + ('n', 'h', 'j', GeomFromText('POINT(211 17)')), + ('v', 'v', 'a', GeomFromText('POINT(41 137)')), + ('q', 'o', 'j', GeomFromText('POINT(5 92)')), + ('z', 'y', 'e', GeomFromText('POINT(175 212)')), + ('j', 'z', 'h', GeomFromText('POINT(224 194)')), + ('a', 'g', 'm', GeomFromText('POINT(31 119)')), + ('p', 'c', 'f', GeomFromText('POINT(17 221)')), + ('t', 'h', 'k', GeomFromText('POINT(26 203)')), + ('u', 'w', 'p', GeomFromText('POINT(47 185)')), + ('z', 'a', 'c', GeomFromText('POINT(61 133)')), + ('u', 'k', 'a', GeomFromText('POINT(210 115)')), + ('k', 'f', 'h', GeomFromText('POINT(125 113)')), + ('t', 'v', 'y', GeomFromText('POINT(12 239)')), + ('u', 'v', 'd', GeomFromText('POINT(90 24)')), + ('m', 'y', 'w', GeomFromText('POINT(25 243)')), + ('d', 'n', 'g', GeomFromText('POINT(122 92)')), + ('z', 'm', 'f', GeomFromText('POINT(235 110)')), + ('q', 'd', 'f', GeomFromText('POINT(233 217)')), + ('a', 'v', 'u', GeomFromText('POINT(69 59)')), + ('x', 'k', 'p', GeomFromText('POINT(240 14)')), + ('i', 'v', 'r', GeomFromText('POINT(154 42)')), + ('w', 'h', 'l', GeomFromText('POINT(178 156)')), + ('d', 'h', 'n', GeomFromText('POINT(65 157)')), + ('c', 'k', 'z', GeomFromText('POINT(62 33)')), + ('e', 'l', 'w', GeomFromText('POINT(162 1)')), + ('r', 'f', 'i', GeomFromText('POINT(127 71)')), + ('q', 'm', 'c', GeomFromText('POINT(63 118)')), + ('c', 'h', 'u', GeomFromText('POINT(205 203)')), + ('d', 't', 'p', GeomFromText('POINT(234 87)')), + ('s', 'g', 'h', GeomFromText('POINT(149 34)')), + ('o', 'b', 'q', GeomFromText('POINT(159 179)')), + ('k', 'u', 'f', GeomFromText('POINT(202 254)')), + ('u', 'f', 'g', GeomFromText('POINT(70 15)')), + ('x', 's', 'b', GeomFromText('POINT(25 181)')), + ('s', 'c', 'g', GeomFromText('POINT(252 17)')), + ('a', 'c', 'f', GeomFromText('POINT(89 67)')), + ('r', 'e', 'q', GeomFromText('POINT(55 54)')), + ('f', 'i', 'k', GeomFromText('POINT(178 230)')), + ('p', 'e', 'l', GeomFromText('POINT(198 28)')), + ('w', 'o', 'd', GeomFromText('POINT(204 189)')), + ('c', 'a', 'g', GeomFromText('POINT(230 178)')), + ('r', 'o', 'e', GeomFromText('POINT(61 116)')), + ('w', 'a', 'a', GeomFromText('POINT(178 237)')), + ('v', 'd', 'e', GeomFromText('POINT(70 85)')), + ('k', 'c', 'e', GeomFromText('POINT(147 118)')), + ('d', 'q', 't', GeomFromText('POINT(218 77)')), + ('k', 'g', 'f', GeomFromText('POINT(192 113)')), + ('w', 'n', 'e', GeomFromText('POINT(92 124)')), + ('r', 'm', 'q', GeomFromText('POINT(130 65)')), + ('o', 'r', 'r', GeomFromText('POINT(174 233)')), + ('k', 'n', 't', GeomFromText('POINT(175 147)')), + ('q', 'm', 'r', GeomFromText('POINT(18 208)')), + ('l', 'd', 'i', GeomFromText('POINT(13 104)')), + ('w', 'o', 'y', GeomFromText('POINT(207 39)')), + ('p', 'u', 'o', GeomFromText('POINT(114 31)')), + ('y', 'a', 'p', GeomFromText('POINT(106 59)')), + ('a', 'x', 'z', GeomFromText('POINT(17 57)')), + ('v', 'h', 'x', GeomFromText('POINT(170 13)')), + ('t', 's', 'u', GeomFromText('POINT(84 18)')), + ('z', 'z', 'f', GeomFromText('POINT(250 197)')), + ('l', 'z', 't', GeomFromText('POINT(59 80)')), + ('j', 'g', 's', GeomFromText('POINT(54 26)')), + ('g', 'v', 'm', GeomFromText('POINT(89 98)')), + ('q', 'v', 'b', GeomFromText('POINT(39 240)')), + ('x', 'k', 'v', GeomFromText('POINT(246 207)')), + ('k', 'u', 'i', GeomFromText('POINT(105 111)')), + ('w', 'z', 's', GeomFromText('POINT(235 8)')), + ('d', 'd', 'd', GeomFromText('POINT(105 4)')), + ('c', 'z', 'q', GeomFromText('POINT(13 140)')), + ('m', 'k', 'i', GeomFromText('POINT(208 120)')), + ('g', 'a', 'g', GeomFromText('POINT(9 182)')), + ('z', 'j', 'r', GeomFromText('POINT(149 153)')), + ('h', 'f', 'g', GeomFromText('POINT(81 236)')), + ('m', 'e', 'q', GeomFromText('POINT(209 215)')), + ('c', 'h', 'y', GeomFromText('POINT(235 70)')), + ('i', 'e', 'g', GeomFromText('POINT(138 26)')), + ('m', 't', 'u', GeomFromText('POINT(119 237)')), + ('o', 'w', 's', GeomFromText('POINT(193 166)')), + ('f', 'm', 'q', GeomFromText('POINT(85 96)')), + ('x', 'l', 'x', GeomFromText('POINT(58 115)')), + ('x', 'q', 'u', GeomFromText('POINT(108 210)')), + ('b', 'h', 'i', GeomFromText('POINT(250 139)')), + ('y', 'd', 'x', GeomFromText('POINT(199 135)')), + ('w', 'h', 'p', GeomFromText('POINT(247 233)')), + ('p', 'z', 't', GeomFromText('POINT(148 249)')), + ('q', 'a', 'u', GeomFromText('POINT(174 78)')), + ('v', 't', 'm', GeomFromText('POINT(70 228)')), + ('t', 'n', 'f', GeomFromText('POINT(123 2)')), + ('x', 't', 'b', GeomFromText('POINT(35 50)')), + ('r', 'j', 'f', GeomFromText('POINT(200 51)')), + ('s', 'q', 'o', GeomFromText('POINT(23 184)')), + ('u', 'v', 'z', GeomFromText('POINT(7 113)')), + ('v', 'u', 'l', GeomFromText('POINT(145 190)')), + ('o', 'k', 'i', GeomFromText('POINT(161 122)')), + ('l', 'y', 'e', GeomFromText('POINT(17 232)')), + ('t', 'b', 'e', GeomFromText('POINT(120 50)')), + ('e', 's', 'u', GeomFromText('POINT(254 1)')), + ('d', 'd', 'u', GeomFromText('POINT(167 140)')), + ('o', 'b', 'x', GeomFromText('POINT(186 237)')), + ('m', 's', 's', GeomFromText('POINT(172 149)')), + ('t', 'y', 'a', GeomFromText('POINT(149 85)')), + ('x', 't', 'r', GeomFromText('POINT(10 165)')), + ('g', 'c', 'e', GeomFromText('POINT(95 165)')), + ('e', 'e', 'z', GeomFromText('POINT(98 65)')), + ('f', 'v', 'i', GeomFromText('POINT(149 144)')), + ('o', 'p', 'm', GeomFromText('POINT(233 67)')), + ('t', 'u', 'b', GeomFromText('POINT(109 215)')), + ('o', 'o', 'b', GeomFromText('POINT(130 48)')), + ('e', 'm', 'h', GeomFromText('POINT(88 189)')), + ('e', 'v', 'y', GeomFromText('POINT(55 29)')), + ('e', 't', 'm', GeomFromText('POINT(129 55)')), + ('p', 'p', 'i', GeomFromText('POINT(126 222)')), + ('c', 'i', 'c', GeomFromText('POINT(19 158)')), + ('c', 'b', 's', GeomFromText('POINT(13 19)')), + ('u', 'y', 'a', GeomFromText('POINT(114 5)')), + ('a', 'o', 'f', GeomFromText('POINT(227 232)')), + ('t', 'c', 'z', GeomFromText('POINT(63 62)')), + ('d', 'o', 'k', GeomFromText('POINT(48 228)')), + ('x', 'c', 'e', GeomFromText('POINT(204 2)')), + ('e', 'e', 'g', GeomFromText('POINT(125 43)')), + ('o', 'r', 'f', GeomFromText('POINT(171 140)')); +UPDATE t1 set spatial_point=GeomFromText('POINT(163 157)') where c1 like 'w%'; +UPDATE t1 set spatial_point=GeomFromText('POINT(53 151)') where c1 like 'd%'; +UPDATE t1 set spatial_point=GeomFromText('POINT(96 183)') where c1 like 'r%'; +UPDATE t1 set spatial_point=GeomFromText('POINT(57 91)') where c1 like 'q%'; +UPDATE t1 set spatial_point=GeomFromText('POINT(202 110)') where c1 like 'c%'; +UPDATE t1 set spatial_point=GeomFromText('POINT(120 137)') where c1 like 'w%'; +UPDATE t1 set spatial_point=GeomFromText('POINT(207 147)') where c1 like 'c%'; +UPDATE t1 set spatial_point=GeomFromText('POINT(31 125)') where c1 like 'e%'; +UPDATE t1 set spatial_point=GeomFromText('POINT(27 36)') where c1 like 'r%'; +INSERT INTO t1 (c2, c1, c3, spatial_point) VALUES + ('b', 'c', 'e', GeomFromText('POINT(41 137)')), + ('p', 'y', 'k', GeomFromText('POINT(50 22)')), + ('s', 'c', 'h', GeomFromText('POINT(208 173)')), + ('x', 'u', 'l', GeomFromText('POINT(199 175)')), + ('s', 'r', 'h', GeomFromText('POINT(85 192)')), + ('j', 'k', 'u', GeomFromText('POINT(18 25)')), + ('p', 'w', 'h', GeomFromText('POINT(152 197)')), + ('e', 'd', 'c', GeomFromText('POINT(229 3)')), + ('o', 'x', 'k', GeomFromText('POINT(187 155)')), + ('o', 'b', 'k', GeomFromText('POINT(208 150)')), + ('d', 'a', 'j', GeomFromText('POINT(70 87)')), + ('f', 'e', 'k', GeomFromText('POINT(156 96)')), + ('u', 'y', 'p', GeomFromText('POINT(239 193)')), + ('n', 'v', 'p', GeomFromText('POINT(223 98)')), + ('z', 'j', 'r', GeomFromText('POINT(87 89)')), + ('h', 'x', 'x', GeomFromText('POINT(92 0)')), + ('r', 'v', 'r', GeomFromText('POINT(159 139)')), + ('v', 'g', 'g', GeomFromText('POINT(16 229)')), + ('z', 'k', 'u', GeomFromText('POINT(99 52)')), + ('p', 'p', 'o', GeomFromText('POINT(105 125)')), + ('w', 'h', 'y', GeomFromText('POINT(105 154)')), + ('v', 'y', 'z', GeomFromText('POINT(134 238)')), + ('x', 'o', 'o', GeomFromText('POINT(178 88)')), + ('z', 'w', 'd', GeomFromText('POINT(123 60)')), + ('q', 'f', 'u', GeomFromText('POINT(64 90)')), + ('s', 'n', 't', GeomFromText('POINT(50 138)')), + ('v', 'p', 't', GeomFromText('POINT(114 91)')), + ('a', 'o', 'n', GeomFromText('POINT(78 43)')), + ('k', 'u', 'd', GeomFromText('POINT(185 161)')), + ('w', 'd', 'n', GeomFromText('POINT(25 92)')), + ('k', 'w', 'a', GeomFromText('POINT(59 238)')), + ('t', 'c', 'f', GeomFromText('POINT(65 87)')), + ('g', 's', 'p', GeomFromText('POINT(238 126)')), + ('d', 'n', 'y', GeomFromText('POINT(107 173)')), + ('l', 'a', 'w', GeomFromText('POINT(125 152)')), + ('m', 'd', 'j', GeomFromText('POINT(146 53)')), + ('q', 'm', 'c', GeomFromText('POINT(217 187)')), + ('i', 'r', 'r', GeomFromText('POINT(6 113)')), + ('e', 'j', 'b', GeomFromText('POINT(37 83)')), + ('w', 'w', 'h', GeomFromText('POINT(83 199)')), + ('k', 'b', 's', GeomFromText('POINT(170 64)')), + ('s', 'b', 'c', GeomFromText('POINT(163 130)')), + ('c', 'h', 'a', GeomFromText('POINT(141 3)')), + ('k', 'j', 'u', GeomFromText('POINT(143 76)')), + ('r', 'h', 'o', GeomFromText('POINT(243 92)')), + ('i', 'd', 'b', GeomFromText('POINT(205 13)')), + ('r', 'y', 'q', GeomFromText('POINT(138 8)')), + ('m', 'o', 'i', GeomFromText('POINT(36 45)')), + ('v', 'g', 'm', GeomFromText('POINT(0 40)')), + ('f', 'e', 'i', GeomFromText('POINT(76 6)')), + ('c', 'q', 'q', GeomFromText('POINT(115 248)')), + ('x', 'c', 'i', GeomFromText('POINT(29 74)')), + ('l', 's', 't', GeomFromText('POINT(83 18)')), + ('t', 't', 'a', GeomFromText('POINT(26 168)')), + ('u', 'n', 'x', GeomFromText('POINT(200 110)')), + ('j', 'b', 'd', GeomFromText('POINT(216 136)')), + ('s', 'p', 'w', GeomFromText('POINT(38 156)')), + ('f', 'b', 'v', GeomFromText('POINT(29 186)')), + ('v', 'e', 'r', GeomFromText('POINT(149 40)')), + ('v', 't', 'm', GeomFromText('POINT(184 24)')), + ('y', 'g', 'a', GeomFromText('POINT(219 105)')), + ('s', 'f', 'i', GeomFromText('POINT(114 130)')), + ('e', 'q', 'h', GeomFromText('POINT(203 135)')), + ('h', 'g', 'b', GeomFromText('POINT(9 208)')), + ('o', 'l', 'r', GeomFromText('POINT(245 79)')), + ('s', 's', 'v', GeomFromText('POINT(238 198)')), + ('w', 'w', 'z', GeomFromText('POINT(209 232)')), + ('v', 'd', 'n', GeomFromText('POINT(30 193)')), + ('q', 'w', 'k', GeomFromText('POINT(133 18)')), + ('o', 'h', 'o', GeomFromText('POINT(42 140)')), + ('f', 'f', 'h', GeomFromText('POINT(145 1)')), + ('u', 's', 'r', GeomFromText('POINT(70 62)')), + ('x', 'n', 'q', GeomFromText('POINT(33 86)')), + ('u', 'p', 'v', GeomFromText('POINT(232 220)')), + ('z', 'e', 'a', GeomFromText('POINT(130 69)')), + ('r', 'u', 'z', GeomFromText('POINT(243 241)')), + ('b', 'n', 't', GeomFromText('POINT(120 12)')), + ('u', 'f', 's', GeomFromText('POINT(190 212)')), + ('a', 'd', 'q', GeomFromText('POINT(235 191)')), + ('f', 'q', 'm', GeomFromText('POINT(176 2)')), + ('n', 'c', 's', GeomFromText('POINT(218 163)')), + ('e', 'm', 'h', GeomFromText('POINT(163 108)')), + ('c', 'f', 'l', GeomFromText('POINT(220 115)')), + ('c', 'v', 'q', GeomFromText('POINT(66 45)')), + ('w', 'v', 'x', GeomFromText('POINT(251 220)')), + ('f', 'w', 'z', GeomFromText('POINT(146 149)')), + ('h', 'n', 'h', GeomFromText('POINT(148 128)')), + ('y', 'k', 'v', GeomFromText('POINT(28 110)')), + ('c', 'x', 'q', GeomFromText('POINT(13 13)')), + ('e', 'd', 's', GeomFromText('POINT(91 190)')), + ('c', 'w', 'c', GeomFromText('POINT(10 231)')), + ('u', 'j', 'n', GeomFromText('POINT(250 21)')), + ('w', 'n', 'x', GeomFromText('POINT(141 69)')), + ('f', 'p', 'y', GeomFromText('POINT(228 246)')), + ('d', 'q', 'f', GeomFromText('POINT(194 22)')), + ('d', 'z', 'l', GeomFromText('POINT(233 181)')), + ('c', 'a', 'q', GeomFromText('POINT(183 96)')), + ('m', 'i', 'd', GeomFromText('POINT(117 226)')), + ('z', 'y', 'y', GeomFromText('POINT(62 81)')), + ('g', 'v', 'm', GeomFromText('POINT(66 158)')); +SET @@RAND_SEED1=481064922, @@RAND_SEED2=438133497; +DELETE FROM t1 ORDER BY RAND() LIMIT 10; +SET @@RAND_SEED1=280535103, @@RAND_SEED2=444518646; +DELETE FROM t1 ORDER BY RAND() LIMIT 10; +SET @@RAND_SEED1=1072017234, @@RAND_SEED2=484203885; +DELETE FROM t1 ORDER BY RAND() LIMIT 10; +SET @@RAND_SEED1=358851897, @@RAND_SEED2=358495224; +DELETE FROM t1 ORDER BY RAND() LIMIT 10; +SET @@RAND_SEED1=509031459, @@RAND_SEED2=675962925; +DELETE FROM t1 ORDER BY RAND() LIMIT 10; +UPDATE t1 set spatial_point=GeomFromText('POINT(61 203)') where c1 like 'y%'; +UPDATE t1 set spatial_point=GeomFromText('POINT(202 194)') where c1 like 'f%'; +UPDATE t1 set spatial_point=GeomFromText('POINT(228 18)') where c1 like 'h%'; +UPDATE t1 set spatial_point=GeomFromText('POINT(88 18)') where c1 like 'l%'; +UPDATE t1 set spatial_point=GeomFromText('POINT(176 94)') where c1 like 'e%'; +UPDATE t1 set spatial_point=GeomFromText('POINT(44 47)') where c1 like 'g%'; +UPDATE t1 set spatial_point=GeomFromText('POINT(95 191)') where c1 like 'b%'; +UPDATE t1 set spatial_point=GeomFromText('POINT(179 218)') where c1 like 'y%'; +UPDATE t1 set spatial_point=GeomFromText('POINT(239 40)') where c1 like 'g%'; +UPDATE t1 set spatial_point=GeomFromText('POINT(248 41)') where c1 like 'q%'; +UPDATE t1 set spatial_point=GeomFromText('POINT(167 82)') where c1 like 't%'; +UPDATE t1 set spatial_point=GeomFromText('POINT(13 104)') where c1 like 'u%'; +UPDATE t1 set spatial_point=GeomFromText('POINT(139 84)') where c1 like 'a%'; +UPDATE t1 set spatial_point=GeomFromText('POINT(145 108)') where c1 like 'p%'; +UPDATE t1 set spatial_point=GeomFromText('POINT(147 57)') where c1 like 't%'; +UPDATE t1 set spatial_point=GeomFromText('POINT(217 144)') where c1 like 'n%'; +UPDATE t1 set spatial_point=GeomFromText('POINT(160 224)') where c1 like 'w%'; +UPDATE t1 set spatial_point=GeomFromText('POINT(38 28)') where c1 like 'j%'; +UPDATE t1 set spatial_point=GeomFromText('POINT(104 114)') where c1 like 'q%'; +UPDATE t1 set spatial_point=GeomFromText('POINT(88 19)') where c1 like 'c%'; +INSERT INTO t1 (c2, c1, c3, spatial_point) VALUES + ('f', 'x', 'p', GeomFromText('POINT(92 181)')), + ('s', 'i', 'c', GeomFromText('POINT(49 60)')), + ('c', 'c', 'i', GeomFromText('POINT(7 57)')), + ('n', 'g', 'k', GeomFromText('POINT(252 105)')), + ('g', 'b', 'm', GeomFromText('POINT(180 11)')), + ('u', 'l', 'r', GeomFromText('POINT(32 90)')), + ('c', 'x', 'e', GeomFromText('POINT(143 24)')), + ('x', 'u', 'a', GeomFromText('POINT(123 92)')), + ('s', 'b', 'h', GeomFromText('POINT(190 108)')), + ('c', 'x', 'b', GeomFromText('POINT(104 100)')), + ('i', 'd', 't', GeomFromText('POINT(214 104)')), + ('r', 'w', 'g', GeomFromText('POINT(29 67)')), + ('b', 'f', 'g', GeomFromText('POINT(149 46)')), + ('r', 'r', 'd', GeomFromText('POINT(242 196)')), + ('j', 'l', 'a', GeomFromText('POINT(90 196)')), + ('e', 't', 'b', GeomFromText('POINT(190 64)')), + ('l', 'x', 'w', GeomFromText('POINT(250 73)')), + ('q', 'y', 'r', GeomFromText('POINT(120 182)')), + ('s', 'j', 'a', GeomFromText('POINT(180 175)')), + ('n', 'i', 'y', GeomFromText('POINT(124 136)')), + ('s', 'x', 's', GeomFromText('POINT(176 209)')), + ('u', 'f', 's', GeomFromText('POINT(215 173)')), + ('m', 'j', 'x', GeomFromText('POINT(44 140)')), + ('v', 'g', 'x', GeomFromText('POINT(177 233)')), + ('u', 't', 'b', GeomFromText('POINT(136 197)')), + ('f', 'g', 'b', GeomFromText('POINT(10 8)')), + ('v', 'c', 'j', GeomFromText('POINT(13 81)')), + ('d', 's', 'q', GeomFromText('POINT(200 100)')), + ('a', 'p', 'j', GeomFromText('POINT(33 40)')), + ('i', 'c', 'g', GeomFromText('POINT(168 204)')), + ('k', 'h', 'i', GeomFromText('POINT(93 243)')), + ('s', 'b', 's', GeomFromText('POINT(157 13)')), + ('v', 'l', 'l', GeomFromText('POINT(103 6)')), + ('r', 'b', 'k', GeomFromText('POINT(244 137)')), + ('l', 'd', 'r', GeomFromText('POINT(162 254)')), + ('q', 'b', 'z', GeomFromText('POINT(136 246)')), + ('x', 'x', 'p', GeomFromText('POINT(120 37)')), + ('m', 'e', 'z', GeomFromText('POINT(203 167)')), + ('q', 'n', 'p', GeomFromText('POINT(94 119)')), + ('b', 'g', 'u', GeomFromText('POINT(93 248)')), + ('r', 'v', 'v', GeomFromText('POINT(53 88)')), + ('y', 'a', 'i', GeomFromText('POINT(98 219)')), + ('a', 's', 'g', GeomFromText('POINT(173 138)')), + ('c', 'a', 't', GeomFromText('POINT(235 135)')), + ('q', 'm', 'd', GeomFromText('POINT(224 208)')), + ('e', 'p', 'k', GeomFromText('POINT(161 238)')), + ('n', 'g', 'q', GeomFromText('POINT(35 204)')), + ('t', 't', 'x', GeomFromText('POINT(230 178)')), + ('w', 'f', 'a', GeomFromText('POINT(150 221)')), + ('z', 'm', 'z', GeomFromText('POINT(119 42)')), + ('l', 'j', 's', GeomFromText('POINT(97 96)')), + ('f', 'z', 'x', GeomFromText('POINT(208 65)')), + ('i', 'v', 'c', GeomFromText('POINT(145 79)')), + ('l', 'f', 'k', GeomFromText('POINT(83 234)')), + ('u', 'a', 's', GeomFromText('POINT(250 49)')), + ('o', 'k', 'p', GeomFromText('POINT(46 50)')), + ('d', 'e', 'z', GeomFromText('POINT(30 198)')), + ('r', 'r', 'l', GeomFromText('POINT(78 189)')), + ('y', 'l', 'f', GeomFromText('POINT(188 132)')), + ('d', 'q', 'm', GeomFromText('POINT(247 107)')), + ('p', 'j', 'n', GeomFromText('POINT(148 227)')), + ('b', 'o', 'i', GeomFromText('POINT(172 25)')), + ('e', 'v', 'd', GeomFromText('POINT(94 248)')), + ('q', 'd', 'f', GeomFromText('POINT(15 29)')), + ('w', 'b', 'b', GeomFromText('POINT(74 111)')), + ('g', 'q', 'f', GeomFromText('POINT(107 215)')), + ('o', 'h', 'r', GeomFromText('POINT(25 168)')), + ('u', 't', 'w', GeomFromText('POINT(251 188)')), + ('h', 's', 'w', GeomFromText('POINT(254 247)')), + ('f', 'f', 'b', GeomFromText('POINT(166 103)')); +SET @@RAND_SEED1=866613816, @@RAND_SEED2=92289615; +INSERT INTO t1 (c2, c1, c3, spatial_point) VALUES + ('l', 'c', 'l', GeomFromText('POINT(202 98)')), + ('k', 'c', 'b', GeomFromText('POINT(46 206)')), + ('r', 'y', 'm', GeomFromText('POINT(74 140)')), + ('y', 'z', 'd', GeomFromText('POINT(200 160)')), + ('s', 'y', 's', GeomFromText('POINT(156 205)')), + ('u', 'v', 'p', GeomFromText('POINT(86 82)')), + ('j', 's', 's', GeomFromText('POINT(91 233)')), + ('x', 'j', 'f', GeomFromText('POINT(3 14)')), + ('l', 'z', 'v', GeomFromText('POINT(123 156)')), + ('h', 'i', 'o', GeomFromText('POINT(145 229)')), + ('o', 'r', 'd', GeomFromText('POINT(15 22)')), + ('f', 'x', 't', GeomFromText('POINT(21 60)')), + ('t', 'g', 'h', GeomFromText('POINT(50 153)')), + ('g', 'u', 'b', GeomFromText('POINT(82 85)')), + ('v', 'a', 'p', GeomFromText('POINT(231 178)')), + ('n', 'v', 'o', GeomFromText('POINT(183 25)')), + ('j', 'n', 'm', GeomFromText('POINT(50 144)')), + ('e', 'f', 'i', GeomFromText('POINT(46 16)')), + ('d', 'w', 'a', GeomFromText('POINT(66 6)')), + ('f', 'x', 'a', GeomFromText('POINT(107 197)')), + ('m', 'o', 'a', GeomFromText('POINT(142 80)')), + ('q', 'l', 'g', GeomFromText('POINT(251 23)')), + ('c', 's', 's', GeomFromText('POINT(158 43)')), + ('y', 'd', 'o', GeomFromText('POINT(196 228)')), + ('d', 'p', 'l', GeomFromText('POINT(107 5)')), + ('h', 'a', 'b', GeomFromText('POINT(183 166)')), + ('m', 'w', 'p', GeomFromText('POINT(19 59)')), + ('b', 'y', 'o', GeomFromText('POINT(178 30)')), + ('x', 'w', 'i', GeomFromText('POINT(168 94)')), + ('t', 'k', 'z', GeomFromText('POINT(171 5)')), + ('r', 'm', 'a', GeomFromText('POINT(222 19)')), + ('u', 'v', 'e', GeomFromText('POINT(224 80)')), + ('q', 'r', 'k', GeomFromText('POINT(212 218)')), + ('d', 'p', 'j', GeomFromText('POINT(169 7)')), + ('d', 'r', 'v', GeomFromText('POINT(193 23)')), + ('n', 'y', 'y', GeomFromText('POINT(130 178)')), + ('m', 'z', 'r', GeomFromText('POINT(81 200)')), + ('j', 'e', 'w', GeomFromText('POINT(145 239)')), + ('v', 'h', 'x', GeomFromText('POINT(24 105)')), + ('z', 'm', 'a', GeomFromText('POINT(175 129)')), + ('b', 'c', 'v', GeomFromText('POINT(213 10)')), + ('t', 't', 'u', GeomFromText('POINT(2 129)')), + ('r', 's', 'v', GeomFromText('POINT(209 192)')), + ('x', 'p', 'g', GeomFromText('POINT(43 63)')), + ('t', 'e', 'u', GeomFromText('POINT(139 210)')), + ('l', 'e', 't', GeomFromText('POINT(245 148)')), + ('a', 'i', 'k', GeomFromText('POINT(167 195)')), + ('m', 'o', 'h', GeomFromText('POINT(206 120)')), + ('g', 'z', 's', GeomFromText('POINT(169 240)')), + ('z', 'u', 's', GeomFromText('POINT(202 120)')), + ('i', 'b', 'a', GeomFromText('POINT(216 18)')), + ('w', 'y', 'g', GeomFromText('POINT(119 236)')), + ('h', 'y', 'p', GeomFromText('POINT(161 24)')); +UPDATE t1 set spatial_point=GeomFromText('POINT(33 100)') where c1 like 't%'; +UPDATE t1 set spatial_point=GeomFromText('POINT(41 46)') where c1 like 'f%'; +CHECK TABLE t1 EXTENDED; +DROP TABLE t1; + # End of 4.1 tests diff --git a/mysql-test/t/gis.test b/mysql-test/t/gis.test index 1ae4f0ae62f..7182e040d46 100644 --- a/mysql-test/t/gis.test +++ b/mysql-test/t/gis.test @@ -377,4 +377,46 @@ load data infile '../../std_data/bad_gis_data.dat' into table t1; alter table t1 enable keys; drop table t1; -# End of 4.1 tests +# +# Bug #26038: is null and bad data +# + +create table t1 (a int, b blob); +insert into t1 values (1, ''), (2, NULL), (3, '1'); +select * from t1; + +select + geometryfromtext(b) IS NULL, geometryfromwkb(b) IS NULL, astext(b) IS NULL, + aswkb(b) IS NULL, geometrytype(b) IS NULL, centroid(b) IS NULL, + envelope(b) IS NULL, startpoint(b) IS NULL, endpoint(b) IS NULL, + exteriorring(b) IS NULL, pointn(b, 1) IS NULL, geometryn(b, 1) IS NULL, + interiorringn(b, 1) IS NULL, multipoint(b) IS NULL, isempty(b) IS NULL, + issimple(b) IS NULL, isclosed(b) IS NULL, dimension(b) IS NULL, + numgeometries(b) IS NULL, numinteriorrings(b) IS NULL, numpoints(b) IS NULL, + area(b) IS NULL, glength(b) IS NULL, srid(b) IS NULL, x(b) IS NULL, + y(b) IS NULL +from t1; + +select + within(b, b) IS NULL, contains(b, b) IS NULL, overlaps(b, b) IS NULL, + equals(b, b) IS NULL, disjoint(b, b) IS NULL, touches(b, b) IS NULL, + intersects(b, b) IS NULL, crosses(b, b) IS NULL +from t1; + +select + point(b, b) IS NULL, linestring(b) IS NULL, polygon(b) IS NULL, multipoint(b) IS NULL, + multilinestring(b) IS NULL, multipolygon(b) IS NULL, + geometrycollection(b) IS NULL +from t1; + +drop table t1; + +# +# Bug #27164: Crash when mixing InnoDB and MyISAM Geospatial tables +# +CREATE TABLE t1(a POINT) ENGINE=MyISAM; +INSERT INTO t1 VALUES (NULL); +SELECT * FROM t1; +DROP TABLE t1; + +--echo End of 4.1 tests diff --git a/mysql-test/t/grant.test b/mysql-test/t/grant.test index 3365145650a..ea148c67262 100644 --- a/mysql-test/t/grant.test +++ b/mysql-test/t/grant.test @@ -451,4 +451,25 @@ delete from mysql.tables_priv where user='mysqltest1'; flush privileges; drop database mysqltest; -# End of 4.1 tests +# +# Bug #27515: DROP previlege is not required for RENAME TABLE +# +connection master; +create database db27515; +use db27515; +create table t1 (a int); +grant alter on db27515.t1 to user27515@localhost; +grant insert, create on db27515.t2 to user27515@localhost; + +connect (conn27515, localhost, user27515, , db27515); +connection conn27515; +--error 1142 +rename table t1 to t2; +disconnect conn27515; + +connection master; +revoke all privileges, grant option from user27515@localhost; +drop user user27515@localhost; +drop database db27515; + +--echo End of 4.1 tests diff --git a/mysql-test/t/grant_cache.test b/mysql-test/t/grant_cache.test index 703ad5d8004..7e17a03ec21 100644 --- a/mysql-test/t/grant_cache.test +++ b/mysql-test/t/grant_cache.test @@ -14,7 +14,7 @@ set GLOBAL query_cache_size=1355776; reset query cache; flush status; -connect (root,localhost,root,,test,$MASTER_MYPORT,master.sock); +connect (root,localhost,root,,test,$MASTER_MYPORT,$MASTER_MYSOCK); connection root; show grants for current_user; show grants; @@ -29,7 +29,7 @@ insert into mysqltest.t2 values (3,3,3); create table test.t1 (a char (10)); insert into test.t1 values ("test.t1"); select * from t1; -connect (root2,localhost,root,,mysqltest,$MASTER_MYPORT,master.sock); +connect (root2,localhost,root,,mysqltest,$MASTER_MYPORT,$MASTER_MYSOCK); connection root2; # put queries in cache select * from t1; @@ -47,7 +47,7 @@ grant SELECT on test.t1 to mysqltest_2@localhost; grant SELECT(a) on mysqltest.t1 to mysqltest_3@localhost; # The following queries should be fetched from cache -connect (user1,localhost,mysqltest_1,,mysqltest,$MASTER_MYPORT,master.sock); +connect (user1,localhost,mysqltest_1,,mysqltest,$MASTER_MYPORT,$MASTER_MYSOCK); connection user1; show grants for current_user(); show status like "Qcache_queries_in_cache"; @@ -72,12 +72,12 @@ show status like "Qcache_hits"; show status like "Qcache_not_cached"; # Don't use '' as user because it will pick Unix login -connect (unkuser,localhost,unkuser,,,$MASTER_MYPORT,master.sock); +connect (unkuser,localhost,unkuser,,,$MASTER_MYPORT,$MASTER_MYSOCK); connection unkuser; show grants for current_user(); # The following queries should be fetched from cache -connect (user2,localhost,mysqltest_2,,mysqltest,$MASTER_MYPORT,master.sock); +connect (user2,localhost,mysqltest_2,,mysqltest,$MASTER_MYPORT,$MASTER_MYSOCK); connection user2; select "user2"; select * from t1; @@ -92,7 +92,7 @@ show status like "Qcache_hits"; show status like "Qcache_not_cached"; # The following queries should not be fetched from cache -connect (user3,localhost,mysqltest_3,,mysqltest,$MASTER_MYPORT,master.sock); +connect (user3,localhost,mysqltest_3,,mysqltest,$MASTER_MYPORT,$MASTER_MYSOCK); connection user3; select "user3"; --replace_result 127.0.0.1 localhost @@ -113,7 +113,7 @@ show status like "Qcache_hits"; show status like "Qcache_not_cached"; # Connect without a database -connect (user4,localhost,mysqltest_1,,*NO-ONE*,$MASTER_MYPORT,master.sock); +connect (user4,localhost,mysqltest_1,,*NO-ONE*,$MASTER_MYPORT,$MASTER_MYSOCK); connection user4; select "user4"; show grants; diff --git a/mysql-test/t/heap_btree.test b/mysql-test/t/heap_btree.test index 9aa820becd9..f8d6afbad04 100644 --- a/mysql-test/t/heap_btree.test +++ b/mysql-test/t/heap_btree.test @@ -182,4 +182,35 @@ delete from t1 where a >= 2; select a from t1 order by a; drop table t1; +# +# Bug#26996 - Update of a Field in a Memory Table ends with wrong result +# +CREATE TABLE t1 ( + c1 CHAR(3), + c2 INTEGER, + KEY USING BTREE(c1), + KEY USING BTREE(c2) +) ENGINE= MEMORY; +INSERT INTO t1 VALUES ('ABC',0), ('A',0), ('B',0), ('C',0); +UPDATE t1 SET c2= c2 + 1 WHERE c1 = 'A'; +SELECT * FROM t1; +DROP TABLE t1; + +# +# Bug#24985 - UTF8 ENUM primary key on MEMORY using BTREE +# causes incorrect duplicate entries +# +CREATE TABLE t1 ( + c1 ENUM('1', '2'), + UNIQUE USING BTREE(c1) +) ENGINE= MEMORY DEFAULT CHARSET= utf8; +INSERT INTO t1 VALUES('1'), ('2'); +DROP TABLE t1; +CREATE TABLE t1 ( + c1 SET('1', '2'), + UNIQUE USING BTREE(c1) +) ENGINE= MEMORY DEFAULT CHARSET= utf8; +INSERT INTO t1 VALUES('1'), ('2'); +DROP TABLE t1; + --echo End of 4.1 tests diff --git a/mysql-test/t/help.test b/mysql-test/t/help.test index ff431fb4ebd..de0cefab76c 100644 --- a/mysql-test/t/help.test +++ b/mysql-test/t/help.test @@ -13,30 +13,30 @@ # impossible_category_3 # impossible_function_7 -insert into mysql.help_category(help_category_id,name)values(1,'impossible_category_1'); -select @category1_id:= 1; -insert into mysql.help_category(help_category_id,name)values(2,'impossible_category_2'); -select @category2_id:= 2; -insert into mysql.help_category(help_category_id,name,parent_category_id)values(3,'impossible_category_3',@category2_id); -select @category3_id:= 3; - -insert into mysql.help_topic(help_topic_id,name,help_category_id,description,example)values(1,'impossible_function_1',@category1_id,'description of \n impossible_function1\n','example of \n impossible_function1'); -select @topic1_id:= 1; -insert into mysql.help_topic(help_topic_id,name,help_category_id,description,example)values(2,'impossible_function_2',@category1_id,'description of \n impossible_function2\n','example of \n impossible_function2'); -select @topic2_id:= 2; -insert into mysql.help_topic(help_topic_id,name,help_category_id,description,example)values(3,'impossible_function_3',@category2_id,'description of \n impossible_function3\n','example of \n impossible_function3'); -select @topic3_id:= 3; -insert into mysql.help_topic(help_topic_id,name,help_category_id,description,example)values(4,'impossible_function_4',@category2_id,'description of \n impossible_function4\n','example of \n impossible_function4'); -select @topic4_id:= 4; -insert into mysql.help_topic(help_topic_id,name,help_category_id,description,example)values(5,'impossible_function_7',@category3_id,'description of \n impossible_function5\n','example of \n impossible_function7'); -select @topic5_id:= 5; - -insert into mysql.help_keyword(help_keyword_id,name)values(1,'impossible_function_1'); -select @keyword1_id:= 1; -insert into mysql.help_keyword(help_keyword_id,name)values(2,'impossible_function_5'); -select @keyword2_id:= 2; -insert into mysql.help_keyword(help_keyword_id,name)values(3,'impossible_function_6'); -select @keyword3_id:= 3; +insert into mysql.help_category(help_category_id,name)values(10001,'impossible_category_1'); +select @category1_id:= 10001; +insert into mysql.help_category(help_category_id,name)values(10002,'impossible_category_2'); +select @category2_id:= 10002; +insert into mysql.help_category(help_category_id,name,parent_category_id)values(10003,'impossible_category_3',@category2_id); +select @category3_id:= 10003; + +insert into mysql.help_topic(help_topic_id,name,help_category_id,description,example)values(10101,'impossible_function_1',@category1_id,'description of \n impossible_function1\n','example of \n impossible_function1'); +select @topic1_id:= 10101; +insert into mysql.help_topic(help_topic_id,name,help_category_id,description,example)values(10102,'impossible_function_2',@category1_id,'description of \n impossible_function2\n','example of \n impossible_function2'); +select @topic2_id:= 10102; +insert into mysql.help_topic(help_topic_id,name,help_category_id,description,example)values(10103,'impossible_function_3',@category2_id,'description of \n impossible_function3\n','example of \n impossible_function3'); +select @topic3_id:= 10103; +insert into mysql.help_topic(help_topic_id,name,help_category_id,description,example)values(10104,'impossible_function_4',@category2_id,'description of \n impossible_function4\n','example of \n impossible_function4'); +select @topic4_id:= 10104; +insert into mysql.help_topic(help_topic_id,name,help_category_id,description,example)values(10105,'impossible_function_7',@category3_id,'description of \n impossible_function5\n','example of \n impossible_function7'); +select @topic5_id:= 10105; + +insert into mysql.help_keyword(help_keyword_id,name)values(10201,'impossible_function_1'); +select @keyword1_id:= 10201; +insert into mysql.help_keyword(help_keyword_id,name)values(10202,'impossible_function_5'); +select @keyword2_id:= 10202; +insert into mysql.help_keyword(help_keyword_id,name)values(10203,'impossible_function_6'); +select @keyword3_id:= 10203; insert into mysql.help_relation(help_keyword_id,help_topic_id)values(@keyword1_id,@topic2_id); insert into mysql.help_relation(help_keyword_id,help_topic_id)values(@keyword2_id,@topic1_id); diff --git a/mysql-test/t/init_file.test b/mysql-test/t/init_file.test index bbe0c4ff884..34d738415c8 100644 --- a/mysql-test/t/init_file.test +++ b/mysql-test/t/init_file.test @@ -6,5 +6,14 @@ # mysql-test/t/init_file-master.opt for the actual test # +# +# Bug#23240 --init-file statements with NOW() reports '1970-01-01 11:00:00'as the date time +# +INSERT INTO init_file.startup VALUES ( NOW() ); +SELECT * INTO @X FROM init_file.startup limit 0,1; +SELECT * INTO @Y FROM init_file.startup limit 1,1; +SELECT YEAR(@X)-YEAR(@Y); +DROP DATABASE init_file; + --echo ok --echo End of 4.1 tests diff --git a/mysql-test/t/innodb.test b/mysql-test/t/innodb.test index 3e53cadf76c..7e594c358f2 100644 --- a/mysql-test/t/innodb.test +++ b/mysql-test/t/innodb.test @@ -1299,6 +1299,7 @@ drop table test_checksum; set foreign_key_checks=0; create table t2 (a int primary key, b int, foreign key (b) references t1(a)) engine = innodb; +--replace_result \\ / $MYSQL_TEST_DIR . /var/master-data/ / -- error 1005 create table t1(a char(10) primary key, b varchar(20)) engine = innodb; set foreign_key_checks=1; @@ -1309,6 +1310,7 @@ drop table t2; set foreign_key_checks=0; create table t1(a varchar(10) primary key) engine = innodb DEFAULT CHARSET=latin1; +--replace_result \\ / $MYSQL_TEST_DIR . /var/master-data/ / -- error 1005 create table t2 (a varchar(10), foreign key (a) references t1(a)) engine = innodb DEFAULT CHARSET=utf8; set foreign_key_checks=1; @@ -1338,6 +1340,7 @@ drop table t2,t1; set foreign_key_checks=0; create table t2 (a varchar(10), foreign key (a) references t1(a)) engine = innodb DEFAULT CHARSET=latin1; create table t3(a varchar(10) primary key) engine = innodb DEFAULT CHARSET=utf8; +--replace_result \\ / $MYSQL_TEST_DIR . /var/master-data/ / -- error 1025 rename table t3 to t1; set foreign_key_checks=1; diff --git a/mysql-test/t/innodb_mysql.test b/mysql-test/t/innodb_mysql.test index c5a5e997775..b5e7fc6e405 100644 --- a/mysql-test/t/innodb_mysql.test +++ b/mysql-test/t/innodb_mysql.test @@ -161,4 +161,59 @@ show /*!50002 GLOBAL */ status like 'Handler_rollback'; connection default; drop table t1; disconnect con1; + +# +# Bug #13191: INSERT...ON DUPLICATE KEY UPDATE of UTF-8 string fields +# used in partial unique indices. +# + +CREATE TABLE t1(c1 TEXT, UNIQUE (c1(1)), cnt INT DEFAULT 1) + ENGINE=INNODB CHARACTER SET UTF8; +INSERT INTO t1 (c1) VALUES ('1a'); +SELECT * FROM t1; +INSERT INTO t1 (c1) VALUES ('1b') ON DUPLICATE KEY UPDATE cnt=cnt+1; +SELECT * FROM t1; +DROP TABLE t1; + +CREATE TABLE t1(c1 VARCHAR(2), UNIQUE (c1(1)), cnt INT DEFAULT 1) + ENGINE=INNODB CHARACTER SET UTF8; +INSERT INTO t1 (c1) VALUES ('1a'); +SELECT * FROM t1; +INSERT INTO t1 (c1) VALUES ('1b') ON DUPLICATE KEY UPDATE cnt=cnt+1; +SELECT * FROM t1; +DROP TABLE t1; + +CREATE TABLE t1(c1 CHAR(2), UNIQUE (c1(1)), cnt INT DEFAULT 1) + ENGINE=INNODB CHARACTER SET UTF8; +INSERT INTO t1 (c1) VALUES ('1a'); +SELECT * FROM t1; +INSERT INTO t1 (c1) VALUES ('1b') ON DUPLICATE KEY UPDATE cnt=cnt+1; +SELECT * FROM t1; +DROP TABLE t1; + +# +# Bug #28272: EXPLAIN for SELECT from an empty InnoDB table +# + +CREATE TABLE t1 ( + a1 decimal(10,0) DEFAULT NULL, + a2 blob, + a3 time DEFAULT NULL, + a4 blob, + a5 char(175) DEFAULT NULL, + a6 timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', + a7 tinyblob, + INDEX idx (a6,a7(239),a5) +) ENGINE=InnoDB; + +EXPLAIN SELECT a4 FROM t1 WHERE +a6=NULL AND +a4='UNcT5pIde4I6c2SheTo4gt92OV1jgJCVkXmzyf325R1DwLURkbYHwhydANIZMbKTgdcR5xS'; + +EXPLAIN SELECT t1.a4 FROM t1, t1 t WHERE +t.a6=t.a6 AND t1.a6=NULL AND +t1.a4='UNcT5pIde4I6c2SheTo4gt92OV1jgJCVkXmzyf325R1DwLURkbYHwhydANIZMbKTgdcR5xS'; + +DROP TABLE t1; + --echo End of 4.1 tests diff --git a/mysql-test/t/join.test b/mysql-test/t/join.test index 2715f30b6cf..0fe5de8c9b7 100644 --- a/mysql-test/t/join.test +++ b/mysql-test/t/join.test @@ -326,4 +326,28 @@ select t1.i,t2.i,t3.i from t2 natural right join t3,t1 order by t1.i,t2.i,t3.i; select t1.i,t2.i,t3.i from t2 right join t3 on (t2.i=t3.i),t1 order by t1.i,t2.i,t3.i; drop table t1,t2,t3; +# +# Bug #27531: Query performance degredation in 4.1.22 and greater +# +CREATE TABLE t1 (a int, b int default 0, c int default 1); + +INSERT INTO t1 (a) VALUES (1),(2),(3),(4),(5),(6),(7),(8); +INSERT INTO t1 (a) SELECT a + 8 FROM t1; +INSERT INTO t1 (a) SELECT a + 16 FROM t1; + +CREATE TABLE t2 (a int, d int, e int default 0); + +INSERT INTO t2 (a, d) VALUES (1,1),(2,2),(3,3),(4,4); +INSERT INTO t2 (a, d) SELECT a+4, a+4 FROM t2; +INSERT INTO t2 (a, d) SELECT a+8, a+8 FROM t2; + +# should use join cache +EXPLAIN +SELECT STRAIGHT_JOIN t2.e FROM t1,t2 WHERE t2.d=1 AND t1.b=t2.e + ORDER BY t1.b, t1.c; +SELECT STRAIGHT_JOIN t2.e FROM t1,t2 WHERE t2.d=1 AND t1.b=t2.e + ORDER BY t1.b, t1.c; + +DROP TABLE t1,t2; + # End of 4.1 tests diff --git a/mysql-test/t/kill.test b/mysql-test/t/kill.test index 50c4239b45e..1cf871d5f9f 100644 --- a/mysql-test/t/kill.test +++ b/mysql-test/t/kill.test @@ -70,11 +70,14 @@ insert into t2 select id from t1; create table t3 (kill_id int); insert into t3 values(connection_id()); +connect (conn2, localhost, root,,); +connection conn2; + +connection conn1; -- disable_result_log send select id from t1 where id in (select distinct id from t2); -- enable_result_log -connect (conn2, localhost, root,,); connection conn2; select ((@id := kill_id) - kill_id) from t3; -- sleep 1 diff --git a/mysql-test/t/merge.test b/mysql-test/t/merge.test index bb03b7b8d62..377160d0312 100644 --- a/mysql-test/t/merge.test +++ b/mysql-test/t/merge.test @@ -383,7 +383,7 @@ drop table t1, t2, t3; CREATE TABLE t1(a INT); INSERT INTO t1 VALUES(2),(1); CREATE TABLE t2(a INT, KEY(a)) ENGINE=MERGE UNION=(t1); ---error 1030 +--error 1168 SELECT * FROM t2 WHERE a=2; DROP TABLE t1, t2; @@ -401,4 +401,70 @@ CREATE TABLE t2(a INT) ENGINE=MERGE UNION=(t3); SELECT * FROM t2; DROP TABLE t2; +# +# Underlying table definition conformance tests. +# +CREATE TABLE t1(a INT, b TEXT); +CREATE TABLE tm1(a TEXT, b INT) ENGINE=MERGE UNION=(t1); +--error 1168 +SELECT * FROM tm1; +DROP TABLE t1, tm1; + +CREATE TABLE t1(a SMALLINT, b SMALLINT); +CREATE TABLE tm1(a INT) ENGINE=MERGE UNION=(t1); +--error 1168 +SELECT * FROM tm1; +DROP TABLE t1, tm1; + +CREATE TABLE t1(a SMALLINT, b SMALLINT, KEY(a, b)); +CREATE TABLE tm1(a SMALLINT, b SMALLINT, KEY(a)) ENGINE=MERGE UNION=(t1); +--error 1168 +SELECT * FROM tm1; +DROP TABLE t1, tm1; + +CREATE TABLE t1(a SMALLINT, b SMALLINT, KEY(b)); +CREATE TABLE tm1(a SMALLINT, b SMALLINT, KEY(a)) ENGINE=MERGE UNION=(t1); +--error 1168 +SELECT * FROM tm1; +DROP TABLE t1, tm1; + +# +# Bug#26464 - insert delayed + update + merge = corruption +# +CREATE TABLE t1(c1 INT) ENGINE=MyISAM; +CREATE TABLE t2(c1 INT) ENGINE=MERGE UNION=(t1); +--error 1031 +INSERT DELAYED INTO t2 VALUES(1); +DROP TABLE t1, t2; + +# +# BUG#26881 - Large MERGE tables report incorrect specification when no +# differences in tables +# +CREATE TABLE t1(c1 VARCHAR(1)); +CREATE TABLE m1 LIKE t1; +ALTER TABLE m1 ENGINE=MERGE UNION=(t1); +SELECT * FROM m1; +DROP TABLE t1, m1; + +CREATE TABLE t1(c1 VARCHAR(4), c2 TINYINT, c3 TINYINT, c4 TINYINT, + c5 TINYINT, c6 TINYINT, c7 TINYINT, c8 TINYINT, c9 TINYINT); +CREATE TABLE m1 LIKE t1; +ALTER TABLE m1 ENGINE=MERGE UNION=(t1); +SELECT * FROM m1; +DROP TABLE t1, m1; + +# +# BUG#24342 - Incorrect results with query over MERGE table +# +CREATE TABLE t1 (a VARCHAR(255) CHARACTER SET latin1 COLLATE latin1_german2_ci, + b INT, INDEX(a,b)); +CREATE TABLE t2 LIKE t1; +CREATE TABLE t3 LIKE t1; +ALTER TABLE t3 ENGINE=MERGE UNION=(t1,t2); +INSERT INTO t1 VALUES ('ss',1); +INSERT INTO t2 VALUES ('ss',2),(0xDF,2); +SELECT COUNT(*) FROM t3 WHERE a=0xDF AND b=2; +DROP TABLE t1,t2,t3; + # End of 4.1 tests diff --git a/mysql-test/t/myisam.test b/mysql-test/t/myisam.test index 0abe0a25f44..f882a393621 100644 --- a/mysql-test/t/myisam.test +++ b/mysql-test/t/myisam.test @@ -495,7 +495,7 @@ insert into t1 values (1),(2),(3),(4),(5),(6); insert into t2 values (1,1),(2,1); lock tables t1 read local, t2 read local; select straight_join * from t1,t2 force index (primary) where t1.a=t2.a; -connect (root,localhost,root,,test,$MASTER_MYPORT,master.sock); +connect (root,localhost,root,,test,$MASTER_MYPORT,$MASTER_MYSOCK); insert into t2 values(2,0); disconnect root; connection default; @@ -882,4 +882,148 @@ CREATE TABLE t1 (c1 TEXT) AVG_ROW_LENGTH=70100 MAX_ROWS=4100100100; SHOW TABLE STATUS LIKE 't1'; DROP TABLE t1; -# End of 4.1 tests +# +# Bug#26231 - select count(*) on myisam table returns wrong value +# when index is used +# +CREATE TABLE t1 (c1 TEXT NOT NULL, KEY c1 (c1(10))) ENGINE=MyISAM; +# Fill at least two key blocks. "Tab, A" must be in both blocks. +INSERT INTO t1 VALUES + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), (CHAR(9,65)), + (''), (''), (''), (''), + (' B'), (' B'), (' B'), (' B'); +SELECT DISTINCT COUNT(*) FROM t1 WHERE c1 = ''; +SELECT DISTINCT length(c1), c1 FROM t1 WHERE c1 = ''; +SELECT DISTINCT COUNT(*) FROM t1 IGNORE INDEX (c1) WHERE c1 = ''; +SELECT DISTINCT length(c1), c1 FROM t1 IGNORE INDEX (c1) WHERE c1 = ''; +SELECT DISTINCT length(c1), c1 FROM t1 ORDER BY c1; +DROP TABLE t1; + +--echo End of 4.1 tests diff --git a/mysql-test/t/mysqladmin.test b/mysql-test/t/mysqladmin.test index 7c016fd7416..3fa03fa910e 100644 --- a/mysql-test/t/mysqladmin.test +++ b/mysql-test/t/mysqladmin.test @@ -1,3 +1,5 @@ +# Embedded server doesn't support external clients +--source include/not_embedded.inc # # Test "mysqladmin ping" # @@ -15,7 +17,7 @@ database=db1 EOF ---replace_regex /\/.*mysqladmin/mysqladmin/ +--replace_regex /.*mysqladmin.*: unknown/mysqladmin: unknown/ --error 7 --exec $MYSQLADMIN --defaults-file=$MYSQLTEST_VARDIR/tmp/bug10608.cnf -S $MASTER_MYSOCK -P $MASTER_MYPORT -u root --password= ping 2>&1 @@ -27,6 +29,6 @@ EOF loose-database=db2 EOF ---replace_regex /Warning: .*mysqladmin/Warning: mysqladmin/ +--replace_regex /Warning: .*mysqladmin.*: unknown/Warning: mysqladmin: unknown/ --exec $MYSQLADMIN --defaults-file=$MYSQLTEST_VARDIR/tmp/bug10608.cnf -S $MASTER_MYSOCK -P $MASTER_MYPORT -u root --password= ping 2>&1 diff --git a/mysql-test/t/mysqldump.test b/mysql-test/t/mysqldump.test index b0df2bb9db2..4a077f1ffa3 100644 --- a/mysql-test/t/mysqldump.test +++ b/mysql-test/t/mysqldump.test @@ -702,4 +702,19 @@ create table t1 (a int); --exec $MYSQL_DUMP --skip-comments --force test t1 --where='xx xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx' 2>&1 drop table t1; +# +# BUG#13926: --order-by-primary fails if PKEY contains quote character +# +CREATE TABLE `t1` ( + `a b` INT, + `c"d` INT, + `e``f` INT, + PRIMARY KEY (`a b`, `c"d`, `e``f`) +) ENGINE=MyISAM DEFAULT CHARSET=latin1; +insert into t1 values (0815, 4711, 2006); + +--exec $MYSQL_DUMP --skip-comments --compatible=ansi --order-by-primary test t1 +--exec $MYSQL_DUMP --skip-comments --order-by-primary test t1 +DROP TABLE `t1`; + --echo End of 4.1 tests diff --git a/mysql-test/t/mysqltest.test b/mysql-test/t/mysqltest.test index c06d51d9d49..8dae94e271e 100644 --- a/mysql-test/t/mysqltest.test +++ b/mysql-test/t/mysqltest.test @@ -552,8 +552,8 @@ echo ; # Illegal use of echo ---error 1 ---exec echo "echo \$;" | $MYSQL_TEST 2>&1 +#--error 1 +#--exec echo "echo \$;" | $MYSQL_TEST 2>&1 # ---------------------------------------------------------------------------- @@ -686,6 +686,21 @@ echo # <whatever> success: $success ; echo $success ; # ---------------------------------------------------------------------------- +# Test let from query with $variable +# let $<var_name>=`<query with $variable>`; +# ---------------------------------------------------------------------------- + +let $var1=content of variable 1; +let $var2= `select "$var1"`; +let $var3= `select concat("$var1", " ", "$var2")`; +echo var2: $var2; +echo var3: $var3; +if (`select length("$var3") > 0`) +{ + echo length of var3 is longer than 0; +} + +# ---------------------------------------------------------------------------- # Test to assign let from query # let $<var_name>=`<query>`; # ---------------------------------------------------------------------------- @@ -1469,8 +1484,8 @@ remove_file non_existing_file; --error 1 --exec echo "write_file filename ;" | $MYSQL_TEST 2>&1 ---error 1 ---exec echo "write_file filename \";" | $MYSQL_TEST 2>&1 +#--error 1 +#--exec echo "write_file filename \";" | $MYSQL_TEST 2>&1 write_file $MYSQLTEST_VARDIR/tmp/test_file1.tmp; Content for test_file1 @@ -1485,6 +1500,74 @@ file_exists $MYSQLTEST_VARDIR/tmp/test_file1.tmp; remove_file $MYSQLTEST_VARDIR/tmp/test_file1.tmp; # ---------------------------------------------------------------------------- +# test for append_file +# ---------------------------------------------------------------------------- + +write_file $MYSQLTEST_VARDIR/tmp/test_file1.tmp; +Content for test_file1 +EOF +file_exists $MYSQLTEST_VARDIR/tmp/test_file1.tmp; + +append_file $MYSQLTEST_VARDIR/tmp/test_file1.tmp; +Appended text +EOF +file_exists $MYSQLTEST_VARDIR/tmp/test_file1.tmp; + +remove_file $MYSQLTEST_VARDIR/tmp/test_file1.tmp; +append_file $MYSQLTEST_VARDIR/tmp/test_file1.tmp; +Appended text on nonexisting file +EOF + +# ---------------------------------------------------------------------------- +# test for cat_file +# ---------------------------------------------------------------------------- + +--write_file $MYSQLTEST_VARDIR/tmp/test_file1.tmp +Some data +for cat_file command +of mysqltest +EOF +cat_file $MYSQLTEST_VARDIR/tmp/test_file1.tmp; + +--error 1 +--exec echo "cat_file non_existing_file;" | $MYSQL_TEST 2>&1 + +# ---------------------------------------------------------------------------- +# test for diff_files +# ---------------------------------------------------------------------------- + +--write_file $MYSQLTEST_VARDIR/tmp/diff1.tmp +Some data +for diff_file command +of mysqltest +EOF + +--write_file $MYSQLTEST_VARDIR/tmp/diff2.tmp +Some data +for diff_file command +of mysqltest +EOF + +--write_file $MYSQLTEST_VARDIR/tmp/diff3.tmp +Some other data +for diff_file command +of mysqltest +EOF + +# Compare equal files +--diff_files $MYSQLTEST_VARDIR/tmp/diff1.tmp $MYSQLTEST_VARDIR/tmp/diff2.tmp +--diff_files $MYSQLTEST_VARDIR/tmp/diff2.tmp $MYSQLTEST_VARDIR/tmp/diff1.tmp + +# Compare files that differ +--error 1 +--diff_files $MYSQLTEST_VARDIR/tmp/diff3.tmp $MYSQLTEST_VARDIR/tmp/diff2.tmp +--error 1 +--diff_files $MYSQLTEST_VARDIR/tmp/diff1.tmp $MYSQLTEST_VARDIR/tmp/diff3.tmp + +# Compare equal files, again... +--diff_files $MYSQLTEST_VARDIR/tmp/diff1.tmp $MYSQLTEST_VARDIR/tmp/diff2.tmp + +# ---------------------------------------------------------------------------- # test for file_exist # ---------------------------------------------------------------------------- --error 1 @@ -1602,5 +1685,251 @@ EOF --exec echo "echo Some output; exit; echo Not this;" | $MYSQL_TEST 2>&1 +# ---------------------------------------------------------------------------- +# test for sorted_result +# ---------------------------------------------------------------------------- + +create table t1( a int, b char(255), c timestamp); +insert into t1 values(1, 'Line 1', '2007-04-05'), (2, "Part 2", '2007-04-05'); +insert into t1 values(1, 'Line 1', '2007-04-05'), (2, "Part 3", '2007-04-05'); +select * from t1; +--sorted_result +select * from t1; +# Should not be sorted +select * from t1; +disable_result_log; +sorted_result; +select * from t1; +enable_result_log; +--sorted_result +select ''; +sorted_result; +select "h"; +--sorted_result +select "he"; +--sorted_result +select "hep"; +--sorted_result +select "hepp"; + +drop table t1; + +# 1. Assignment of result set sorting +sorted_result; + SELECT 2 as "my_col" +UNION +SELECT 1; +# +--sorted_result +SELECT 2 as "my_col" UNION SELECT 1; +--sorted_result +SELECT 2 as "my_col" +UNION +SELECT 1; + +# 2. Ensure that the table header will be not sorted into the result +--sorted_result +SELECT '2' as "3" +UNION +SELECT '1'; + +# 3. Ensure that an empty result set does not cause problems +CREATE TABLE t1( a CHAR); +--sorted_result +SELECT * FROM t1; +DROP TABLE t1; + +# 4. Ensure that NULL values within the result set do not cause problems +SELECT NULL as "my_col1",2 AS "my_col2" +UNION +SELECT NULL,1; +--sorted_result +SELECT NULL as "my_col1",2 AS "my_col2" +UNION +SELECT NULL,1; +# +SELECT 2 as "my_col1",NULL AS "my_col2" +UNION +SELECT 1,NULL; +--sorted_result +SELECT 2 as "my_col1",NULL AS "my_col2" +UNION +SELECT 1,NULL; + +# 5. "sorted_result" changes nothing when applied to a non query statement. +sorted_result; + SET @a = 17; +# +# 6. Show that "sorted_result;" before the "SET @a = 17;" above does not affect +# the now following query. +SELECT 2 as "my_col" +UNION +SELECT 1; + +# 7. Ensure that "sorted_result" in combination with $variables works +let $my_stmt=SELECT 2 as "my_col" +UNION +SELECT 1; +--sorted_result +eval $my_stmt; + +# 8. Ensure that "sorted_result " does not change the semantics of +# "--error ...." or the protocol output after such an expected failure +--sorted_result +--error 1146 +SELECT '2' as "my_col1",2 as "my_col2" +UNION +SELECT '1',1 from t2; + +# 9. Ensure that several result formatting options including "sorted_result" +# - have all an effect +# - "--sorted_result" does not need to be direct before the statement +# - Row sorting is applied after modification of the column content +--sorted_result +--replace_column 1 # +SELECT '1' as "my_col1",2 as "my_col2" +UNION +SELECT '2',1; + +# 10. Ensure that at least 1024 rows within a result set do not cause problems +# +CREATE TABLE t1 (f1 INT); +INSERT INTO t1 SET f1 = 1024; +INSERT INTO t1 SELECT f1 - 1 FROM t1; +INSERT INTO t1 SELECT f1 - 2 FROM t1; +INSERT INTO t1 SELECT f1 - 4 FROM t1; +INSERT INTO t1 SELECT f1 - 8 FROM t1; +INSERT INTO t1 SELECT f1 - 16 FROM t1; +INSERT INTO t1 SELECT f1 - 32 FROM t1; +INSERT INTO t1 SELECT f1 - 64 FROM t1; +INSERT INTO t1 SELECT f1 - 128 FROM t1; +INSERT INTO t1 SELECT f1 - 256 FROM t1; +INSERT INTO t1 SELECT f1 - 512 FROM t1; +--disable_result_log +--sorted_result +SELECT * FROM t1; +--enable_result_log +DROP TABLE t1; + +# ---------------------------------------------------------------------------- +# test for query_get_value +# ---------------------------------------------------------------------------- + +CREATE TABLE t1( + a int, b varchar(255), c datetime +); +SHOW COLUMNS FROM t1; + +#------------ Positive tests ------------ +# 1. constant parameters +# value is simple string without spaces +let $value= query_get_value(SHOW COLUMNS FROM t1, Type, 1); +--echo statement=SHOW COLUMNS FROM t1 row_number=1, column_name="Type", Value=$value +let $value= query_get_value("SHOW COLUMNS FROM t1", Type, 1); +--echo statement="SHOW COLUMNS FROM t1" row_number=1, column_name="Type", Value=$value +# +# 2. $variables as parameters +# value IS NULL +let $my_show= SHOW COLUMNS FROM t1; +let $column_name= Default; +let $row_number= 1; +let $value= query_get_value($my_show, $column_name, $row_number); +--echo statement=$my_show row_number=$row_number, column_name=$column_name, Value=$value +# +# 3. result set of a SELECT (not recommended, because projection and +# selection could be done much better by pure SELECT functionality) +# value is string with space in the middle +let $value= query_get_value(SELECT 'A B' AS "MyColumn", MyColumn, 1); +--echo value= ->$value<- +# +# 4. column name with space +let $value= query_get_value(SELECT 1 AS "My Column", My Column, 1); +--echo value= $value +# +#------------ Negative tests ------------ +# 5. Incomplete statement including missing parameters +# 5.1 incomplete statement +--error 1 +--exec echo "let \$value= query_get_value(SHOW;" | $MYSQL_TEST 2>&1 +# 5.2 missing query +--error 1 +--exec echo "let \$value= query_get_value;" | $MYSQL_TEST 2>&1 +# 5.3 missing column name +--error 1 +--exec echo "let \$value= query_get_value(SHOW COLUMNS FROM t1);" | $MYSQL_TEST 2>&1 +# 5.4 missing row number +--error 1 +--exec echo "let \$value= query_get_value(SHOW COLUMNS FROM t1, Field);" | $MYSQL_TEST 2>&1 +# +# 6. Somehow "wrong" value of parameters +# 6.1 row parameter +# 6.1.1 non sense number 0 +let $value= initialized; +let $value= query_get_value(SHOW COLUMNS FROM t1, Field, 0); +--echo value= $value +# 6.1.2 after the last row +let $value= initialized; +let $value= query_get_value(SHOW COLUMNS FROM t1, Field, 10); +--echo value= $value +# 6.1.3 invalid row number +--error 1 +--exec echo "let \$value= query_get_value(SHOW COLUMNS FROM t1, Field, notnumber);" | $MYSQL_TEST 2>&1 +# 6.2 column name parameter, name of not existing column +--error 1 +--exec echo "let \$value= query_get_value(SHOW COLUMNS FROM t1, column_not_exists, 1);" | $MYSQL_TEST 2>&1 +# 6.3. statement which never gives a result set +--error 1 +--exec echo "let \$value= query_get_value(SET @A = 1, Field, 1);" | $MYSQL_TEST 2>&1 +# 6.4. statement contains a "," +# Note: There is no need to improve this, because we need query_get_value +# for SHOW commands only. +--error 1 +--exec echo "let \$value= query_get_value(SELECT 1 AS "A", 1 AS "B", 1);" | $MYSQL_TEST 2>&1 +# +# 7. empty result set +let $value= initialized; +let $value= query_get_value(SELECT a FROM t1, a, 1); +--echo value= $value +# +# 9. failing statement +--error 1 +--exec echo "let \$value= query_get_value(SHOW COLNS FROM t1, Field, 1);" | $MYSQL_TEST 2>&1 +# +# 10. Artificial example how to process a complete SHOW result set: +let $show_statement= SHOW COLUMNS FROM t1; +let $rowno= 1; +let $run=1; +let $count= 0; +--echo +--echo Field Type Null Key Default Extra +while ($run) +{ + let $Field= query_get_value($show_statement, Field, $rowno); + if (`SELECT '$Field' = 'No such row'`) + { + let $run= 0; + } + if (`SELECT '$Field' <> 'No such row'`) + { + let $Type= query_get_value($show_statement, Type, $rowno); + let $Null= query_get_value($show_statement, Null, $rowno); + if (`SELECT '$Null' = 'YES'`) + { + inc $count; + } + let $Key= query_get_value($show_statement, Key, $rowno); + let $Default= query_get_value($show_statement, Default, $rowno); + let $Extra= query_get_value($show_statement, Extra, $rowno); + --echo $Field $Type $Null ->$Key<- $Default $Extra + inc $rowno; + } +} +--echo +--echo Number of columns with Default NULL: $count +--echo +eval $show_statement; + +drop table t1; --echo End of tests + diff --git a/mysql-test/t/ndb_index_unique.test b/mysql-test/t/ndb_index_unique.test index 458f6a165f8..2bf4a763313 100644 --- a/mysql-test/t/ndb_index_unique.test +++ b/mysql-test/t/ndb_index_unique.test @@ -83,6 +83,14 @@ delete from t2 where a = 1; insert into t2 values(8, 2, 3); select * from t2 order by a; +# Bug #24818 CREATE UNIQUE INDEX (...) USING HASH on a NDB table crashes mysqld +create unique index bi using hash on t2(b); +-- error 1062 +insert into t2 values(9, 3, 1); +alter table t2 drop index bi; +insert into t2 values(9, 3, 1); +select * from t2 order by a; + drop table t2; -- error 1121 diff --git a/mysql-test/t/ndb_types.test b/mysql-test/t/ndb_types.test index 1ca89447892..7b0b4972248 100644 --- a/mysql-test/t/ndb_types.test +++ b/mysql-test/t/ndb_types.test @@ -1,5 +1,5 @@ --- source include/have_ndb.inc --- source include/not_embedded.inc +--source include/have_ndb.inc +--source include/not_embedded.inc --disable_warnings DROP TABLE IF EXISTS t1; @@ -48,7 +48,7 @@ CREATE TABLE t1 ( ); set @now = now(); -sleep 1; +--sleep 1.5 insert into t1 (string,vstring,bin,vbin,tiny,short,medium,long_int,longlong, real_float,real_double, utiny, ushort, umedium,ulong,ulonglong, @@ -64,7 +64,7 @@ from t1; select time_stamp>@now from t1; set @now = now(); -sleep 1; +--sleep 1.5 update t1 set string="bbbb",vstring="bbbb",bin=0xBBBB,vbin=0xBBBB, tiny=-2,short=-2,medium=-2,long_int=-2,longlong=-2,real_float=2.2, real_double=2.2,utiny=2,ushort=2,umedium=2,ulong=2,ulonglong=2, @@ -79,4 +79,4 @@ select time_stamp>@now from t1; drop table t1; -# End of 4.1 tests +--echo End of 4.1 tests diff --git a/mysql-test/t/olap.test b/mysql-test/t/olap.test index 683e1402678..61c1bd45e5f 100644 --- a/mysql-test/t/olap.test +++ b/mysql-test/t/olap.test @@ -281,4 +281,23 @@ select left(a,10), a, sum(b) from t1 group by 1,2 with rollup; select left(a,10) x, a, sum(b) from t1 group by x,a with rollup; drop table t1; +# +# Bug #24856: ROLLUP by const item in a query with DISTINCT +# + +CREATE TABLE t1 (a int, b int); +INSERT INTO t1 + VALUES (2,10),(3,30),(2,40),(1,10),(2,30),(1,20),(2,10); + +SELECT a, SUM(b) FROM t1 GROUP BY a WITH ROLLUP; +SELECT DISTINCT a, SUM(b) FROM t1 GROUP BY a WITH ROLLUP; +SELECT a, b, COUNT(*) FROM t1 GROUP BY a,b WITH ROLLUP; +SELECT DISTINCT a, b, COUNT(*) FROM t1 GROUP BY a,b WITH ROLLUP; + +SELECT 'x', a, SUM(b) FROM t1 GROUP BY 1,2 WITH ROLLUP; +SELECT DISTINCT 'x', a, SUM(b) FROM t1 GROUP BY 1,2 WITH ROLLUP; +SELECT DISTINCT 'x', a, SUM(b) FROM t1 GROUP BY 1,2 WITH ROLLUP; + +DROP TABLE t1; + # End of 4.1 tests diff --git a/mysql-test/t/order_by.test b/mysql-test/t/order_by.test index a8024be7032..5c607608462 100644 --- a/mysql-test/t/order_by.test +++ b/mysql-test/t/order_by.test @@ -575,4 +575,83 @@ SELECT a + 1 AS num, (select num + 2 FROM t1 LIMIT 1) FROM t1; SELECT a.a + 1 AS num FROM t1 a JOIN t1 b ON num = b.a; DROP TABLE t1; +# +# Bug#25126: Reference to non-existant column in UPDATE...ORDER BY... +# crashes server +# +CREATE TABLE bug25126 ( + val int unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY +); +--error 1054 +UPDATE bug25126 SET MissingCol = MissingCol; +--error 1054 +UPDATE bug25126 SET val = val ORDER BY MissingCol; +UPDATE bug25126 SET val = val ORDER BY val; +UPDATE bug25126 SET val = 1 ORDER BY val; +--error 1054 +UPDATE bug25126 SET val = 1 ORDER BY MissingCol; +--error 1054 +UPDATE bug25126 SET val = 1 ORDER BY val, MissingCol; +--error 1054 +UPDATE bug25126 SET val = MissingCol ORDER BY MissingCol; +--error 1054 +UPDATE bug25126 SET MissingCol = 1 ORDER BY val, MissingCol; +--error 1054 +UPDATE bug25126 SET MissingCol = 1 ORDER BY MissingCol; +--error 1054 +UPDATE bug25126 SET MissingCol = val ORDER BY MissingCol; +--error 1054 +UPDATE bug25126 SET MissingCol = MissingCol ORDER BY MissingCol; +DROP TABLE bug25126; + +# +# Bug #25427: crash when order by expression contains a name +# that cannot be resolved unambiguously +# + +CREATE TABLE t1 (a int); + +SELECT p.a AS val, q.a AS val1 FROM t1 p, t1 q ORDER BY val > 1; +--error 1052 +SELECT p.a AS val, q.a AS val FROM t1 p, t1 q ORDER BY val; +--error 1052 +SELECT p.a AS val, q.a AS val FROM t1 p, t1 q ORDER BY val > 1; + +DROP TABLE t1; + +# +# Bug #27532: ORDER/GROUP BY expressions with IN/BETWEEN and NOT IN/BETWEEN +# + +CREATE TABLE t1 (a int); +INSERT INTO t1 VALUES (3), (2), (4), (1); + +SELECT a, IF(a IN (2,3), a, a+10) FROM t1 + ORDER BY IF(a IN (2,3), a, a+10); +SELECT a, IF(a NOT IN (2,3), a, a+10) FROM t1 + ORDER BY IF(a NOT IN (2,3), a, a+10); +SELECT a, IF(a IN (2,3), a, a+10) FROM t1 + ORDER BY IF(a NOT IN (2,3), a, a+10); + +SELECT a, IF(a BETWEEN 2 AND 3, a, a+10) FROM t1 + ORDER BY IF(a BETWEEN 2 AND 3, a, a+10); +SELECT a, IF(a NOT BETWEEN 2 AND 3, a, a+10) FROM t1 + ORDER BY IF(a NOT BETWEEN 2 AND 3, a, a+10); +SELECT a, IF(a BETWEEN 2 AND 3, a, a+10) FROM t1 + ORDER BY IF(a NOT BETWEEN 2 AND 3, a, a+10); + +SELECT IF(a IN (1,2), a, '') as x1, IF(a NOT IN (1,2), a, '') as x2 + FROM t1 GROUP BY x1, x2; +SELECT IF(a IN (1,2), a, '') as x1, IF(a NOT IN (1,2), a, '') as x2 + FROM t1 GROUP BY x1, IF(a NOT IN (1,2), a, ''); + +# The remaining queries are for better coverage +SELECT a, a IN (1,2) FROM t1 ORDER BY a IN (1,2); +SELECT a FROM t1 ORDER BY a IN (1,2); +SELECT a+10 FROM t1 ORDER BY a IN (1,2); +SELECT a, IF(a IN (1,2), a, a+10) FROM t1 + ORDER BY IF(a IN (3,4), a, a+10); + +DROP TABLE t1; + # End of 4.1 tests diff --git a/mysql-test/t/outfile.test b/mysql-test/t/outfile.test index 81fcc7fd564..149c92ece38 100644 --- a/mysql-test/t/outfile.test +++ b/mysql-test/t/outfile.test @@ -39,9 +39,9 @@ select load_file(concat(@tmpdir,"/outfile-test.3")); #eval select * into dumpfile "$MYSQL_TEST_DIR/var/tmp/outfile-test.3" from t1; #enable_query_log; select load_file(concat(@tmpdir,"/outfile-test.not-exist")); ---exec rm $MYSQL_TEST_DIR/var/tmp/outfile-test.1 ---exec rm $MYSQL_TEST_DIR/var/tmp/outfile-test.2 ---exec rm $MYSQL_TEST_DIR/var/tmp/outfile-test.3 +--remove_file $MYSQL_TEST_DIR/var/tmp/outfile-test.1 +--remove_file $MYSQL_TEST_DIR/var/tmp/outfile-test.2 +--remove_file $MYSQL_TEST_DIR/var/tmp/outfile-test.3 drop table t1; # Bug#8191 @@ -49,7 +49,7 @@ disable_query_log; eval select 1 into outfile "$MYSQL_TEST_DIR/var/tmp/outfile-test.4"; enable_query_log; select load_file(concat(@tmpdir,"/outfile-test.4")); ---exec rm $MYSQL_TEST_DIR/var/tmp/outfile-test.4 +--remove_file $MYSQL_TEST_DIR/var/tmp/outfile-test.4 # # Bug #5382: 'explain select into outfile' crashes the server diff --git a/mysql-test/t/ps.test b/mysql-test/t/ps.test index fbeaaa494e0..5e5dcc36b19 100644 --- a/mysql-test/t/ps.test +++ b/mysql-test/t/ps.test @@ -1068,5 +1068,82 @@ EXECUTE stmt USING @a; DEALLOCATE PREPARE stmt; DROP TABLE t1; +# +# Bug#19182: CREATE TABLE bar (m INT) SELECT n FROM foo; doesn't work +# from stored procedure. +# +# The cause of a bug was that cached LEX::create_list was modified, +# and then together with LEX::key_list was reset. +# +--disable_warnings +DROP TABLE IF EXISTS t1, t2; +--enable_warnings + +CREATE TABLE t1 (i INT); + +PREPARE st_19182 +FROM "CREATE TABLE t2 (i INT, j INT, KEY (i), KEY(j)) SELECT i FROM t1"; + +EXECUTE st_19182; +DESC t2; + +DROP TABLE t2; + +# Check that on second execution we don't loose 'j' column and the keys +# on 'i' and 'j' columns. +EXECUTE st_19182; +DESC t2; + +DEALLOCATE PREPARE st_19182; +DROP TABLE t2, t1; + +# +# Bug #22060 "ALTER TABLE x AUTO_INCREMENT=y in SP crashes server" +# +# Code which implemented CREATE/ALTER TABLE and CREATE DATABASE +# statement modified HA_CREATE_INFO structure in LEX, making these +# statements PS/SP-unsafe (their re-execution might have resulted +# in incorrect results). +# +--disable_warnings +drop database if exists mysqltest; +drop table if exists t1, t2; +--enable_warnings +# CREATE TABLE and CREATE TABLE ... SELECT +create database mysqltest character set utf8; +prepare stmt1 from "create table mysqltest.t1 (c char(10))"; +prepare stmt2 from "create table mysqltest.t2 select 'test'"; +execute stmt1; +execute stmt2; +show create table mysqltest.t1; +show create table mysqltest.t2; +drop table mysqltest.t1; +drop table mysqltest.t2; +alter database mysqltest character set latin1; +execute stmt1; +execute stmt2; +show create table mysqltest.t1; +show create table mysqltest.t2; +drop database mysqltest; +deallocate prepare stmt1; +deallocate prepare stmt2; +# + +# +# Bug #27937: crash on the second execution for prepared statement +# from UNION with ORDER BY an expression containing RAND() +# + +CREATE TABLE t1(a int); +INSERT INTO t1 VALUES (2), (3), (1); + +PREPARE st1 FROM + '(SELECT a FROM t1) UNION (SELECT a+10 FROM t1) ORDER BY RAND()*0+a'; + +EXECUTE st1; +EXECUTE st1; + +DEALLOCATE PREPARE st1; +DROP TABLE t1; --echo End of 4.1 tests. diff --git a/mysql-test/t/query_cache.test b/mysql-test/t/query_cache.test index 3140739309e..d447df9187b 100644 --- a/mysql-test/t/query_cache.test +++ b/mysql-test/t/query_cache.test @@ -729,4 +729,50 @@ drop table t1; set GLOBAL query_cache_size=0; +# +# Bug #27792 query cache returns wrong result, with certain system variables +# + +create table t1 (a int); +insert into t1 values (1),(2),(3); +set GLOBAL query_cache_type=1; +set GLOBAL query_cache_limit=10000; +set GLOBAL query_cache_min_res_unit=0; +set GLOBAL query_cache_size= 100000; + +# default_week_format +reset query cache; +set LOCAL default_week_format = 0; +select week('2007-01-04'); +select week('2007-01-04') from t1; + +set LOCAL default_week_format = 2; +select week('2007-01-04'); +select week('2007-01-04') from t1; + +drop table t1; + +CREATE TABLE t1 (a VARCHAR(200), b TEXT, FULLTEXT (a,b)); +INSERT INTO t1 VALUES('MySQL has now support', 'for full-text search'), + ('Full-text indexes', 'are called collections'), + ('Only MyISAM tables','support collections'), + ('Function MATCH ... AGAINST()','is used to do a search'), + ('Full-text search in MySQL', 'implements vector space model'); + + +set GLOBAL ft_boolean_syntax='+ -><()~*:""&|'; + +select *, MATCH(a,b) AGAINST("+called +collections" IN BOOLEAN MODE) as x from t1; + +# swap +/- +set GLOBAL ft_boolean_syntax='- +><()~*:""&|'; + +select *, MATCH(a,b) AGAINST("+called +collections" IN BOOLEAN MODE) as x from t1; + +set GLOBAL query_cache_type=default; +set GLOBAL query_cache_limit=default; +set GLOBAL query_cache_min_res_unit=default; +set GLOBAL query_cache_size=default; + + # End of 4.1 tests diff --git a/mysql-test/t/query_cache_notembedded.test b/mysql-test/t/query_cache_notembedded.test index fd4785ffe95..20baf3450e2 100644 --- a/mysql-test/t/query_cache_notembedded.test +++ b/mysql-test/t/query_cache_notembedded.test @@ -81,12 +81,12 @@ drop table t1, t2, t3, t11, t21; # # do not use QC if tables locked (BUG#12385) # -connect (root,localhost,root,,test,$MASTER_MYPORT,master.sock); +connect (root,localhost,root,,test,$MASTER_MYPORT,$MASTER_MYSOCK); connection root; CREATE TABLE t1 ( a INT NOT NULL PRIMARY KEY AUTO_INCREMENT ) ENGINE = MyISAM; LOCK TABLE t1 READ LOCAL; -connect (root2,localhost,root,,test,$MASTER_MYPORT,master.sock); +connect (root2,localhost,root,,test,$MASTER_MYPORT,$MASTER_MYSOCK); connection root2; INSERT INTO t1 VALUES (), (), (); connection root; diff --git a/mysql-test/t/range.test b/mysql-test/t/range.test index 245178d7d4a..0dbfde92bd5 100644 --- a/mysql-test/t/range.test +++ b/mysql-test/t/range.test @@ -400,8 +400,8 @@ select count(*) from t1 where x = 18446744073709551601; create table t2 (x bigint not null); -insert into t2(x) values (0xfffffffffffffff0); -insert into t2(x) values (0xfffffffffffffff1); +insert into t2(x) values (-16); +insert into t2(x) values (-15); select * from t2; select count(*) from t2 where x>0; select count(*) from t2 where x=0; @@ -520,4 +520,190 @@ explain select a from t1 where a > 'x'; select a from t1 where a > 'x'; drop table t1; +# +# Bug #24776: assertion abort for 'range checked for each record' +# + +CREATE TABLE t1 ( + OXID varchar(32) COLLATE latin1_german2_ci NOT NULL DEFAULT '', + OXPARENTID varchar(32) COLLATE latin1_german2_ci NOT NULL DEFAULT 'oxrootid', + OXLEFT int NOT NULL DEFAULT '0', + OXRIGHT int NOT NULL DEFAULT '0', + OXROOTID varchar(32) COLLATE latin1_german2_ci NOT NULL DEFAULT '', + PRIMARY KEY (OXID), + KEY OXNID (OXID), + KEY OXLEFT (OXLEFT), + KEY OXRIGHT (OXRIGHT), + KEY OXROOTID (OXROOTID) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_german2_ci; + +INSERT INTO t1 VALUES +('d8c4177d09f8b11f5.52725521','oxrootid',1,40,'d8c4177d09f8b11f5.52725521'), +('d8c4177d151affab2.81582770','d8c4177d09f8b11f5.52725521',2,3, + 'd8c4177d09f8b11f5.52725521'), +('d8c4177d206a333d2.74422679','d8c4177d09f8b11f5.52725521',4,5, + 'd8c4177d09f8b11f5.52725521'), +('d8c4177d225791924.30714720','d8c4177d09f8b11f5.52725521',6,7, + 'd8c4177d09f8b11f5.52725521'), +('d8c4177d2380fc201.39666693','d8c4177d09f8b11f5.52725521',8,9, + 'd8c4177d09f8b11f5.52725521'), +('d8c4177d24ccef970.14957924','d8c4177d09f8b11f5.52725521',10,11, + 'd8c4177d09f8b11f5.52725521'); + +EXPLAIN +SELECT s.oxid FROM t1 v, t1 s + WHERE s.oxrootid = 'd8c4177d09f8b11f5.52725521' AND + v.oxrootid ='d8c4177d09f8b11f5.52725521' AND + s.oxleft > v.oxleft AND s.oxleft < v.oxright; + +SELECT s.oxid FROM t1 v, t1 s + WHERE s.oxrootid = 'd8c4177d09f8b11f5.52725521' AND + v.oxrootid ='d8c4177d09f8b11f5.52725521' AND + s.oxleft > v.oxleft AND s.oxleft < v.oxright; + +DROP TABLE t1; + +# BUG#26624 high mem usage (crash) in range optimizer (depends on order of fields in where) +create table t1 ( + c1 char(10), c2 char(10), c3 char(10), c4 char(10), + c5 char(10), c6 char(10), c7 char(10), c8 char(10), + c9 char(10), c10 char(10), c11 char(10), c12 char(10), + c13 char(10), c14 char(10), c15 char(10), c16 char(10), + index(c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12,c13,c14,c15,c16) +); +insert into t1 (c1) values ('1'),('1'),('1'),('1'); + +# This must run without crash and fast: +select * from t1 where + c1 in ("abcdefgh", "123456789", "qwertyuio", "asddfgh", + "abcdefg1", "123456781", "qwertyui1", "asddfg1", + "abcdefg2", "123456782", "qwertyui2", "asddfg2", + "abcdefg3", "123456783", "qwertyui3", "asddfg3", + "abcdefg4", "123456784", "qwertyui4", "asddfg4", + "abcdefg5", "123456785", "qwertyui5", "asddfg5", + "abcdefg6", "123456786", "qwertyui6", "asddfg6", + "abcdefg7", "123456787", "qwertyui7", "asddfg7", + "abcdefg8", "123456788", "qwertyui8", "asddfg8", + "abcdefg9", "123456789", "qwertyui9", "asddfg9", + "abcdefgA", "12345678A", "qwertyuiA", "asddfgA", + "abcdefgB", "12345678B", "qwertyuiB", "asddfgB", + "abcdefgC", "12345678C", "qwertyuiC", "asddfgC") + and c2 in ("abcdefgh", "123456789", "qwertyuio", "asddfgh", + "abcdefg1", "123456781", "qwertyui1", "asddfg1", + "abcdefg2", "123456782", "qwertyui2", "asddfg2", + "abcdefg3", "123456783", "qwertyui3", "asddfg3", + "abcdefg4", "123456784", "qwertyui4", "asddfg4", + "abcdefg5", "123456785", "qwertyui5", "asddfg5", + "abcdefg6", "123456786", "qwertyui6", "asddfg6", + "abcdefg7", "123456787", "qwertyui7", "asddfg7", + "abcdefg8", "123456788", "qwertyui8", "asddfg8", + "abcdefg9", "123456789", "qwertyui9", "asddfg9", + "abcdefgA", "12345678A", "qwertyuiA", "asddfgA", + "abcdefgB", "12345678B", "qwertyuiB", "asddfgB", + "abcdefgC", "12345678C", "qwertyuiC", "asddfgC") + and c3 in ("abcdefgh", "123456789", "qwertyuio", "asddfgh", + "abcdefg1", "123456781", "qwertyui1", "asddfg1", + "abcdefg2", "123456782", "qwertyui2", "asddfg2", + "abcdefg3", "123456783", "qwertyui3", "asddfg3", + "abcdefg4", "123456784", "qwertyui4", "asddfg4", + "abcdefg5", "123456785", "qwertyui5", "asddfg5", + "abcdefg6", "123456786", "qwertyui6", "asddfg6", + "abcdefg7", "123456787", "qwertyui7", "asddfg7", + "abcdefg8", "123456788", "qwertyui8", "asddfg8", + "abcdefg9", "123456789", "qwertyui9", "asddfg9", + "abcdefgA", "12345678A", "qwertyuiA", "asddfgA", + "abcdefgB", "12345678B", "qwertyuiB", "asddfgB", + "abcdefgC", "12345678C", "qwertyuiC", "asddfgC") + and c4 in ("abcdefgh", "123456789", "qwertyuio", "asddfgh", + "abcdefg1", "123456781", "qwertyui1", "asddfg1", + "abcdefg2", "123456782", "qwertyui2", "asddfg2", + "abcdefg3", "123456783", "qwertyui3", "asddfg3", + "abcdefg4", "123456784", "qwertyui4", "asddfg4", + "abcdefg5", "123456785", "qwertyui5", "asddfg5", + "abcdefg6", "123456786", "qwertyui6", "asddfg6", + "abcdefg7", "123456787", "qwertyui7", "asddfg7", + "abcdefg8", "123456788", "qwertyui8", "asddfg8", + "abcdefg9", "123456789", "qwertyui9", "asddfg9", + "abcdefgA", "12345678A", "qwertyuiA", "asddfgA", + "abcdefgB", "12345678B", "qwertyuiB", "asddfgB", + "abcdefgC", "12345678C", "qwertyuiC", "asddfgC") + and c5 in ("abcdefgh", "123456789", "qwertyuio", "asddfgh", + "abcdefg1", "123456781", "qwertyui1", "asddfg1", + "abcdefg2", "123456782", "qwertyui2", "asddfg2", + "abcdefg3", "123456783", "qwertyui3", "asddfg3", + "abcdefg4", "123456784", "qwertyui4", "asddfg4", + "abcdefg5", "123456785", "qwertyui5", "asddfg5", + "abcdefg6", "123456786", "qwertyui6", "asddfg6", + "abcdefg7", "123456787", "qwertyui7", "asddfg7", + "abcdefg8", "123456788", "qwertyui8", "asddfg8", + "abcdefg9", "123456789", "qwertyui9", "asddfg9", + "abcdefgA", "12345678A", "qwertyuiA", "asddfgA", + "abcdefgB", "12345678B", "qwertyuiB", "asddfgB", + "abcdefgC", "12345678C", "qwertyuiC", "asddfgC") + and c6 in ("abcdefgh", "123456789", "qwertyuio", "asddfgh", + "abcdefg1", "123456781", "qwertyui1", "asddfg1", + "abcdefg2", "123456782", "qwertyui2", "asddfg2", + "abcdefg3", "123456783", "qwertyui3", "asddfg3", + "abcdefg4", "123456784", "qwertyui4", "asddfg4", + "abcdefg5", "123456785", "qwertyui5", "asddfg5", + "abcdefg6", "123456786", "qwertyui6", "asddfg6", + "abcdefg7", "123456787", "qwertyui7", "asddfg7", + "abcdefg8", "123456788", "qwertyui8", "asddfg8", + "abcdefg9", "123456789", "qwertyui9", "asddfg9", + "abcdefgA", "12345678A", "qwertyuiA", "asddfgA", + "abcdefgB", "12345678B", "qwertyuiB", "asddfgB", + "abcdefgC", "12345678C", "qwertyuiC", "asddfgC") + and c7 in ("abcdefgh", "123456789", "qwertyuio", "asddfgh", + "abcdefg1", "123456781", "qwertyui1", "asddfg1", + "abcdefg2", "123456782", "qwertyui2", "asddfg2", + "abcdefg3", "123456783", "qwertyui3", "asddfg3", + "abcdefg4", "123456784", "qwertyui4", "asddfg4", + "abcdefg5", "123456785", "qwertyui5", "asddfg5", + "abcdefg6", "123456786", "qwertyui6", "asddfg6", + "abcdefg7", "123456787", "qwertyui7", "asddfg7", + "abcdefg8", "123456788", "qwertyui8", "asddfg8", + "abcdefg9", "123456789", "qwertyui9", "asddfg9", + "abcdefgA", "12345678A", "qwertyuiA", "asddfgA", + "abcdefgB", "12345678B", "qwertyuiB", "asddfgB", + "abcdefgC", "12345678C", "qwertyuiC", "asddfgC") + and c8 in ("abcdefgh", "123456789", "qwertyuio", "asddfgh", + "abcdefg1", "123456781", "qwertyui1", "asddfg1", + "abcdefg2", "123456782", "qwertyui2", "asddfg2", + "abcdefg3", "123456783", "qwertyui3", "asddfg3", + "abcdefg4", "123456784", "qwertyui4", "asddfg4", + "abcdefg5", "123456785", "qwertyui5", "asddfg5", + "abcdefg6", "123456786", "qwertyui6", "asddfg6", + "abcdefg7", "123456787", "qwertyui7", "asddfg7", + "abcdefg8", "123456788", "qwertyui8", "asddfg8", + "abcdefg9", "123456789", "qwertyui9", "asddfg9", + "abcdefgA", "12345678A", "qwertyuiA", "asddfgA", + "abcdefgB", "12345678B", "qwertyuiB", "asddfgB", + "abcdefgC", "12345678C", "qwertyuiC", "asddfgC") + and c9 in ("abcdefgh", "123456789", "qwertyuio", "asddfgh", + "abcdefg1", "123456781", "qwertyui1", "asddfg1", + "abcdefg2", "123456782", "qwertyui2", "asddfg2", + "abcdefg3", "123456783", "qwertyui3", "asddfg3", + "abcdefg4", "123456784", "qwertyui4", "asddfg4", + "abcdefg5", "123456785", "qwertyui5", "asddfg5", + "abcdefg6", "123456786", "qwertyui6", "asddfg6", + "abcdefg7", "123456787", "qwertyui7", "asddfg7", + "abcdefg8", "123456788", "qwertyui8", "asddfg8", + "abcdefg9", "123456789", "qwertyui9", "asddfg9", + "abcdefgA", "12345678A", "qwertyuiA", "asddfgA", + "abcdefgB", "12345678B", "qwertyuiB", "asddfgB", + "abcdefgC", "12345678C", "qwertyuiC", "asddfgC") + and c10 in ("abcdefgh", "123456789", "qwertyuio", "asddfgh", + "abcdefg1", "123456781", "qwertyui1", "asddfg1", + "abcdefg2", "123456782", "qwertyui2", "asddfg2", + "abcdefg3", "123456783", "qwertyui3", "asddfg3", + "abcdefg4", "123456784", "qwertyui4", "asddfg4", + "abcdefg5", "123456785", "qwertyui5", "asddfg5", + "abcdefg6", "123456786", "qwertyui6", "asddfg6", + "abcdefg7", "123456787", "qwertyui7", "asddfg7", + "abcdefg8", "123456788", "qwertyui8", "asddfg8", + "abcdefg9", "123456789", "qwertyui9", "asddfg9", + "abcdefgA", "12345678A", "qwertyuiA", "asddfgA", + "abcdefgB", "12345678B", "qwertyuiB", "asddfgB", + "abcdefgC", "12345678C", "qwertyuiC", "asddfgC"); +drop table t1; --echo End of 4.1 tests diff --git a/mysql-test/t/row.test b/mysql-test/t/row.test index d8d9a244134..6f845607d8c 100644 --- a/mysql-test/t/row.test +++ b/mysql-test/t/row.test @@ -83,4 +83,74 @@ drop table t1; SELECT ROW(2,10) <=> ROW(3,4); SELECT ROW(NULL,10) <=> ROW(3,NULL); +# +# Bug #27484: nested row expressions in IN predicate +# + +--error 1241 +SELECT ROW(1,ROW(2,3)) IN (ROW(1,ROW(2,3)),ROW(1,1)); +--error 1241 +SELECT ROW(1,ROW(2,3)) IN (ROW(1,ROW(2,3)),ROW(1,1),ROW(1,ROW(2,3))); +--error 1241 +SELECT ROW(1,ROW(2,3)) IN (ROW(1,ROW(2,3)),ROW(1,ROW(2,2,2))); +--error 1241 +SELECT ROW(1,ROW(2,3,4)) IN (ROW(1,ROW(2,3,4)),ROW(1,ROW(2,2))); + +--error 1241 +SELECT ROW(1,ROW(2,3)) IN (ROW(1,ROW(2,3)),(SELECT 1,1)); +--error 1241 +SELECT ROW(1,ROW(2,3)) IN (ROW(1,ROW(2,3)),(SELECT 1,1),ROW(1,ROW(2,4))); +--error 1241 +SELECT ROW(1,ROW(2,3)) IN ((SELECT 1,1),ROW(1,ROW(2,3))); + +--error 1241 +SELECT ROW(2,1) IN (ROW(21,2),ROW(ROW(1,1,3),0)); +--error 1241 +SELECT ROW(2,1) IN (ROW(ROW(1,1,3),0),ROW(21,2)); + +# +# Bug#27704: erroneous comparison of rows with NULL components +# +CREATE TABLE t1(a int, b int, c int); +INSERT INTO t1 VALUES (1, 2, 3), + (NULL, 2, 3 ), (1, NULL, 3 ), (1, 2, NULL), + (NULL, 2, 3+1), (1, NULL, 3+1), (1, 2+1, NULL), + (NULL, 2, 3-1), (1, NULL, 3-1), (1, 2-1, NULL); + +SELECT (1,2,3) = (1, NULL, 3); +SELECT (1,2,3) = (1+1, NULL, 3); +SELECT (1,2,3) = (1, NULL, 3+1); +SELECT * FROM t1 WHERE (a,b,c) = (1,2,3); + +SELECT (1,2,3) <> (1, NULL, 3); +SELECT (1,2,3) <> (1+1, NULL, 3); +SELECT (1,2,3) <> (1, NULL, 3+1); +SELECT * FROM t1 WHERE (a,b,c) <> (1,2,3); + +SELECT (1,2,3) < (NULL, 2, 3); +SELECT (1,2,3) < (1, NULL, 3); +SELECT (1,2,3) < (1-1, NULL, 3); +SELECT (1,2,3) < (1+1, NULL, 3); +SELECT * FROM t1 WHERE (a,b,c) < (1,2,3); + +SELECT (1,2,3) <= (NULL, 2, 3); +SELECT (1,2,3) <= (1, NULL, 3); +SELECT (1,2,3) <= (1-1, NULL, 3); +SELECT (1,2,3) <= (1+1, NULL, 3); +SELECT * FROM t1 WHERE (a,b,c) <= (1,2,3); + +SELECT (1,2,3) > (NULL, 2, 3); +SELECT (1,2,3) > (1, NULL, 3); +SELECT (1,2,3) > (1-1, NULL, 3); +SELECT (1,2,3) > (1+1, NULL, 3); +SELECT * FROM t1 WHERE (a,b,c) > (1,2,3); + +SELECT (1,2,3) >= (NULL, 2, 3); +SELECT (1,2,3) >= (1, NULL, 3); +SELECT (1,2,3) >= (1-1, NULL, 3); +SELECT (1,2,3) >= (1+1, NULL, 3); +SELECT * FROM t1 WHERE (a,b,c) >= (1,2,3); + +DROP TABLE t1; + # End of 4.1 tests diff --git a/mysql-test/t/rpl000015.test b/mysql-test/t/rpl000015.test index 4e329fc87ea..a23a4b0ba5f 100644 --- a/mysql-test/t/rpl000015.test +++ b/mysql-test/t/rpl000015.test @@ -1,5 +1,5 @@ -connect (master,localhost,root,,test,$MASTER_MYPORT,master.sock); -connect (slave,localhost,root,,test,$SLAVE_MYPORT,slave.sock); +connect (master,localhost,root,,test,$MASTER_MYPORT,$MASTER_MYSOCK); +connect (slave,localhost,root,,test,$SLAVE_MYPORT,$SLAVE_MYSOCK); connection master; reset master; show master status; diff --git a/mysql-test/t/rpl000017.test b/mysql-test/t/rpl000017.test index 7b4e6bf4d3a..ea3aba8fb75 100644 --- a/mysql-test/t/rpl000017.test +++ b/mysql-test/t/rpl000017.test @@ -1,5 +1,5 @@ -connect (master,localhost,root,,test,$MASTER_MYPORT,master.sock); -connect (slave,localhost,root,,test,$SLAVE_MYPORT,slave.sock); +connect (master,localhost,root,,test,$MASTER_MYPORT,$MASTER_MYSOCK); +connect (slave,localhost,root,,test,$SLAVE_MYPORT,$SLAVE_MYSOCK); connection master; reset master; grant replication slave on *.* to replicate@localhost identified by 'aaaaaaaaaaaaaaab'; diff --git a/mysql-test/t/rpl_ignore_table.test b/mysql-test/t/rpl_ignore_table.test index bc651779208..660921a94be 100644 --- a/mysql-test/t/rpl_ignore_table.test +++ b/mysql-test/t/rpl_ignore_table.test @@ -26,3 +26,101 @@ SELECT * FROM t4; connection master; DROP TABLE t1; DROP TABLE t4; + + +# +# Bug#25482 GRANT statements are not replicated if +# you use "replicate-ignore-table" +# + +--echo **** Test case for BUG#25482 **** +--echo **** Adding GRANTS on master **** + +connection master; +create table test.t1(a int); +create table test.t4(a int); + +# Simple user that should not replicate +GRANT SELECT ON test.t1 TO mysqltest1@localhost; + +# Partial replicate +GRANT INSERT ON test.t4 TO mysqltest2@localhost; +GRANT select, update, insert, references on t1 + to mysqltest2@localhost; + +# Partial replicate 2 +GRANT SELECT ON test.* TO mysqltest3@localhost; +GRANT INSERT ON test.t4 TO mysqltest3@localhost; +GRANT select(a), update(a), insert(a), references(a) on t4 + to mysqltest3@localhost; + +# Create another database and table +create database mysqltest2; +create table mysqltest2.t2 (id int); +# Create a grant that should replicate +GRANT SELECT ON mysqltest2.t2 TO mysqltest4@localhost IDENTIFIED BY 'pass'; + +# Create a grant manually +insert into mysql.user (user, host) values ("mysqltest5", "somehost"); + +# Partial replicate 3 with *.* +GRANT SELECT ON *.* TO mysqltest6@localhost; +GRANT INSERT ON *.* TO mysqltest6@localhost; +GRANT INSERT ON test.* TO mysqltest6@localhost; +GRANT INSERT ON test.t1 TO mysqltest6@localhost; + +show grants for mysqltest1@localhost; +show grants for mysqltest2@localhost; +show grants for mysqltest3@localhost; +show grants for mysqltest4@localhost; +show grants for mysqltest6@localhost; + +flush privileges; +show grants for mysqltest5@somehost; + + +sync_slave_with_master; + +--echo **** Checking grants on slave **** + +# Check that grants are replicated to slave +show grants for mysqltest2@localhost; +show grants for mysqltest3@localhost; +show grants for mysqltest4@localhost; +show grants for mysqltest5@somehost; +show grants for mysqltest6@localhost; + +# mysqltest1 should not be on slave +--error 1141 +show grants for mysqltest1@localhost; + +--echo **** Revoking grants on master **** +connection master; +REVOKE SELECT ON test.t1 FROM mysqltest1@localhost; +REVOKE SELECT ON mysqltest2.t2 FROM mysqltest4@localhost; +REVOKE select(a) on t4 + from mysqltest3@localhost; + +show grants for mysqltest1@localhost; +show grants for mysqltest3@localhost; +show grants for mysqltest4@localhost; + +sync_slave_with_master; + +--echo **** Checking grants on slave **** + +# mysqltest1 should not be on slave +--error 1141 +show grants for mysqltest1@localhost; +show grants for mysqltest3@localhost; +show grants for mysqltest4@localhost; + +# Cleanup +connection master; +drop table t1, t4, mysqltest2.t2; +drop database mysqltest2; +delete from mysql.user where user like "mysqltest%"; +delete from mysql.db where user like "mysqltest%"; +delete from mysql.tables_priv where user like "mysqltest%"; +delete from mysql.columns_priv where user like "mysqltest%"; +sync_slave_with_master; diff --git a/mysql-test/t/rpl_locale.test b/mysql-test/t/rpl_locale.test new file mode 100644 index 00000000000..530a3d77636 --- /dev/null +++ b/mysql-test/t/rpl_locale.test @@ -0,0 +1,22 @@ +# Replication of locale variables + +source include/master-slave.inc; + +# +# Bug#22645 LC_TIME_NAMES: Statement not replicated +# +connection master; +create table t1 (s1 char(10)); +set lc_time_names= 'de_DE'; +insert into t1 values (date_format('2001-01-01','%W')); +select * from t1; +sync_slave_with_master; +connection slave; +select * from t1; +connection master; +drop table t1; +sync_slave_with_master; + +# End of 4.1 tests + + diff --git a/mysql-test/t/rpl_rotate_logs.test b/mysql-test/t/rpl_rotate_logs.test index 891582a167c..8d751e43958 100644 --- a/mysql-test/t/rpl_rotate_logs.test +++ b/mysql-test/t/rpl_rotate_logs.test @@ -9,11 +9,11 @@ # changes # - Test creating a duplicate key error and recover from it -connect (master,localhost,root,,test,$MASTER_MYPORT,master.sock); +connect (master,localhost,root,,test,$MASTER_MYPORT,$MASTER_MYSOCK); --disable_warnings drop table if exists t1, t2, t3, t4; --enable_warnings -connect (slave,localhost,root,,test,$SLAVE_MYPORT,slave.sock); +connect (slave,localhost,root,,test,$SLAVE_MYPORT,$SLAVE_MYSOCK); system cat /dev/null > var/slave-data/master.info; system chmod 000 var/slave-data/master.info; connection slave; diff --git a/mysql-test/t/select.test b/mysql-test/t/select.test index 3f9fb59d26f..0dc179e9b4b 100644 --- a/mysql-test/t/select.test +++ b/mysql-test/t/select.test @@ -2342,4 +2342,15 @@ select min(key1) from t1 where key1 >= 0.3762 and rand() + 0.5 >= 0.5; DROP TABLE t1,t2; --enable_ps_protocol -# End of 4.1 tests +# +# Bug #22533: storing large hex strings +# + +create table t1(a bigint unsigned, b bigint); +insert into t1 values (0xfffffffffffffffff, 0xfffffffffffffffff), + (0x10000000000000000, 0x10000000000000000), + (0x8fffffffffffffff, 0x8fffffffffffffff); +select hex(a), hex(b) from t1; +drop table t1; + +--echo End of 4.1 tests diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test index 804cc2274c9..67a18e7a30f 100644 --- a/mysql-test/t/subselect.test +++ b/mysql-test/t/subselect.test @@ -1981,4 +1981,75 @@ SHOW CREATE TABLE t3; DROP TABLE t1,t2,t3; +# +# Bug 24670: subquery witout tables but with a WHERE clause +# + +CREATE TABLE t1 (a int); +INSERT INTO t1 VALUES (1), (2); + +SELECT a FROM t1 WHERE (SELECT 1 FROM DUAL WHERE 1=0) > 0; +SELECT a FROM t1 WHERE (SELECT 1 FROM DUAL WHERE 1=0) IS NULL; +EXPLAIN SELECT a FROM t1 WHERE (SELECT 1 FROM DUAL WHERE 1=0) IS NULL; + +DROP TABLE t1; + +# +# Bug 24653: sorting by expressions containing subselects +# that return more than one row +# + +CREATE TABLE t1 (a int); +INSERT INTO t1 VALUES (2), (4), (1), (3); + +CREATE TABLE t2 (b int, c int); +INSERT INTO t2 VALUES + (2,1), (1,3), (2,1), (4,4), (2,2), (1,4); + +SELECT a FROM t1 ORDER BY (SELECT c FROM t2 WHERE b > 2 ); +--error 1242 +SELECT a FROM t1 ORDER BY (SELECT c FROM t2 WHERE b > 1); +SELECT a FROM t1 ORDER BY (SELECT c FROM t2 WHERE b > 2), a; +--error 1242 +SELECT a FROM t1 ORDER BY (SELECT c FROM t2 WHERE b > 1), a; + +SELECT b, MAX(c) FROM t2 GROUP BY b, (SELECT c FROM t2 WHERE b > 2); +--error 1242 +SELECT b, MAX(c) FROM t2 GROUP BY b, (SELECT c FROM t2 WHERE b > 1); + + +SELECT a FROM t1 GROUP BY a + HAVING IFNULL((SELECT b FROM t2 WHERE b > 2), + (SELECT c FROM t2 WHERE c=a AND b > 2 ORDER BY b)) > 3; +--error 1242 +SELECT a FROM t1 GROUP BY a + HAVING IFNULL((SELECT b FROM t2 WHERE b > 1), + (SELECT c FROM t2 WHERE c=a AND b > 2 ORDER BY b)) > 3; + +SELECT a FROM t1 GROUP BY a + HAVING IFNULL((SELECT b FROM t2 WHERE b > 4), + (SELECT c FROM t2 WHERE c=a AND b > 2 ORDER BY b)) > 3; +--error 1242 +SELECT a FROM t1 GROUP BY a + HAVING IFNULL((SELECT b FROM t2 WHERE b > 4), + (SELECT c FROM t2 WHERE c=a AND b > 1 ORDER BY b)) > 3; + +SELECT a FROM t1 + ORDER BY IFNULL((SELECT b FROM t2 WHERE b > 2), + (SELECT c FROM t2 WHERE c=a AND b > 2 ORDER BY b)); +--error 1242 +SELECT a FROM t1 + ORDER BY IFNULL((SELECT b FROM t2 WHERE b > 1), + (SELECT c FROM t2 WHERE c=a AND b > 1 ORDER BY b)); + +SELECT a FROM t1 + ORDER BY IFNULL((SELECT b FROM t2 WHERE b > 4), + (SELECT c FROM t2 WHERE c=a AND b > 2 ORDER BY b)); +--error 1242 +SELECT a FROM t1 + ORDER BY IFNULL((SELECT b FROM t2 WHERE b > 4), + (SELECT c FROM t2 WHERE c=a AND b > 1 ORDER BY b)); + +DROP TABLE t1,t2; + # End of 4.1 tests diff --git a/mysql-test/t/symlink.test b/mysql-test/t/symlink.test index 201a2866c4f..010ef496399 100644 --- a/mysql-test/t/symlink.test +++ b/mysql-test/t/symlink.test @@ -170,4 +170,23 @@ connection default; select * from t1; drop table t1; -# End of 4.1 tests +# +# CREATE TABLE with DATA DIRECTORY option +# +# Protect ourselves from data left in tmp/ by a previos possibly failed +# test +--system rm -f $MYSQLTEST_VARDIR/tmp/t1.* +--disable_query_log +eval prepare stmt from "create table t1 (c char(10)) data directory='$MYSQLTEST_VARDIR/tmp'"; +--enable_query_log +execute stmt; +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +show create table t1; +drop table t1; +execute stmt; +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +show create table t1; +drop table t1; +deallocate prepare stmt; + +--echo End of 4.1 tests diff --git a/mysql-test/t/type_datetime.test b/mysql-test/t/type_datetime.test index 4b6741b4242..87b86b55fc9 100644 --- a/mysql-test/t/type_datetime.test +++ b/mysql-test/t/type_datetime.test @@ -113,4 +113,10 @@ insert into t1 values ("00-00-00"), ("00-00-00 00:00:00"); select * from t1; drop table t1; +# +# Bug #16546 DATETIME+0 not always coerced the same way +# +select cast('2006-12-05 22:10:10' as datetime) + 0; + + # End of 4.1 tests diff --git a/mysql-test/t/type_enum.test b/mysql-test/t/type_enum.test index 68f5664c36d..4b3429d9ea0 100644 --- a/mysql-test/t/type_enum.test +++ b/mysql-test/t/type_enum.test @@ -136,4 +136,24 @@ alter table t1 add f2 enum(0xFFFF); show create table t1; drop table t1; -# End of 4.1 tests +# +# Bug#24660 "enum" field type definition problem +# +create table t1(russian enum('E','F','EF','FE') NOT NULL DEFAULT'E'); +show create table t1; +drop table t1; + +create table t1(denormal enum('E','F','E,F','F,E') NOT NULL DEFAULT'E'); +show create table t1; +drop table t1; + +create table t1(russian_deviant enum('E','F','EF','F,E') NOT NULL DEFAULT'E'); +show create table t1; +drop table t1; + +# ER_WRONG_FIELD_TERMINATORS +--error 1083 +create table t1(exhausting_charset enum('ABCDEFGHIJKLMNOPQRSTUVWXYZ',' +
!"','#$%&\'()*+,-./0123456789:;<=>?@ABCDEFGHIJKLMNOPQRSTUVWXYZ[\\]^_`abcdefghijklmnopqrstuvwxyz{|}~','xx\','yy\','zz')); + +--echo End of 4.1 tests diff --git a/mysql-test/t/type_float.test b/mysql-test/t/type_float.test index 8a484f7bcd0..8c22d22ca66 100644 --- a/mysql-test/t/type_float.test +++ b/mysql-test/t/type_float.test @@ -188,4 +188,29 @@ select 1e-308, 1.00000001e-300, 100000000e-300; # check if overflows are detected correctly select 10e307; +# +# Bug #19690: ORDER BY eliminates rows from the result +# +create table t1(a int, b double(8, 2)); +insert into t1 values +(1, 28.50), (1, 121.85), (1, 157.23), (1, 1351.00), (1, -1965.35), (1, 81.75), +(1, 217.08), (1, 7.94), (4, 96.07), (4, 6404.65), (4, -6500.72), (2, 100.00), +(5, 5.00), (5, -2104.80), (5, 2033.80), (5, 0.07), (5, 65.93), +(3, -4986.24), (3, 5.00), (3, 4857.34), (3, 123.74), (3, 0.16), +(6, -1695.31), (6, 1003.77), (6, 499.72), (6, 191.82); +explain select sum(b) s from t1 group by a; +select sum(b) s from t1 group by a; +select sum(b) s from t1 group by a having s <> 0; +select sum(b) s from t1 group by a having s <> 0 order by s; +select sum(b) s from t1 group by a having s <=> 0; +select sum(b) s from t1 group by a having s <=> 0 order by s; +alter table t1 add key (a, b); +explain select sum(b) s from t1 group by a; +select sum(b) s from t1 group by a; +select sum(b) s from t1 group by a having s <> 0; +select sum(b) s from t1 group by a having s <> 0 order by s; +select sum(b) s from t1 group by a having s <=> 0; +select sum(b) s from t1 group by a having s <=> 0 order by s; +drop table t1; + --echo End of 4.1 tests diff --git a/mysql-test/t/update.test b/mysql-test/t/update.test index c69c56f0331..e5287eacbc8 100644 --- a/mysql-test/t/update.test +++ b/mysql-test/t/update.test @@ -306,4 +306,12 @@ create table t1(f1 int, `*f2` int); insert into t1 values (1,1); update t1 set `*f2`=1; drop table t1; + +# +# Bug#25126: Wrongly resolved field leads to a crash +# +create table t1(f1 int); +--error 1054 +update t1 set f2=1 order by f2; +drop table t1; # End of 4.1 tests diff --git a/mysql-test/t/variables.test b/mysql-test/t/variables.test index 8322c0f84bd..808dc0973d4 100644 --- a/mysql-test/t/variables.test +++ b/mysql-test/t/variables.test @@ -397,6 +397,50 @@ set names latin1; select @@have_innodb; # +# Tests for lc_time_names +# Note, when adding new locales, please fix ID accordingly: +# - to test the last ID (currently 108) +# - and the next after the last (currently 109) +# +--echo *** Various tests with LC_TIME_NAMES +--echo *** LC_TIME_NAMES: testing case insensitivity +set @@lc_time_names='ru_ru'; +select @@lc_time_names; +--echo *** LC_TIME_NAMES: testing with a user variable +set @lc='JA_JP'; +set @@lc_time_names=@lc; +select @@lc_time_names; +--echo *** LC_TIME_NAMES: testing with string expressions +set lc_time_names=concat('de','_','DE'); +select @@lc_time_names; +--error 1105 +set lc_time_names=concat('de','+','DE'); +select @@lc_time_names; +--echo LC_TIME_NAMES: testing with numeric expressions +set @@lc_time_names=1+2; +select @@lc_time_names; +--error 1232 +set @@lc_time_names=1/0; +select @@lc_time_names; +set lc_time_names=en_US; +--echo LC_TIME_NAMES: testing NULL and a negative number: +--error 1231 +set lc_time_names=NULL; +--error 1105 +set lc_time_names=-1; +select @@lc_time_names; +--echo LC_TIME_NAMES: testing locale with the last ID: +set lc_time_names=108; +select @@lc_time_names; +--echo LC_TIME_NAMES: testing a number beyond the valid ID range: +--error 1105 +set lc_time_names=109; +select @@lc_time_names; +--echo LC_TIME_NAMES: testing that 0 is en_US: +set lc_time_names=0; +select @@lc_time_names; + +# # Bug #13334: query_prealloc_size default less than minimum # set @test = @@query_prealloc_size; diff --git a/mysql-test/t/windows.test b/mysql-test/t/windows.test index d6bcfeb8cb3..b5377a9b9b0 100644 --- a/mysql-test/t/windows.test +++ b/mysql-test/t/windows.test @@ -17,4 +17,13 @@ use prn; create table nu (a int); drop table nu; +# +# Bug17489: ailed to put data file in custom directory use "data directory" option +# +--disable_warnings +drop table if exists t1; +--enable_warnings +CREATE TABLE t1 ( `ID` int(6) ) data directory 'c:/tmp/' index directory 'c:/tmp/' engine=MyISAM; +drop table t1; + # End of 4.1 tests |