1drop table if exists t1; 2CREATE FUNCTION metaphon RETURNS STRING SONAME "UDF_EXAMPLE_LIB"; 3CREATE FUNCTION myfunc_double RETURNS REAL SONAME "UDF_EXAMPLE_LIB"; 4CREATE FUNCTION myfunc_nonexist RETURNS INTEGER SONAME "UDF_EXAMPLE_LIB"; 5ERROR HY000: Can't find symbol 'myfunc_nonexist' in library 6CREATE FUNCTION myfunc_int RETURNS INTEGER SONAME "UDF_EXAMPLE_LIB"; 7CREATE FUNCTION udf_sequence RETURNS INTEGER SONAME "UDF_EXAMPLE_LIB"; 8CREATE FUNCTION lookup RETURNS STRING SONAME "UDF_EXAMPLE_LIB"; 9CREATE FUNCTION reverse_lookup 10RETURNS STRING SONAME "UDF_EXAMPLE_LIB"; 11CREATE AGGREGATE FUNCTION avgcost 12RETURNS REAL SONAME "UDF_EXAMPLE_LIB"; 13select myfunc_double(); 14ERROR HY000: Can't initialize function 'myfunc_double'; myfunc_double must have at least one argument 15select myfunc_double(1); 16myfunc_double(1) 1749.00 18select myfunc_double(78654); 19myfunc_double(78654) 2054.00 21select myfunc_nonexist(); 22ERROR 42000: FUNCTION test.myfunc_nonexist does not exist 23select myfunc_int(); 24myfunc_int() 250 26select lookup(); 27ERROR HY000: Can't initialize function 'lookup'; Wrong arguments to lookup; Use the source 28select lookup("127.0.0.1"); 29lookup("127.0.0.1") 30127.0.0.1 31select lookup(127,0,0,1); 32ERROR HY000: Can't initialize function 'lookup'; Wrong arguments to lookup; Use the source 33select lookup("localhost") rlike '^127\.\\d+\.\\d+.\\d+$'; 34lookup("localhost") rlike '^127\.\\d+\.\\d+.\\d+$' 351 36select reverse_lookup(); 37ERROR HY000: Can't initialize function 'reverse_lookup'; Wrong number of arguments to reverse_lookup; Use the source 38select reverse_lookup("127.0.0.1"); 39select reverse_lookup(127,0,0,1); 40select reverse_lookup("localhost"); 41select avgcost(); 42ERROR HY000: Can't initialize function 'avgcost'; wrong number of arguments: AVGCOST() requires two arguments 43select avgcost(100,23.76); 44ERROR HY000: Can't initialize function 'avgcost'; wrong argument type: AVGCOST() requires an INT and a REAL 45create table t1(sum int, price float(24)); 46insert into t1 values(100, 50.00), (100, 100.00); 47select avgcost(sum, price) from t1; 48avgcost(sum, price) 4975.0000 50delete from t1; 51insert into t1 values(100, 54.33), (200, 199.99); 52select avgcost(sum, price) from t1; 53avgcost(sum, price) 54151.4367 55drop table t1; 56select metaphon('hello'); 57metaphon('hello') 58HL 59CREATE PROCEDURE `XXX1`(in testval varchar(10)) 60begin 61select metaphon(testval); 62end// 63call XXX1('hello'); 64metaphon(testval) 65HL 66drop procedure xxx1; 67CREATE PROCEDURE `XXX2`() 68begin 69declare testval varchar(10); 70set testval = 'hello'; 71select metaphon(testval); 72end// 73call XXX2(); 74metaphon(testval) 75HL 76drop procedure xxx2; 77CREATE TABLE bug19904(n INT, v varchar(10)); 78INSERT INTO bug19904 VALUES (1,'one'),(2,'two'),(NULL,NULL),(3,'three'),(4,'four'); 79SELECT myfunc_double(n) AS f FROM bug19904; 80f 8149.00 8250.00 83NULL 8451.00 8552.00 86SELECT metaphon(v) AS f FROM bug19904; 87f 88ON 89TW 90NULL 910R 92FR 93DROP TABLE bug19904; 94CREATE DEFINER=CURRENT_USER() FUNCTION should_not_parse 95RETURNS STRING SONAME "should_not_parse.so"; 96ERROR 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 97CREATE DEFINER=someone@somewhere FUNCTION should_not_parse 98RETURNS STRING SONAME "should_not_parse.so"; 99ERROR 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 100create table t1(f1 int); 101insert into t1 values(1),(2); 102explain select myfunc_int(f1) from t1 order by 1; 103id select_type table type possible_keys key key_len ref rows Extra 1041 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using temporary; Using filesort 105drop table t1; 106CREATE TABLE t1(a INT, b INT); 107INSERT INTO t1 values (1,1),(2,2); 108CREATE FUNCTION fn(a int) RETURNS int DETERMINISTIC 109BEGIN 110RETURN a; 111END 112|| 113CREATE VIEW v1 AS SELECT a, fn(MIN(b)) as c FROM t1 GROUP BY a; 114SELECT myfunc_int(a AS attr_name) FROM t1; 115myfunc_int(a AS attr_name) 1161 1172 118EXPLAIN EXTENDED SELECT myfunc_int(a AS attr_name) FROM t1; 119id select_type table type possible_keys key key_len ref rows filtered Extra 1201 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 121Warnings: 122Note 1003 select myfunc_int(`test`.`t1`.`a` AS `attr_name`) AS `myfunc_int(a AS attr_name)` from `test`.`t1` 123EXPLAIN EXTENDED SELECT myfunc_int(a) FROM t1; 124id select_type table type possible_keys key key_len ref rows filtered Extra 1251 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 126Warnings: 127Note 1003 select myfunc_int(`test`.`t1`.`a` AS `a`) AS `myfunc_int(a)` from `test`.`t1` 128SELECT a,c FROM v1; 129a c 1301 1 1312 2 132SELECT a, fn(MIN(b) xx) as c FROM t1 GROUP BY a; 133ERROR 42000: Incorrect parameters in the call to stored function 'fn' 134SELECT myfunc_int(fn(MIN(b) xx)) as c FROM t1 GROUP BY a; 135ERROR 42000: Incorrect parameters in the call to stored function 'fn' 136SELECT myfunc_int(test.fn(MIN(b) xx)) as c FROM t1 GROUP BY a; 137ERROR 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 138SELECT myfunc_int(fn(MIN(b)) xx) as c FROM t1 GROUP BY a; 139c 1401 1412 142SELECT myfunc_int(test.fn(MIN(b)) xx) as c FROM t1 GROUP BY a; 143c 1441 1452 146EXPLAIN EXTENDED SELECT myfunc_int(MIN(b) xx) as c FROM t1 GROUP BY a; 147id select_type table type possible_keys key key_len ref rows filtered Extra 1481 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using temporary; Using filesort 149Warnings: 150Note 1003 select myfunc_int(min(`test`.`t1`.`b`) AS `xx`) AS `c` from `test`.`t1` group by `test`.`t1`.`a` 151EXPLAIN EXTENDED SELECT test.fn(MIN(b)) as c FROM t1 GROUP BY a; 152id select_type table type possible_keys key key_len ref rows filtered Extra 1531 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using temporary; Using filesort 154Warnings: 155Note 1003 select `test`.`fn`(min(`test`.`t1`.`b`)) AS `c` from `test`.`t1` group by `test`.`t1`.`a` 156EXPLAIN EXTENDED SELECT myfunc_int(fn(MIN(b))) as c FROM t1 GROUP BY a; 157id select_type table type possible_keys key key_len ref rows filtered Extra 1581 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using temporary; Using filesort 159Warnings: 160Note 1003 select myfunc_int(`fn`(min(`test`.`t1`.`b`)) AS `fn(MIN(b))`) AS `c` from `test`.`t1` group by `test`.`t1`.`a` 161EXPLAIN EXTENDED SELECT myfunc_int(test.fn(MIN(b))) as c FROM t1 GROUP BY a; 162id select_type table type possible_keys key key_len ref rows filtered Extra 1631 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using temporary; Using filesort 164Warnings: 165Note 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` 166SELECT myfunc_int(MIN(b) xx) as c FROM t1 GROUP BY a; 167c 1681 1692 170SELECT test.fn(MIN(b)) as c FROM t1 GROUP BY a; 171c 1721 1732 174SELECT myfunc_int(fn(MIN(b))) as c FROM t1 GROUP BY a; 175c 1761 1772 178SELECT myfunc_int(test.fn(MIN(b))) as c FROM t1 GROUP BY a; 179c 1801 1812 182DROP VIEW v1; 183DROP TABLE t1; 184DROP FUNCTION fn; 185End of 5.0 tests. 186select myfunc_double(3); 187myfunc_double(3) 18851.00 189select myfunc_double(3 AS three); 190myfunc_double(3 AS three) 19151.00 192select myfunc_double(abs(3)); 193myfunc_double(abs(3)) 19451.00 195select myfunc_double(abs(3) AS named_param); 196myfunc_double(abs(3) AS named_param) 19751.00 198select abs(myfunc_double(3)); 199abs(myfunc_double(3)) 20051.00 201select abs(myfunc_double(3 AS three)); 202abs(myfunc_double(3 AS three)) 20351.00 204select myfunc_double(abs(3 AS wrong)); 205ERROR 42000: Incorrect parameters in the call to native function 'abs' 206select abs(myfunc_double(3) AS wrong); 207ERROR 42000: Incorrect parameters in the call to native function 'abs' 208drop function if exists pi; 209CREATE FUNCTION pi RETURNS STRING SONAME "should_not_parse.so"; 210ERROR HY000: This function 'pi' has the same name as a native function 211DROP FUNCTION IF EXISTS metaphon; 212CREATE FUNCTION metaphon(a int) RETURNS int 213return 0; 214CREATE FUNCTION metaphon RETURNS STRING SONAME "UDF_EXAMPLE_LIB"; 215DROP FUNCTION metaphon; 216DROP FUNCTION metaphon; 217CREATE FUNCTION metaphon RETURNS STRING SONAME "UDF_EXAMPLE_LIB"; 218CREATE FUNCTION metaphon(a int) RETURNS int 219return 0; 220ERROR HY000: Function 'metaphon' already exists 221CREATE FUNCTION test.metaphon(a int) RETURNS int 222return 0; 223ERROR HY000: Function 'metaphon' already exists 224DROP FUNCTION metaphon; 225DROP FUNCTION myfunc_double; 226DROP FUNCTION myfunc_nonexist; 227ERROR 42000: FUNCTION test.myfunc_nonexist does not exist 228DROP FUNCTION myfunc_int; 229DROP FUNCTION udf_sequence; 230DROP FUNCTION lookup; 231DROP FUNCTION reverse_lookup; 232DROP FUNCTION avgcost; 233select * from mysql.func; 234name ret dl type 235CREATE FUNCTION is_const RETURNS STRING SONAME "UDF_EXAMPLE_LIB"; 236select IS_const(3); 237IS_const(3) 238const 239drop function IS_const; 240select * from mysql.func; 241name ret dl type 242select is_const(3); 243ERROR 42000: FUNCTION test.is_const does not exist 244CREATE FUNCTION is_const RETURNS STRING SONAME "UDF_EXAMPLE_LIB"; 245select 246is_const(3) as const, 247is_const(3.14) as const, 248is_const('fnord') as const, 249is_const(2+3) as const, 250is_const(rand()) as 'nc rand()', 251is_const(sin(3.14)) as const, 252is_const(upper('test')) as const; 253const const const const nc rand() const const 254const const const const not const const const 255create table bug18761 (n int); 256insert into bug18761 values (null),(2); 257select 258is_const(3) as const, 259is_const(3.14) as const, 260is_const('fnord') as const, 261is_const(2+3) as const, 262is_const(2+n) as 'nc 2+n ', 263is_const(sin(n)) as 'nc sin(n)', 264is_const(sin(3.14)) as const, 265is_const(upper('test')) as const, 266is_const(rand()) as 'nc rand()', 267is_const(n) as 'nc n ', 268is_const(is_const(n)) as 'nc ic?(n)', 269is_const(is_const('c')) as const 270from 271bug18761; 272const const const const nc 2+n nc sin(n) const const nc rand() nc n nc ic?(n) const 273const const const const not const not const const const not const not const not const const 274const const const const not const not const const const not const not const not const const 275drop table bug18761; 276select is_const((1,2,3)); 277ERROR 21000: Operand should contain 1 column(s) 278drop function if exists is_const; 279CREATE FUNCTION metaphon RETURNS STRING SONAME "UDF_EXAMPLE_LIB"; 280CREATE FUNCTION myfunc_double RETURNS REAL SONAME "UDF_EXAMPLE_LIB"; 281CREATE FUNCTION myfunc_int RETURNS INTEGER SONAME "UDF_EXAMPLE_LIB"; 282create function f1(p1 varchar(255)) 283returns varchar(255) 284begin 285return metaphon(p1); 286end// 287create function f2(p1 varchar(255)) 288returns double 289begin 290return myfunc_double(p1); 291end// 292create function f3(p1 varchar(255)) 293returns double 294begin 295return myfunc_int(p1); 296end// 297select f3(NULL); 298f3(NULL) 2990 300select f2(NULL); 301f2(NULL) 302NULL 303select f1(NULL); 304f1(NULL) 305NULL 306drop function f1; 307drop function f2; 308drop function f3; 309drop function metaphon; 310drop function myfunc_double; 311drop function myfunc_int; 312DROP DATABASE IF EXISTS mysqltest; 313CREATE DATABASE mysqltest; 314USE mysqltest; 315DROP DATABASE mysqltest; 316CREATE FUNCTION metaphon RETURNS STRING SONAME "UDF_EXAMPLE_LIB"; 317DROP FUNCTION metaphon; 318USE test; 319CREATE TABLE const_len_bug ( 320str_const varchar(4000), 321result1 varchar(4000), 322result2 varchar(4000) 323); 324CREATE TRIGGER check_const_len_trigger BEFORE INSERT ON const_len_bug FOR EACH ROW BEGIN 325set NEW.str_const = 'bar'; 326set NEW.result2 = check_const_len(NEW.str_const); 327END | 328CREATE PROCEDURE check_const_len_sp (IN str_const VARCHAR(4000)) 329BEGIN 330DECLARE result VARCHAR(4000); 331SET result = check_const_len(str_const); 332insert into const_len_bug values(str_const, result, ""); 333END | 334CREATE FUNCTION check_const_len RETURNS string SONAME "UDF_EXAMPLE_LIB"; 335CALL check_const_len_sp("foo"); 336SELECT * from const_len_bug; 337str_const result1 result2 338bar Correct length Correct length 339DROP FUNCTION check_const_len; 340DROP PROCEDURE check_const_len_sp; 341DROP TRIGGER check_const_len_trigger; 342DROP TABLE const_len_bug; 343CREATE FUNCTION udf_sequence RETURNS INTEGER SONAME "UDF_EXAMPLE_LIB"; 344CREATE TABLE t1 (a INT); 345CREATE TABLE t2 (a INT PRIMARY KEY); 346INSERT INTO t1 VALUES (4),(3),(2),(1); 347INSERT INTO t2 SELECT * FROM t1; 348SELECT udf_sequence() AS seq, a FROM t1 ORDER BY seq ASC; 349seq a 3501 4 3512 3 3523 2 3534 1 354SELECT udf_sequence() AS seq, a FROM t1 ORDER BY seq DESC; 355seq a 3564 1 3573 2 3582 3 3591 4 360SELECT * FROM t1 WHERE a = udf_sequence(); 361a 362SELECT * FROM t2 WHERE a = udf_sequence(); 363a 3641 3652 3663 3674 368DROP FUNCTION udf_sequence; 369DROP TABLE t1,t2; 370drop function if exists test.metaphon; 371drop function if exists metaphon; 372CREATE FUNCTION metaphon RETURNS STRING SONAME "UDF_EXAMPLE_LIB"; 373select metaphon("Hello"); 374metaphon("Hello") 375HL 376drop function if exists test.metaphon; 377Warnings: 378Note 1305 FUNCTION test.metaphon does not exist 379select metaphon("Hello"); 380metaphon("Hello") 381HL 382drop function metaphon; 383CREATE FUNCTION test.metaphon(a TEXT) RETURNS TEXT return "This is a SF"; 384create database db_31767; 385use db_31767; 386CREATE FUNCTION metaphon RETURNS STRING SONAME "UDF_EXAMPLE_LIB"; 387use test; 388select metaphon("Hello"); 389metaphon("Hello") 390HL 391select test.metaphon("Hello"); 392test.metaphon("Hello") 393This is a SF 394drop function metaphon; 395select metaphon("Hello"); 396metaphon("Hello") 397This is a SF 398drop function metaphon; 399use db_31767; 400drop database db_31767; 401drop function if exists no_such_func; 402Warnings: 403Note 1305 FUNCTION (UDF) no_such_func does not exist 404drop function no_such_func; 405ERROR 42000: FUNCTION (UDF) no_such_func does not exist 406drop function if exists test.no_such_func; 407Warnings: 408Note 1305 FUNCTION test.no_such_func does not exist 409drop function test.no_such_func; 410ERROR 42000: FUNCTION test.no_such_func does not exist 411drop procedure if exists no_such_proc; 412ERROR 3D000: No database selected 413drop procedure no_such_proc; 414ERROR 3D000: No database selected 415use test; 416# 417# Bug#46259: 5.0.83 -> 5.1.36, query doesn't work 418# 419CREATE TABLE t1 ( a INT ); 420INSERT INTO t1 VALUES (1), (2), (3); 421SELECT IF( a = 1, a, a ) AS `b` FROM t1 ORDER BY field( `b` + 1, 1 ); 422b 4231 4242 4253 426SELECT IF( a = 1, a, a ) AS `b` FROM t1 ORDER BY field( `b`, 1 ); 427b 4282 4293 4301 431DROP TABLE t1; 432End of 5.0 tests. 433# 434# Bug#33546: Slowdown on re-evaluation of constant expressions. 435# 436CREATE TABLE t1 (f1 INT); 437INSERT INTO t1 VALUES(1),(50); 438CREATE FUNCTION myfunc_double RETURNS INTEGER SONAME "UDF_EXAMPLE_LIB"; 439EXPLAIN EXTENDED SELECT 1 FROM t1 WHERE f1=1 + myfunc_double(1); 440id select_type table type possible_keys key key_len ref rows filtered Extra 4411 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where 442Warnings: 443Note 1003 select 1 AS `1` from `test`.`t1` where `test`.`t1`.`f1` = <cache>(1 + myfunc_double(1 AS `1`)) 444DROP FUNCTION myfunc_double; 445DROP TABLE t1; 446# 447End of 5.1 tests. 448# 449# MDEV-10134 Add full support for DEFAULT 450# 451CREATE FUNCTION metaphon RETURNS STRING SONAME "UDF_EXAMPLE_LIB"; 452SELECT METAPHON('Hello'); 453METAPHON('Hello') 454HL 455CREATE TABLE t1 (a VARCHAR(10), b VARCHAR(10) DEFAULT METAPHON(a)); 456SHOW CREATE TABLE t1; 457Table Create Table 458t1 CREATE TABLE `t1` ( 459 `a` varchar(10) DEFAULT NULL, 460 `b` varchar(10) DEFAULT metaphon(`a` AS `a`) 461) ENGINE=MyISAM DEFAULT CHARSET=latin1 462INSERT INTO t1 (a) VALUES ('Hello'); 463SELECT * FROM t1; 464a b 465Hello HL 466DROP FUNCTION METAPHON; 467DROP TABLE t1; 468# 469# MDEV-15424: Unreasonal SQL Error (1356) on select from view 470# 471CREATE FUNCTION myfunc_int RETURNS INTEGER SONAME "UDF_EXAMPLE_LIB"; 472create table t1(a int , b int); 473insert into t1 values(100, 54), (200, 199); 474create view v1 as select myfunc_int(max(a) over (order by b) , b) from t1; 475select * from v1; 476myfunc_int(max(a) over (order by b) , b) 477154 478399 479drop view v1; 480drop function myfunc_int; 481drop table t1; 482# 483# MDEV-23327: Can't uninstall UDF if the implementation library 484# file doesn't exist 485# 486insert into mysql.func values ("unexisting_udf", 0, "soname", "function"); 487select * from mysql.func WHERE name='unexisting_udf'; 488name ret dl type 489unexisting_udf 0 soname function 490DROP FUNCTION unexisting_udf; 491select * from mysql.plugin WHERE name='unexisting_udf'; 492name dl 493DROP FUNCTION unexisting_udf; 494ERROR 42000: FUNCTION test.unexisting_udf does not exist 495# 496# Bug #31674599: THE UDF_INIT() FUNCTION CAUSE SERVER CRASH 497# 498call mtr.add_suppression('Invalid row in mysql.func table'); 499insert mysql.func () values (); 500# restart 501delete from mysql.func where name = ''; 502# 503# End of 10.2 tests 504# 505# 506# MDEV-15073: Generic UDAF parser code in server for window functions 507# 508CREATE AGGREGATE FUNCTION avgcost 509RETURNS REAL SONAME "UDF_EXAMPLE_LIB"; 510CREATE AGGREGATE FUNCTION avg2 511RETURNS REAL SONAME "UDF_EXAMPLE_LIB"; 512CREATE FUNCTION myfunc_double RETURNS REAL SONAME "UDF_EXAMPLE_LIB"; 513create table t1(pk int primary key, 514a int, 515sum int, 516price float(24)); 517insert into t1 values 518(1, 1, 100, 50.00), 519(2, 1, 100, 100.00), 520(3, 1, 100, 50.00), 521(4, 1, 100, 50.00), 522(5, 1, 100, 50.00), 523(6, 1, 100, NULL), 524(7, 1, NULL, NULL), 525(8, 2, 2, 2), 526(9, 2, 4, 4); 527select pk, a, sum, price, avgcost(sum, price) over (partition by a order by pk ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) 528from t1; 529pk a sum price avgcost(sum, price) over (partition by a order by pk ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) 5301 1 100 50 75.0000 5312 1 100 100 66.6667 5323 1 100 50 66.6667 5334 1 100 50 50.0000 5345 1 100 50 50.0000 5356 1 100 NULL 50.0000 5367 1 NULL NULL 0.0000 5378 2 2 2 3.3333 5389 2 4 4 3.3333 539select pk, a, sum, price, avgcost(sum, price) over (partition by a order by pk ROWS BETWEEN 1 PRECEDING AND 0 FOLLOWING) 540from t1; 541pk a sum price avgcost(sum, price) over (partition by a order by pk ROWS BETWEEN 1 PRECEDING AND 0 FOLLOWING) 5421 1 100 50 50.0000 5432 1 100 100 75.0000 5443 1 100 50 75.0000 5454 1 100 50 50.0000 5465 1 100 50 50.0000 5476 1 100 NULL 50.0000 5487 1 NULL NULL 0.0000 5498 2 2 2 2.0000 5509 2 4 4 3.3333 551select pk, a, sum, price, avg2(sum, price) over (partition by a order by pk ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) 552from t1; 553pk a sum price avg2(sum, price) over (partition by a order by pk ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) 5541 1 100 50 0.7500 5552 1 100 100 0.6667 5563 1 100 50 0.6667 5574 1 100 50 0.5000 5585 1 100 50 0.5000 5596 1 100 NULL 0.5000 5607 1 NULL NULL 0.0000 5618 2 2 2 1.0000 5629 2 4 4 1.0000 563select pk, a, sum, price, avg2(sum, price) over (partition by a order by pk ROWS BETWEEN 1 PRECEDING AND 0 FOLLOWING) 564from t1; 565pk a sum price avg2(sum, price) over (partition by a order by pk ROWS BETWEEN 1 PRECEDING AND 0 FOLLOWING) 5661 1 100 50 0.5000 5672 1 100 100 0.7500 5683 1 100 50 0.7500 5694 1 100 50 0.5000 5705 1 100 50 0.5000 5716 1 100 NULL 0.5000 5727 1 NULL NULL 0.0000 5738 2 2 2 1.0000 5749 2 4 4 1.0000 575select pk, a, sum, price, tttttttt(sprice,sum) over (partition by a order by pk ROWS BETWEEN 1 PRECEDING AND 0 FOLLOWING) 576from t1; 577ERROR 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) 578fr...' at line 1 579select pk, a, sum, price, myfunc_double(sum) over (partition by a order by pk ROWS BETWEEN 1 PRECEDING AND 0 FOLLOWING) 580from t1; 581ERROR 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) 582fr...' at line 1 583select pk, a, sum, price, round(sprice,sum) over (partition by a order by pk ROWS BETWEEN 1 PRECEDING AND 0 FOLLOWING) 584from t1; 585ERROR 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) 586fr...' at line 1 587select pk, a, sum, price, myfunc_double(sum) over (partition by a order by pk ROWS BETWEEN 1 PRECEDING AND 0 FOLLOWING) 588from t1; 589ERROR 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) 590fr...' at line 1 591set @save_sql_mode = @@sql_mode; 592set sql_mode="oracle"; 593select pk, a, sum, price, avg2(sum, price) over (partition by a order by pk ROWS BETWEEN 1 PRECEDING AND 0 FOLLOWING) 594from t1; 595pk a sum price avg2(sum, price) over (partition by a order by pk ROWS BETWEEN 1 PRECEDING AND 0 FOLLOWING) 5961 1 100 50 0.5000 5972 1 100 100 0.7500 5983 1 100 50 0.7500 5994 1 100 50 0.5000 6005 1 100 50 0.5000 6016 1 100 NULL 0.5000 6027 1 NULL NULL 0.0000 6038 2 2 2 1.0000 6049 2 4 4 1.0000 605set sql_mode= @save_sql_mode; 606drop table t1; 607DROP FUNCTION avgcost; 608DROP FUNCTION avg2; 609DROP FUNCTION myfunc_double; 610# End of 10.4 tests 611