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()

4 files changed, 39 insertions, 64 deletions

diff --git a/mysql-test/r/subselect4.result b/mysql-test/r/subselect4.result
index e8822ebf6bd..ff822113338 100644
--- a/mysql-test/r/subselect4.result
+++ b/mysql-test/r/subselect4.result
@@ -1820,37 +1820,6 @@ AND t2.f2 = t1.f1;
 f1	f2	f1	f2
 drop table t1,t2,t3,t4;
 #
-# LP BUG#806943 Second crash with select_describe with nested subqueries in maria-5.3
-#
-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));
-id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
-2	SUBQUERY	t4	ALL	NULL	NULL	NULL	NULL	2	
-3	SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	2	
-SELECT *
-FROM t2, t3
-WHERE t3.f1 = (
-SELECT SUM( f2 )
-FROM t4
-WHERE EXISTS (
-SELECT DISTINCT f4
-FROM t1));
-f2	f1
-drop table t1, t2, t3, t4;
-#
 # LP BUG#611690 Crash in select_describe() with nested subqueries
 #
 CREATE TABLE t1 (
diff --git a/mysql-test/r/type_year.result b/mysql-test/r/type_year.result
index 5a484f3ce3c..20cab45c4f7 100644
--- a/mysql-test/r/type_year.result
+++ b/mysql-test/r/type_year.result
@@ -359,3 +359,25 @@ total_rows	min_value	MAX(c1+0)
 DROP TABLE t1;
 #
 End of 5.1 tests
+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;
+a
+00
+select a from t1 where a=1000+1000;
+a
+00
+select a from t1 where a=(select 2000);
+a
+00
+select a from t1 where a=(select 2000 from dual where 1);
+a
+00
+select a from t1 where a=y2k();
+a
+00
+select a from t1 where a=b;
+a
+drop table t1;
+drop function y2k;
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
@@ -1485,39 +1485,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;
+
+