mirror of
https://github.com/MariaDB/server.git
synced 2025-11-04 04:46:15 +01:00
674 lines
21 KiB
Text
674 lines
21 KiB
Text
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 'UDF_EXAMPLE_LIB'
|
|
CREATE FUNCTION myfunc_int RETURNS INTEGER SONAME "UDF_EXAMPLE_LIB";
|
|
CREATE FUNCTION udf_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";
|
|
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") rlike '^127\.\\d+\.\\d+.\\d+$';
|
|
lookup("localhost") rlike '^127\.\\d+\.\\d+.\\d+$'
|
|
1
|
|
select reverse_lookup();
|
|
ERROR HY000: Can't initialize function 'reverse_lookup'; Wrong number of arguments to reverse_lookup; Use the source
|
|
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 MariaDB 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 MariaDB 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 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 filtered Extra
|
|
1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00
|
|
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 filtered Extra
|
|
1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00
|
|
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: 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 MariaDB 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 filtered Extra
|
|
1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 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 filtered Extra
|
|
1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 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 filtered Extra
|
|
1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using temporary; Using filesort
|
|
Warnings:
|
|
Note 1003 select myfunc_int(`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 filtered Extra
|
|
1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 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.
|
|
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
|
|
DROP FUNCTION IF EXISTS metaphon;
|
|
CREATE FUNCTION metaphon(a int) RETURNS int
|
|
return 0;
|
|
CREATE FUNCTION metaphon RETURNS STRING SONAME "UDF_EXAMPLE_LIB";
|
|
DROP FUNCTION metaphon;
|
|
DROP FUNCTION metaphon;
|
|
CREATE FUNCTION metaphon RETURNS STRING SONAME "UDF_EXAMPLE_LIB";
|
|
CREATE FUNCTION metaphon(a int) RETURNS int
|
|
return 0;
|
|
ERROR HY000: Function 'metaphon' already exists
|
|
CREATE FUNCTION test.metaphon(a int) RETURNS int
|
|
return 0;
|
|
ERROR HY000: Function 'metaphon' already exists
|
|
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 udf_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 udf_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 udf_sequence() AS seq, a FROM t1 ORDER BY seq ASC;
|
|
seq a
|
|
1 4
|
|
2 3
|
|
3 2
|
|
4 1
|
|
SELECT udf_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 = udf_sequence();
|
|
a
|
|
SELECT * FROM t2 WHERE a = udf_sequence();
|
|
a
|
|
1
|
|
2
|
|
3
|
|
4
|
|
DROP FUNCTION udf_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 EXTENDED SELECT 1 FROM t1 WHERE f1=1 + myfunc_double(1);
|
|
id select_type table type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where
|
|
Warnings:
|
|
Note 1003 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.
|
|
#
|
|
# MDEV-10134 Add full support for DEFAULT
|
|
#
|
|
CREATE FUNCTION metaphon RETURNS STRING SONAME "UDF_EXAMPLE_LIB";
|
|
SELECT METAPHON('Hello');
|
|
METAPHON('Hello')
|
|
HL
|
|
CREATE TABLE t1 (a VARCHAR(10), b VARCHAR(10) DEFAULT METAPHON(a));
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`a` varchar(10) DEFAULT NULL,
|
|
`b` varchar(10) DEFAULT metaphon(`a` AS `a`)
|
|
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
|
|
INSERT INTO t1 (a) VALUES ('Hello');
|
|
SELECT * FROM t1;
|
|
a b
|
|
Hello HL
|
|
DROP FUNCTION METAPHON;
|
|
DROP TABLE t1;
|
|
#
|
|
# MDEV-15424: Unreasonal SQL Error (1356) on select from view
|
|
#
|
|
CREATE FUNCTION myfunc_int RETURNS INTEGER SONAME "UDF_EXAMPLE_LIB";
|
|
create table t1(a int , b int);
|
|
insert into t1 values(100, 54), (200, 199);
|
|
create view v1 as select myfunc_int(max(a) over (order by b) , b) from t1;
|
|
select * from v1;
|
|
myfunc_int(max(a) over (order by b) , b)
|
|
154
|
|
399
|
|
drop view v1;
|
|
drop function myfunc_int;
|
|
drop table t1;
|
|
#
|
|
# MDEV-23327: Can't uninstall UDF if the implementation library
|
|
# file doesn't exist
|
|
#
|
|
insert into mysql.func values ("unexisting_udf", 0, "soname", "function");
|
|
select * from mysql.func WHERE name='unexisting_udf';
|
|
name ret dl type
|
|
unexisting_udf 0 soname function
|
|
DROP FUNCTION unexisting_udf;
|
|
select * from mysql.plugin WHERE name='unexisting_udf';
|
|
name dl
|
|
DROP FUNCTION unexisting_udf;
|
|
ERROR 42000: FUNCTION test.unexisting_udf does not exist
|
|
#
|
|
# Bug #31674599: THE UDF_INIT() FUNCTION CAUSE SERVER CRASH
|
|
#
|
|
call mtr.add_suppression('Invalid row in mysql.func table');
|
|
insert mysql.func () values ();
|
|
# restart
|
|
delete from mysql.func where name = '';
|
|
#
|
|
# End of 10.2 tests
|
|
#
|
|
#
|
|
# MDEV-15073: Generic UDAF parser code in server for window functions
|
|
#
|
|
CREATE AGGREGATE FUNCTION avgcost
|
|
RETURNS REAL SONAME "UDF_EXAMPLE_LIB";
|
|
CREATE AGGREGATE FUNCTION avg2
|
|
RETURNS REAL SONAME "UDF_EXAMPLE_LIB";
|
|
CREATE FUNCTION myfunc_double RETURNS REAL SONAME "UDF_EXAMPLE_LIB";
|
|
create table t1(pk int primary key,
|
|
a int,
|
|
sum int,
|
|
price float(24));
|
|
insert into t1 values
|
|
(1, 1, 100, 50.00),
|
|
(2, 1, 100, 100.00),
|
|
(3, 1, 100, 50.00),
|
|
(4, 1, 100, 50.00),
|
|
(5, 1, 100, 50.00),
|
|
(6, 1, 100, NULL),
|
|
(7, 1, NULL, NULL),
|
|
(8, 2, 2, 2),
|
|
(9, 2, 4, 4);
|
|
select pk, a, sum, price, avgcost(sum, price) over (partition by a order by pk ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)
|
|
from t1;
|
|
pk a sum price avgcost(sum, price) over (partition by a order by pk ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)
|
|
1 1 100 50 75.0000
|
|
2 1 100 100 66.6667
|
|
3 1 100 50 66.6667
|
|
4 1 100 50 50.0000
|
|
5 1 100 50 50.0000
|
|
6 1 100 NULL 50.0000
|
|
7 1 NULL NULL 0.0000
|
|
8 2 2 2 3.3333
|
|
9 2 4 4 3.3333
|
|
select pk, a, sum, price, avgcost(sum, price) over (partition by a order by pk ROWS BETWEEN 1 PRECEDING AND 0 FOLLOWING)
|
|
from t1;
|
|
pk a sum price avgcost(sum, price) over (partition by a order by pk ROWS BETWEEN 1 PRECEDING AND 0 FOLLOWING)
|
|
1 1 100 50 50.0000
|
|
2 1 100 100 75.0000
|
|
3 1 100 50 75.0000
|
|
4 1 100 50 50.0000
|
|
5 1 100 50 50.0000
|
|
6 1 100 NULL 50.0000
|
|
7 1 NULL NULL 0.0000
|
|
8 2 2 2 2.0000
|
|
9 2 4 4 3.3333
|
|
select pk, a, sum, price, avg2(sum, price) over (partition by a order by pk ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)
|
|
from t1;
|
|
pk a sum price avg2(sum, price) over (partition by a order by pk ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)
|
|
1 1 100 50 0.7500
|
|
2 1 100 100 0.6667
|
|
3 1 100 50 0.6667
|
|
4 1 100 50 0.5000
|
|
5 1 100 50 0.5000
|
|
6 1 100 NULL 0.5000
|
|
7 1 NULL NULL 0.0000
|
|
8 2 2 2 1.0000
|
|
9 2 4 4 1.0000
|
|
select pk, a, sum, price, avg2(sum, price) over (partition by a order by pk ROWS BETWEEN 1 PRECEDING AND 0 FOLLOWING)
|
|
from t1;
|
|
pk a sum price avg2(sum, price) over (partition by a order by pk ROWS BETWEEN 1 PRECEDING AND 0 FOLLOWING)
|
|
1 1 100 50 0.5000
|
|
2 1 100 100 0.7500
|
|
3 1 100 50 0.7500
|
|
4 1 100 50 0.5000
|
|
5 1 100 50 0.5000
|
|
6 1 100 NULL 0.5000
|
|
7 1 NULL NULL 0.0000
|
|
8 2 2 2 1.0000
|
|
9 2 4 4 1.0000
|
|
select pk, a, sum, price, tttttttt(sprice,sum) over (partition by a order by pk ROWS BETWEEN 1 PRECEDING AND 0 FOLLOWING)
|
|
from t1;
|
|
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'over (partition by a order by pk ROWS BETWEEN 1 PRECEDING AND 0 FOLLOWING)
|
|
fr...' at line 1
|
|
select pk, a, sum, price, myfunc_double(sum) over (partition by a order by pk ROWS BETWEEN 1 PRECEDING AND 0 FOLLOWING)
|
|
from t1;
|
|
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'over (partition by a order by pk ROWS BETWEEN 1 PRECEDING AND 0 FOLLOWING)
|
|
fr...' at line 1
|
|
select pk, a, sum, price, round(sprice,sum) over (partition by a order by pk ROWS BETWEEN 1 PRECEDING AND 0 FOLLOWING)
|
|
from t1;
|
|
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'over (partition by a order by pk ROWS BETWEEN 1 PRECEDING AND 0 FOLLOWING)
|
|
fr...' at line 1
|
|
select pk, a, sum, price, myfunc_double(sum) over (partition by a order by pk ROWS BETWEEN 1 PRECEDING AND 0 FOLLOWING)
|
|
from t1;
|
|
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'over (partition by a order by pk ROWS BETWEEN 1 PRECEDING AND 0 FOLLOWING)
|
|
fr...' at line 1
|
|
set @save_sql_mode = @@sql_mode;
|
|
set sql_mode="oracle";
|
|
select pk, a, sum, price, avg2(sum, price) over (partition by a order by pk ROWS BETWEEN 1 PRECEDING AND 0 FOLLOWING)
|
|
from t1;
|
|
pk a sum price avg2(sum, price) over (partition by a order by pk ROWS BETWEEN 1 PRECEDING AND 0 FOLLOWING)
|
|
1 1 100 50 0.5000
|
|
2 1 100 100 0.7500
|
|
3 1 100 50 0.7500
|
|
4 1 100 50 0.5000
|
|
5 1 100 50 0.5000
|
|
6 1 100 NULL 0.5000
|
|
7 1 NULL NULL 0.0000
|
|
8 2 2 2 1.0000
|
|
9 2 4 4 1.0000
|
|
set sql_mode= @save_sql_mode;
|
|
drop table t1;
|
|
DROP FUNCTION avgcost;
|
|
DROP FUNCTION avg2;
|
|
DROP FUNCTION myfunc_double;
|
|
#
|
|
# MDEV-24507: Server Crash using UDF in WHERE clause of VIEW
|
|
#
|
|
CREATE FUNCTION myfunc_int RETURNS INTEGER SONAME "UDF_EXAMPLE_LIB";
|
|
create table t1(pk int primary key, a varchar(20));
|
|
create table t2(pk int primary key, a varchar(20));
|
|
create view v1 as select pk, a from t1 union select pk, a from t2;
|
|
insert into t1 values (1, "One"), (3, "Three"), (5, "Five");
|
|
insert into t2 values (2, "Dos"), (4, "Quatro"), (6, "Seis");
|
|
select pk, myfunc_int(a) from t1;
|
|
pk myfunc_int(a)
|
|
1 3
|
|
3 5
|
|
5 4
|
|
select pk, myfunc_int(a) from t2;
|
|
pk myfunc_int(a)
|
|
2 3
|
|
4 6
|
|
6 4
|
|
select pk, myfunc_int(a) from v1;
|
|
pk myfunc_int(a)
|
|
1 3
|
|
3 5
|
|
5 4
|
|
2 3
|
|
4 6
|
|
6 4
|
|
select pk from t1 where myfunc_int(a) > 4;
|
|
pk
|
|
3
|
|
select pk from (select pk, a from t1) A where myfunc_int(A.a) > 4;
|
|
pk
|
|
3
|
|
set @save_optimizer_switch = @@optimizer_switch;
|
|
set optimizer_switch = 'derived_merge=OFF';
|
|
select pk, myfunc_int(a) from t1;
|
|
pk myfunc_int(a)
|
|
1 3
|
|
3 5
|
|
5 4
|
|
select pk, myfunc_int(a) from t2;
|
|
pk myfunc_int(a)
|
|
2 3
|
|
4 6
|
|
6 4
|
|
select pk, myfunc_int(a) from v1;
|
|
pk myfunc_int(a)
|
|
1 3
|
|
3 5
|
|
5 4
|
|
2 3
|
|
4 6
|
|
6 4
|
|
select pk from t1 where myfunc_int(a) > 4;
|
|
pk
|
|
3
|
|
select pk from (select pk, a from t1) A where myfunc_int(A.a) > 4;
|
|
pk
|
|
3
|
|
set optimizer_switch = @save_optimizer_switch;
|
|
drop view v1;
|
|
drop table t2;
|
|
drop table t1;
|
|
drop function myfunc_int;
|
|
# End of 10.4 tests
|