summaryrefslogtreecommitdiff
path: root/mysql-test/t/select.test
diff options
context:
space:
mode:
Diffstat (limited to 'mysql-test/t/select.test')
-rw-r--r--mysql-test/t/select.test181
1 files changed, 178 insertions, 3 deletions
diff --git a/mysql-test/t/select.test b/mysql-test/t/select.test
index 54168332664..b036cc6bc01 100644
--- a/mysql-test/t/select.test
+++ b/mysql-test/t/select.test
@@ -1344,6 +1344,7 @@ explain select fld1 from t2 where fld1=250501 or fld1=250502 or fld1 >= 250505 a
# If the like starts with a certain letter key will be used.
#
+--sorted_result
select fld1,fld3 from t2 where companynr = 37 and fld3 like 'f%';
select fld3 from t2 where fld3 like "L%" and fld3 = "ok";
select fld3 from t2 where (fld3 like "C%" and fld3 = "Chantilly");
@@ -1865,7 +1866,9 @@ select * from t1 inner join (t1 as t2 left join t1 as t3 using (a)) on t1.a>1;
select * from (t1 as t2 left join t1 as t3 using (a)) inner join t1 using ( a );
select * from t1 inner join (t1 as t2 left join t1 as t3 using (a)) using ( a );
# left [outer] join on
+--sorted_result
select * from (t1 as t2 left join t1 as t3 using (a)) left outer join t1 on t1.a>1;
+--sorted_result
select * from t1 left outer join (t1 as t2 left join t1 as t3 using (a)) on t1.a>1;
# left join using
select * from (t1 as t2 left join t1 as t3 using (a)) left join t1 using ( a );
@@ -1874,7 +1877,9 @@ select * from t1 left join (t1 as t2 left join t1 as t3 using (a)) using ( a );
select * from (t1 as t2 left join t1 as t3 using (a)) natural left join t1;
select * from t1 natural left join (t1 as t2 left join t1 as t3 using (a));
# right join on
+--sorted_result
select * from (t1 as t2 left join t1 as t3 using (a)) right join t1 on t1.a>1;
+--sorted_result
select * from t1 right join (t1 as t2 left join t1 as t3 using (a)) on t1.a>1;
# right [outer] joing using
select * from (t1 as t2 left join t1 as t3 using (a)) right outer join t1 using ( a );
@@ -2001,7 +2006,7 @@ DROP TABLE t1;
#
create table t1 (a int(11) unsigned, b int(11) unsigned);
-insert into t1 values (1,0), (1,1), (1,2);
+insert into t1 values (1,0), (1,1), (18446744073709551615,0);
select a-b from t1 order by 1;
select a-b , (a-b < 0) from t1 order by 1;
select a-b as d, (a-b >= 0), b from t1 group by b having d >= 0;
@@ -2546,10 +2551,14 @@ insert into t1 values(1),(2);
insert into t2 values(1),(2);
create view v2 (c) as select a1 from t1;
+--sorted_result
select * from t1 natural left join t2;
+--sorted_result
select * from t1 natural right join t2;
+--sorted_result
select * from v2 natural left join t2;
+--sorted_result
select * from v2 natural right join t2;
drop table t1, t2;
@@ -2718,16 +2727,20 @@ create view v3 as select (t1.id+2) as id from t1 natural left join t2;
# all queries must return the same result
select t1.id from t1 left join v2 using (id);
select t1.id from v2 right join t1 using (id);
+--sorted_result
select t1.id from t1 left join v3 using (id);
select * from t1 left join v2 using (id);
select * from v2 right join t1 using (id);
+--sorted_result
select * from t1 left join v3 using (id);
select v1.id from v1 left join v2 using (id);
select v1.id from v2 right join v1 using (id);
+--sorted_result
select v1.id from v1 left join v3 using (id);
select * from v1 left join v2 using (id);
select * from v2 right join v1 using (id);
+--sorted_result
select * from v1 left join v3 using (id);
drop table t1, t2;
@@ -2922,6 +2935,7 @@ DROP TABLE t1,t2;
# cases to prevent fixing this accidently. It is intended behaviour)
#
+SET SQL_MODE='NO_UNSIGNED_SUBTRACTION';
CREATE TABLE t1 (i TINYINT UNSIGNED NOT NULL);
INSERT t1 SET i = 0;
UPDATE t1 SET i = -1;
@@ -2931,6 +2945,7 @@ SELECT * FROM t1;
UPDATE t1 SET i = i - 1;
SELECT * FROM t1;
DROP TABLE t1;
+SET SQL_MODE=default;
# BUG#17379
@@ -3782,7 +3797,7 @@ INSERT INTO t1 VALUES (2),(3);
--echo # Should not crash
--error ER_SUBQUERY_NO_1_ROW
SELECT 1 FROM t1 WHERE a <> 1 AND NOT
-ROW(a,a) <=> ROW((SELECT 1 FROM t1 WHERE 1=2),(SELECT 1 FROM t1))
+ROW(1,a) <=> ROW(1,(SELECT 1 FROM t1))
INTO @var0;
DROP TABLE t1;
@@ -4037,6 +4052,39 @@ SELECT table1 .`time_key` field2 FROM B table1 LEFT JOIN BB JOIN A table5 ON
drop table A,AA,B,BB;
--echo #end of test for bug#45266
+--echo #
+--echo # Bug#33546: Slowdown on re-evaluation of constant expressions.
+--echo #
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
+CREATE TABLE t2 (b INT);
+INSERT INTO t2 VALUES (2);
+SELECT * FROM t1 WHERE a = 1 + 1;
+EXPLAIN EXTENDED SELECT * FROM t1 WHERE a = 1 + 1;
+SELECT * FROM t1 HAVING a = 1 + 1;
+EXPLAIN EXTENDED SELECT * FROM t1 HAVING a = 1 + 1;
+SELECT * FROM t1, t2 WHERE a = b + (1 + 1);
+EXPLAIN EXTENDED SELECT * FROM t1, t2 WHERE a = b + (1 + 1);
+SELECT * FROM t2 LEFT JOIN t1 ON a = b + 1;
+EXPLAIN EXTENDED SELECT * FROM t2 LEFT JOIN t1 ON a = b + 1;
+EXPLAIN EXTENDED SELECT * FROM t1 WHERE a > UNIX_TIMESTAMP('2009-03-10 00:00:00');
+
+delimiter |;
+CREATE FUNCTION f1() RETURNS INT DETERMINISTIC
+BEGIN
+ SET @cnt := @cnt + 1;
+ RETURN 1;
+END;|
+delimiter ;|
+
+SET @cnt := 0;
+SELECT * FROM t1 WHERE a = f1();
+SELECT @cnt;
+EXPLAIN EXTENDED SELECT * FROM t1 WHERE a = f1();
+DROP TABLE t1, t2;
+DROP FUNCTION f1;
+--echo # End of bug#33546
+
--echo #
--echo # BUG#48052: Valgrind warning - uninitialized value in init_read_record()
--echo #
@@ -4243,9 +4291,79 @@ SELECT 1 FROM t1 GROUP BY 1;
drop table t1;
set sql_buffer_result= 0;
---echo End of 5.1 tests
--echo #
+--echo # Bug #58422: Incorrect result when OUTER JOIN'ing
+--echo # with an empty table
+--echo #
+
+CREATE TABLE t_empty(pk INT PRIMARY KEY, i INT) ENGINE = MYISAM;
+CREATE TABLE t1(pk INT PRIMARY KEY, i INT) ENGINE = MYISAM;
+INSERT INTO t1 VALUES (1,1), (2,2), (3,3);
+CREATE TABLE t2(pk INT PRIMARY KEY, i INT) ENGINE = MYISAM;
+INSERT INTO t2 VALUES (1,1), (2,2), (3,3);
+
+EXPLAIN
+SELECT *
+ FROM
+ t1
+ LEFT OUTER JOIN
+ (t2 INNER JOIN t_empty ON TRUE)
+ ON t1.pk=t2.pk
+ WHERE t2.pk <> 2;
+
+SELECT *
+ FROM
+ t1
+ LEFT OUTER JOIN
+ (t2 INNER JOIN t_empty ON TRUE)
+ ON t1.pk=t2.pk
+ WHERE t2.pk <> 2;
+
+
+EXPLAIN
+SELECT *
+ FROM
+ t1
+ LEFT OUTER JOIN
+ (t2 CROSS JOIN t_empty)
+ ON t1.pk=t2.pk
+ WHERE t2.pk <> 2;
+
+SELECT *
+ FROM
+ t1
+ LEFT OUTER JOIN
+ (t2 CROSS JOIN t_empty)
+ ON t1.pk=t2.pk
+ WHERE t2.pk <> 2;
+
+
+EXPLAIN
+SELECT *
+ FROM
+ t1
+ LEFT OUTER JOIN
+ (t2 INNER JOIN t_empty ON t_empty.i=t2.i)
+ ON t1.pk=t2.pk
+ WHERE t2.pk <> 2;
+
+SELECT *
+ FROM
+ t1
+ LEFT OUTER JOIN
+ (t2 INNER JOIN t_empty ON t_empty.i=t2.i)
+ ON t1.pk=t2.pk
+ WHERE t2.pk <> 2;
+
+
+
+DROP TABLE t1,t2,t_empty;
+
+
+--echo End of 5.1 tests
+
+--echo #
--echo # BUG#776274: substitution of a single row table
--echo #
@@ -4259,6 +4377,48 @@ SELECT * FROM t1 WHERE a = b;
DROP TABLE t1;
--echo #
+--echo # Bug#54515: Crash in opt_range.cc::get_best_group_min_max on
+--echo # SELECT from VIEW with GROUP BY
+--echo #
+
+CREATE TABLE t1 (
+ col_int_key int DEFAULT NULL,
+ KEY int_key (col_int_key)
+) ;
+
+INSERT INTO t1 VALUES (1),(2);
+
+CREATE VIEW view_t1 AS
+ SELECT t1.col_int_key AS col_int_key
+ FROM t1;
+
+SELECT col_int_key FROM view_t1 GROUP BY col_int_key;
+
+DROP VIEW view_t1;
+DROP TABLE t1;
+
+--echo # End of test BUG#54515
+
+--echo #
+--echo # Bug #57203 Assertion `field_length <= 255' failed.
+--echo #
+
+SELECT coalesce((avg(distinct (geomfromtext("point(25379 -22010)")))))
+UNION ALL
+SELECT coalesce((avg(distinct (geomfromtext("point(25379 -22010)")))))
+AS foo
+;
+
+CREATE table t1(a text);
+INSERT INTO t1 VALUES (''), ('');
+SELECT avg(distinct(t1.a)) FROM t1, t1 t2
+GROUP BY t2.a ORDER BY t1.a;
+
+DROP TABLE t1;
+
+--echo # End of test BUG#57203
+
+--echo #
--echo # lp:822760 Wrong result with view + invalid dates
--echo #
CREATE TABLE t1 (f1 date);
@@ -4272,6 +4432,20 @@ SELECT * FROM v1 HAVING f1 = 'zz' AND f1 <= 'aa' ;
DROP TABLE t1;
DROP VIEW v1;
+--echo #
+--echo # Bug#63020: Function "format"'s 'locale' argument is not considered
+--echo # when creating a "view'
+--echo #
+
+CREATE TABLE t1 (f1 DECIMAL(10,2));
+INSERT INTO t1 VALUES (11.67),(17865.3),(12345678.92);
+CREATE VIEW view_t1 AS SELECT FORMAT(f1,1,'sk_SK') AS f1 FROM t1;
+SHOW CREATE VIEW view_t1;
+SELECT * FROM view_t1;
+DROP TABLE t1;
+DROP VIEW view_t1;
+--echo # End of test BUG#63020
+
SET optimizer_switch=@save_optimizer_switch;
--echo #
@@ -4343,3 +4517,4 @@ SELECT * FROM t1, t2
DROP TABLE t1, t2;
--echo End of 5.3 tests
+
View Lorry Controller Status