diff options
Diffstat (limited to 'mysql-test/main/udf.result')
| -rw-r--r-- | mysql-test/main/udf.result | 610 |
1 files changed, 610 insertions, 0 deletions
diff --git a/mysql-test/main/udf.result b/mysql-test/main/udf.result new file mode 100644 index 00000000..8dc24a8d --- /dev/null +++ b/mysql-test/main/udf.result @@ -0,0 +1,610 @@ +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 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=latin1 COLLATE=latin1_swedish_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; +# End of 10.4 tests |