From 54f9fe6c8b0472f0e12602eabf94ff4d73161806 Mon Sep 17 00:00:00 2001
From: Sergei Golubchik <sergii@pisem.net>
Date: Tue, 28 Feb 2012 15:41:55 +0100
Subject: lp:938977 - Query performance with join/index super slow on MariaDB
 5.3.4RC

make sure that stored routines are evaluated (that is, de facto - cached) in convert_const_to_int().
revert the fix for lp:806943 because it cannot be repeated anymore.
add few tests for convert_const_to_int()
---
 mysql-test/t/subselect4.test | 33 ---------------------------------
 mysql-test/t/type_year.test  | 17 +++++++++++++++++
 2 files changed, 17 insertions(+), 33 deletions(-)

(limited to 'mysql-test/t')

diff --git a/mysql-test/t/subselect4.test b/mysql-test/t/subselect4.test
index eee0140815b..371ee016fb8 100644
--- a/mysql-test/t/subselect4.test
+++ b/mysql-test/t/subselect4.test
@@ -1484,39 +1484,6 @@ WHERE t2.f2 = (SELECT f2 FROM t3
 
 drop table t1,t2,t3,t4;
 
---echo #
---echo # LP BUG#806943 Second crash with select_describe with nested subqueries in maria-5.3
---echo #
-
-CREATE TABLE t1 ( f4 int) ;
-INSERT INTO t1 VALUES (0),(0);
-
-CREATE TABLE t2 ( f2 int) ;
-
-CREATE TABLE t3 ( f1 int NOT NULL );
-
-CREATE TABLE t4 ( f2 int, f3 int) ;
-INSERT INTO t4 VALUES (8,0),(3,0);
-
-EXPLAIN SELECT *
-FROM t2, t3
-WHERE t3.f1 = (
-        SELECT SUM( f2 )
-        FROM t4
-        WHERE EXISTS (
-                SELECT DISTINCT f4
-                FROM t1));
-SELECT *
-FROM t2, t3
-WHERE t3.f1 = (
-        SELECT SUM( f2 )
-        FROM t4
-        WHERE EXISTS (
-                SELECT DISTINCT f4
-                FROM t1));
-
-drop table t1, t2, t3, t4;
-
 --echo #
 --echo # LP BUG#611690 Crash in select_describe() with nested subqueries
 --echo #
diff --git a/mysql-test/t/type_year.test b/mysql-test/t/type_year.test
index de38306e88f..53cfd3a7c30 100644
--- a/mysql-test/t/type_year.test
+++ b/mysql-test/t/type_year.test
@@ -163,3 +163,20 @@ DROP TABLE t1;
 --echo #
 
 --echo End of 5.1 tests
+#
+# fun with convert_const_to_int
+# in some cases 00 is equal to 2000, in others it is not.
+#
+create function y2k() returns int deterministic return 2000;
+create table t1 (a year(2), b int);
+insert t1 values (0,2000);
+select a from t1 where a=2000;  # constant.
+select a from t1 where a=1000+1000; # still a constant.
+select a from t1 where a=(select 2000); # even this is a constant
+select a from t1 where a=(select 2000 from dual where 1); # constant, but "expensive"
+select a from t1 where a=y2k(); # constant, but "expensive"
+select a from t1 where a=b; # not a constant
+drop table t1;
+drop function y2k;
+
+
-- 
cgit v1.2.1