From 932d86bbb93328d5916b2a9e5b1b788ddf820742 Mon Sep 17 00:00:00 2001 From: "gkodinov/kgeorge@macbook.gmz" <> Date: Tue, 24 Oct 2006 15:26:41 +0300 Subject: Bug #21809: Error 1356 while selecting from view with grouping though underlying select OK. The SQL parser was using Item::name to transfer user defined function attributes to the user defined function (udf). It was not distinguishing between user defined function call arguments and stored procedure call arguments. Setting Item::name was causing Item_ref::print() method to print the argument as quoted identifiers and caused views that reference aggregate functions as udf call arguments (and rely on Item::print() for the text of the view to store) to throw an undefined identifier error. Overloaded Item_ref::print to print aggregate functions as such when printing the references to aggregate functions taken out of context by split_sum_func2() Fixed the parser to properly detect using AS clause in stored procedure arguments as an error. Fixed printing the arguments of udf call to print properly the udf attribute. --- mysql-test/r/udf.result | 79 +++++++++++++++++++++++++++++++++++++++++++++++++ mysql-test/t/udf.test | 44 +++++++++++++++++++++++++++ 2 files changed, 123 insertions(+) (limited to 'mysql-test') diff --git a/mysql-test/r/udf.result b/mysql-test/r/udf.result index 8e37cca6aa9..396f1efa1b7 100644 --- a/mysql-test/r/udf.result +++ b/mysql-test/r/udf.result @@ -105,6 +105,85 @@ explain select myfunc_int(f1) from t1 order by 1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using temporary; Using filesort drop table t1; +CREATE TABLE t1(a INT, b INT); +INSERT INTO t1 values (1,1),(2,2); +CREATE FUNCTION fn(a int) RETURNS int DETERMINISTIC +BEGIN +RETURN a; +END +|| +CREATE VIEW v1 AS SELECT a, fn(MIN(b)) as c FROM t1 GROUP BY a; +SELECT myfunc_int(a AS attr_name) FROM t1; +myfunc_int(a AS attr_name) +1 +2 +EXPLAIN EXTENDED SELECT myfunc_int(a AS attr_name) FROM t1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 +Warnings: +Note 1003 select myfunc_int(`test`.`t1`.`a` AS `attr_name`) AS `myfunc_int(a AS attr_name)` from `test`.`t1` +EXPLAIN EXTENDED SELECT myfunc_int(a) FROM t1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 +Warnings: +Note 1003 select myfunc_int(`test`.`t1`.`a` AS `a`) AS `myfunc_int(a)` from `test`.`t1` +SELECT a,c FROM v1; +a c +1 1 +2 2 +SELECT a, fn(MIN(b) xx) as c FROM t1 GROUP BY a; +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'xx) as c FROM t1 GROUP BY a' at line 1 +SELECT myfunc_int(fn(MIN(b) xx)) as c FROM t1 GROUP BY a; +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'xx)) as c FROM t1 GROUP BY a' at line 1 +SELECT myfunc_int(test.fn(MIN(b) xx)) as c FROM t1 GROUP BY a; +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'xx)) as c FROM t1 GROUP BY a' at line 1 +SELECT myfunc_int(fn(MIN(b)) xx) as c FROM t1 GROUP BY a; +c +1 +2 +SELECT myfunc_int(test.fn(MIN(b)) xx) as c FROM t1 GROUP BY a; +c +1 +2 +EXPLAIN EXTENDED SELECT myfunc_int(MIN(b) xx) as c FROM t1 GROUP BY a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using temporary; Using filesort +Warnings: +Note 1003 select myfunc_int(min(`test`.`t1`.`b`) AS `xx`) AS `c` from `test`.`t1` group by `test`.`t1`.`a` +EXPLAIN EXTENDED SELECT test.fn(MIN(b)) as c FROM t1 GROUP BY a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using temporary; Using filesort +Warnings: +Note 1003 select `test`.`fn`(min(`test`.`t1`.`b`)) AS `c` from `test`.`t1` group by `test`.`t1`.`a` +EXPLAIN EXTENDED SELECT myfunc_int(fn(MIN(b))) as c FROM t1 GROUP BY a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using temporary; Using filesort +Warnings: +Note 1003 select myfunc_int(`test`.`fn`(min(`test`.`t1`.`b`)) AS `fn(MIN(b))`) AS `c` from `test`.`t1` group by `test`.`t1`.`a` +EXPLAIN EXTENDED SELECT myfunc_int(test.fn(MIN(b))) as c FROM t1 GROUP BY a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using temporary; Using filesort +Warnings: +Note 1003 select myfunc_int(`test`.`fn`(min(`test`.`t1`.`b`)) AS `test.fn(MIN(b))`) AS `c` from `test`.`t1` group by `test`.`t1`.`a` +SELECT myfunc_int(MIN(b) xx) as c FROM t1 GROUP BY a; +c +1 +2 +SELECT test.fn(MIN(b)) as c FROM t1 GROUP BY a; +c +1 +2 +SELECT myfunc_int(fn(MIN(b))) as c FROM t1 GROUP BY a; +c +1 +2 +SELECT myfunc_int(test.fn(MIN(b))) as c FROM t1 GROUP BY a; +c +1 +2 +DROP VIEW v1; +DROP TABLE t1; +DROP FUNCTION fn; End of 5.0 tests. DROP FUNCTION metaphon; DROP FUNCTION myfunc_double; diff --git a/mysql-test/t/udf.test b/mysql-test/t/udf.test index 96e559f5c05..37358a292be 100644 --- a/mysql-test/t/udf.test +++ b/mysql-test/t/udf.test @@ -127,6 +127,50 @@ create table t1(f1 int); insert into t1 values(1),(2); explain select myfunc_int(f1) from t1 order by 1; drop table t1; + +# +# Bug #21809: Error 1356 while selecting from view with grouping though +# underlying select OK. +# +CREATE TABLE t1(a INT, b INT); INSERT INTO t1 values (1,1),(2,2); + +DELIMITER ||; +CREATE FUNCTION fn(a int) RETURNS int DETERMINISTIC +BEGIN + RETURN a; +END +|| +DELIMITER ;|| + +CREATE VIEW v1 AS SELECT a, fn(MIN(b)) as c FROM t1 GROUP BY a; + +SELECT myfunc_int(a AS attr_name) FROM t1; +EXPLAIN EXTENDED SELECT myfunc_int(a AS attr_name) FROM t1; +EXPLAIN EXTENDED SELECT myfunc_int(a) FROM t1; +SELECT a,c FROM v1; + +--error ER_PARSE_ERROR +SELECT a, fn(MIN(b) xx) as c FROM t1 GROUP BY a; +--error ER_PARSE_ERROR +SELECT myfunc_int(fn(MIN(b) xx)) as c FROM t1 GROUP BY a; +--error ER_PARSE_ERROR +SELECT myfunc_int(test.fn(MIN(b) xx)) as c FROM t1 GROUP BY a; + +SELECT myfunc_int(fn(MIN(b)) xx) as c FROM t1 GROUP BY a; +SELECT myfunc_int(test.fn(MIN(b)) xx) as c FROM t1 GROUP BY a; + +EXPLAIN EXTENDED SELECT myfunc_int(MIN(b) xx) as c FROM t1 GROUP BY a; +EXPLAIN EXTENDED SELECT test.fn(MIN(b)) as c FROM t1 GROUP BY a; +EXPLAIN EXTENDED SELECT myfunc_int(fn(MIN(b))) as c FROM t1 GROUP BY a; +EXPLAIN EXTENDED SELECT myfunc_int(test.fn(MIN(b))) as c FROM t1 GROUP BY a; +SELECT myfunc_int(MIN(b) xx) as c FROM t1 GROUP BY a; +SELECT test.fn(MIN(b)) as c FROM t1 GROUP BY a; +SELECT myfunc_int(fn(MIN(b))) as c FROM t1 GROUP BY a; +SELECT myfunc_int(test.fn(MIN(b))) as c FROM t1 GROUP BY a; +DROP VIEW v1; +DROP TABLE t1; +DROP FUNCTION fn; + --echo End of 5.0 tests. # -- cgit v1.2.1 From 6cd1f7b2e507b4b108086d09d9d64a17db4d7f3e Mon Sep 17 00:00:00 2001 From: "gkodinov/kgeorge@macbook.gmz" <> Date: Tue, 31 Oct 2006 11:01:27 +0200 Subject: Bug #23184: SELECT causes server crash Item::val_xxx() may be called by the server several times at execute time for a single query. Calls to val_xxx() may be very expensive and sometimes (count(distinct), sum(distinct), avg(distinct)) not possible. To avoid that problem the results of calculation for these aggregate functions are cached so that val_xxx() methods just return the calculated value for the second and subsequent calls. --- mysql-test/r/func_group.result | 26 ++++++++++++++++++++++++++ mysql-test/t/func_group.test | 25 +++++++++++++++++++++++++ 2 files changed, 51 insertions(+) (limited to 'mysql-test') diff --git a/mysql-test/r/func_group.result b/mysql-test/r/func_group.result index c6117053a60..23517f7b603 100644 --- a/mysql-test/r/func_group.result +++ b/mysql-test/r/func_group.result @@ -1029,3 +1029,29 @@ t1 CREATE TABLE `t1` ( `stddev(0)` double(8,4) default NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; +CREATE TABLE t1 (a INT, b INT); +INSERT INTO t1 VALUES (1,1),(1,2),(1,3),(1,4),(1,5),(1,6),(1,7),(1,8); +INSERT INTO t1 SELECT a, b+8 FROM t1; +INSERT INTO t1 SELECT a, b+16 FROM t1; +INSERT INTO t1 SELECT a, b+32 FROM t1; +INSERT INTO t1 SELECT a, b+64 FROM t1; +INSERT INTO t1 SELECT a, b+128 FROM t1; +INSERT INTO t1 SELECT a, b+256 FROM t1; +INSERT INTO t1 SELECT a, b+512 FROM t1; +INSERT INTO t1 SELECT a, b+1024 FROM t1; +INSERT INTO t1 SELECT a, b+2048 FROM t1; +INSERT INTO t1 SELECT a, b+4096 FROM t1; +INSERT INTO t1 SELECT a, b+8192 FROM t1; +INSERT INTO t1 SELECT a, b+16384 FROM t1; +INSERT INTO t1 SELECT a, b+32768 FROM t1; +SELECT a,COUNT(DISTINCT b) AS cnt FROM t1 GROUP BY a HAVING cnt > 50; +a cnt +1 65536 +SELECT a,SUM(DISTINCT b) AS sumation FROM t1 GROUP BY a HAVING sumation > 50; +a sumation +1 2147516416 +SELECT a,AVG(DISTINCT b) AS average FROM t1 GROUP BY a HAVING average > 50; +a average +1 32768.5000 +DROP TABLE t1; +End of 5.0 tests diff --git a/mysql-test/t/func_group.test b/mysql-test/t/func_group.test index 079d107fad8..089f5ed9911 100644 --- a/mysql-test/t/func_group.test +++ b/mysql-test/t/func_group.test @@ -700,3 +700,28 @@ create table t1 select stddev(0); show create table t1; drop table t1; +# +# Bug #23184: SELECT causes server crash +# +CREATE TABLE t1 (a INT, b INT); +INSERT INTO t1 VALUES (1,1),(1,2),(1,3),(1,4),(1,5),(1,6),(1,7),(1,8); +INSERT INTO t1 SELECT a, b+8 FROM t1; +INSERT INTO t1 SELECT a, b+16 FROM t1; +INSERT INTO t1 SELECT a, b+32 FROM t1; +INSERT INTO t1 SELECT a, b+64 FROM t1; +INSERT INTO t1 SELECT a, b+128 FROM t1; +INSERT INTO t1 SELECT a, b+256 FROM t1; +INSERT INTO t1 SELECT a, b+512 FROM t1; +INSERT INTO t1 SELECT a, b+1024 FROM t1; +INSERT INTO t1 SELECT a, b+2048 FROM t1; +INSERT INTO t1 SELECT a, b+4096 FROM t1; +INSERT INTO t1 SELECT a, b+8192 FROM t1; +INSERT INTO t1 SELECT a, b+16384 FROM t1; +INSERT INTO t1 SELECT a, b+32768 FROM t1; +SELECT a,COUNT(DISTINCT b) AS cnt FROM t1 GROUP BY a HAVING cnt > 50; +SELECT a,SUM(DISTINCT b) AS sumation FROM t1 GROUP BY a HAVING sumation > 50; +SELECT a,AVG(DISTINCT b) AS average FROM t1 GROUP BY a HAVING average > 50; + +DROP TABLE t1; + +--echo End of 5.0 tests -- cgit v1.2.1