summaryrefslogtreecommitdiff
path: root/mysql-test
diff options
context:
space:
mode:
Diffstat (limited to 'mysql-test')
-rw-r--r--mysql-test/r/alter_table.result8
-rw-r--r--mysql-test/r/order_by.result22
-rw-r--r--mysql-test/r/select.result38
-rw-r--r--mysql-test/r/subselect.result13
-rw-r--r--mysql-test/t/order_by.test15
-rw-r--r--mysql-test/t/select.test47
-rw-r--r--mysql-test/t/subselect.test12
7 files changed, 151 insertions, 4 deletions
diff --git a/mysql-test/r/alter_table.result b/mysql-test/r/alter_table.result
index fa18b58a927..f2317961e99 100644
--- a/mysql-test/r/alter_table.result
+++ b/mysql-test/r/alter_table.result
@@ -886,7 +886,7 @@ ALTER TABLE t1 ADD b GEOMETRY NOT NULL, ADD SPATIAL INDEX(b);
SHOW CREATE TABLE t1;
Table Create Table
t1 CREATE TABLE `t1` (
- `a` varchar(500) DEFAULT NULL,
+ `a` varchar(500) default NULL,
`b` geometry NOT NULL,
SPATIAL KEY `b` (`b`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
@@ -894,7 +894,7 @@ ALTER TABLE t1 ADD KEY(b(50));
SHOW CREATE TABLE t1;
Table Create Table
t1 CREATE TABLE `t1` (
- `a` varchar(500) DEFAULT NULL,
+ `a` varchar(500) default NULL,
`b` geometry NOT NULL,
SPATIAL KEY `b` (`b`),
KEY `b_2` (`b`(50))
@@ -903,9 +903,9 @@ ALTER TABLE t1 ADD c POINT;
SHOW CREATE TABLE t1;
Table Create Table
t1 CREATE TABLE `t1` (
- `a` varchar(500) DEFAULT NULL,
+ `a` varchar(500) default NULL,
`b` geometry NOT NULL,
- `c` point DEFAULT NULL,
+ `c` point default NULL,
SPATIAL KEY `b` (`b`),
KEY `b_2` (`b`(50))
) ENGINE=MyISAM DEFAULT CHARSET=latin1
diff --git a/mysql-test/r/order_by.result b/mysql-test/r/order_by.result
index 8a19cfcded2..c7adfb784de 100644
--- a/mysql-test/r/order_by.result
+++ b/mysql-test/r/order_by.result
@@ -958,6 +958,28 @@ a ratio
19 1.3333
9 2.6667
drop table t1;
+CREATE TABLE t1 (a INT UNSIGNED NOT NULL, b TIME);
+INSERT INTO t1 (a) VALUES (100000), (0), (100), (1000000),(10000), (1000), (10);
+UPDATE t1 SET b = SEC_TO_TIME(a);
+SELECT a, b FROM t1 ORDER BY b DESC;
+a b
+1000000 277:46:40
+100000 27:46:40
+10000 02:46:40
+1000 00:16:40
+100 00:01:40
+10 00:00:10
+0 00:00:00
+SELECT a, b FROM t1 ORDER BY SEC_TO_TIME(a) DESC;
+a b
+1000000 277:46:40
+100000 27:46:40
+10000 02:46:40
+1000 00:16:40
+100 00:01:40
+10 00:00:10
+0 00:00:00
+DROP TABLE t1;
CREATE TABLE t1 (a INT, b INT, PRIMARY KEY (a), UNIQUE KEY b (b));
INSERT INTO t1 VALUES (1,1),(2,2);
CREATE TABLE t2 (a INT, b INT, KEY a (a,b));
diff --git a/mysql-test/r/select.result b/mysql-test/r/select.result
index e9fda0c2ad6..f88f046f0ac 100644
--- a/mysql-test/r/select.result
+++ b/mysql-test/r/select.result
@@ -3933,4 +3933,42 @@ cc cc 7
aa aa 2
aa aa 2
DROP TABLE t1,t2;
+CREATE TABLE t1 (
+access_id int NOT NULL default '0',
+name varchar(20) default NULL,
+rank int NOT NULL default '0',
+KEY idx (access_id)
+);
+CREATE TABLE t2 (
+faq_group_id int NOT NULL default '0',
+faq_id int NOT NULL default '0',
+access_id int default NULL,
+UNIQUE KEY idx1 (faq_id),
+KEY idx2 (faq_group_id,faq_id)
+);
+INSERT INTO t1 VALUES
+(1,'Everyone',2),(2,'Help',3),(3,'Technical Support',1),(4,'Chat User',4);
+INSERT INTO t2 VALUES
+(261,265,1),(490,494,1);
+SELECT t2.faq_id
+FROM t1 INNER JOIN t2 IGNORE INDEX (idx1)
+ON (t1.access_id = t2.access_id)
+LEFT JOIN t2 t
+ON (t.faq_group_id = t2.faq_group_id AND
+find_in_set(t.access_id, '1,4') < find_in_set(t2.access_id, '1,4'))
+WHERE
+t2.access_id IN (1,4) AND t.access_id IS NULL AND t2.faq_id in (265);
+faq_id
+265
+SELECT t2.faq_id
+FROM t1 INNER JOIN t2
+ON (t1.access_id = t2.access_id)
+LEFT JOIN t2 t
+ON (t.faq_group_id = t2.faq_group_id AND
+find_in_set(t.access_id, '1,4') < find_in_set(t2.access_id, '1,4'))
+WHERE
+t2.access_id IN (1,4) AND t.access_id IS NULL AND t2.faq_id in (265);
+faq_id
+265
+DROP TABLE t1,t2;
End of 5.0 tests
diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result
index 2afdd045203..1372ac3687c 100644
--- a/mysql-test/r/subselect.result
+++ b/mysql-test/r/subselect.result
@@ -3873,3 +3873,16 @@ id_1
DROP TABLE t1;
DROP TABLE t2;
DROP TABLE t1xt2;
+CREATE TABLE t1 (a int);
+INSERT INTO t1 VALUES (3), (1), (2);
+SELECT 'this is ' 'a test.' AS col1, a AS col2 FROM t1;
+col1 col2
+this is a test. 3
+this is a test. 1
+this is a test. 2
+SELECT * FROM (SELECT 'this is ' 'a test.' AS col1, a AS t2 FROM t1) t;
+col1 t2
+this is a test. 3
+this is a test. 1
+this is a test. 2
+DROP table t1;
diff --git a/mysql-test/t/order_by.test b/mysql-test/t/order_by.test
index c9ae0964e17..5093263b864 100644
--- a/mysql-test/t/order_by.test
+++ b/mysql-test/t/order_by.test
@@ -679,6 +679,21 @@ select a,(sum(b)/sum(c)) as ratio from t1 group by a order by sum(b)/sum(c) asc;
drop table t1;
#
+# Bug#26672: Incorrect SEC_TO_TIME() casting in ORDER BY
+#
+CREATE TABLE t1 (a INT UNSIGNED NOT NULL, b TIME);
+INSERT INTO t1 (a) VALUES (100000), (0), (100), (1000000),(10000), (1000), (10);
+UPDATE t1 SET b = SEC_TO_TIME(a);
+
+-- Correct ORDER
+SELECT a, b FROM t1 ORDER BY b DESC;
+
+-- must be ordered as the above
+SELECT a, b FROM t1 ORDER BY SEC_TO_TIME(a) DESC;
+
+DROP TABLE t1;
+
+#
# BUG#16590: Optimized does not do right "const" table pre-read
#
CREATE TABLE t1 (a INT, b INT, PRIMARY KEY (a), UNIQUE KEY b (b));
diff --git a/mysql-test/t/select.test b/mysql-test/t/select.test
index f7f458e277c..f613b836350 100644
--- a/mysql-test/t/select.test
+++ b/mysql-test/t/select.test
@@ -3299,4 +3299,51 @@ SELECT * FROM t1 LEFT JOIN t2 ON t1.name=t2.name;
DROP TABLE t1,t2;
+
+#
+# Bug #26963: join with predicates that contain fields from equalities evaluated
+# to constants after constant table substitution
+#
+
+CREATE TABLE t1 (
+ access_id int NOT NULL default '0',
+ name varchar(20) default NULL,
+ rank int NOT NULL default '0',
+ KEY idx (access_id)
+);
+
+CREATE TABLE t2 (
+ faq_group_id int NOT NULL default '0',
+ faq_id int NOT NULL default '0',
+ access_id int default NULL,
+ UNIQUE KEY idx1 (faq_id),
+ KEY idx2 (faq_group_id,faq_id)
+);
+
+INSERT INTO t1 VALUES
+ (1,'Everyone',2),(2,'Help',3),(3,'Technical Support',1),(4,'Chat User',4);
+INSERT INTO t2 VALUES
+ (261,265,1),(490,494,1);
+
+
+SELECT t2.faq_id
+ FROM t1 INNER JOIN t2 IGNORE INDEX (idx1)
+ ON (t1.access_id = t2.access_id)
+ LEFT JOIN t2 t
+ ON (t.faq_group_id = t2.faq_group_id AND
+ find_in_set(t.access_id, '1,4') < find_in_set(t2.access_id, '1,4'))
+ WHERE
+ t2.access_id IN (1,4) AND t.access_id IS NULL AND t2.faq_id in (265);
+
+SELECT t2.faq_id
+ FROM t1 INNER JOIN t2
+ ON (t1.access_id = t2.access_id)
+ LEFT JOIN t2 t
+ ON (t.faq_group_id = t2.faq_group_id AND
+ find_in_set(t.access_id, '1,4') < find_in_set(t2.access_id, '1,4'))
+ WHERE
+ t2.access_id IN (1,4) AND t.access_id IS NULL AND t2.faq_id in (265);
+
+DROP TABLE t1,t2;
+
--echo End of 5.0 tests
diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test
index 8ca99130921..9c2248cbcc2 100644
--- a/mysql-test/t/subselect.test
+++ b/mysql-test/t/subselect.test
@@ -2736,3 +2736,15 @@ DROP TABLE t1;
DROP TABLE t2;
DROP TABLE t1xt2;
+#
+# Bug #26728: derived table with concatanation of literals in select list
+#
+
+CREATE TABLE t1 (a int);
+INSERT INTO t1 VALUES (3), (1), (2);
+
+SELECT 'this is ' 'a test.' AS col1, a AS col2 FROM t1;
+SELECT * FROM (SELECT 'this is ' 'a test.' AS col1, a AS t2 FROM t1) t;
+
+DROP table t1;
+
View Lorry Controller Status