From 1e44259440f19d76c2422b64761530d57ec49a10 Mon Sep 17 00:00:00 2001 From: unknown Date: Thu, 13 Jul 2006 20:48:26 -0700 Subject: Fixed bug #19714. DESCRIBE returned the type BIGINT for a column of a view if the column was specified by an expression over values of the type INT. E.g. for the view defined as follows: CREATE VIEW v1 SELECT COALESCE(f1,f2) FROM t1 DESCRIBE returned type BIGINT for the only column of the view if f1,f2 are columns of the INT type. At the same time DESCRIBE returned type INT for the only column of the table defined by the statement: CREATE TABLE t2 SELECT COALESCE(f1,f2) FROM t1. This inconsistency was removed by the patch. Now the code chooses between INT/BIGINT depending on the precision of the aggregated column type. Thus both DESCRIBE commands above returns type INT for v1 and t2. mysql-test/r/analyse.result: Adjusted the results after having fixed bug #19714. mysql-test/r/bigint.result: Adjusted the results after having fixed bug #19714. mysql-test/r/create.result: Adjusted the results after having fixed bug #19714. mysql-test/r/olap.result: Adjusted the results after having fixed bug #19714. mysql-test/r/ps_2myisam.result: Adjusted the results after having fixed bug #19714. mysql-test/r/ps_3innodb.result: Adjusted the results after having fixed bug #19714. mysql-test/r/ps_4heap.result: Adjusted the results after having fixed bug #19714. mysql-test/r/ps_5merge.result: Adjusted the results after having fixed bug #19714. mysql-test/r/ps_6bdb.result: Adjusted the results after having fixed bug #19714. mysql-test/r/ps_7ndb.result: Adjusted the results after having fixed bug #19714. mysql-test/r/sp.result: Adjusted the results after having fixed bug #19714. mysql-test/r/subselect.result: Adjusted the results after having fixed bug #19714. mysql-test/r/type_ranges.result: Adjusted the results after having fixed bug #19714. mysql-test/r/view.result: Added a test case for bug #19714. mysql-test/t/view.test: Added a test case for bug #19714. --- mysql-test/r/sp.result | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) (limited to 'mysql-test/r/sp.result') diff --git a/mysql-test/r/sp.result b/mysql-test/r/sp.result index 96bf2f01f86..50913fb1b90 100644 --- a/mysql-test/r/sp.result +++ b/mysql-test/r/sp.result @@ -4921,7 +4921,7 @@ create table t3 as select * from v1| show create table t3| Table Create Table t3 CREATE TABLE `t3` ( - `j` bigint(11) default NULL + `j` int(11) default NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 select * from t3| j -- cgit v1.2.1 From 9a63adc8fd18489aa3a75c7715abaea0dcd16349 Mon Sep 17 00:00:00 2001 From: unknown Date: Wed, 26 Jul 2006 00:31:29 +0400 Subject: Fixed bug#19862: Sort with filesort by function evaluates function twice When there is no index defined filesort is used to sort the result of a query. If there is a function in the select list and the result set should be ordered by it's value then this function will be evaluated twice. First time to get the value of the sort key and second time to send its value to a user. This happens because filesort when sorts a table remembers only values of its fields but not values of functions. All functions are affected. But taking into account that SP and UDF functions can be both expensive and non-deterministic a temporary table should be used to store their results and then sort it to avoid twice SP evaluation and to get a correct result. If an expression referenced in an ORDER clause contains a SP or UDF function, force the use of a temporary table. A new Item_processor function called func_type_checker_processor is added to check whether the expression contains a function of a particular type. mysql-test/t/udf.test: Added test case for bug#19862: Sort with filesort by function evaluates function twice mysql-test/t/sp.test: Added test case for bug#19862: Sort with filesort by function evaluates function twice mysql-test/r/sp.result: Added test case for bug#19862: Sort with filesort by function evaluates function twice mysql-test/r/udf.result: Added test case for bug#19862: Sort with filesort by function evaluates function twice sql/sql_select.cc: Fixed bug#19862: Sort with filesort by function evaluates function twice If an expression referenced in an ORDER clause contains a SP or UDF function, force the use of a temporary table. sql/item_func.h: Fixed bug#19862: Sort with filesort by function evaluates function twice A new Item_processor function called func_type_checker_processor is added to check whether the expression contains a function of a particular type. sql/item.h: Fixed bug#19862: Sort with filesort by function evaluates function twice A new Item_processor function called func_type_checker_processor is added to check whether the expression contains a function of a particular type. sql/item_func.cc: Fixed bug#19862: Sort with filesort by function evaluates function twice A new Item_processor function called func_type_checker_processor is added to check whether the expression contains a function of a particular type. --- mysql-test/r/sp.result | 19 +++++++++++++++++++ 1 file changed, 19 insertions(+) (limited to 'mysql-test/r/sp.result') diff --git a/mysql-test/r/sp.result b/mysql-test/r/sp.result index 50913fb1b90..f04b5b2c635 100644 --- a/mysql-test/r/sp.result +++ b/mysql-test/r/sp.result @@ -5057,4 +5057,23 @@ concat('data was: /', var1, '/') data was: /1/ drop table t3| drop procedure bug15217| +drop procedure if exists bug19862| +CREATE TABLE t11 (a INT)| +CREATE TABLE t12 (a INT)| +CREATE FUNCTION bug19862(x INT) RETURNS INT +BEGIN +INSERT INTO t11 VALUES (x); +RETURN x+1; +END| +INSERT INTO t12 VALUES (1), (2)| +SELECT bug19862(a) FROM t12 ORDER BY 1| +bug19862(a) +2 +3 +SELECT * FROM t11| +a +1 +2 +DROP TABLE t11, t12| +DROP FUNCTION bug19862| drop table t1,t2; -- cgit v1.2.1