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.test173
1 files changed, 160 insertions, 13 deletions
diff --git a/mysql-test/t/select.test b/mysql-test/t/select.test
index d2447dae193..e9ae69826e5 100644
--- a/mysql-test/t/select.test
+++ b/mysql-test/t/select.test
@@ -1333,6 +1333,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");
@@ -1854,7 +1855,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 );
@@ -1863,7 +1866,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 );
@@ -1989,7 +1994,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;
@@ -2534,10 +2539,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;
@@ -2706,16 +2715,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;
@@ -2910,6 +2923,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;
@@ -2919,6 +2933,7 @@ SELECT * FROM t1;
UPDATE t1 SET i = i - 1;
SELECT * FROM t1;
DROP TABLE t1;
+SET SQL_MODE=default;
# BUG#17379
@@ -3770,7 +3785,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;
@@ -4025,6 +4040,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 #
@@ -4214,9 +4262,79 @@ SELECT t2.f23 FROM
DROP TABLE t1,t2,t3,t4,t5,t6,t7;
---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 #
@@ -4230,15 +4348,44 @@ SELECT * FROM t1 WHERE a = b;
DROP TABLE t1;
--echo #
---echo # lp:822760 Wrong result with view + invalid dates
+--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 #
-CREATE TABLE t1 (f1 date);
-INSERT IGNORE INTO t1 VALUES ('0000-00-00');
-CREATE OR REPLACE VIEW v1 AS SELECT * FROM t1;
-SELECT * FROM t1 HAVING f1 = 'zz';
-SELECT * FROM t1 HAVING f1 <= 'aa' ;
-SELECT * FROM t1 HAVING f1 = 'zz' AND f1 <= 'aa' ;
-SELECT * FROM t1 WHERE f1 = 'zz' AND f1 <= 'aa' ;
-SELECT * FROM v1 HAVING f1 = 'zz' AND f1 <= 'aa' ;
+
+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;
-DROP VIEW v1;
+
+--echo # End of test BUG#57203
+
View Lorry Controller Status