drop table if exists t1;
CREATE FUNCTION metaphon RETURNS STRING SONAME "UDF_EXAMPLE_LIB";
CREATE FUNCTION myfunc_double RETURNS REAL SONAME "UDF_EXAMPLE_LIB";
CREATE FUNCTION myfunc_nonexist RETURNS INTEGER SONAME "UDF_EXAMPLE_LIB";
ERROR HY000: Can't find symbol 'myfunc_nonexist' in library
CREATE FUNCTION myfunc_int RETURNS INTEGER SONAME "UDF_EXAMPLE_LIB";
CREATE FUNCTION sequence RETURNS INTEGER SONAME "UDF_EXAMPLE_LIB";
CREATE FUNCTION lookup RETURNS STRING SONAME "UDF_EXAMPLE_LIB";
CREATE FUNCTION reverse_lookup
RETURNS STRING SONAME "UDF_EXAMPLE_LIB";
CREATE AGGREGATE FUNCTION avgcost
RETURNS REAL SONAME "UDF_EXAMPLE_LIB";
# Should get 7 rows 1 aggregate
SELECT * FROM performance_schema.user_defined_functions
WHERE udf_library='UDF_EXAMPLE_LIB'
  ORDER BY UDF_NAME;
UDF_NAME	UDF_RETURN_TYPE	UDF_TYPE	UDF_LIBRARY	UDF_USAGE_COUNT
avgcost	double	aggregate	UDF_EXAMPLE_LIB	1
lookup	char	function	UDF_EXAMPLE_LIB	1
metaphon	char	function	UDF_EXAMPLE_LIB	1
myfunc_double	double	function	UDF_EXAMPLE_LIB	1
myfunc_int	integer	function	UDF_EXAMPLE_LIB	1
reverse_lookup	char	function	UDF_EXAMPLE_LIB	1
sequence	integer	function	UDF_EXAMPLE_LIB	1
# Should get an index lookup
EXPLAIN
SELECT udf_type FROM performance_schema.user_defined_functions
WHERE udf_name = 'metaphon';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	user_defined_functions	NULL	const	PRIMARY	PRIMARY	258	const	1	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select 'function' AS `udf_type` from `performance_schema`.`user_defined_functions` where true
select myfunc_double();
ERROR HY000: Can't initialize function 'myfunc_double'; myfunc_double must have at least one argument
select myfunc_double(1);
myfunc_double(1)
49.00
select myfunc_double(78654);
myfunc_double(78654)
54.00
select myfunc_nonexist();
ERROR 42000: FUNCTION test.myfunc_nonexist does not exist
select myfunc_int();
myfunc_int()
0
select lookup();
ERROR HY000: Can't initialize function 'lookup'; Wrong arguments to lookup;  Use the source
select lookup("127.0.0.1");
lookup("127.0.0.1")
127.0.0.1
select lookup(127,0,0,1);
ERROR HY000: Can't initialize function 'lookup'; Wrong arguments to lookup;  Use the source
select lookup("localhost");
lookup("localhost")
127.0.0.1
select reverse_lookup();
ERROR HY000: Can't initialize function 'reverse_lookup'; Wrong number of arguments to reverse_lookup;  Use the source
prepare s from "select myfunc_double(?)";
set @one = 1;
execute s using @one;
myfunc_double(?)
49.00
deallocate prepare s;
select reverse_lookup("127.0.0.1");
select reverse_lookup(127,0,0,1);
select reverse_lookup("localhost");
select avgcost();
ERROR HY000: Can't initialize function 'avgcost'; wrong number of arguments: AVGCOST() requires two arguments
select avgcost(100,23.76);
ERROR HY000: Can't initialize function 'avgcost'; wrong argument type: AVGCOST() requires an INT and a REAL
create table t1(sum int, price float(24));
insert into t1 values(100, 50.00), (100, 100.00);
select avgcost(sum, price) from t1;
avgcost(sum, price)
75.0000
delete from t1;
insert into t1 values(100, 54.33), (200, 199.99);
select avgcost(sum, price) from t1;
avgcost(sum, price)
151.4367
drop table t1;
select metaphon('hello');
metaphon('hello')
HL
CREATE PROCEDURE `XXX1`(in testval varchar(10))
begin
select metaphon(testval);
end//
call XXX1('hello');
metaphon(testval)
HL
drop procedure xxx1;
CREATE PROCEDURE `XXX2`()
begin
declare testval varchar(10);
set testval = 'hello';
select metaphon(testval);
end//
call XXX2();
metaphon(testval)
HL
drop procedure xxx2;
CREATE TABLE bug19904(n INT, v varchar(10));
INSERT INTO bug19904 VALUES (1,'one'),(2,'two'),(NULL,NULL),(3,'three'),(4,'four');
SELECT myfunc_double(n) AS f FROM bug19904;
f
49.00
50.00
NULL
51.00
52.00
SELECT metaphon(v) AS f FROM bug19904;
f
ON
TW
NULL
0R
FR
DROP TABLE bug19904;
CREATE DEFINER=CURRENT_USER() FUNCTION should_not_parse
RETURNS STRING SONAME "should_not_parse.so";
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 'RETURNS STRING SONAME "should_not_parse.so"' at line 2
CREATE DEFINER=someone@somewhere FUNCTION should_not_parse
RETURNS STRING SONAME "should_not_parse.so";
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 'RETURNS STRING SONAME "should_not_parse.so"' at line 2
create table t1(f1 int);
insert into t1 values(1),(2);
explain select myfunc_int(f1) from t1 order by 1;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	ALL	NULL	NULL	NULL	NULL	2	100.00	Using temporary; Using filesort
Warnings:
Note	1003	/* select#1 */ select myfunc_int(`test`.`t1`.`f1` AS `f1`) AS `myfunc_int(f1)` from `test`.`t1` order by myfunc_int(`test`.`t1`.`f1` AS `f1`)
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 SELECT myfunc_int(a AS attr_name) FROM t1;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	ALL	NULL	NULL	NULL	NULL	2	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select myfunc_int(`test`.`t1`.`a` AS `attr_name`) AS `myfunc_int(a AS attr_name)` from `test`.`t1`
EXPLAIN SELECT myfunc_int(a) FROM t1;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	ALL	NULL	NULL	NULL	NULL	2	100.00	NULL
Warnings:
Note	1003	/* select#1 */ 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: Incorrect parameters in the call to stored function `fn`
SELECT myfunc_int(fn(MIN(b) xx)) as c FROM t1 GROUP BY a;
ERROR 42000: Incorrect parameters in the call to stored function `fn`
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 SELECT myfunc_int(MIN(b) xx) as c FROM t1 GROUP BY a;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	ALL	NULL	NULL	NULL	NULL	2	100.00	Using temporary
Warnings:
Note	1003	/* select#1 */ select myfunc_int(min(`test`.`t1`.`b`) AS `xx`) AS `c` from `test`.`t1` group by `test`.`t1`.`a`
EXPLAIN SELECT test.fn(MIN(b)) as c FROM t1 GROUP BY a;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	ALL	NULL	NULL	NULL	NULL	2	100.00	Using temporary
Warnings:
Note	1003	/* select#1 */ select `test`.`fn`(min(`test`.`t1`.`b`)) AS `c` from `test`.`t1` group by `test`.`t1`.`a`
EXPLAIN SELECT myfunc_int(fn(MIN(b))) as c FROM t1 GROUP BY a;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	ALL	NULL	NULL	NULL	NULL	2	100.00	Using temporary
Warnings:
Note	1003	/* select#1 */ select myfunc_int(`fn`(min(`test`.`t1`.`b`)) AS `fn(MIN(b))`) AS `c` from `test`.`t1` group by `test`.`t1`.`a`
EXPLAIN SELECT myfunc_int(test.fn(MIN(b))) as c FROM t1 GROUP BY a;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	ALL	NULL	NULL	NULL	NULL	2	100.00	Using temporary
Warnings:
Note	1003	/* select#1 */ 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.
select myfunc_double(3);
myfunc_double(3)
51.00
select myfunc_double(3 AS three);
myfunc_double(3 AS three)
51.00
select myfunc_double(abs(3));
myfunc_double(abs(3))
51.00
select myfunc_double(abs(3) AS named_param);
myfunc_double(abs(3) AS named_param)
51.00
select abs(myfunc_double(3));
abs(myfunc_double(3))
51.00
select abs(myfunc_double(3 AS three));
abs(myfunc_double(3 AS three))
51.00
select myfunc_double(abs(3 AS wrong));
ERROR 42000: Incorrect parameters in the call to native function 'abs'
select abs(myfunc_double(3) AS wrong);
ERROR 42000: Incorrect parameters in the call to native function 'abs'
drop function if exists pi;
CREATE FUNCTION pi RETURNS STRING SONAME "should_not_parse.so";
ERROR HY000: This function 'pi' has the same name as a native function

# Bug#33301931 LOADABLE AND STORED FUNCTIONS SHARE NAMESPACE INCONSISTENTLY

# Verify that UDF and Stored function can be created with the same name.

# UDF can be created with the same name as SF. UDF eclipses the stored function when used without schema qualifier
DROP FUNCTION IF EXISTS metaphon;
CREATE FUNCTION metaphon(a int) RETURNS int return 0;
CREATE FUNCTION metaphon RETURNS STRING SONAME "UDF_EXAMPLE_LIB";
SELECT metaphon('a');
metaphon('a')
A
SELECT test.metaphon(0);
test.metaphon(0)
0
DROP FUNCTION test.metaphon;

# SF can be created with the same name as UDF. UDF eclipses the stored function when used without schema qualifier
DROP FUNCTION IF EXISTS metaphon;
CREATE FUNCTION metaphon RETURNS STRING SONAME "UDF_EXAMPLE_LIB";
CREATE FUNCTION metaphon(a int) RETURNS int return 0;
Warnings:
Note	4084	This function 'metaphon' has the same name as a loadable function (UDF). To invoke the stored function, it is necessary to qualify it with the schema name.
SELECT metaphon('a');
metaphon('a')
A
SELECT test.metaphon(0);
test.metaphon(0)
0
DROP FUNCTION test.metaphon;
create table t1(sum int, price float(24));
select distinct avgcost(sum,price) from t1 order by avgcost(sum,price);
avgcost(sum,price)
NULL
drop table t1;
myfunc_int(20)
20
myfunc_double(20)
49.00
DROP FUNCTION metaphon;
DROP FUNCTION myfunc_double;
DROP FUNCTION myfunc_nonexist;
ERROR 42000: FUNCTION test.myfunc_nonexist does not exist
DROP FUNCTION myfunc_int;
DROP FUNCTION sequence;
DROP FUNCTION lookup;
DROP FUNCTION reverse_lookup;
DROP FUNCTION avgcost;
select * from mysql.func;
name	ret	dl	type
CREATE FUNCTION is_const RETURNS STRING SONAME "UDF_EXAMPLE_LIB";
select IS_const(3);
IS_const(3)
const
drop function IS_const;
select * from mysql.func;
name	ret	dl	type
select is_const(3);
ERROR 42000: FUNCTION test.is_const does not exist
CREATE FUNCTION is_const RETURNS STRING SONAME "UDF_EXAMPLE_LIB";
select
is_const(3) as const,
is_const(3.14) as const,
is_const('fnord') as const,
is_const(2+3) as const,
is_const(rand()) as 'nc rand()',
is_const(sin(3.14)) as const,
is_const(upper('test')) as const;
const	const	const	const	nc rand()	const	const
const	const	const	const	not const	const	const
create table bug18761 (n int);
insert into bug18761 values (null),(2);
select
is_const(3) as const,
is_const(3.14) as const,
is_const('fnord') as const,
is_const(2+3) as const,
is_const(2+n) as 'nc  2+n  ',
is_const(sin(n)) as 'nc sin(n)',
is_const(sin(3.14)) as const,
is_const(upper('test')) as const,
is_const(rand()) as 'nc rand()',
is_const(n) as 'nc   n   ',
is_const(is_const(n)) as 'nc ic?(n)',
is_const(is_const('c')) as const
from
bug18761;
const	const	const	const	nc  2+n  	nc sin(n)	const	const	nc rand()	nc   n   	nc ic?(n)	const
const	const	const	const	not const	not const	const	const	not const	not const	not const	const
const	const	const	const	not const	not const	const	const	not const	not const	not const	const
drop table bug18761;
select is_const((1,2,3));
ERROR 21000: Operand should contain 1 column(s)
drop function if exists is_const;
CREATE FUNCTION metaphon RETURNS STRING SONAME "UDF_EXAMPLE_LIB";
CREATE FUNCTION myfunc_double RETURNS REAL SONAME "UDF_EXAMPLE_LIB";
CREATE FUNCTION myfunc_int RETURNS INTEGER SONAME "UDF_EXAMPLE_LIB";
create function f1(p1 varchar(255))
returns varchar(255)
begin
return metaphon(p1);
end//
create function f2(p1 varchar(255))
returns double
begin
return myfunc_double(p1);
end//
create function f3(p1 varchar(255))
returns double
begin
return myfunc_int(p1);
end//
select f3(NULL);
f3(NULL)
0
select f2(NULL);
f2(NULL)
NULL
select f1(NULL);
f1(NULL)
NULL
drop function f1;
drop function f2;
drop function f3;
drop function metaphon;
drop function myfunc_double;
drop function myfunc_int;
DROP DATABASE IF EXISTS mysqltest;
CREATE DATABASE mysqltest;
USE mysqltest;
DROP DATABASE mysqltest;
CREATE FUNCTION metaphon RETURNS STRING SONAME "UDF_EXAMPLE_LIB";
DROP FUNCTION metaphon;
USE test;
CREATE TABLE const_len_bug (
str_const varchar(4000),
result1 varchar(4000),
result2 varchar(4000)
);
CREATE TRIGGER check_const_len_trigger BEFORE INSERT ON const_len_bug FOR EACH ROW BEGIN
set NEW.str_const = 'bar';
set NEW.result2 = check_const_len(NEW.str_const);
END |
CREATE PROCEDURE check_const_len_sp (IN str_const VARCHAR(4000))
BEGIN
DECLARE result VARCHAR(4000);
SET result = check_const_len(str_const);
insert into const_len_bug values(str_const, result, "");
END |
CREATE FUNCTION check_const_len RETURNS string SONAME "UDF_EXAMPLE_LIB";
CALL check_const_len_sp("foo");
SELECT * from const_len_bug;
str_const	result1	result2
bar	Correct length	Correct length
DROP FUNCTION check_const_len;
DROP PROCEDURE check_const_len_sp;
DROP TRIGGER check_const_len_trigger;
DROP TABLE const_len_bug;
CREATE FUNCTION sequence RETURNS INTEGER SONAME "UDF_EXAMPLE_LIB";
CREATE TABLE t1 (a INT);
CREATE TABLE t2 (a INT PRIMARY KEY);
INSERT INTO t1 VALUES (4),(3),(2),(1);
INSERT INTO t2 SELECT * FROM t1;
SELECT sequence() AS seq, a FROM t1 ORDER BY seq ASC;
seq	a
1	4
2	3
3	2
4	1
SELECT sequence() AS seq, a FROM t1 ORDER BY seq DESC;
seq	a
4	1
3	2
2	3
1	4
SELECT * FROM t1 WHERE a = sequence();
a
SELECT * FROM t2 WHERE a = sequence();
a
1
2
3
4
DROP FUNCTION sequence;
DROP TABLE t1,t2;
drop function if exists test.metaphon;
drop function if exists metaphon;
CREATE FUNCTION metaphon RETURNS STRING SONAME "UDF_EXAMPLE_LIB";
select metaphon("Hello");
metaphon("Hello")
HL
drop function if exists test.metaphon;
Warnings:
Note	1305	FUNCTION test.metaphon does not exist
select metaphon("Hello");
metaphon("Hello")
HL
drop function metaphon;
CREATE FUNCTION test.metaphon(a TEXT) RETURNS TEXT return "This is a SF";
create database db_31767;
use db_31767;
CREATE FUNCTION metaphon RETURNS STRING SONAME "UDF_EXAMPLE_LIB";
use test;
select metaphon("Hello");
metaphon("Hello")
HL
select test.metaphon("Hello");
test.metaphon("Hello")
This is a SF
drop function metaphon;
select metaphon("Hello");
metaphon("Hello")
This is a SF
drop function metaphon;
use db_31767;
drop database db_31767;
drop function if exists no_such_func;
Warnings:
Note	1305	FUNCTION (UDF) no_such_func does not exist
drop function no_such_func;
ERROR 42000: FUNCTION (UDF) no_such_func does not exist
drop function if exists test.no_such_func;
Warnings:
Note	1305	FUNCTION test.no_such_func does not exist
drop function test.no_such_func;
ERROR 42000: FUNCTION test.no_such_func does not exist
drop procedure if exists no_such_proc;
ERROR 3D000: No database selected
drop procedure no_such_proc;
ERROR 3D000: No database selected
use test;
#
# Bug#46259: 5.0.83 -> 5.1.36, query doesn't work
#
CREATE TABLE t1 ( a INT );
INSERT INTO t1 VALUES (1), (2), (3);
SELECT IF( a = 1, a, a ) AS `b` FROM t1 ORDER BY field( `b` + 1, 1 );
b
1
2
3
SELECT IF( a = 1, a, a ) AS `b` FROM t1 ORDER BY field( `b`, 1 );
b
2
3
1
DROP TABLE t1;
End of 5.0 tests.
#
# Bug#33546: Slowdown on re-evaluation of constant expressions.
#
CREATE TABLE t1 (f1 INT);
INSERT INTO t1 VALUES(1),(50);
CREATE FUNCTION myfunc_double RETURNS INTEGER SONAME "UDF_EXAMPLE_LIB";
EXPLAIN SELECT 1 FROM t1 WHERE f1=1 + myfunc_double(1);
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	ALL	NULL	NULL	NULL	NULL	2	50.00	Using where
Warnings:
Note	1003	/* select#1 */ select 1 AS `1` from `test`.`t1` where (`test`.`t1`.`f1` = <cache>((1 + myfunc_double(1 AS `1`))))
DROP FUNCTION myfunc_double;
DROP TABLE t1;
#
End of 5.1 tests.
#
# Bug#53322 deadlock with FLUSH TABLES WITH READ LOCK and DROP FUNCTION
#
CREATE FUNCTION metaphon RETURNS STRING SONAME "UDF_EXAMPLE_LIB";
CREATE FUNCTION reverse_lookup RETURNS STRING SONAME "UDF_EXAMPLE_LIB";
# Connection con1
FLUSH TABLES WITH READ LOCK;
# Connection default
# Sending:
DROP FUNCTION metaphon;
# Connection con1
# Wait until DROP FUNCTION is blocked by GRL
SELECT metaphon("foo");
metaphon("foo")
F
UNLOCK TABLES;
# Connection default
# Reaping: DROP FUNCTION metaphon
# Connection con1
FLUSH TABLES WITH READ LOCK;
# Connection default
# Sending:
CREATE FUNCTION metaphon RETURNS STRING SONAME "UDF_EXAMPLE_LIB";
# Connection con1
# Wait until CREATE FUNCTION is blocked by GRL
SELECT reverse_lookup("127.0.0.1");
UNLOCK TABLES;
# Connection default
# Reaping: CREATE FUNCTION metaphon ...
DROP FUNCTION metaphon;
DROP FUNCTION reverse_lookup;
#
# WL#5825 Using C++ Standard Library with MySQL code
#
CREATE AGGREGATE FUNCTION my_median
RETURNS INTEGER SONAME "UDF_EXAMPLE_LIB";
SELECT my_median(1);
my_median(1)
1
CREATE TABLE t1(a INT, b INT);
SELECT a, my_median(b) from t1 group by a;
a	my_median(b)
INSERT INTO t1 values
(1, 1), (1, 2), (1, 200),
(2, 1), (2, 200), (2, 200), (2, 1000),
(3, 1), (3, 1), (3, 100), (3, 100), (3, 42)
;
SELECT a, my_median(b) from t1 GROUP BY a;
a	my_median(b)
1	2
2	200
3	42
DROP TABLE t1;
# Bug#32528898: Calling UDF aggregate function corrupts plugin with JOIN
CREATE TABLE users(id INTEGER, name VARCHAR(255));
INSERT INTO users(id, name) VALUES (1, 'Jason'), (2, 'Brian');
CREATE TABLE user_values(id INTEGER, user_id INTEGER, value INTEGER);
INSERT INTO user_values(id, user_id, value) VALUES (1,1,1), (2,2,10);
SELECT my_median(value) FROM user_values;
my_median(value)
10
SELECT my_median(value) FROM user_values;
my_median(value)
10
SELECT user_id, my_median(value) FROM user_values GROUP BY user_id;
user_id	my_median(value)
1	1
2	10
SELECT user_id, my_median(value) FROM user_values GROUP BY user_id;
user_id	my_median(value)
1	1
2	10
SELECT users.id, my_median(value)
FROM users,user_values
WHERE users.id = user_values.user_id
GROUP BY users.id;
id	my_median(value)
1	1
2	10
SELECT users.id, my_median(value)
FROM users,user_values
WHERE users.id = user_values.user_id
GROUP BY users.id;
id	my_median(value)
1	1
2	10
prepare ps from "SELECT users.id, my_median(value)
FROM users,user_values
WHERE users.id = user_values.user_id
GROUP BY users.id";
execute ps;
id	my_median(value)
1	1
2	10
execute ps;
id	my_median(value)
1	1
2	10
DEALLOCATE PREPARE ps;
DROP TABLE users, user_values;
DROP FUNCTION my_median;
#
# WL8980 - Move udf metatable table from MyISAM to InnoDB.
#
SET autocommit= 0;
SET innodb_lock_wait_timeout= 1;

# Connection: con1

#######################################################
# A.1. Checking lock-wait-timeout with CREATE USER
#######################################################
START TRANSACTION;
INSERT INTO mysql.func(name, ret, dl, type) VALUES ('metaphon', 0, 'UDF_EXAMPLE_LIB', 'function');

# Connection: con1
CREATE FUNCTION metaphon RETURNS STRING SONAME "UDF_EXAMPLE_LIB";;

# Connection: default
ROLLBACK;

# Connection: con1

# Connection: default
DROP FUNCTION metaphon;

###############################################################
# A.2. Checking lock-wait-timeout with DROP FUNCTION.
###############################################################
CREATE FUNCTION metaphon RETURNS STRING SONAME "UDF_EXAMPLE_LIB";
START TRANSACTION;
SELECT name FROM mysql.func WHERE name ='metaphon' FOR UPDATE;
name
metaphon

# Connection: con1
DROP FUNCTION metaphon;

# Connection: default
ROLLBACK;

# Connection: con1

# Connection: default
SELECT name FROM mysql.func WHERE name = 'metaphon';
name

##############################################################
# B.1 Check that CREATE FUNCTION does implicit commit.
##############################################################
CREATE TABLE t1(a INT);
START TRANSACTION;
INSERT INTO t1 VALUES(1),(2),(3);
CREATE FUNCTION metaphon RETURNS STRING SONAME "UDF_EXAMPLE_LIB";

# Connection: con1
SELECT * FROM t1;
a
1
2
3
SELECT * FROM mysql.func;
name	ret	dl	type
metaphon	0	UDF_EXAMPLE_LIB	function

# Connection: default
ROLLBACK;
SELECT * FROM mysql.func;
name	ret	dl	type
metaphon	0	UDF_EXAMPLE_LIB	function
DROP FUNCTION metaphon;
DROP TABLE t1;
#
# Bug #27270498: DROPPING A UDF FUNCTION DOES NOT REMOVE ENTRY FROM PFS TABLE
#
CREATE FUNCTION is_const RETURNS STRING SONAME "UDF_EXAMPLE_LIB";
SELECT is_const((1,2,3));
ERROR 21000: Operand should contain 1 column(s)
DROP FUNCTION  is_const;
# Should return 0 rows.
SELECT * FROM performance_schema.user_defined_functions
WHERE udf_name = 'is_const';
UDF_NAME	UDF_RETURN_TYPE	UDF_TYPE	UDF_LIBRARY	UDF_USAGE_COUNT
#
# BUG#29264874: ASAN ERROR WHEN ACCESSING INFORMATION SCHEMA TABLES FROM
#               AGGREGATE UDF
#
CREATE AGGREGATE FUNCTION avgcost RETURNS REAL
SONAME "UDF_EXAMPLE_LIB";
CREATE TABLE t1(fld1 INT);
SELECT avgcost(T.TABLE_ROWS, 0e0)
FROM INFORMATION_SCHEMA.TABLES T,
INFORMATION_SCHEMA.COLUMNS C
WHERE C.TABLE_SCHEMA = T.TABLE_SCHEMA AND
C.TABLE_NAME = T.TABLE_NAME
AND C.TABLE_SCHEMA = DATABASE();
avgcost(T.TABLE_ROWS, 0e0)
NULL
DROP TABLE t1;
DROP FUNCTION avgcost;
#
# WL#8084: Condition pushdown to materialized derived table
#
CREATE AGGREGATE FUNCTION my_median RETURNS INTEGER
SONAME "UDF_EXAMPLE_LIB";
CREATE TABLE t1(f1 INTEGER, f2 INTEGER);
INSERT INTO t1 VALUES (1,100),(1,2),(2,100),(2,3);
EXPLAIN FORMAT=tree SELECT * FROM (SELECT f1, my_median(f2) as median
FROM t1 GROUP BY f1) as dt
WHERE median != 1;
EXPLAIN
-> Table scan on dt  (...)
    -> Materialize  (...)
        -> Filter: (my_median(t1.f2) <> 1)  (...)
            -> Group aggregate: my_median(t1.f2)  (...)
                -> Sort: t1.f1  (...)
                    -> Table scan on t1  (...)

SELECT * FROM (SELECT f1, my_median(f2) as median
FROM t1 GROUP BY f1) as dt
WHERE median != 1;
f1	median
1	100
2	100
DROP TABLE t1;
DROP FUNCTION my_median;
CREATE FUNCTION sequence RETURNS INTEGER SONAME "UDF_EXAMPLE_LIB";
CREATE TABLE t1 (a INT);
INSERT INTO t1 VALUES (1),(2),(3),(4);
SELECT a FROM t1 WHERE a = sequence();
a
1
2
3
4
SELECT a FROM (SELECT sequence() AS seq, a FROM t1) AS dt WHERE a = seq;
a
1
2
3
4
DROP FUNCTION sequence;
DROP TABLE t1;
# End of the 8.0 tests