summaryrefslogtreecommitdiff
path: root/mysql-test
diff options
context:
space:
mode:
Diffstat (limited to 'mysql-test')
-rw-r--r--mysql-test/r/innodb_mysql.result53
-rw-r--r--mysql-test/r/loaddata.result54
-rw-r--r--mysql-test/t/innodb_mysql.test58
-rw-r--r--mysql-test/t/loaddata.test35
4 files changed, 198 insertions, 2 deletions
diff --git a/mysql-test/r/innodb_mysql.result b/mysql-test/r/innodb_mysql.result
index 11c7c2aedc9..8ddd3d2f1e8 100644
--- a/mysql-test/r/innodb_mysql.result
+++ b/mysql-test/r/innodb_mysql.result
@@ -1,4 +1,4 @@
-drop table if exists t1,t2;
+drop table if exists t1,t2,t3,t4;
create table t1 (
c_id int(11) not null default '0',
org_id int(11) default null,
@@ -676,4 +676,55 @@ INSERT INTO t1 VALUES (1);
switch to connection default
SET AUTOCOMMIT=default;
DROP TABLE t1,t2;
+CREATE TABLE t1 (
+id int NOT NULL auto_increment PRIMARY KEY,
+b int NOT NULL,
+c datetime NOT NULL,
+INDEX idx_b(b),
+INDEX idx_c(c)
+) ENGINE=InnoDB;
+CREATE TABLE t2 (
+b int NOT NULL auto_increment PRIMARY KEY,
+c datetime NOT NULL
+) ENGINE= MyISAM;
+INSERT INTO t2(c) VALUES ('2007-01-01');
+INSERT INTO t2(c) SELECT c FROM t2;
+INSERT INTO t2(c) SELECT c FROM t2;
+INSERT INTO t2(c) SELECT c FROM t2;
+INSERT INTO t2(c) SELECT c FROM t2;
+INSERT INTO t2(c) SELECT c FROM t2;
+INSERT INTO t2(c) SELECT c FROM t2;
+INSERT INTO t2(c) SELECT c FROM t2;
+INSERT INTO t2(c) SELECT c FROM t2;
+INSERT INTO t2(c) SELECT c FROM t2;
+INSERT INTO t2(c) SELECT c FROM t2;
+INSERT INTO t1(b,c) SELECT b,c FROM t2;
+UPDATE t2 SET c='2007-01-02';
+INSERT INTO t1(b,c) SELECT b,c FROM t2;
+UPDATE t2 SET c='2007-01-03';
+INSERT INTO t1(b,c) SELECT b,c FROM t2;
+set @@sort_buffer_size=8192;
+SELECT COUNT(*) FROM t1;
+COUNT(*)
+3072
+EXPLAIN
+SELECT COUNT(*) FROM t1
+WHERE (c >= '2007-01-02' AND c <= '2007-01-03') OR b >= 1;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL idx_b,idx_c NULL NULL NULL # Using where
+SELECT COUNT(*) FROM t1
+WHERE (c >= '2007-01-02' AND c <= '2007-01-03') OR b >= 1;
+COUNT(*)
+3072
+EXPLAIN
+SELECT COUNT(*) FROM t1 FORCE INDEX(idx_b, idx_c)
+WHERE (c >= '2007-01-02' AND c <= '2007-01-03') OR b >= 1;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index_merge idx_b,idx_c idx_c,idx_b 8,4 NULL # Using sort_union(idx_c,idx_b); Using where
+SELECT COUNT(*) FROM t1 FORCE INDEX(idx_b, idx_c)
+WHERE (c >= '2007-01-02' AND c <= '2007-01-03') OR b >= 1;
+COUNT(*)
+3072
+set @@sort_buffer_size=default;
+DROP TABLE t1,t2;
End of 5.0 tests
diff --git a/mysql-test/r/loaddata.result b/mysql-test/r/loaddata.result
index a02aaccf8f6..031aa5c700c 100644
--- a/mysql-test/r/loaddata.result
+++ b/mysql-test/r/loaddata.result
@@ -86,6 +86,60 @@ field1 field2
a"b cd"ef
a"b c"d"e
drop table t1;
+CREATE TABLE t1 (
+id INT AUTO_INCREMENT PRIMARY KEY,
+c1 VARCHAR(255)
+);
+CREATE TABLE t2 (
+id INT,
+c2 VARCHAR(255)
+);
+INSERT INTO t1 (c1) VALUES
+('r'), ('rr'), ('rrr'), ('rrrr'),
+('.r'), ('.rr'), ('.rrr'), ('.rrrr'),
+('r.'), ('rr.'), ('rrr.'), ('rrrr.'),
+('.r.'), ('.rr.'), ('.rrr.'), ('.rrrr.');
+SELECT * FROM t1;
+id c1
+1 r
+2 rr
+3 rrr
+4 rrrr
+5 .r
+6 .rr
+7 .rrr
+8 .rrrr
+9 r.
+10 rr.
+11 rrr.
+12 rrrr.
+13 .r.
+14 .rr.
+15 .rrr.
+16 .rrrr.
+SELECT * INTO OUTFILE 'MYSQLTEST_VARDIR/tmp/t1' FIELDS ENCLOSED BY 'r' FROM t1;
+r1r rrrr
+r2r rrrrrr
+r3r rrrrrrrr
+r4r rrrrrrrrrr
+r5r r.rrr
+r6r r.rrrrr
+r7r r.rrrrrrr
+r8r r.rrrrrrrrr
+r9r rrr.r
+r10r rrrrr.r
+r11r rrrrrrr.r
+r12r rrrrrrrrr.r
+r13r r.rr.r
+r14r r.rrrr.r
+r15r r.rrrrrr.r
+r16r r.rrrrrrrr.r
+LOAD DATA INFILE 'MYSQLTEST_VARDIR/tmp/t1' INTO TABLE t2 FIELDS ENCLOSED BY 'r';
+SELECT t1.id, c1, c2 FROM t1 LEFT JOIN t2 ON t1.id=t2.id WHERE c1 != c2;
+id c1 c2
+SELECT t1.id, c1, c2 FROM t1 RIGHT JOIN t2 ON t1.id=t2.id WHERE c1 != c2;
+id c1 c2
+DROP TABLE t1,t2;
create table t1 (a int default 100, b int, c varchar(60));
load data infile '../std_data_ln/rpl_loaddata.dat' into table t1 (a, @b) set b=@b+10, c=concat("b=",@b);
select * from t1;
diff --git a/mysql-test/t/innodb_mysql.test b/mysql-test/t/innodb_mysql.test
index 25ba9a258ff..148181ee443 100644
--- a/mysql-test/t/innodb_mysql.test
+++ b/mysql-test/t/innodb_mysql.test
@@ -1,7 +1,7 @@
-- source include/have_innodb.inc
--disable_warnings
-drop table if exists t1,t2;
+drop table if exists t1,t2,t3,t4;
--enable_warnings
# BUG#16798: Uninitialized row buffer reads in ref-or-null optimizer
@@ -670,5 +670,61 @@ DISCONNECT c1;
DISCONNECT c2;
DROP TABLE t1,t2;
+#
+# Bug #25798: a query with forced index merge returns wrong result
+#
+
+CREATE TABLE t1 (
+ id int NOT NULL auto_increment PRIMARY KEY,
+ b int NOT NULL,
+ c datetime NOT NULL,
+ INDEX idx_b(b),
+ INDEX idx_c(c)
+) ENGINE=InnoDB;
+
+CREATE TABLE t2 (
+ b int NOT NULL auto_increment PRIMARY KEY,
+ c datetime NOT NULL
+) ENGINE= MyISAM;
+
+INSERT INTO t2(c) VALUES ('2007-01-01');
+INSERT INTO t2(c) SELECT c FROM t2;
+INSERT INTO t2(c) SELECT c FROM t2;
+INSERT INTO t2(c) SELECT c FROM t2;
+INSERT INTO t2(c) SELECT c FROM t2;
+INSERT INTO t2(c) SELECT c FROM t2;
+INSERT INTO t2(c) SELECT c FROM t2;
+INSERT INTO t2(c) SELECT c FROM t2;
+INSERT INTO t2(c) SELECT c FROM t2;
+INSERT INTO t2(c) SELECT c FROM t2;
+INSERT INTO t2(c) SELECT c FROM t2;
+
+INSERT INTO t1(b,c) SELECT b,c FROM t2;
+UPDATE t2 SET c='2007-01-02';
+INSERT INTO t1(b,c) SELECT b,c FROM t2;
+UPDATE t2 SET c='2007-01-03';
+INSERT INTO t1(b,c) SELECT b,c FROM t2;
+
+set @@sort_buffer_size=8192;
+
+SELECT COUNT(*) FROM t1;
+
+--replace_column 9 #
+EXPLAIN
+SELECT COUNT(*) FROM t1
+ WHERE (c >= '2007-01-02' AND c <= '2007-01-03') OR b >= 1;
+SELECT COUNT(*) FROM t1
+ WHERE (c >= '2007-01-02' AND c <= '2007-01-03') OR b >= 1;
+
+--replace_column 9 #
+EXPLAIN
+SELECT COUNT(*) FROM t1 FORCE INDEX(idx_b, idx_c)
+ WHERE (c >= '2007-01-02' AND c <= '2007-01-03') OR b >= 1;
+SELECT COUNT(*) FROM t1 FORCE INDEX(idx_b, idx_c)
+ WHERE (c >= '2007-01-02' AND c <= '2007-01-03') OR b >= 1;
+
+set @@sort_buffer_size=default;
+
+DROP TABLE t1,t2;
--echo End of 5.0 tests
diff --git a/mysql-test/t/loaddata.test b/mysql-test/t/loaddata.test
index 2757a37b881..cdd3bb80b6e 100644
--- a/mysql-test/t/loaddata.test
+++ b/mysql-test/t/loaddata.test
@@ -67,6 +67,41 @@ load data infile '../std_data_ln/loaddata_dq.dat' into table t1 fields terminate
select * from t1;
drop table t1;
+#
+# Bug #29294 SELECT INTO OUTFILE/LOAD DATA INFILE with special
+# characters in the FIELDS ENCLOSED BY clause
+#
+
+CREATE TABLE t1 (
+ id INT AUTO_INCREMENT PRIMARY KEY,
+ c1 VARCHAR(255)
+);
+
+CREATE TABLE t2 (
+ id INT,
+ c2 VARCHAR(255)
+);
+
+INSERT INTO t1 (c1) VALUES
+ ('r'), ('rr'), ('rrr'), ('rrrr'),
+ ('.r'), ('.rr'), ('.rrr'), ('.rrrr'),
+ ('r.'), ('rr.'), ('rrr.'), ('rrrr.'),
+ ('.r.'), ('.rr.'), ('.rrr.'), ('.rrrr.');
+SELECT * FROM t1;
+
+--exec rm -f $MYSQLTEST_VARDIR/tmp/t1
+--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
+eval SELECT * INTO OUTFILE '$MYSQLTEST_VARDIR/tmp/t1' FIELDS ENCLOSED BY 'r' FROM t1;
+--exec cat $MYSQLTEST_VARDIR/tmp/t1
+
+--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
+eval LOAD DATA INFILE '$MYSQLTEST_VARDIR/tmp/t1' INTO TABLE t2 FIELDS ENCLOSED BY 'r';
+SELECT t1.id, c1, c2 FROM t1 LEFT JOIN t2 ON t1.id=t2.id WHERE c1 != c2;
+SELECT t1.id, c1, c2 FROM t1 RIGHT JOIN t2 ON t1.id=t2.id WHERE c1 != c2;
+
+--exec rm $MYSQLTEST_VARDIR/tmp/t1
+DROP TABLE t1,t2;
+
# End of 4.1 tests
#
View Lorry Controller Status