diff options
41 files changed, 4469 insertions, 1236 deletions
diff --git a/mysql-test/include/sp-vars.inc b/mysql-test/include/sp-vars.inc new file mode 100644 index 00000000000..3e02c9d1709 --- /dev/null +++ b/mysql-test/include/sp-vars.inc @@ -0,0 +1,122 @@ +delimiter |; + +--------------------------------------------------------------------------- + +CREATE PROCEDURE sp_vars_check_dflt() +BEGIN + DECLARE v1 TINYINT DEFAULT 1e200; + DECLARE v1u TINYINT UNSIGNED DEFAULT 1e200; + DECLARE v2 TINYINT DEFAULT -1e200; + DECLARE v2u TINYINT UNSIGNED DEFAULT -1e200; + DECLARE v3 TINYINT DEFAULT 300; + DECLARE v3u TINYINT UNSIGNED DEFAULT 300; + DECLARE v4 TINYINT DEFAULT -300; + DECLARE v4u TINYINT UNSIGNED DEFAULT -300; + + DECLARE v5 TINYINT DEFAULT 10 * 10 * 10; + DECLARE v5u TINYINT UNSIGNED DEFAULT 10 * 10 * 10; + DECLARE v6 TINYINT DEFAULT -10 * 10 * 10; + DECLARE v6u TINYINT UNSIGNED DEFAULT -10 * 10 * 10; + + DECLARE v7 TINYINT DEFAULT '10'; + DECLARE v8 TINYINT DEFAULT '10 '; + DECLARE v9 TINYINT DEFAULT ' 10 '; + DECLARE v10 TINYINT DEFAULT 'String 10 '; + DECLARE v11 TINYINT DEFAULT 'String10'; + DECLARE v12 TINYINT DEFAULT '10 String'; + DECLARE v13 TINYINT DEFAULT '10String'; + DECLARE v14 TINYINT DEFAULT concat('10', ' '); + DECLARE v15 TINYINT DEFAULT concat(' ', '10'); + DECLARE v16 TINYINT DEFAULT concat('Hello, ', 'world'); + + DECLARE v17 DECIMAL(64, 2) DEFAULT 12; + DECLARE v18 DECIMAL(64, 2) DEFAULT 12.123; + DECLARE v19 DECIMAL(64, 2) DEFAULT 11 + 1; + DECLARE v20 DECIMAL(64, 2) DEFAULT 12 + 0.123; + + SELECT v1, v1u, v2, v2u, v3, v3u, v4, v4u; + SELECT v5, v5u, v6, v6u; + SELECT v7, v8, v9, v10, v11, v12, v13, v14, v15, v16; + SELECT v17, v18, v19, v20; +END| + +--------------------------------------------------------------------------- + +CREATE PROCEDURE sp_vars_check_assignment() +BEGIN + DECLARE i1, i2, i3, i4 TINYINT; + DECLARE u1, u2, u3, u4 TINYINT UNSIGNED; + DECLARE d1, d2, d3 DECIMAL(64, 2); + + SET i1 = 1e200; + SET i2 = -1e200; + SET i3 = 300; + SET i4 = -300; + + SELECT i1, i2, i3, i4; + + SET i1 = 10 * 10 * 10; + SET i2 = -10 * 10 * 10; + SET i3 = sign(10 * 10) * 10 * 20; + SET i4 = sign(-10 * 10) * -10 * 20; + + SELECT i1, i2, i3, i4; + + SET u1 = 1e200; + SET u2 = -1e200; + SET u3 = 300; + SET u4 = -300; + + SELECT u1, u2, u3, u4; + + SET u1 = 10 * 10 * 10; + SET u2 = -10 * 10 * 10; + SET u3 = sign(10 * 10) * 10 * 20; + SET u4 = sign(-10 * 10) * -10 * 20; + + SELECT u1, u2, u3, u4; + + SET d1 = 1234; + SET d2 = 1234.12; + SET d3 = 1234.1234; + + SELECT d1, d2, d3; + + SET d1 = 12 * 100 + 34; + SET d2 = 12 * 100 + 34 + 0.12; + SET d3 = 12 * 100 + 34 + 0.1234; + + SELECT d1, d2, d3; +END| + +--------------------------------------------------------------------------- + +CREATE FUNCTION sp_vars_check_ret1() RETURNS TINYINT +BEGIN + RETURN 1e200; +END| + +--------------------------------------------------------------------------- + +CREATE FUNCTION sp_vars_check_ret2() RETURNS TINYINT +BEGIN + RETURN 10 * 10 * 10; +END| + +--------------------------------------------------------------------------- + +CREATE FUNCTION sp_vars_check_ret3() RETURNS TINYINT +BEGIN + RETURN 'Hello, world'; +END| + +--------------------------------------------------------------------------- + +CREATE FUNCTION sp_vars_check_ret4() RETURNS DECIMAL(64, 2) +BEGIN + RETURN 12 * 10 + 34 + 0.1234; +END| + +--------------------------------------------------------------------------- + +delimiter ;| diff --git a/mysql-test/r/ctype_ujis.result b/mysql-test/r/ctype_ujis.result index 15de93440fc..2e14fe34430 100644 --- a/mysql-test/r/ctype_ujis.result +++ b/mysql-test/r/ctype_ujis.result @@ -2317,7 +2317,7 @@ CREATE TABLE t2(c2 char(2)) default charset = ujis; INSERT INTO t1 VALUES(_ujis 0xA4A2); CREATE PROCEDURE sp1() BEGIN -DECLARE a CHAR(1); +DECLARE a CHAR(2) CHARSET ujis; DECLARE cur1 CURSOR FOR SELECT c1 FROM t1; OPEN cur1; FETCH cur1 INTO a; diff --git a/mysql-test/r/schema.result b/mysql-test/r/schema.result index 48e6ebcfad2..538abd8d039 100644 --- a/mysql-test/r/schema.result +++ b/mysql-test/r/schema.result @@ -1,3 +1,4 @@ +drop database if exists mysqltest1; create schema foo; show create schema foo; Database Create Database diff --git a/mysql-test/r/show_check.result b/mysql-test/r/show_check.result index fdb0db602ff..61a820b4469 100644 --- a/mysql-test/r/show_check.result +++ b/mysql-test/r/show_check.result @@ -1,6 +1,7 @@ drop table if exists t1,t2; drop table if exists t1aa,t2aa; drop database if exists mysqltest; +drop database if exists mysqltest1; delete from mysql.user where user='mysqltest_1' || user='mysqltest_2' || user='mysqltest_3'; delete from mysql.db where user='mysqltest_1' || user='mysqltest_2' || user='mysqltest_3'; flush privileges; diff --git a/mysql-test/r/skip_name_resolve.result b/mysql-test/r/skip_name_resolve.result index a969c5c9ae0..8ef52e75238 100644 --- a/mysql-test/r/skip_name_resolve.result +++ b/mysql-test/r/skip_name_resolve.result @@ -10,5 +10,5 @@ user() # show processlist; Id User Host db Command Time State Info -# root # test Sleep # NULL -# root # test Query # NULL show processlist +<id> root <host> test <command> <time> <state> <info> +<id> root <host> test <command> <time> <state> <info> diff --git a/mysql-test/r/sp-big.result b/mysql-test/r/sp-big.result index 1f0b6b34651..9765508859c 100644 --- a/mysql-test/r/sp-big.result +++ b/mysql-test/r/sp-big.result @@ -25,6 +25,7 @@ count(*) select count(*) from t2; count(*) 0 +drop procedure if exists p1; create procedure p1() begin declare done integer default 0; diff --git a/mysql-test/r/sp-dynamic.result b/mysql-test/r/sp-dynamic.result index cf07f540608..c00b09f90e1 100644 --- a/mysql-test/r/sp-dynamic.result +++ b/mysql-test/r/sp-dynamic.result @@ -1,3 +1,5 @@ +drop procedure if exists p1| +drop procedure if exists p2| create procedure p1() begin prepare stmt from "select 1"; diff --git a/mysql-test/r/sp-vars.result b/mysql-test/r/sp-vars.result new file mode 100644 index 00000000000..6b4d7b1a6d3 --- /dev/null +++ b/mysql-test/r/sp-vars.result @@ -0,0 +1,1077 @@ +DROP PROCEDURE IF EXISTS sp_vars_check_dflt; +DROP PROCEDURE IF EXISTS sp_vars_check_assignment; +DROP FUNCTION IF EXISTS sp_vars_check_ret1; +DROP FUNCTION IF EXISTS sp_vars_check_ret2; +DROP FUNCTION IF EXISTS sp_vars_check_ret3; +DROP FUNCTION IF EXISTS sp_vars_check_ret4; +SET @@sql_mode = 'ansi'; +CREATE PROCEDURE sp_vars_check_dflt() +BEGIN +DECLARE v1 TINYINT DEFAULT 1e200; +DECLARE v1u TINYINT UNSIGNED DEFAULT 1e200; +DECLARE v2 TINYINT DEFAULT -1e200; +DECLARE v2u TINYINT UNSIGNED DEFAULT -1e200; +DECLARE v3 TINYINT DEFAULT 300; +DECLARE v3u TINYINT UNSIGNED DEFAULT 300; +DECLARE v4 TINYINT DEFAULT -300; +DECLARE v4u TINYINT UNSIGNED DEFAULT -300; +DECLARE v5 TINYINT DEFAULT 10 * 10 * 10; +DECLARE v5u TINYINT UNSIGNED DEFAULT 10 * 10 * 10; +DECLARE v6 TINYINT DEFAULT -10 * 10 * 10; +DECLARE v6u TINYINT UNSIGNED DEFAULT -10 * 10 * 10; +DECLARE v7 TINYINT DEFAULT '10'; +DECLARE v8 TINYINT DEFAULT '10 '; +DECLARE v9 TINYINT DEFAULT ' 10 '; +DECLARE v10 TINYINT DEFAULT 'String 10 '; +DECLARE v11 TINYINT DEFAULT 'String10'; +DECLARE v12 TINYINT DEFAULT '10 String'; +DECLARE v13 TINYINT DEFAULT '10String'; +DECLARE v14 TINYINT DEFAULT concat('10', ' '); +DECLARE v15 TINYINT DEFAULT concat(' ', '10'); +DECLARE v16 TINYINT DEFAULT concat('Hello, ', 'world'); +DECLARE v17 DECIMAL(64, 2) DEFAULT 12; +DECLARE v18 DECIMAL(64, 2) DEFAULT 12.123; +DECLARE v19 DECIMAL(64, 2) DEFAULT 11 + 1; +DECLARE v20 DECIMAL(64, 2) DEFAULT 12 + 0.123; +SELECT v1, v1u, v2, v2u, v3, v3u, v4, v4u; +SELECT v5, v5u, v6, v6u; +SELECT v7, v8, v9, v10, v11, v12, v13, v14, v15, v16; +SELECT v17, v18, v19, v20; +END| +CREATE PROCEDURE sp_vars_check_assignment() +BEGIN +DECLARE i1, i2, i3, i4 TINYINT; +DECLARE u1, u2, u3, u4 TINYINT UNSIGNED; +DECLARE d1, d2, d3 DECIMAL(64, 2); +SET i1 = 1e200; +SET i2 = -1e200; +SET i3 = 300; +SET i4 = -300; +SELECT i1, i2, i3, i4; +SET i1 = 10 * 10 * 10; +SET i2 = -10 * 10 * 10; +SET i3 = sign(10 * 10) * 10 * 20; +SET i4 = sign(-10 * 10) * -10 * 20; +SELECT i1, i2, i3, i4; +SET u1 = 1e200; +SET u2 = -1e200; +SET u3 = 300; +SET u4 = -300; +SELECT u1, u2, u3, u4; +SET u1 = 10 * 10 * 10; +SET u2 = -10 * 10 * 10; +SET u3 = sign(10 * 10) * 10 * 20; +SET u4 = sign(-10 * 10) * -10 * 20; +SELECT u1, u2, u3, u4; +SET d1 = 1234; +SET d2 = 1234.12; +SET d3 = 1234.1234; +SELECT d1, d2, d3; +SET d1 = 12 * 100 + 34; +SET d2 = 12 * 100 + 34 + 0.12; +SET d3 = 12 * 100 + 34 + 0.1234; +SELECT d1, d2, d3; +END| +CREATE FUNCTION sp_vars_check_ret1() RETURNS TINYINT +BEGIN +RETURN 1e200; +END| +CREATE FUNCTION sp_vars_check_ret2() RETURNS TINYINT +BEGIN +RETURN 10 * 10 * 10; +END| +CREATE FUNCTION sp_vars_check_ret3() RETURNS TINYINT +BEGIN +RETURN 'Hello, world'; +END| +CREATE FUNCTION sp_vars_check_ret4() RETURNS DECIMAL(64, 2) +BEGIN +RETURN 12 * 10 + 34 + 0.1234; +END| + +--------------------------------------------------------------- +Calling the routines, created in ANSI mode. +--------------------------------------------------------------- + +CALL sp_vars_check_dflt(); +v1 v1u v2 v2u v3 v3u v4 v4u +127 255 -128 0 127 255 -128 0 +v5 v5u v6 v6u +127 255 -128 0 +v7 v8 v9 v10 v11 v12 v13 v14 v15 v16 +10 10 10 0 0 10 10 10 10 0 +v17 v18 v19 v20 +12.00 12.12 12.00 12.12 +Warnings: +Warning 1264 Out of range value adjusted for column 'v1' at row 1 +Warning 1264 Out of range value adjusted for column 'v1u' at row 1 +Warning 1264 Out of range value adjusted for column 'v2' at row 1 +Warning 1264 Out of range value adjusted for column 'v2u' at row 1 +Warning 1264 Out of range value adjusted for column 'v3' at row 1 +Warning 1264 Out of range value adjusted for column 'v3u' at row 1 +Warning 1264 Out of range value adjusted for column 'v4' at row 1 +Warning 1264 Out of range value adjusted for column 'v4u' at row 1 +Warning 1264 Out of range value adjusted for column 'v5' at row 1 +Warning 1264 Out of range value adjusted for column 'v5u' at row 1 +Warning 1264 Out of range value adjusted for column 'v6' at row 1 +Warning 1264 Out of range value adjusted for column 'v6u' at row 1 +Warning 1366 Incorrect integer value: 'String 10 ' for column 'v10' at row 1 +Warning 1366 Incorrect integer value: 'String10' for column 'v11' at row 1 +Warning 1265 Data truncated for column 'v12' at row 1 +Warning 1265 Data truncated for column 'v13' at row 1 +Warning 1366 Incorrect integer value: 'Hello, world' for column 'v16' at row 1 +Note 1265 Data truncated for column 'v18' at row 1 +Note 1265 Data truncated for column 'v20' at row 1 +CALL sp_vars_check_assignment(); +i1 i2 i3 i4 +127 -128 127 -128 +i1 i2 i3 i4 +127 -128 127 127 +u1 u2 u3 u4 +255 0 255 0 +u1 u2 u3 u4 +255 0 200 200 +d1 d2 d3 +1234.00 1234.12 1234.12 +d1 d2 d3 +1234.00 1234.12 1234.12 +Warnings: +Warning 1264 Out of range value adjusted for column 'i1' at row 1 +Warning 1264 Out of range value adjusted for column 'i2' at row 1 +Warning 1264 Out of range value adjusted for column 'i3' at row 1 +Warning 1264 Out of range value adjusted for column 'i4' at row 1 +Warning 1264 Out of range value adjusted for column 'i1' at row 1 +Warning 1264 Out of range value adjusted for column 'i2' at row 1 +Warning 1264 Out of range value adjusted for column 'i3' at row 1 +Warning 1264 Out of range value adjusted for column 'i4' at row 1 +Warning 1264 Out of range value adjusted for column 'u1' at row 1 +Warning 1264 Out of range value adjusted for column 'u2' at row 1 +Warning 1264 Out of range value adjusted for column 'u3' at row 1 +Warning 1264 Out of range value adjusted for column 'u4' at row 1 +Warning 1264 Out of range value adjusted for column 'u1' at row 1 +Warning 1264 Out of range value adjusted for column 'u2' at row 1 +Note 1265 Data truncated for column 'd3' at row 1 +Note 1265 Data truncated for column 'd3' at row 1 +SELECT sp_vars_check_ret1(); +sp_vars_check_ret1() +127 +Warnings: +Warning 1264 Out of range value adjusted for column 'sp_vars_check_ret1()' at row 1 +SELECT sp_vars_check_ret2(); +sp_vars_check_ret2() +127 +Warnings: +Warning 1264 Out of range value adjusted for column 'sp_vars_check_ret2()' at row 1 +SELECT sp_vars_check_ret3(); +sp_vars_check_ret3() +0 +Warnings: +Warning 1366 Incorrect integer value: 'Hello, world' for column 'sp_vars_check_ret3()' at row 1 +SELECT sp_vars_check_ret4(); +sp_vars_check_ret4() +154.12 +Warnings: +Note 1265 Data truncated for column 'sp_vars_check_ret4()' at row 1 +SET @@sql_mode = 'traditional'; + +--------------------------------------------------------------- +Calling in TRADITIONAL mode the routines, created in ANSI mode. +--------------------------------------------------------------- + +CALL sp_vars_check_dflt(); +v1 v1u v2 v2u v3 v3u v4 v4u +127 255 -128 0 127 255 -128 0 +v5 v5u v6 v6u +127 255 -128 0 +v7 v8 v9 v10 v11 v12 v13 v14 v15 v16 +10 10 10 0 0 10 10 10 10 0 +v17 v18 v19 v20 +12.00 12.12 12.00 12.12 +Warnings: +Warning 1264 Out of range value adjusted for column 'v1' at row 1 +Warning 1264 Out of range value adjusted for column 'v1u' at row 1 +Warning 1264 Out of range value adjusted for column 'v2' at row 1 +Warning 1264 Out of range value adjusted for column 'v2u' at row 1 +Warning 1264 Out of range value adjusted for column 'v3' at row 1 +Warning 1264 Out of range value adjusted for column 'v3u' at row 1 +Warning 1264 Out of range value adjusted for column 'v4' at row 1 +Warning 1264 Out of range value adjusted for column 'v4u' at row 1 +Warning 1264 Out of range value adjusted for column 'v5' at row 1 +Warning 1264 Out of range value adjusted for column 'v5u' at row 1 +Warning 1264 Out of range value adjusted for column 'v6' at row 1 +Warning 1264 Out of range value adjusted for column 'v6u' at row 1 +Warning 1366 Incorrect integer value: 'String 10 ' for column 'v10' at row 1 +Warning 1366 Incorrect integer value: 'String10' for column 'v11' at row 1 +Warning 1265 Data truncated for column 'v12' at row 1 +Warning 1265 Data truncated for column 'v13' at row 1 +Warning 1366 Incorrect integer value: 'Hello, world' for column 'v16' at row 1 +Note 1265 Data truncated for column 'v18' at row 1 +Note 1265 Data truncated for column 'v20' at row 1 +CALL sp_vars_check_assignment(); +i1 i2 i3 i4 +127 -128 127 -128 +i1 i2 i3 i4 +127 -128 127 127 +u1 u2 u3 u4 +255 0 255 0 +u1 u2 u3 u4 +255 0 200 200 +d1 d2 d3 +1234.00 1234.12 1234.12 +d1 d2 d3 +1234.00 1234.12 1234.12 +Warnings: +Warning 1264 Out of range value adjusted for column 'i1' at row 1 +Warning 1264 Out of range value adjusted for column 'i2' at row 1 +Warning 1264 Out of range value adjusted for column 'i3' at row 1 +Warning 1264 Out of range value adjusted for column 'i4' at row 1 +Warning 1264 Out of range value adjusted for column 'i1' at row 1 +Warning 1264 Out of range value adjusted for column 'i2' at row 1 +Warning 1264 Out of range value adjusted for column 'i3' at row 1 +Warning 1264 Out of range value adjusted for column 'i4' at row 1 +Warning 1264 Out of range value adjusted for column 'u1' at row 1 +Warning 1264 Out of range value adjusted for column 'u2' at row 1 +Warning 1264 Out of range value adjusted for column 'u3' at row 1 +Warning 1264 Out of range value adjusted for column 'u4' at row 1 +Warning 1264 Out of range value adjusted for column 'u1' at row 1 +Warning 1264 Out of range value adjusted for column 'u2' at row 1 +Note 1265 Data truncated for column 'd3' at row 1 +Note 1265 Data truncated for column 'd3' at row 1 +SELECT sp_vars_check_ret1(); +sp_vars_check_ret1() +127 +Warnings: +Warning 1264 Out of range value adjusted for column 'sp_vars_check_ret1()' at row 1 +SELECT sp_vars_check_ret2(); +sp_vars_check_ret2() +127 +Warnings: +Warning 1264 Out of range value adjusted for column 'sp_vars_check_ret2()' at row 1 +SELECT sp_vars_check_ret3(); +sp_vars_check_ret3() +0 +Warnings: +Warning 1366 Incorrect integer value: 'Hello, world' for column 'sp_vars_check_ret3()' at row 1 +SELECT sp_vars_check_ret4(); +sp_vars_check_ret4() +154.12 +Warnings: +Note 1265 Data truncated for column 'sp_vars_check_ret4()' at row 1 +DROP PROCEDURE sp_vars_check_dflt; +DROP PROCEDURE sp_vars_check_assignment; +DROP FUNCTION sp_vars_check_ret1; +DROP FUNCTION sp_vars_check_ret2; +DROP FUNCTION sp_vars_check_ret3; +DROP FUNCTION sp_vars_check_ret4; +CREATE PROCEDURE sp_vars_check_dflt() +BEGIN +DECLARE v1 TINYINT DEFAULT 1e200; +DECLARE v1u TINYINT UNSIGNED DEFAULT 1e200; +DECLARE v2 TINYINT DEFAULT -1e200; +DECLARE v2u TINYINT UNSIGNED DEFAULT -1e200; +DECLARE v3 TINYINT DEFAULT 300; +DECLARE v3u TINYINT UNSIGNED DEFAULT 300; +DECLARE v4 TINYINT DEFAULT -300; +DECLARE v4u TINYINT UNSIGNED DEFAULT -300; +DECLARE v5 TINYINT DEFAULT 10 * 10 * 10; +DECLARE v5u TINYINT UNSIGNED DEFAULT 10 * 10 * 10; +DECLARE v6 TINYINT DEFAULT -10 * 10 * 10; +DECLARE v6u TINYINT UNSIGNED DEFAULT -10 * 10 * 10; +DECLARE v7 TINYINT DEFAULT '10'; +DECLARE v8 TINYINT DEFAULT '10 '; +DECLARE v9 TINYINT DEFAULT ' 10 '; +DECLARE v10 TINYINT DEFAULT 'String 10 '; +DECLARE v11 TINYINT DEFAULT 'String10'; +DECLARE v12 TINYINT DEFAULT '10 String'; +DECLARE v13 TINYINT DEFAULT '10String'; +DECLARE v14 TINYINT DEFAULT concat('10', ' '); +DECLARE v15 TINYINT DEFAULT concat(' ', '10'); +DECLARE v16 TINYINT DEFAULT concat('Hello, ', 'world'); +DECLARE v17 DECIMAL(64, 2) DEFAULT 12; +DECLARE v18 DECIMAL(64, 2) DEFAULT 12.123; +DECLARE v19 DECIMAL(64, 2) DEFAULT 11 + 1; +DECLARE v20 DECIMAL(64, 2) DEFAULT 12 + 0.123; +SELECT v1, v1u, v2, v2u, v3, v3u, v4, v4u; +SELECT v5, v5u, v6, v6u; +SELECT v7, v8, v9, v10, v11, v12, v13, v14, v15, v16; +SELECT v17, v18, v19, v20; +END| +CREATE PROCEDURE sp_vars_check_assignment() +BEGIN +DECLARE i1, i2, i3, i4 TINYINT; +DECLARE u1, u2, u3, u4 TINYINT UNSIGNED; +DECLARE d1, d2, d3 DECIMAL(64, 2); +SET i1 = 1e200; +SET i2 = -1e200; +SET i3 = 300; +SET i4 = -300; +SELECT i1, i2, i3, i4; +SET i1 = 10 * 10 * 10; +SET i2 = -10 * 10 * 10; +SET i3 = sign(10 * 10) * 10 * 20; +SET i4 = sign(-10 * 10) * -10 * 20; +SELECT i1, i2, i3, i4; +SET u1 = 1e200; +SET u2 = -1e200; +SET u3 = 300; +SET u4 = -300; +SELECT u1, u2, u3, u4; +SET u1 = 10 * 10 * 10; +SET u2 = -10 * 10 * 10; +SET u3 = sign(10 * 10) * 10 * 20; +SET u4 = sign(-10 * 10) * -10 * 20; +SELECT u1, u2, u3, u4; +SET d1 = 1234; +SET d2 = 1234.12; +SET d3 = 1234.1234; +SELECT d1, d2, d3; +SET d1 = 12 * 100 + 34; +SET d2 = 12 * 100 + 34 + 0.12; +SET d3 = 12 * 100 + 34 + 0.1234; +SELECT d1, d2, d3; +END| +CREATE FUNCTION sp_vars_check_ret1() RETURNS TINYINT +BEGIN +RETURN 1e200; +END| +CREATE FUNCTION sp_vars_check_ret2() RETURNS TINYINT +BEGIN +RETURN 10 * 10 * 10; +END| +CREATE FUNCTION sp_vars_check_ret3() RETURNS TINYINT +BEGIN +RETURN 'Hello, world'; +END| +CREATE FUNCTION sp_vars_check_ret4() RETURNS DECIMAL(64, 2) +BEGIN +RETURN 12 * 10 + 34 + 0.1234; +END| + +--------------------------------------------------------------- +Calling the routines, created in TRADITIONAL mode. +--------------------------------------------------------------- + +CALL sp_vars_check_dflt(); +ERROR 22003: Out of range value adjusted for column 'v1' at row 1 +CALL sp_vars_check_assignment(); +ERROR 22003: Out of range value adjusted for column 'i1' at row 1 +SELECT sp_vars_check_ret1(); +ERROR 22003: Out of range value adjusted for column 'sp_vars_check_ret1()' at row 1 +SELECT sp_vars_check_ret2(); +ERROR 22003: Out of range value adjusted for column 'sp_vars_check_ret2()' at row 1 +SELECT sp_vars_check_ret3(); +ERROR HY000: Incorrect integer value: 'Hello, world' for column 'sp_vars_check_ret3()' at row 1 +SELECT sp_vars_check_ret4(); +sp_vars_check_ret4() +154.12 +Warnings: +Note 1265 Data truncated for column 'sp_vars_check_ret4()' at row 1 +SET @@sql_mode = 'ansi'; +DROP PROCEDURE sp_vars_check_dflt; +DROP PROCEDURE sp_vars_check_assignment; +DROP FUNCTION sp_vars_check_ret1; +DROP FUNCTION sp_vars_check_ret2; +DROP FUNCTION sp_vars_check_ret3; +DROP FUNCTION sp_vars_check_ret4; + +--------------------------------------------------------------- +BIT data type tests +--------------------------------------------------------------- + +DROP PROCEDURE IF EXISTS p1; +CREATE PROCEDURE p1() +BEGIN +DECLARE v1 BIT; +DECLARE v2 BIT(1); +DECLARE v3 BIT(3) DEFAULT b'101'; +DECLARE v4 BIT(64) DEFAULT 0x5555555555555555; +DECLARE v5 BIT(3); +DECLARE v6 BIT(64); +DECLARE v7 BIT(8) DEFAULT 128; +DECLARE v8 BIT(8) DEFAULT '128'; +DECLARE v9 BIT(8) DEFAULT ' 128'; +DECLARE v10 BIT(8) DEFAULT 'x 128'; +SET v1 = v4; +SET v2 = 0; +SET v5 = v4; # check overflow +SET v6 = v3; # check padding +SELECT HEX(v1); +SELECT HEX(v2); +SELECT HEX(v3); +SELECT HEX(v4); +SELECT HEX(v5); +SELECT HEX(v6); +SELECT HEX(v7); +SELECT HEX(v8); +SELECT HEX(v9); +SELECT HEX(v10); +END| +CALL p1(); +HEX(v1) +01 +HEX(v2) +00 +HEX(v3) +05 +HEX(v4) +5555555555555555 +HEX(v5) +07 +HEX(v6) +0000000000000005 +HEX(v7) +80 +HEX(v8) +FF +HEX(v9) +FF +HEX(v10) +FF +Warnings: +Warning 1264 Out of range value adjusted for column 'v8' at row 1 +Warning 1264 Out of range value adjusted for column 'v9' at row 1 +Warning 1264 Out of range value adjusted for column 'v10' at row 1 +Warning 1264 Out of range value adjusted for column 'v1' at row 1 +Warning 1264 Out of range value adjusted for column 'v5' at row 1 +DROP PROCEDURE p1; + +--------------------------------------------------------------- +CASE expression tests. +--------------------------------------------------------------- + +DROP PROCEDURE IF EXISTS p1; +Warnings: +Note 1305 PROCEDURE p1 does not exist +DROP PROCEDURE IF EXISTS p2; +Warnings: +Note 1305 PROCEDURE p2 does not exist +DROP TABLE IF EXISTS t1; +Warnings: +Note 1051 Unknown table 't1' +CREATE TABLE t1(log_msg VARCHAR(1024)); +CREATE PROCEDURE p1(arg VARCHAR(255)) +BEGIN +INSERT INTO t1 VALUES('p1: step1'); +CASE arg * 10 +WHEN 10 * 10 THEN +INSERT INTO t1 VALUES('p1: case1: on 10'); +WHEN 10 * 10 + 10 * 10 THEN +BEGIN +CASE arg / 10 +WHEN 1 THEN +INSERT INTO t1 VALUES('p1: case1: case2: on 1'); +WHEN 2 THEN +BEGIN +DECLARE i TINYINT DEFAULT 10; +WHILE i > 0 DO +INSERT INTO t1 VALUES(CONCAT('p1: case1: case2: loop: i: ', i)); +CASE MOD(i, 2) +WHEN 0 THEN +INSERT INTO t1 VALUES('p1: case1: case2: loop: i is even'); +WHEN 1 THEN +INSERT INTO t1 VALUES('p1: case1: case2: loop: i is odd'); +ELSE +INSERT INTO t1 VALUES('p1: case1: case2: loop: ERROR'); +END CASE; +SET i = i - 1; +END WHILE; +END; +ELSE +INSERT INTO t1 VALUES('p1: case1: case2: ERROR'); +END CASE; +CASE arg +WHEN 10 THEN +INSERT INTO t1 VALUES('p1: case1: case3: on 10'); +WHEN 20 THEN +INSERT INTO t1 VALUES('p1: case1: case3: on 20'); +ELSE +INSERT INTO t1 VALUES('p1: case1: case3: ERROR'); +END CASE; +END; +ELSE +INSERT INTO t1 VALUES('p1: case1: ERROR'); +END CASE; +CASE arg * 10 +WHEN 10 * 10 THEN +INSERT INTO t1 VALUES('p1: case4: on 10'); +WHEN 10 * 10 + 10 * 10 THEN +BEGIN +CASE arg / 10 +WHEN 1 THEN +INSERT INTO t1 VALUES('p1: case4: case5: on 1'); +WHEN 2 THEN +BEGIN +DECLARE i TINYINT DEFAULT 10; +WHILE i > 0 DO +INSERT INTO t1 VALUES(CONCAT('p1: case4: case5: loop: i: ', i)); +CASE MOD(i, 2) +WHEN 0 THEN +INSERT INTO t1 VALUES('p1: case4: case5: loop: i is even'); +WHEN 1 THEN +INSERT INTO t1 VALUES('p1: case4: case5: loop: i is odd'); +ELSE +INSERT INTO t1 VALUES('p1: case4: case5: loop: ERROR'); +END CASE; +SET i = i - 1; +END WHILE; +END; +ELSE +INSERT INTO t1 VALUES('p1: case4: case5: ERROR'); +END CASE; +CASE arg +WHEN 10 THEN +INSERT INTO t1 VALUES('p1: case4: case6: on 10'); +WHEN 20 THEN +INSERT INTO t1 VALUES('p1: case4: case6: on 20'); +ELSE +INSERT INTO t1 VALUES('p1: case4: case6: ERROR'); +END CASE; +END; +ELSE +INSERT INTO t1 VALUES('p1: case4: ERROR'); +END CASE; +END| +CREATE PROCEDURE p2() +BEGIN +DECLARE i TINYINT DEFAULT 3; +WHILE i > 0 DO +IF MOD(i, 2) = 0 THEN +SET @_test_session_var = 10; +ELSE +SET @_test_session_var = 'test'; +END IF; +CASE @_test_session_var +WHEN 10 THEN +INSERT INTO t1 VALUES('p2: case: numerical type'); +WHEN 'test' THEN +INSERT INTO t1 VALUES('p2: case: string type'); +ELSE +INSERT INTO t1 VALUES('p2: case: ERROR'); +END CASE; +SET i = i - 1; +END WHILE; +END| +CALL p1(10); +CALL p1(20); +CALL p2(); +SELECT * FROM t1; +log_msg +p1: step1 +p1: case1: on 10 +p1: case4: on 10 +p1: step1 +p1: case1: case2: loop: i: 10 +p1: case1: case2: loop: i is even +p1: case1: case2: loop: i: 9 +p1: case1: case2: loop: i is odd +p1: case1: case2: loop: i: 8 +p1: case1: case2: loop: i is even +p1: case1: case2: loop: i: 7 +p1: case1: case2: loop: i is odd +p1: case1: case2: loop: i: 6 +p1: case1: case2: loop: i is even +p1: case1: case2: loop: i: 5 +p1: case1: case2: loop: i is odd +p1: case1: case2: loop: i: 4 +p1: case1: case2: loop: i is even +p1: case1: case2: loop: i: 3 +p1: case1: case2: loop: i is odd +p1: case1: case2: loop: i: 2 +p1: case1: case2: loop: i is even +p1: case1: case2: loop: i: 1 +p1: case1: case2: loop: i is odd +p1: case1: case3: on 20 +p1: case4: case5: loop: i: 10 +p1: case4: case5: loop: i is even +p1: case4: case5: loop: i: 9 +p1: case4: case5: loop: i is odd +p1: case4: case5: loop: i: 8 +p1: case4: case5: loop: i is even +p1: case4: case5: loop: i: 7 +p1: case4: case5: loop: i is odd +p1: case4: case5: loop: i: 6 +p1: case4: case5: loop: i is even +p1: case4: case5: loop: i: 5 +p1: case4: case5: loop: i is odd +p1: case4: case5: loop: i: 4 +p1: case4: case5: loop: i is even +p1: case4: case5: loop: i: 3 +p1: case4: case5: loop: i is odd +p1: case4: case5: loop: i: 2 +p1: case4: case5: loop: i is even +p1: case4: case5: loop: i: 1 +p1: case4: case5: loop: i is odd +p1: case4: case6: on 20 +p2: case: string type +p2: case: numerical type +p2: case: string type +DROP PROCEDURE p1; +DROP PROCEDURE p2; +DROP TABLE t1; + +--------------------------------------------------------------- +BUG#14161 +--------------------------------------------------------------- + +DROP TABLE IF EXISTS t1; +DROP PROCEDURE IF EXISTS p1; +CREATE TABLE t1(col BIGINT UNSIGNED); +INSERT INTO t1 VALUE(18446744073709551614); +CREATE PROCEDURE p1(IN arg BIGINT UNSIGNED) +BEGIN +SELECT arg; +SELECT * FROM t1; +SELECT * FROM t1 WHERE col = arg; +END| +CALL p1(18446744073709551614); +arg +18446744073709551614 +col +18446744073709551614 +col +18446744073709551614 +DROP TABLE t1; +DROP PROCEDURE p1; + +--------------------------------------------------------------- +BUG#13705 +--------------------------------------------------------------- + +DROP PROCEDURE IF EXISTS p1; +CREATE PROCEDURE p1(x VARCHAR(10), y CHAR(3)) READS SQL DATA +BEGIN +SELECT x, y; +END| +CALL p1('alpha', 'abc'); +x y +alpha abc +CALL p1('alpha', 'abcdef'); +x y +alpha abc +Warnings: +Warning 1265 Data truncated for column 'y' at row 1 +DROP PROCEDURE p1; + +--------------------------------------------------------------- +BUG#13675 +--------------------------------------------------------------- + +DROP PROCEDURE IF EXISTS p1; +DROP TABLE IF EXISTS t1; +CREATE PROCEDURE p1(x DATETIME) +BEGIN +CREATE TABLE t1 SELECT x; +SHOW CREATE TABLE t1; +DROP TABLE t1; +END| +CALL p1(NOW()); +Table Create Table +t1 CREATE TABLE "t1" ( + "x" varbinary(19) default NULL +) +CALL p1('test'); +Table Create Table +t1 CREATE TABLE "t1" ( + "x" varbinary(19) default NULL +) +Warnings: +Warning 1264 Out of range value adjusted for column 'x' at row 1 +DROP PROCEDURE p1; + +--------------------------------------------------------------- +BUG#12976 +--------------------------------------------------------------- + +DROP TABLE IF EXISTS t1; +DROP PROCEDURE IF EXISTS p1; +DROP PROCEDURE IF EXISTS p2; +CREATE TABLE t1(b BIT(1)); +INSERT INTO t1(b) VALUES(b'0'), (b'1'); +CREATE PROCEDURE p1() +BEGIN +SELECT HEX(b), +b = 0, +b = FALSE, +b IS FALSE, +b = 1, +b = TRUE, +b IS TRUE +FROM t1; +END| +CREATE PROCEDURE p2() +BEGIN +DECLARE vb BIT(1); +SELECT b INTO vb FROM t1 WHERE b = 0; +SELECT HEX(vb), +vb = 0, +vb = FALSE, +vb IS FALSE, +vb = 1, +vb = TRUE, +vb IS TRUE; +SELECT b INTO vb FROM t1 WHERE b = 1; +SELECT HEX(vb), +vb = 0, +vb = FALSE, +vb IS FALSE, +vb = 1, +vb = TRUE, +vb IS TRUE; +END| +call p1(); +HEX(b) b = 0 b = FALSE b IS FALSE b = 1 b = TRUE b IS TRUE + +0 1 1 1 0 0 0 +1 0 0 0 1 1 1 +call p2(); +HEX(vb) vb = 0 vb = FALSE vb IS FALSE vb = 1 vb = TRUE vb IS TRUE +00 1 1 1 0 0 0 +HEX(vb) vb = 0 vb = FALSE vb IS FALSE vb = 1 vb = TRUE vb IS TRUE +01 0 0 1 1 1 0 +DROP TABLE t1; +DROP PROCEDURE p1; +DROP PROCEDURE p2; + +--------------------------------------------------------------- +BUG#9572 +--------------------------------------------------------------- + +DROP PROCEDURE IF EXISTS p1; +DROP PROCEDURE IF EXISTS p2; +DROP PROCEDURE IF EXISTS p3; +DROP PROCEDURE IF EXISTS p4; +DROP PROCEDURE IF EXISTS p5; +DROP PROCEDURE IF EXISTS p6; +SET @@sql_mode = 'traditional'; +CREATE PROCEDURE p1() +BEGIN +DECLARE v TINYINT DEFAULT 1e200; +SELECT v; +END| +CREATE PROCEDURE p2() +BEGIN +DECLARE v DECIMAL(5) DEFAULT 1e200; +SELECT v; +END| +CREATE PROCEDURE p3() +BEGIN +DECLARE v CHAR(5) DEFAULT 'abcdef'; +SELECT v LIKE 'abc___'; +END| +CREATE PROCEDURE p4(arg VARCHAR(2)) +BEGIN +DECLARE var VARCHAR(1); +SET var := arg; +SELECT arg, var; +END| +CREATE PROCEDURE p5(arg CHAR(2)) +BEGIN +DECLARE var CHAR(1); +SET var := arg; +SELECT arg, var; +END| +CREATE PROCEDURE p6(arg DECIMAL(2)) +BEGIN +DECLARE var DECIMAL(1); +SET var := arg; +SELECT arg, var; +END| +CALL p1(); +ERROR 22003: Out of range value adjusted for column 'v' at row 1 +CALL p2(); +ERROR 22003: Out of range value adjusted for column 'v' at row 1 +CALL p3(); +ERROR 22001: Data too long for column 'v' at row 1 +CALL p4('aaa'); +ERROR 22001: Data too long for column 'arg' at row 1 +CALL p5('aa'); +ERROR 22001: Data too long for column 'var' at row 1 +CALL p6(10); +ERROR 22003: Out of range value adjusted for column 'var' at row 1 +SET @@sql_mode = 'ansi'; +DROP PROCEDURE p1; +DROP PROCEDURE p2; +DROP PROCEDURE p3; +DROP PROCEDURE p4; +DROP PROCEDURE p5; +DROP PROCEDURE p6; + +--------------------------------------------------------------- +BUG#9078 +--------------------------------------------------------------- + +DROP PROCEDURE IF EXISTS p1; +CREATE PROCEDURE p1 (arg DECIMAL(64,2)) +BEGIN +DECLARE var DECIMAL(64,2); +SET var = arg; +SELECT var; +END| +CALL p1(1929); +var +1929.00 +CALL p1(1929.00); +var +1929.00 +CALL p1(1929.003); +var +1929.00 +Warnings: +Note 1265 Data truncated for column 'arg' at row 1 +DROP PROCEDURE p1; + +--------------------------------------------------------------- +BUG#8768 +--------------------------------------------------------------- + +DROP FUNCTION IF EXISTS f1; +CREATE FUNCTION f1(arg TINYINT UNSIGNED) RETURNS TINYINT +BEGIN +RETURN arg; +END| +SELECT f1(-2500); +f1(-2500) +0 +Warnings: +Warning 1264 Out of range value adjusted for column 'arg' at row 1 +SET @@sql_mode = 'traditional'; +SELECT f1(-2500); +ERROR 22003: Out of range value adjusted for column 'arg' at row 1 +DROP FUNCTION f1; +CREATE FUNCTION f1(arg TINYINT UNSIGNED) RETURNS TINYINT +BEGIN +RETURN arg; +END| +SELECT f1(-2500); +ERROR 22003: Out of range value adjusted for column 'arg' at row 1 +SET @@sql_mode = 'ansi'; +DROP FUNCTION f1; + +--------------------------------------------------------------- +BUG#8769 +--------------------------------------------------------------- + +DROP FUNCTION IF EXISTS f1; +CREATE FUNCTION f1(arg MEDIUMINT) RETURNS MEDIUMINT +BEGIN +RETURN arg; +END| +SELECT f1(8388699); +f1(8388699) +8388607 +Warnings: +Warning 1264 Out of range value adjusted for column 'arg' at row 1 +SET @@sql_mode = 'traditional'; +SELECT f1(8388699); +ERROR 22003: Out of range value adjusted for column 'arg' at row 1 +DROP FUNCTION f1; +CREATE FUNCTION f1(arg MEDIUMINT) RETURNS MEDIUMINT +BEGIN +RETURN arg; +END| +SELECT f1(8388699); +ERROR 22003: Out of range value adjusted for column 'arg' at row 1 +SET @@sql_mode = 'ansi'; +DROP FUNCTION f1; + +--------------------------------------------------------------- +BUG#8702 +--------------------------------------------------------------- + +DROP PROCEDURE IF EXISTS p1; +DROP TABLE IF EXISTS t1; +CREATE TABLE t1(col VARCHAR(255)); +INSERT INTO t1(col) VALUES('Hello, world!'); +CREATE PROCEDURE p1() +BEGIN +DECLARE sp_var INTEGER; +SELECT col INTO sp_var FROM t1 LIMIT 1; +SET @user_var = sp_var; +SELECT sp_var; +SELECT @user_var; +END| +CALL p1(); +sp_var +0 +@user_var +0 +Warnings: +Warning 1264 Out of range value adjusted for column 'sp_var' at row 1 +DROP PROCEDURE p1; +DROP TABLE t1; + +--------------------------------------------------------------- +BUG#12903 +--------------------------------------------------------------- + +DROP FUNCTION IF EXISTS f1; +DROP TABLE IF EXISTS t1; +CREATE TABLE t1(txt VARCHAR(255)); +CREATE FUNCTION f1(arg VARCHAR(255)) RETURNS VARCHAR(255) +BEGIN +DECLARE v1 VARCHAR(255); +DECLARE v2 VARCHAR(255); +SET v1 = CONCAT(LOWER(arg), UPPER(arg)); +SET v2 = CONCAT(LOWER(v1), UPPER(v1)); +INSERT INTO t1 VALUES(v1), (v2); +RETURN CONCAT(LOWER(arg), UPPER(arg)); +END| +SELECT f1('_aBcDe_'); +f1('_aBcDe_') +_abcde__ABCDE_ +SELECT * FROM t1; +txt +_abcde__ABCDE_ +_abcde__abcde__ABCDE__ABCDE_ +DROP FUNCTION f1; +DROP TABLE t1; + +--------------------------------------------------------------- +BUG#13808 +--------------------------------------------------------------- + +DROP PROCEDURE IF EXISTS p1; +DROP PROCEDURE IF EXISTS p2; +DROP FUNCTION IF EXISTS f1; +CREATE PROCEDURE p1(arg ENUM('a', 'b')) +BEGIN +SELECT arg; +END| +CREATE PROCEDURE p2(arg ENUM('a', 'b')) +BEGIN +DECLARE var ENUM('c', 'd') DEFAULT arg; +SELECT arg, var; +END| +CREATE FUNCTION f1(arg ENUM('a', 'b')) RETURNS ENUM('c', 'd') +BEGIN +RETURN arg; +END| +CALL p1('c'); +arg + +Warnings: +Warning 1265 Data truncated for column 'arg' at row 1 +CALL p2('a'); +arg var +a +Warnings: +Warning 1265 Data truncated for column 'var' at row 1 +SELECT f1('a'); +f1('a') + +Warnings: +Warning 1265 Data truncated for column 'f1('a')' at row 1 +DROP PROCEDURE p1; +DROP PROCEDURE p2; +DROP FUNCTION f1; + +--------------------------------------------------------------- +BUG#13909 +--------------------------------------------------------------- + +DROP PROCEDURE IF EXISTS p1; +DROP PROCEDURE IF EXISTS p2; +CREATE PROCEDURE p1(arg VARCHAR(255)) +BEGIN +SELECT CHARSET(arg); +END| +CREATE PROCEDURE p2(arg VARCHAR(255) CHARACTER SET UTF8) +BEGIN +SELECT CHARSET(arg); +END| +CALL p1('t'); +CHARSET(arg) +latin1 +CALL p1(_UTF8 't'); +CHARSET(arg) +latin1 +CALL p2('t'); +CHARSET(arg) +utf8 +CALL p2(_LATIN1 't'); +CHARSET(arg) +utf8 +DROP PROCEDURE p1; +DROP PROCEDURE p2; + +--------------------------------------------------------------- +BUG#14188 +--------------------------------------------------------------- + +DROP PROCEDURE IF EXISTS p1; +CREATE PROCEDURE p1(arg1 BINARY(2), arg2 VARBINARY(2)) +BEGIN +DECLARE var1 BINARY(2) DEFAULT 0x41; +DECLARE var2 VARBINARY(2) DEFAULT 0x42; +SELECT HEX(arg1), HEX(arg2); +SELECT HEX(var1), HEX(var2); +END| +CALL p1(0x41, 0x42); +HEX(arg1) HEX(arg2) +4100 42 +HEX(var1) HEX(var2) +4100 42 +DROP PROCEDURE p1; + +--------------------------------------------------------------- +BUG#15148 +--------------------------------------------------------------- + +DROP PROCEDURE IF EXISTS p1; +DROP TABLE IF EXISTS t1; +CREATE TABLE t1(col1 TINYINT, col2 TINYINT); +INSERT INTO t1 VALUES(1, 2), (11, 12); +CREATE PROCEDURE p1(arg TINYINT) +BEGIN +SELECT arg; +END| +CALL p1((1, 2)); +ERROR 21000: Operand should contain 1 column(s) +CALL p1((SELECT * FROM t1 LIMIT 1)); +ERROR 21000: Operand should contain 1 column(s) +CALL p1((SELECT col1, col2 FROM t1 LIMIT 1)); +ERROR 21000: Operand should contain 1 column(s) +DROP PROCEDURE p1; +DROP TABLE t1; + +--------------------------------------------------------------- +BUG#13613 +--------------------------------------------------------------- + +DROP PROCEDURE IF EXISTS p1; +DROP FUNCTION IF EXISTS f1; +CREATE PROCEDURE p1(x VARCHAR(50)) +BEGIN +SET x = SUBSTRING(x, 1, 3); +SELECT x; +END| +CREATE FUNCTION f1(x VARCHAR(50)) RETURNS VARCHAR(50) +BEGIN +RETURN SUBSTRING(x, 1, 3); +END| +CALL p1('abcdef'); +x +abc +SELECT f1('ABCDEF'); +f1('ABCDEF') +ABC +DROP PROCEDURE p1; +DROP FUNCTION f1; + +--------------------------------------------------------------- +BUG#13665 +--------------------------------------------------------------- + +DROP FUNCTION IF EXISTS f1; +CREATE FUNCTION f1() RETURNS VARCHAR(20000) +BEGIN +DECLARE var VARCHAR(2000); +SET var = ''; +SET var = CONCAT(var, 'abc'); +SET var = CONCAT(var, ''); +RETURN var; +END| +SELECT f1(); +f1() +abc +DROP FUNCTION f1; diff --git a/mysql-test/r/sp.result b/mysql-test/r/sp.result index 66752b79204..2725c42ccbe 100644 --- a/mysql-test/r/sp.result +++ b/mysql-test/r/sp.result @@ -248,13 +248,13 @@ return i+1| call sub1("sub1a", (select 7))| call sub1("sub1b", (select max(i) from t2))| call sub1("sub1c", (select i,d from t2 limit 1))| +ERROR 21000: Operand should contain 1 column(s) call sub1("sub1d", (select 1 from (select 1) a))| call sub2("sub2")| select * from t1| id data sub1a 7 sub1b 3 -sub1c 1 sub1d 1 sub2 6 select sub3((select max(i) from t2))| @@ -2686,7 +2686,7 @@ call bug8937()| s x y z 16 3 1 6 a -3.2000 +3.2 drop procedure bug8937| delete from t1| drop procedure if exists bug6900| @@ -2890,21 +2890,30 @@ create function bug9775(v1 char(1)) returns enum('a','b') return v1| select bug9775('a'),bug9775('b'),bug9775('c')| bug9775('a') bug9775('b') bug9775('c') a b +Warnings: +Warning 1265 Data truncated for column 'bug9775('c')' at row 1 drop function bug9775| create function bug9775(v1 int) returns enum('a','b') return v1| select bug9775(1),bug9775(2),bug9775(3)| bug9775(1) bug9775(2) bug9775(3) a b +Warnings: +Warning 1265 Data truncated for column 'bug9775(3)' at row 1 drop function bug9775| create function bug9775(v1 char(1)) returns set('a','b') return v1| select bug9775('a'),bug9775('b'),bug9775('a,b'),bug9775('c')| bug9775('a') bug9775('b') bug9775('a,b') bug9775('c') -a b a,b +a b a +Warnings: +Warning 1265 Data truncated for column 'v1' at row 1 +Warning 1265 Data truncated for column 'bug9775('c')' at row 1 drop function bug9775| create function bug9775(v1 int) returns set('a','b') return v1| select bug9775(1),bug9775(2),bug9775(3),bug9775(4)| bug9775(1) bug9775(2) bug9775(3) bug9775(4) a b a,b +Warnings: +Warning 1265 Data truncated for column 'bug9775(4)' at row 1 drop function bug9775| drop function if exists bug8861| create function bug8861(v1 int) returns year return v1| @@ -2927,12 +2936,10 @@ create procedure bug9004_2(x char(16)) call bug9004_1(x)| call bug9004_1('12345678901234567')| Warnings: -Warning 1265 Data truncated for column 'id' at row 1 -Warning 1265 Data truncated for column 'id' at row 2 +Warning 1265 Data truncated for column 'x' at row 1 call bug9004_2('12345678901234567890')| Warnings: -Warning 1265 Data truncated for column 'id' at row 1 -Warning 1265 Data truncated for column 'id' at row 2 +Warning 1265 Data truncated for column 'x' at row 1 delete from t1| drop procedure bug9004_1| drop procedure bug9004_2| @@ -3527,14 +3534,15 @@ end| call bug12589_1()| Table Create Table tm1 CREATE TEMPORARY TABLE `tm1` ( - `spv1` decimal(1,0) unsigned default NULL + `spv1` decimal(3,3) default NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 Warnings: -Warning 1292 Truncated incorrect DECIMAL value: 'test' +Warning 1264 Out of range value adjusted for column 'spv1' at row 1 +Warning 1366 Incorrect decimal value: 'test' for column 'spv1' at row 1 call bug12589_2()| Table Create Table tm1 CREATE TEMPORARY TABLE `tm1` ( - `spv1` decimal(6,3) unsigned default NULL + `spv1` decimal(6,3) default NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 call bug12589_3()| Table Create Table @@ -4016,34 +4024,37 @@ create procedure bug14643_1() begin declare continue handler for sqlexception select 'boo' as 'Handler'; begin -declare v int default x; +declare v int default undefined_var; if v = 1 then select 1; else -select 2; +select v, isnull(v); end if; end; end| create procedure bug14643_2() begin declare continue handler for sqlexception select 'boo' as 'Handler'; -case x +case undefined_var when 1 then select 1; else select 2; end case; +select undefined_var; end| call bug14643_1()| Handler boo -2 -2 +v isnull(v) +NULL 1 call bug14643_2()| Handler boo 2 2 +Handler +boo drop procedure bug14643_1| drop procedure bug14643_2| drop procedure if exists bug14304| diff --git a/mysql-test/r/sum_distinct-big.result b/mysql-test/r/sum_distinct-big.result index 06bfc6b1f78..9b55d59ab91 100644 --- a/mysql-test/r/sum_distinct-big.result +++ b/mysql-test/r/sum_distinct-big.result @@ -1,5 +1,4 @@ -using_big_test -0 +DROP TABLE IF EXISTS t1, t2; CREATE TABLE t1 (id INTEGER); CREATE TABLE t2 (id INTEGER); INSERT INTO t1 (id) VALUES (1), (1), (1),(1); @@ -40,19 +39,19 @@ AVG(DISTINCT id) 512.5000 SELECT SUM(DISTINCT id)/COUNT(DISTINCT id) FROM t1 GROUP BY id % 13; SUM(DISTINCT id)/COUNT(DISTINCT id) -513.50000 -508.00000 -509.00000 -510.00000 -511.00000 -512.00000 -513.00000 -514.00000 -515.00000 -516.00000 -517.00000 -511.50000 -512.50000 +513.5000 +508.0000 +509.0000 +510.0000 +511.0000 +512.0000 +513.0000 +514.0000 +515.0000 +516.0000 +517.0000 +511.5000 +512.5000 INSERT INTO t1 SELECT id+1024 FROM t1; INSERT INTO t1 SELECT id+2048 FROM t1; INSERT INTO t1 SELECT id+4096 FROM t1; diff --git a/mysql-test/r/type_newdecimal-big.result b/mysql-test/r/type_newdecimal-big.result index e95f2f3f781..4e694702d14 100644 --- a/mysql-test/r/type_newdecimal-big.result +++ b/mysql-test/r/type_newdecimal-big.result @@ -1,11 +1,26 @@ drop procedure if exists sp1; -create procedure sp1 () begin -declare v1, v2, v3, v4 decimal(16,12); declare v5 int; -set v1 = 1; set v2 = 2; set v3 = 1000000000000; set v4 = 2000000000000; set v5 = 0; -while v5 < 100000 do -set v1 = v1 + 0.000000000001; set v2 = v2 - 0.000000000001; set v3 = v3 + 1; set v4 = v4 - 1; set v5 = v5 + 1; -end while; select v1, v2, v3 * 0.000000000001, v4 * 0.000000000001; end;// +CREATE PROCEDURE sp1() +BEGIN +DECLARE v1, v2, v3, v4 DECIMAL(28,12); +DECLARE v3_2, v4_2 DECIMAL(28, 12); +DECLARE counter INT; +SET v1 = 1; +SET v2 = 2; +SET v3 = 1000000000000; +SET v4 = 2000000000000; +SET counter = 0; +WHILE counter < 100000 DO +SET v1 = v1 + 0.000000000001; +SET v2 = v2 - 0.000000000001; +SET v3 = v3 + 1; +SET v4 = v4 - 1; +SET counter = counter + 1; +END WHILE; +SET v3_2 = v3 * 0.000000000001; +SET v4_2 = v4 * 0.000000000001; +SELECT v1, v2, v3, v3_2, v4, v4_2; +END// call sp1()// -v1 v2 v3 * 0.000000000001 v4 * 0.000000000001 -1.000000100000 1.999999900000 1.000000100000 1.999999900000 +v1 v2 v3 v3_2 v4 v4_2 +1.000000100000 1.999999900000 1000000100000.000000000000 1.000000100000 1999999900000.000000000000 1.999999900000 drop procedure sp1; diff --git a/mysql-test/sp-vars.test b/mysql-test/sp-vars.test new file mode 100644 index 00000000000..81504904797 --- /dev/null +++ b/mysql-test/sp-vars.test @@ -0,0 +1,1273 @@ +########################################################################### +# +# Cleanup. +# +########################################################################### + +--disable_warnings + +# Drop stored routines (if any) for general SP-vars test cases. These routines +# are created in include/sp-vars.inc file. + +DROP PROCEDURE IF EXISTS sp_vars_check_dflt; +DROP PROCEDURE IF EXISTS sp_vars_check_assignment; +DROP FUNCTION IF EXISTS sp_vars_check_ret1; +DROP FUNCTION IF EXISTS sp_vars_check_ret2; +DROP FUNCTION IF EXISTS sp_vars_check_ret3; +DROP FUNCTION IF EXISTS sp_vars_check_ret4; + +--enable_warnings + +########################################################################### +# +# Some general tests for SP-vars functionality. +# +########################################################################### + +# Create the procedure in ANSI mode. Check that all necessary warnings are +# emitted properly. + +SET @@sql_mode = 'ansi'; + +--source include/sp-vars.inc + +--echo +--echo --------------------------------------------------------------- +--echo Calling the routines, created in ANSI mode. +--echo --------------------------------------------------------------- +--echo + +CALL sp_vars_check_dflt(); + +CALL sp_vars_check_assignment(); + +SELECT sp_vars_check_ret1(); + +SELECT sp_vars_check_ret2(); + +SELECT sp_vars_check_ret3(); + +SELECT sp_vars_check_ret4(); + +# Check that changing sql_mode after creating a store procedure does not +# matter. + +SET @@sql_mode = 'traditional'; + +--echo +--echo --------------------------------------------------------------- +--echo Calling in TRADITIONAL mode the routines, created in ANSI mode. +--echo --------------------------------------------------------------- +--echo + +CALL sp_vars_check_dflt(); + +CALL sp_vars_check_assignment(); + +SELECT sp_vars_check_ret1(); + +SELECT sp_vars_check_ret2(); + +SELECT sp_vars_check_ret3(); + +SELECT sp_vars_check_ret4(); + +# Create the procedure in TRADITIONAL mode. Check that error will be thrown on +# execution. + +DROP PROCEDURE sp_vars_check_dflt; +DROP PROCEDURE sp_vars_check_assignment; +DROP FUNCTION sp_vars_check_ret1; +DROP FUNCTION sp_vars_check_ret2; +DROP FUNCTION sp_vars_check_ret3; +DROP FUNCTION sp_vars_check_ret4; + +--source include/sp-vars.inc + +--echo +--echo --------------------------------------------------------------- +--echo Calling the routines, created in TRADITIONAL mode. +--echo --------------------------------------------------------------- +--echo + +--error ER_WARN_DATA_OUT_OF_RANGE +CALL sp_vars_check_dflt(); + +--error ER_WARN_DATA_OUT_OF_RANGE +CALL sp_vars_check_assignment(); + +--error ER_WARN_DATA_OUT_OF_RANGE +SELECT sp_vars_check_ret1(); + +--error ER_WARN_DATA_OUT_OF_RANGE +SELECT sp_vars_check_ret2(); + +--error ER_TRUNCATED_WRONG_VALUE_FOR_FIELD +SELECT sp_vars_check_ret3(); + +# TODO: Is it an error, that only a warning is emitted here? Check the same +# behaviour with tables. + +SELECT sp_vars_check_ret4(); + +SET @@sql_mode = 'ansi'; + +# +# Cleanup. +# + +DROP PROCEDURE sp_vars_check_dflt; +DROP PROCEDURE sp_vars_check_assignment; +DROP FUNCTION sp_vars_check_ret1; +DROP FUNCTION sp_vars_check_ret2; +DROP FUNCTION sp_vars_check_ret3; +DROP FUNCTION sp_vars_check_ret4; + +########################################################################### +# +# Tests for BIT data type. +# +########################################################################### + +--echo +--echo --------------------------------------------------------------- +--echo BIT data type tests +--echo --------------------------------------------------------------- +--echo + +# +# Prepare. +# + +--disable_warnings +DROP PROCEDURE IF EXISTS p1; +--enable_warnings + +# +# Test case. +# + +delimiter |; +CREATE PROCEDURE p1() +BEGIN + DECLARE v1 BIT; + DECLARE v2 BIT(1); + DECLARE v3 BIT(3) DEFAULT b'101'; + DECLARE v4 BIT(64) DEFAULT 0x5555555555555555; + DECLARE v5 BIT(3); + DECLARE v6 BIT(64); + DECLARE v7 BIT(8) DEFAULT 128; + DECLARE v8 BIT(8) DEFAULT '128'; + DECLARE v9 BIT(8) DEFAULT ' 128'; + DECLARE v10 BIT(8) DEFAULT 'x 128'; + + SET v1 = v4; + SET v2 = 0; + SET v5 = v4; # check overflow + SET v6 = v3; # check padding + + SELECT HEX(v1); + SELECT HEX(v2); + SELECT HEX(v3); + SELECT HEX(v4); + SELECT HEX(v5); + SELECT HEX(v6); + SELECT HEX(v7); + SELECT HEX(v8); + SELECT HEX(v9); + SELECT HEX(v10); +END| +delimiter ;| + +CALL p1(); + +# +# Cleanup. +# + +DROP PROCEDURE p1; + +########################################################################### +# +# Tests for CASE statements functionality: +# - test for general functionality (scopes, nested cases, CASE in loops); +# - test that if type of the CASE expression is changed on each iteration, +# the execution will be correct. +# +########################################################################### + +--echo +--echo --------------------------------------------------------------- +--echo CASE expression tests. +--echo --------------------------------------------------------------- +--echo + +# +# Prepare. +# + +DROP PROCEDURE IF EXISTS p1; +DROP PROCEDURE IF EXISTS p2; +DROP TABLE IF EXISTS t1; + +# +# Test case. +# + +CREATE TABLE t1(log_msg VARCHAR(1024)); + +delimiter |; + +CREATE PROCEDURE p1(arg VARCHAR(255)) +BEGIN + INSERT INTO t1 VALUES('p1: step1'); + + CASE arg * 10 + WHEN 10 * 10 THEN + INSERT INTO t1 VALUES('p1: case1: on 10'); + WHEN 10 * 10 + 10 * 10 THEN + BEGIN + CASE arg / 10 + WHEN 1 THEN + INSERT INTO t1 VALUES('p1: case1: case2: on 1'); + WHEN 2 THEN + BEGIN + DECLARE i TINYINT DEFAULT 10; + + WHILE i > 0 DO + INSERT INTO t1 VALUES(CONCAT('p1: case1: case2: loop: i: ', i)); + + CASE MOD(i, 2) + WHEN 0 THEN + INSERT INTO t1 VALUES('p1: case1: case2: loop: i is even'); + WHEN 1 THEN + INSERT INTO t1 VALUES('p1: case1: case2: loop: i is odd'); + ELSE + INSERT INTO t1 VALUES('p1: case1: case2: loop: ERROR'); + END CASE; + + SET i = i - 1; + END WHILE; + END; + ELSE + INSERT INTO t1 VALUES('p1: case1: case2: ERROR'); + END CASE; + + CASE arg + WHEN 10 THEN + INSERT INTO t1 VALUES('p1: case1: case3: on 10'); + WHEN 20 THEN + INSERT INTO t1 VALUES('p1: case1: case3: on 20'); + ELSE + INSERT INTO t1 VALUES('p1: case1: case3: ERROR'); + END CASE; + END; + ELSE + INSERT INTO t1 VALUES('p1: case1: ERROR'); + END CASE; + + CASE arg * 10 + WHEN 10 * 10 THEN + INSERT INTO t1 VALUES('p1: case4: on 10'); + WHEN 10 * 10 + 10 * 10 THEN + BEGIN + CASE arg / 10 + WHEN 1 THEN + INSERT INTO t1 VALUES('p1: case4: case5: on 1'); + WHEN 2 THEN + BEGIN + DECLARE i TINYINT DEFAULT 10; + + WHILE i > 0 DO + INSERT INTO t1 VALUES(CONCAT('p1: case4: case5: loop: i: ', i)); + + CASE MOD(i, 2) + WHEN 0 THEN + INSERT INTO t1 VALUES('p1: case4: case5: loop: i is even'); + WHEN 1 THEN + INSERT INTO t1 VALUES('p1: case4: case5: loop: i is odd'); + ELSE + INSERT INTO t1 VALUES('p1: case4: case5: loop: ERROR'); + END CASE; + + SET i = i - 1; + END WHILE; + END; + ELSE + INSERT INTO t1 VALUES('p1: case4: case5: ERROR'); + END CASE; + + CASE arg + WHEN 10 THEN + INSERT INTO t1 VALUES('p1: case4: case6: on 10'); + WHEN 20 THEN + INSERT INTO t1 VALUES('p1: case4: case6: on 20'); + ELSE + INSERT INTO t1 VALUES('p1: case4: case6: ERROR'); + END CASE; + END; + ELSE + INSERT INTO t1 VALUES('p1: case4: ERROR'); + END CASE; +END| + +CREATE PROCEDURE p2() +BEGIN + DECLARE i TINYINT DEFAULT 3; + + WHILE i > 0 DO + IF MOD(i, 2) = 0 THEN + SET @_test_session_var = 10; + ELSE + SET @_test_session_var = 'test'; + END IF; + + CASE @_test_session_var + WHEN 10 THEN + INSERT INTO t1 VALUES('p2: case: numerical type'); + WHEN 'test' THEN + INSERT INTO t1 VALUES('p2: case: string type'); + ELSE + INSERT INTO t1 VALUES('p2: case: ERROR'); + END CASE; + + SET i = i - 1; + END WHILE; +END| + +delimiter ;| + +CALL p1(10); +CALL p1(20); + +CALL p2(); + +SELECT * FROM t1; + +# +# Cleanup. +# + +DROP PROCEDURE p1; +DROP PROCEDURE p2; +DROP TABLE t1; + +########################################################################### +# +# Test case for BUG#14161: Stored procedure cannot retrieve bigint unsigned. +# +########################################################################### + +--echo +--echo --------------------------------------------------------------- +--echo BUG#14161 +--echo --------------------------------------------------------------- +--echo + +# +# Prepare. +# + +--disable_warnings +DROP TABLE IF EXISTS t1; +DROP PROCEDURE IF EXISTS p1; +--enable_warnings + +# +# Test case. +# + +CREATE TABLE t1(col BIGINT UNSIGNED); + +INSERT INTO t1 VALUE(18446744073709551614); + +delimiter |; +CREATE PROCEDURE p1(IN arg BIGINT UNSIGNED) +BEGIN + SELECT arg; + SELECT * FROM t1; + SELECT * FROM t1 WHERE col = arg; +END| +delimiter ;| + +CALL p1(18446744073709551614); + +# +# Cleanup. +# + +DROP TABLE t1; +DROP PROCEDURE p1; + +########################################################################### +# +# Test case for BUG#13705: parameters to stored procedures are not verified. +# +########################################################################### + +--echo +--echo --------------------------------------------------------------- +--echo BUG#13705 +--echo --------------------------------------------------------------- +--echo + +# +# Prepare. +# + +--disable_warnings +DROP PROCEDURE IF EXISTS p1; +--enable_warnings + +# +# Test case. +# + +delimiter |; +CREATE PROCEDURE p1(x VARCHAR(10), y CHAR(3)) READS SQL DATA +BEGIN + SELECT x, y; +END| +delimiter ;| + +CALL p1('alpha', 'abc'); +CALL p1('alpha', 'abcdef'); + +# +# Cleanup. +# + +DROP PROCEDURE p1; + +########################################################################### +# +# Test case for BUG#13675: DATETIME/DATE type in store proc param seems to be +# converted as varbinary. +# +# TODO: test case failed. +# +########################################################################### + +--echo +--echo --------------------------------------------------------------- +--echo BUG#13675 +--echo --------------------------------------------------------------- +--echo + +# +# Prepare. +# + +--disable_warnings +DROP PROCEDURE IF EXISTS p1; +DROP TABLE IF EXISTS t1; +--enable_warnings + +# +# Test case. +# + +delimiter |; +CREATE PROCEDURE p1(x DATETIME) +BEGIN + CREATE TABLE t1 SELECT x; + SHOW CREATE TABLE t1; + DROP TABLE t1; +END| +delimiter ;| + +CALL p1(NOW()); + +CALL p1('test'); + +# +# Cleanup. +# + +DROP PROCEDURE p1; + +########################################################################### +# +# Test case for BUG#12976: Boolean values reversed in stored procedures? +# +# TODO: test case failed. +# +########################################################################### + +--echo +--echo --------------------------------------------------------------- +--echo BUG#12976 +--echo --------------------------------------------------------------- +--echo + +# +# Prepare. +# + +--disable_warnings +DROP TABLE IF EXISTS t1; +DROP PROCEDURE IF EXISTS p1; +DROP PROCEDURE IF EXISTS p2; +--enable_warnings + +# +# Test case. +# + +CREATE TABLE t1(b BIT(1)); + +INSERT INTO t1(b) VALUES(b'0'), (b'1'); + +delimiter |; +CREATE PROCEDURE p1() +BEGIN + SELECT HEX(b), + b = 0, + b = FALSE, + b IS FALSE, + b = 1, + b = TRUE, + b IS TRUE + FROM t1; +END| + +CREATE PROCEDURE p2() +BEGIN + DECLARE vb BIT(1); + SELECT b INTO vb FROM t1 WHERE b = 0; + + SELECT HEX(vb), + vb = 0, + vb = FALSE, + vb IS FALSE, + vb = 1, + vb = TRUE, + vb IS TRUE; + + SELECT b INTO vb FROM t1 WHERE b = 1; + + SELECT HEX(vb), + vb = 0, + vb = FALSE, + vb IS FALSE, + vb = 1, + vb = TRUE, + vb IS TRUE; +END| +delimiter ;| + +# The expected and correct result. + +call p1(); + +# The wrong result. Note that only hex(vb) works, but is printed with two +# digits for some reason in this case. + +call p2(); + +# +# Cleanup. +# + +DROP TABLE t1; +DROP PROCEDURE p1; +DROP PROCEDURE p2; + +########################################################################### +# +# Test case for BUG#9572: Stored procedures: variable type declarations +# ignored. +# +########################################################################### + +--echo +--echo --------------------------------------------------------------- +--echo BUG#9572 +--echo --------------------------------------------------------------- +--echo + +# +# Prepare. +# + +--disable_warnings +DROP PROCEDURE IF EXISTS p1; +DROP PROCEDURE IF EXISTS p2; +DROP PROCEDURE IF EXISTS p3; + +DROP PROCEDURE IF EXISTS p4; +DROP PROCEDURE IF EXISTS p5; +DROP PROCEDURE IF EXISTS p6; +--enable_warnings + +# +# Test case. +# + +SET @@sql_mode = 'traditional'; + +delimiter |; + +CREATE PROCEDURE p1() +BEGIN + DECLARE v TINYINT DEFAULT 1e200; + SELECT v; +END| + +CREATE PROCEDURE p2() +BEGIN + DECLARE v DECIMAL(5) DEFAULT 1e200; + SELECT v; +END| + +CREATE PROCEDURE p3() +BEGIN + DECLARE v CHAR(5) DEFAULT 'abcdef'; + SELECT v LIKE 'abc___'; +END| + +CREATE PROCEDURE p4(arg VARCHAR(2)) +BEGIN + DECLARE var VARCHAR(1); + SET var := arg; + SELECT arg, var; +END| + +CREATE PROCEDURE p5(arg CHAR(2)) +BEGIN + DECLARE var CHAR(1); + SET var := arg; + SELECT arg, var; +END| + +CREATE PROCEDURE p6(arg DECIMAL(2)) +BEGIN + DECLARE var DECIMAL(1); + SET var := arg; + SELECT arg, var; +END| + +delimiter ;| + +--error ER_WARN_DATA_OUT_OF_RANGE +CALL p1(); +--error ER_WARN_DATA_OUT_OF_RANGE +CALL p2(); +--error ER_DATA_TOO_LONG +CALL p3(); + +--error ER_DATA_TOO_LONG +CALL p4('aaa'); +--error ER_DATA_TOO_LONG +CALL p5('aa'); +--error ER_WARN_DATA_OUT_OF_RANGE +CALL p6(10); + +# +# Cleanup. +# + +SET @@sql_mode = 'ansi'; + +DROP PROCEDURE p1; +DROP PROCEDURE p2; +DROP PROCEDURE p3; + +DROP PROCEDURE p4; +DROP PROCEDURE p5; +DROP PROCEDURE p6; + +########################################################################### +# +# Test case for BUG#9078: STORED PROCDURE: Decimal digits are not displayed +# when we use DECIMAL datatype. +# +########################################################################### + +--echo +--echo --------------------------------------------------------------- +--echo BUG#9078 +--echo --------------------------------------------------------------- +--echo + +# +# Prepare. +# + +--disable_warnings +DROP PROCEDURE IF EXISTS p1; +--enable_warnings + +# +# Test case. +# + +delimiter |; +CREATE PROCEDURE p1 (arg DECIMAL(64,2)) +BEGIN + DECLARE var DECIMAL(64,2); + + SET var = arg; + SELECT var; +END| +delimiter ;| + +CALL p1(1929); +CALL p1(1929.00); +CALL p1(1929.003); + +# +# Cleanup. +# + +DROP PROCEDURE p1; + +########################################################################### +# +# Test case for BUG#8768: Functions: For any unsigned data type, -ve values can +# be passed and returned. +# +# TODO: there is a bug here -- the function created in ANSI mode should not +# throw errors instead of warnings if called in TRADITIONAL mode. +# +########################################################################### + +--echo +--echo --------------------------------------------------------------- +--echo BUG#8768 +--echo --------------------------------------------------------------- +--echo + +# +# Prepare. +# + +--disable_warnings +DROP FUNCTION IF EXISTS f1; +--enable_warnings + +# +# Test case. +# + +# Create a function in ANSI mode. + +delimiter |; +CREATE FUNCTION f1(arg TINYINT UNSIGNED) RETURNS TINYINT +BEGIN + RETURN arg; +END| +delimiter ;| + +SELECT f1(-2500); + +# Call in TRADITIONAL mode the function created in ANSI mode. + +SET @@sql_mode = 'traditional'; + +# TODO: a warning should be emitted here. +--error ER_WARN_DATA_OUT_OF_RANGE +SELECT f1(-2500); + +# Recreate the function in TRADITIONAL mode. + +DROP FUNCTION f1; + +delimiter |; +CREATE FUNCTION f1(arg TINYINT UNSIGNED) RETURNS TINYINT +BEGIN + RETURN arg; +END| +delimiter ;| + +--error ER_WARN_DATA_OUT_OF_RANGE +SELECT f1(-2500); + +# +# Cleanup. +# + +SET @@sql_mode = 'ansi'; + +DROP FUNCTION f1; + +########################################################################### +# +# Test case for BUG#8769: Functions: For Int datatypes, out of range values can +# be passed and returned. +# +# TODO: there is a bug here -- the function created in ANSI mode should not +# throw errors instead of warnings if called in TRADITIONAL mode. +# +########################################################################### + +--echo +--echo --------------------------------------------------------------- +--echo BUG#8769 +--echo --------------------------------------------------------------- +--echo + +# +# Prepare. +# + +--disable_warnings +DROP FUNCTION IF EXISTS f1; +--enable_warnings + +# +# Test case. +# + +# Create a function in ANSI mode. + +delimiter |; +CREATE FUNCTION f1(arg MEDIUMINT) RETURNS MEDIUMINT +BEGIN + RETURN arg; +END| +delimiter ;| + +SELECT f1(8388699); + +# Call in TRADITIONAL mode the function created in ANSI mode. + +SET @@sql_mode = 'traditional'; + +# TODO: a warning should be emitted here. +--error ER_WARN_DATA_OUT_OF_RANGE +SELECT f1(8388699); + +# Recreate the function in TRADITIONAL mode. + +DROP FUNCTION f1; + +delimiter |; +CREATE FUNCTION f1(arg MEDIUMINT) RETURNS MEDIUMINT +BEGIN + RETURN arg; +END| +delimiter ;| + +--error ER_WARN_DATA_OUT_OF_RANGE +SELECT f1(8388699); + +# +# Cleanup. +# + +SET @@sql_mode = 'ansi'; + +DROP FUNCTION f1; + +########################################################################### +# +# Test case for BUG#8702: Stored Procedures: No Error/Warning shown for +# inappropriate data type matching. +# +########################################################################### + +--echo +--echo --------------------------------------------------------------- +--echo BUG#8702 +--echo --------------------------------------------------------------- +--echo + +# +# Prepare. +# + +--disable_warnings +DROP PROCEDURE IF EXISTS p1; +DROP TABLE IF EXISTS t1; +--enable_warnings + +# +# Test case. +# + +CREATE TABLE t1(col VARCHAR(255)); + +INSERT INTO t1(col) VALUES('Hello, world!'); + +delimiter |; +CREATE PROCEDURE p1() +BEGIN + DECLARE sp_var INTEGER; + + SELECT col INTO sp_var FROM t1 LIMIT 1; + SET @user_var = sp_var; + + SELECT sp_var; + SELECT @user_var; +END| +delimiter ;| + +CALL p1(); + +# +# Cleanup. +# + +DROP PROCEDURE p1; +DROP TABLE t1; + +########################################################################### +# +# Test case for BUG#12903: upper function does not work inside a function. +# +########################################################################### + +--echo +--echo --------------------------------------------------------------- +--echo BUG#12903 +--echo --------------------------------------------------------------- +--echo + +# +# Prepare. +# + +--disable_warnings +DROP FUNCTION IF EXISTS f1; +DROP TABLE IF EXISTS t1; +--enable_warnings + +# +# Test case. +# + +CREATE TABLE t1(txt VARCHAR(255)); + +delimiter |; +CREATE FUNCTION f1(arg VARCHAR(255)) RETURNS VARCHAR(255) +BEGIN + DECLARE v1 VARCHAR(255); + DECLARE v2 VARCHAR(255); + + SET v1 = CONCAT(LOWER(arg), UPPER(arg)); + SET v2 = CONCAT(LOWER(v1), UPPER(v1)); + + INSERT INTO t1 VALUES(v1), (v2); + + RETURN CONCAT(LOWER(arg), UPPER(arg)); +END| +delimiter ;| + +SELECT f1('_aBcDe_'); + +SELECT * FROM t1; + +# +# Cleanup. +# + +DROP FUNCTION f1; +DROP TABLE t1; + +########################################################################### +# +# Test case for BUG#13808: ENUM type stored procedure parameter accepts +# non-enumerated data. +# +########################################################################### + +--echo +--echo --------------------------------------------------------------- +--echo BUG#13808 +--echo --------------------------------------------------------------- +--echo + +# +# Prepare. +# + +--disable_warnings +DROP PROCEDURE IF EXISTS p1; +DROP PROCEDURE IF EXISTS p2; +DROP FUNCTION IF EXISTS f1; +--enable_warnings + +# +# Test case. +# + +delimiter |; + +CREATE PROCEDURE p1(arg ENUM('a', 'b')) +BEGIN + SELECT arg; +END| + +CREATE PROCEDURE p2(arg ENUM('a', 'b')) +BEGIN + DECLARE var ENUM('c', 'd') DEFAULT arg; + + SELECT arg, var; +END| + +CREATE FUNCTION f1(arg ENUM('a', 'b')) RETURNS ENUM('c', 'd') +BEGIN + RETURN arg; +END| + +delimiter ;| + +CALL p1('c'); + +CALL p2('a'); + +SELECT f1('a'); + +# +# Cleanup. +# + +DROP PROCEDURE p1; +DROP PROCEDURE p2; +DROP FUNCTION f1; + +########################################################################### +# +# Test case for BUG#13909: Varchar Stored Procedure Parameter always BINARY +# string (ignores CHARACTER SET). +# +########################################################################### + +--echo +--echo --------------------------------------------------------------- +--echo BUG#13909 +--echo --------------------------------------------------------------- +--echo + +# +# Prepare. +# + +--disable_warnings +DROP PROCEDURE IF EXISTS p1; +DROP PROCEDURE IF EXISTS p2; +--enable_warnings + +# +# Test case. +# + +delimiter |; + +CREATE PROCEDURE p1(arg VARCHAR(255)) +BEGIN + SELECT CHARSET(arg); +END| + +CREATE PROCEDURE p2(arg VARCHAR(255) CHARACTER SET UTF8) +BEGIN + SELECT CHARSET(arg); +END| + +delimiter ;| + +CALL p1('t'); +CALL p1(_UTF8 't'); + + +CALL p2('t'); +CALL p2(_LATIN1 't'); + +# +# Cleanup. +# + +DROP PROCEDURE p1; +DROP PROCEDURE p2; + +########################################################################### +# +# Test case for BUG#14188: BINARY variables have no 0x00 padding. +# +########################################################################### + +--echo +--echo --------------------------------------------------------------- +--echo BUG#14188 +--echo --------------------------------------------------------------- +--echo + +# +# Prepare. +# + +--disable_warnings +DROP PROCEDURE IF EXISTS p1; +--enable_warnings + +# +# Test case. +# + +delimiter |; +CREATE PROCEDURE p1(arg1 BINARY(2), arg2 VARBINARY(2)) +BEGIN + DECLARE var1 BINARY(2) DEFAULT 0x41; + DECLARE var2 VARBINARY(2) DEFAULT 0x42; + + SELECT HEX(arg1), HEX(arg2); + SELECT HEX(var1), HEX(var2); +END| +delimiter ;| + +CALL p1(0x41, 0x42); + +# +# Cleanup. +# + +DROP PROCEDURE p1; + +########################################################################### +# +# Test case for BUG#15148: Stored procedure variables accept non-scalar values. +# +########################################################################### + +--echo +--echo --------------------------------------------------------------- +--echo BUG#15148 +--echo --------------------------------------------------------------- +--echo + +# +# Prepare. +# + +--disable_warnings +DROP PROCEDURE IF EXISTS p1; +DROP TABLE IF EXISTS t1; +--enable_warnings + +# +# Test case. +# + +CREATE TABLE t1(col1 TINYINT, col2 TINYINT); + +INSERT INTO t1 VALUES(1, 2), (11, 12); + +delimiter |; +CREATE PROCEDURE p1(arg TINYINT) +BEGIN + SELECT arg; +END| +delimiter ;| + +--error ER_OPERAND_COLUMNS +CALL p1((1, 2)); + +--error ER_OPERAND_COLUMNS +CALL p1((SELECT * FROM t1 LIMIT 1)); + +--error ER_OPERAND_COLUMNS +CALL p1((SELECT col1, col2 FROM t1 LIMIT 1)); + +# +# Cleanup. +# + +DROP PROCEDURE p1; +DROP TABLE t1; + +########################################################################### +# +# Test case for BUG#13613: substring function in stored procedure. +# +########################################################################### + +--echo +--echo --------------------------------------------------------------- +--echo BUG#13613 +--echo --------------------------------------------------------------- +--echo + +# +# Prepare. +# + +--disable_warnings +DROP PROCEDURE IF EXISTS p1; +DROP FUNCTION IF EXISTS f1; +--enable_warnings + +# +# Test case. +# + +delimiter |; + +CREATE PROCEDURE p1(x VARCHAR(50)) +BEGIN + SET x = SUBSTRING(x, 1, 3); + SELECT x; +END| + +CREATE FUNCTION f1(x VARCHAR(50)) RETURNS VARCHAR(50) +BEGIN + RETURN SUBSTRING(x, 1, 3); +END| + +delimiter ;| + +CALL p1('abcdef'); + +SELECT f1('ABCDEF'); + +# +# Cleanup. +# + +DROP PROCEDURE p1; +DROP FUNCTION f1; + +########################################################################### +# +# Test case for BUG#13665: concat with '' produce incorrect results in SP. +# +########################################################################### + +--echo +--echo --------------------------------------------------------------- +--echo BUG#13665 +--echo --------------------------------------------------------------- +--echo + +# +# Prepare. +# + +--disable_warnings +DROP FUNCTION IF EXISTS f1; +--enable_warnings + +# +# Test case. +# + +delimiter |; +CREATE FUNCTION f1() RETURNS VARCHAR(20000) +BEGIN + DECLARE var VARCHAR(2000); + + SET var = ''; + SET var = CONCAT(var, 'abc'); + SET var = CONCAT(var, ''); + + RETURN var; +END| +delimiter ;| + +SELECT f1(); + +# +# Cleanup. +# + +DROP FUNCTION f1; diff --git a/mysql-test/t/ctype_ujis.test b/mysql-test/t/ctype_ujis.test index c1d6c67387b..77d250b5c45 100644 --- a/mysql-test/t/ctype_ujis.test +++ b/mysql-test/t/ctype_ujis.test @@ -1170,7 +1170,7 @@ INSERT INTO t1 VALUES(_ujis 0xA4A2); DELIMITER |; CREATE PROCEDURE sp1() BEGIN - DECLARE a CHAR(1); + DECLARE a CHAR(2) CHARSET ujis; DECLARE cur1 CURSOR FOR SELECT c1 FROM t1; OPEN cur1; FETCH cur1 INTO a; diff --git a/mysql-test/t/schema.test b/mysql-test/t/schema.test index d9bd607b2db..a08d9b38935 100644 --- a/mysql-test/t/schema.test +++ b/mysql-test/t/schema.test @@ -1,6 +1,12 @@ # # Just a couple of tests to make sure that schema works. # +# Drop mysqltest1 database, as it can left from the previous tests. +# + +--disable_warnings +drop database if exists mysqltest1; +--enable_warnings create schema foo; show create schema foo; diff --git a/mysql-test/t/show_check.test b/mysql-test/t/show_check.test index 89d281a2c58..4d418303e6d 100644 --- a/mysql-test/t/show_check.test +++ b/mysql-test/t/show_check.test @@ -10,6 +10,7 @@ drop table if exists t1,t2; drop table if exists t1aa,t2aa; drop database if exists mysqltest; +drop database if exists mysqltest1; delete from mysql.user where user='mysqltest_1' || user='mysqltest_2' || user='mysqltest_3'; delete from mysql.db where user='mysqltest_1' || user='mysqltest_2' || user='mysqltest_3'; diff --git a/mysql-test/t/skip_name_resolve.test b/mysql-test/t/skip_name_resolve.test index b67869692d2..3f732c8912b 100644 --- a/mysql-test/t/skip_name_resolve.test +++ b/mysql-test/t/skip_name_resolve.test @@ -15,6 +15,6 @@ DROP USER mysqltest_1@'127.0.0.1/255.255.255.255'; connect (con1, 127.0.0.1, root, , test, $MASTER_MYPORT, ); --replace_column 1 # select user(); ---replace_column 1 # 6 # 3 # +--replace_column 1 <id> 3 <host> 5 <command> 6 <time> 7 <state> 8 <info> show processlist; connection default; diff --git a/mysql-test/t/sp-big.test b/mysql-test/t/sp-big.test index 389a6f04504..90a3a79dd53 100644 --- a/mysql-test/t/sp-big.test +++ b/mysql-test/t/sp-big.test @@ -52,6 +52,9 @@ while ($1) --enable_query_log select count(*) from t1; select count(*) from t2; +--disable_warnings +drop procedure if exists p1; +--enable_warnings delimiter |; create procedure p1() begin diff --git a/mysql-test/t/sp-dynamic.test b/mysql-test/t/sp-dynamic.test index 5416f5931ff..6546a5ab548 100644 --- a/mysql-test/t/sp-dynamic.test +++ b/mysql-test/t/sp-dynamic.test @@ -1,4 +1,10 @@ delimiter |; + +--disable_warnings +drop procedure if exists p1| +drop procedure if exists p2| +--enable_warnings + ###################################################################### # Test Dynamic SQL in stored procedures. ############################# ###################################################################### diff --git a/mysql-test/t/sp.test b/mysql-test/t/sp.test index 47e4f94a208..f8b638da59d 100644 --- a/mysql-test/t/sp.test +++ b/mysql-test/t/sp.test @@ -367,6 +367,7 @@ create function sub3(i int) returns int call sub1("sub1a", (select 7))| call sub1("sub1b", (select max(i) from t2))| +--error ER_OPERAND_COLUMNS call sub1("sub1c", (select i,d from t2 limit 1))| call sub1("sub1d", (select 1 from (select 1) a))| call sub2("sub2")| @@ -4797,12 +4798,12 @@ begin declare continue handler for sqlexception select 'boo' as 'Handler'; begin - declare v int default x; + declare v int default undefined_var; if v = 1 then select 1; else - select 2; + select v, isnull(v); end if; end; end| @@ -4811,12 +4812,14 @@ create procedure bug14643_2() begin declare continue handler for sqlexception select 'boo' as 'Handler'; - case x + case undefined_var when 1 then select 1; else select 2; end case; + + select undefined_var; end| call bug14643_1()| diff --git a/mysql-test/t/type_newdecimal-big.test b/mysql-test/t/type_newdecimal-big.test index e200017f2ba..9a1104e4fe6 100644 --- a/mysql-test/t/type_newdecimal-big.test +++ b/mysql-test/t/type_newdecimal-big.test @@ -12,12 +12,31 @@ drop procedure if exists sp1; delimiter //; # -create procedure sp1 () begin - declare v1, v2, v3, v4 decimal(16,12); declare v5 int; - set v1 = 1; set v2 = 2; set v3 = 1000000000000; set v4 = 2000000000000; set v5 = 0; - while v5 < 100000 do - set v1 = v1 + 0.000000000001; set v2 = v2 - 0.000000000001; set v3 = v3 + 1; set v4 = v4 - 1; set v5 = v5 + 1; - end while; select v1, v2, v3 * 0.000000000001, v4 * 0.000000000001; end;// +CREATE PROCEDURE sp1() +BEGIN + DECLARE v1, v2, v3, v4 DECIMAL(28,12); + DECLARE v3_2, v4_2 DECIMAL(28, 12); + DECLARE counter INT; + + SET v1 = 1; + SET v2 = 2; + SET v3 = 1000000000000; + SET v4 = 2000000000000; + SET counter = 0; + + WHILE counter < 100000 DO + SET v1 = v1 + 0.000000000001; + SET v2 = v2 - 0.000000000001; + SET v3 = v3 + 1; + SET v4 = v4 - 1; + SET counter = counter + 1; + END WHILE; + + SET v3_2 = v3 * 0.000000000001; + SET v4_2 = v4 * 0.000000000001; + + SELECT v1, v2, v3, v3_2, v4, v4_2; +END// # call sp1()// #-- should return diff --git a/sql/field.cc b/sql/field.cc index 3903d8323ad..8f9dc832520 100644 --- a/sql/field.cc +++ b/sql/field.cc @@ -6759,7 +6759,10 @@ Field_blob::Field_blob(char *ptr_arg, uchar *null_ptr_arg, uchar null_bit_arg, { flags|= BLOB_FLAG; if (table) + { table->s->blob_fields++; + /* TODO: why do not fill table->s->blob_field array here? */ + } } @@ -8283,6 +8286,350 @@ void create_field::init_for_tmp_table(enum_field_types sql_type_arg, } +/* + Initialize field definition for create + + SYNOPSIS + thd Thread handle + fld_name Field name + fld_type Field type + fld_length Field length + fld_decimals Decimal (if any) + fld_type_modifier Additional type information + fld_default_value Field default value (if any) + fld_on_update_value The value of ON UPDATE clause + fld_comment Field comment + fld_change Field change + fld_interval_list Interval list (if any) + fld_charset Field charset + fld_geom_type Field geometry type (if any) + + RETURN + FALSE on success + TRUE on error +*/ + +bool create_field::init(THD *thd, char *fld_name, enum_field_types fld_type, + char *fld_length, char *fld_decimals, + uint fld_type_modifier, Item *fld_default_value, + Item *fld_on_update_value, LEX_STRING *fld_comment, + char *fld_change, List<String> *fld_interval_list, + CHARSET_INFO *fld_charset, uint fld_geom_type) +{ + uint sign_len, allowed_type_modifier= 0; + ulong max_field_charlength= MAX_FIELD_CHARLENGTH; + + DBUG_ENTER("create_field::init()"); + + field= 0; + field_name= fld_name; + def= fld_default_value; + flags= fld_type_modifier; + unireg_check= (fld_type_modifier & AUTO_INCREMENT_FLAG ? + Field::NEXT_NUMBER : Field::NONE); + decimals= fld_decimals ? (uint)atoi(fld_decimals) : 0; + if (decimals >= NOT_FIXED_DEC) + { + my_error(ER_TOO_BIG_SCALE, MYF(0), decimals, fld_name, + NOT_FIXED_DEC-1); + DBUG_RETURN(TRUE); + } + + sql_type= fld_type; + length= 0; + change= fld_change; + interval= 0; + pack_length= key_length= 0; + charset= fld_charset; + geom_type= (Field::geometry_type) fld_geom_type; + interval_list.empty(); + + comment= *fld_comment; + /* + Set flag if this field doesn't have a default value + */ + if (!fld_default_value && !(fld_type_modifier & AUTO_INCREMENT_FLAG) && + (fld_type_modifier & NOT_NULL_FLAG) && fld_type != FIELD_TYPE_TIMESTAMP) + flags|= NO_DEFAULT_VALUE_FLAG; + + if (fld_length && !(length= (uint) atoi(fld_length))) + fld_length= 0; /* purecov: inspected */ + sign_len= fld_type_modifier & UNSIGNED_FLAG ? 0 : 1; + + switch (fld_type) { + case FIELD_TYPE_TINY: + if (!fld_length) + length= MAX_TINYINT_WIDTH+sign_len; + allowed_type_modifier= AUTO_INCREMENT_FLAG; + break; + case FIELD_TYPE_SHORT: + if (!fld_length) + length= MAX_SMALLINT_WIDTH+sign_len; + allowed_type_modifier= AUTO_INCREMENT_FLAG; + break; + case FIELD_TYPE_INT24: + if (!fld_length) + length= MAX_MEDIUMINT_WIDTH+sign_len; + allowed_type_modifier= AUTO_INCREMENT_FLAG; + break; + case FIELD_TYPE_LONG: + if (!fld_length) + length= MAX_INT_WIDTH+sign_len; + allowed_type_modifier= AUTO_INCREMENT_FLAG; + break; + case FIELD_TYPE_LONGLONG: + if (!fld_length) + length= MAX_BIGINT_WIDTH; + allowed_type_modifier= AUTO_INCREMENT_FLAG; + break; + case FIELD_TYPE_NULL: + break; + case FIELD_TYPE_NEWDECIMAL: + if (!fld_length && !decimals) + length= 10; + if (length > DECIMAL_MAX_PRECISION) + { + my_error(ER_TOO_BIG_PRECISION, MYF(0), length, fld_name, + DECIMAL_MAX_PRECISION); + DBUG_RETURN(TRUE); + } + if (length < decimals) + { + my_error(ER_M_BIGGER_THAN_D, MYF(0), fld_name); + DBUG_RETURN(TRUE); + } + length= + my_decimal_precision_to_length(length, decimals, + fld_type_modifier & UNSIGNED_FLAG); + pack_length= + my_decimal_get_binary_size(length, decimals); + break; + case MYSQL_TYPE_VARCHAR: + /* + Long VARCHAR's are automaticly converted to blobs in mysql_prepare_table + if they don't have a default value + */ + max_field_charlength= MAX_FIELD_VARCHARLENGTH; + break; + case MYSQL_TYPE_STRING: + break; + case FIELD_TYPE_BLOB: + case FIELD_TYPE_TINY_BLOB: + case FIELD_TYPE_LONG_BLOB: + case FIELD_TYPE_MEDIUM_BLOB: + case FIELD_TYPE_GEOMETRY: + if (fld_default_value) + { + /* Allow empty as default value. */ + String str,*res; + res= fld_default_value->val_str(&str); + if (res->length()) + { + my_error(ER_BLOB_CANT_HAVE_DEFAULT, MYF(0), + fld_name); /* purecov: inspected */ + DBUG_RETURN(TRUE); + } + def= 0; + } + flags|= BLOB_FLAG; + break; + case FIELD_TYPE_YEAR: + if (!fld_length || length != 2) + length= 4; /* Default length */ + flags|= ZEROFILL_FLAG | UNSIGNED_FLAG; + break; + case FIELD_TYPE_FLOAT: + /* change FLOAT(precision) to FLOAT or DOUBLE */ + allowed_type_modifier= AUTO_INCREMENT_FLAG; + if (fld_length && !fld_decimals) + { + uint tmp_length= length; + if (tmp_length > PRECISION_FOR_DOUBLE) + { + my_error(ER_WRONG_FIELD_SPEC, MYF(0), fld_name); + DBUG_RETURN(TRUE); + } + else if (tmp_length > PRECISION_FOR_FLOAT) + { + sql_type= FIELD_TYPE_DOUBLE; + length= DBL_DIG+7; /* -[digits].E+### */ + } + else + length= FLT_DIG+6; /* -[digits].E+## */ + decimals= NOT_FIXED_DEC; + break; + } + if (!fld_length && !fld_decimals) + { + length= FLT_DIG+6; + decimals= NOT_FIXED_DEC; + } + if (length < decimals && + decimals != NOT_FIXED_DEC) + { + my_error(ER_M_BIGGER_THAN_D, MYF(0), fld_name); + DBUG_RETURN(TRUE); + } + break; + case FIELD_TYPE_DOUBLE: + allowed_type_modifier= AUTO_INCREMENT_FLAG; + if (!fld_length && !fld_decimals) + { + length= DBL_DIG+7; + decimals= NOT_FIXED_DEC; + } + if (length < decimals && + decimals != NOT_FIXED_DEC) + { + my_error(ER_M_BIGGER_THAN_D, MYF(0), fld_name); + DBUG_RETURN(TRUE); + } + break; + case FIELD_TYPE_TIMESTAMP: + if (!fld_length) + length= 14; /* Full date YYYYMMDDHHMMSS */ + else if (length != 19) + { + /* + We support only even TIMESTAMP lengths less or equal than 14 + and 19 as length of 4.1 compatible representation. + */ + length= ((length+1)/2)*2; /* purecov: inspected */ + length= min(length,14); /* purecov: inspected */ + } + flags|= ZEROFILL_FLAG | UNSIGNED_FLAG; + if (fld_default_value) + { + /* Grammar allows only NOW() value for ON UPDATE clause */ + if (fld_default_value->type() == Item::FUNC_ITEM && + ((Item_func*)fld_default_value)->functype() == Item_func::NOW_FUNC) + { + unireg_check= (fld_on_update_value ? Field::TIMESTAMP_DNUN_FIELD: + Field::TIMESTAMP_DN_FIELD); + /* + We don't need default value any longer moreover it is dangerous. + Everything handled by unireg_check further. + */ + def= 0; + } + else + unireg_check= (fld_on_update_value ? Field::TIMESTAMP_UN_FIELD: + Field::NONE); + } + else + { + /* + If we have default TIMESTAMP NOT NULL column without explicit DEFAULT + or ON UPDATE values then for the sake of compatiblity we should treat + this column as having DEFAULT NOW() ON UPDATE NOW() (when we don't + have another TIMESTAMP column with auto-set option before this one) + or DEFAULT 0 (in other cases). + So here we are setting TIMESTAMP_OLD_FIELD only temporary, and will + replace this value by TIMESTAMP_DNUN_FIELD or NONE later when + information about all TIMESTAMP fields in table will be availiable. + + If we have TIMESTAMP NULL column without explicit DEFAULT value + we treat it as having DEFAULT NULL attribute. + */ + unireg_check= (fld_on_update_value ? Field::TIMESTAMP_UN_FIELD : + (flags & NOT_NULL_FLAG ? Field::TIMESTAMP_OLD_FIELD : + Field::NONE)); + } + break; + case FIELD_TYPE_DATE: + /* Old date type. */ + if (protocol_version != PROTOCOL_VERSION-1) + sql_type= FIELD_TYPE_NEWDATE; + /* fall trough */ + case FIELD_TYPE_NEWDATE: + length= 10; + break; + case FIELD_TYPE_TIME: + length= 10; + break; + case FIELD_TYPE_DATETIME: + length= 19; + break; + case FIELD_TYPE_SET: + { + if (fld_interval_list->elements > sizeof(longlong)*8) + { + my_error(ER_TOO_BIG_SET, MYF(0), fld_name); /* purecov: inspected */ + DBUG_RETURN(TRUE); + } + pack_length= get_set_pack_length(fld_interval_list->elements); + + List_iterator<String> it(*fld_interval_list); + String *tmp; + while ((tmp= it++)) + interval_list.push_back(tmp); + /* + Set fake length to 1 to pass the below conditions. + Real length will be set in mysql_prepare_table() + when we know the character set of the column + */ + length= 1; + break; + } + case FIELD_TYPE_ENUM: + { + /* Should be safe. */ + pack_length= get_enum_pack_length(fld_interval_list->elements); + + List_iterator<String> it(*fld_interval_list); + String *tmp; + while ((tmp= it++)) + interval_list.push_back(tmp); + length= 1; /* See comment for FIELD_TYPE_SET above. */ + break; + } + case MYSQL_TYPE_VAR_STRING: + DBUG_ASSERT(0); /* Impossible. */ + break; + case MYSQL_TYPE_BIT: + { + if (!fld_length) + length= 1; + if (length > MAX_BIT_FIELD_LENGTH) + { + my_error(ER_TOO_BIG_DISPLAYWIDTH, MYF(0), fld_name, + MAX_BIT_FIELD_LENGTH); + DBUG_RETURN(TRUE); + } + pack_length= (length + 7) / 8; + break; + } + case FIELD_TYPE_DECIMAL: + DBUG_ASSERT(0); /* Was obsolete */ + } + + if (!(flags & BLOB_FLAG) && + ((length > max_field_charlength && fld_type != FIELD_TYPE_SET && + fld_type != FIELD_TYPE_ENUM && + (fld_type != MYSQL_TYPE_VARCHAR || fld_default_value)) || + (!length && + fld_type != MYSQL_TYPE_STRING && + fld_type != MYSQL_TYPE_VARCHAR && fld_type != FIELD_TYPE_GEOMETRY))) + { + my_error((fld_type == MYSQL_TYPE_VAR_STRING || + fld_type == MYSQL_TYPE_VARCHAR || + fld_type == MYSQL_TYPE_STRING) ? ER_TOO_BIG_FIELDLENGTH : + ER_TOO_BIG_DISPLAYWIDTH, + MYF(0), + fld_name, max_field_charlength); /* purecov: inspected */ + DBUG_RETURN(TRUE); + } + fld_type_modifier&= AUTO_INCREMENT_FLAG; + if ((~allowed_type_modifier) & fld_type_modifier) + { + my_error(ER_WRONG_FIELD_SPEC, MYF(0), fld_name); + DBUG_RETURN(TRUE); + } + + DBUG_RETURN(FALSE); /* success */ +} + + enum_field_types get_blob_type_from_length(ulong length) { enum_field_types type; diff --git a/sql/field.h b/sql/field.h index ed6bf1c0a9c..67705523088 100644 --- a/sql/field.h +++ b/sql/field.h @@ -130,7 +130,19 @@ public: null_bit == field->null_bit); } virtual bool eq_def(Field *field); + + /* + pack_length() returns size (in bytes) used to store field data in memory + (i.e. it returns the maximum size of the field in a row of the table, + which is located in RAM). + */ virtual uint32 pack_length() const { return (uint32) field_length; } + + /* + pack_length_in_rec() returns size (in bytes) used to store field data on + storage (i.e. it returns the maximal size of the field in a row of the + table, which is located on disk). + */ virtual uint32 pack_length_in_rec() const { return pack_length(); } virtual uint32 sort_length() const { return pack_length(); } virtual void reset(void) { bzero(ptr,pack_length()); } @@ -1395,6 +1407,12 @@ public: void init_for_tmp_table(enum_field_types sql_type_arg, uint32 max_length, uint32 decimals, bool maybe_null, bool is_unsigned); + + bool init(THD *thd, char *field_name, enum_field_types type, char *length, + char *decimals, uint type_modifier, Item *default_value, + Item *on_update_value, LEX_STRING *comment, char *change, + List<String> *interval_list, CHARSET_INFO *cs, + uint uint_geom_type); }; diff --git a/sql/item.cc b/sql/item.cc index c15fd948b5a..3721528672a 100644 --- a/sql/item.cc +++ b/sql/item.cc @@ -296,23 +296,6 @@ longlong Item::val_int_from_decimal() } -void *Item::operator new(size_t size, Item *reuse, uint *rsize) -{ - if (reuse && size <= reuse->rsize) - { - if (rsize) - (*rsize)= reuse->rsize; - reuse->cleanup(); - delete reuse; - TRASH((void *)reuse, size); - return (void *)reuse; - } - if (rsize) - (*rsize)= (uint) size; - return (void *)sql_alloc((uint)size); -} - - Item::Item(): rsize(0), name(0), orig_name(0), name_length(0), fixed(0), is_autogenerated_name(TRUE), @@ -802,9 +785,41 @@ int Item::save_in_field_no_warnings(Field *field, bool no_conversions) /***************************************************************************** - Item_splocal methods + Item_sp_variable methods *****************************************************************************/ -double Item_splocal::val_real() + +Item_sp_variable::Item_sp_variable(char *sp_var_name_str, + uint sp_var_name_length) + :m_thd(0) +#ifndef DBUG_OFF + , m_sp(0) +#endif +{ + m_name.str= sp_var_name_str; + m_name.length= sp_var_name_length; +} + + +bool Item_sp_variable::fix_fields(THD *thd, Item **) +{ + Item *it; + + m_thd= thd; /* NOTE: this must be set before any this_xxx() */ + it= this_item(); + + DBUG_ASSERT(it->fixed); + + max_length= it->max_length; + decimals= it->decimals; + unsigned_flag= it->unsigned_flag; + fixed= 1; + collation.set(it->collation.collation, it->collation.derivation); + + return FALSE; +} + + +double Item_sp_variable::val_real() { DBUG_ASSERT(fixed); Item *it= this_item(); @@ -814,7 +829,7 @@ double Item_splocal::val_real() } -longlong Item_splocal::val_int() +longlong Item_sp_variable::val_int() { DBUG_ASSERT(fixed); Item *it= this_item(); @@ -824,13 +839,14 @@ longlong Item_splocal::val_int() } -String *Item_splocal::val_str(String *sp) +String *Item_sp_variable::val_str(String *sp) { DBUG_ASSERT(fixed); Item *it= this_item(); String *res= it->val_str(sp); null_value= it->null_value; + if (!res) return NULL; @@ -854,11 +870,12 @@ String *Item_splocal::val_str(String *sp) str_value.set(res->ptr(), res->length(), res->charset()); else res->mark_as_const(); + return &str_value; } -my_decimal *Item_splocal::val_decimal(my_decimal *decimal_value) +my_decimal *Item_sp_variable::val_decimal(my_decimal *decimal_value) { DBUG_ASSERT(fixed); Item *it= this_item(); @@ -868,73 +885,108 @@ my_decimal *Item_splocal::val_decimal(my_decimal *decimal_value) } -bool Item_splocal::is_null() +bool Item_sp_variable::is_null() { - Item *it= this_item(); - return it->is_null(); + return this_item()->is_null(); +} + + +/***************************************************************************** + Item_splocal methods +*****************************************************************************/ + +Item_splocal::Item_splocal(const LEX_STRING &sp_var_name, + uint sp_var_idx, + enum_field_types sp_var_type, + uint pos_in_q) + :Item_sp_variable(sp_var_name.str, sp_var_name.length), + m_var_idx(sp_var_idx), pos_in_query(pos_in_q) +{ + maybe_null= TRUE; + + m_type= sp_map_item_type(sp_var_type); + m_result_type= sp_map_result_type(sp_var_type); } Item * Item_splocal::this_item() { - DBUG_ASSERT(owner == thd->spcont->owner); - return thd->spcont->get_item(m_offset); + DBUG_ASSERT(m_sp == m_thd->spcont->sp); + + return m_thd->spcont->get_item(m_var_idx); +} + +const Item * +Item_splocal::this_item() const +{ + DBUG_ASSERT(m_sp == m_thd->spcont->sp); + + return m_thd->spcont->get_item(m_var_idx); } Item ** -Item_splocal::this_item_addr(THD *thd, Item **addr) +Item_splocal::this_item_addr(THD *thd, Item **) { - DBUG_ASSERT(owner == thd->spcont->owner); - return thd->spcont->get_item_addr(m_offset); + DBUG_ASSERT(m_sp == thd->spcont->sp); + + return thd->spcont->get_item_addr(m_var_idx); } -Item * -Item_splocal::this_const_item() const + +void Item_splocal::print(String *str) { - DBUG_ASSERT(owner == thd->spcont->owner); - return thd->spcont->get_item(m_offset); + str->reserve(m_name.length+8); + str->append(m_name.str, m_name.length); + str->append('@'); + str->qs_append(m_var_idx); } -Item::Type -Item_splocal::type() const + +/***************************************************************************** + Item_case_expr methods +*****************************************************************************/ + +Item_case_expr::Item_case_expr(int case_expr_id) + :Item_sp_variable(STRING_WITH_LEN("case_expr")), + m_case_expr_id(case_expr_id) { - if (thd && thd->spcont) - { - DBUG_ASSERT(owner == thd->spcont->owner); - return thd->spcont->get_item(m_offset)->type(); - } - return NULL_ITEM; // Anything but SUBSELECT_ITEM } -bool Item_splocal::fix_fields(THD *thd_arg, Item **ref) +Item * +Item_case_expr::this_item() { - Item *it; - thd= thd_arg; // Must be set before this_item() - it= this_item(); - DBUG_ASSERT(it->fixed); - max_length= it->max_length; - decimals= it->decimals; - unsigned_flag= it->unsigned_flag; - fixed= 1; - return FALSE; + DBUG_ASSERT(m_sp == m_thd->spcont->sp); + + return m_thd->spcont->get_case_expr(m_case_expr_id); } -void Item_splocal::cleanup() + +const Item * +Item_case_expr::this_item() const { - fixed= 0; + DBUG_ASSERT(m_sp == m_thd->spcont->sp); + + return m_thd->spcont->get_case_expr(m_case_expr_id); } -void Item_splocal::print(String *str) +Item ** +Item_case_expr::this_item_addr(THD *thd, Item **) { - str->reserve(m_name.length+8); - str->append(m_name.str, m_name.length); - str->append('@'); - str->qs_append(m_offset); + DBUG_ASSERT(m_sp == thd->spcont->sp); + + return thd->spcont->get_case_expr_addr(m_case_expr_id); +} + + +void Item_case_expr::print(String *str) +{ + str->append(STRING_WITH_LEN("case_expr@")); + str->qs_append(m_case_expr_id); } @@ -1013,12 +1065,6 @@ bool Item_name_const::fix_fields(THD *thd, Item **ref) } -void Item_name_const::cleanup() -{ - fixed= 0; -} - - void Item_name_const::print(String *str) { str->append(STRING_WITH_LEN("NAME_CONST(")); @@ -3911,6 +3957,9 @@ int Item::save_in_field(Field *field, bool no_conversions) str_value.set_quick(0, 0, cs); return set_field_to_null_with_conversions(field, no_conversions); } + + /* NOTE: If null_value == FALSE, "result" must be not NULL. */ + field->set_notnull(); error=field->store(result->ptr(),result->length(),cs); str_value.set_quick(0, 0, cs); diff --git a/sql/item.h b/sql/item.h index ab9c93e207d..900442a45e9 100644 --- a/sql/item.h +++ b/sql/item.h @@ -383,8 +383,6 @@ public: { return (void*) sql_alloc((uint) size); } static void *operator new(size_t size, MEM_ROOT *mem_root) { return (void*) alloc_root(mem_root, (uint) size); } - /* Special for SP local variable assignment - reusing slots */ - static void *operator new(size_t size, Item *reuse, uint *rsize); static void operator delete(void *ptr,size_t size) { TRASH(ptr, size); } static void operator delete(void *ptr, MEM_ROOT *mem_root) {} @@ -713,13 +711,13 @@ public: current value and pointer to current Item otherwise. */ virtual Item *this_item() { return this; } + virtual const Item *this_item() const { return this; } + /* For SP local variable returns address of pointer to Item representing its current value and pointer passed via parameter otherwise. */ virtual Item **this_item_addr(THD *thd, Item **addr) { return addr; } - /* For SPs mostly. */ - virtual Item *this_const_item() const { return const_cast<Item*>(this); } // Row emulation virtual uint cols() { return 1; } @@ -748,21 +746,32 @@ public: class sp_head; -/* - A reference to local SP variable (incl. reference to SP parameter), used in - runtime. - - NOTE - This item has a "value" item, defined as - this_item() = thd->spcont->get_item(m_offset) - and it delegates everything to that item (if !this_item() then this item - poses as Item_null) except for name, which is the name of SP local - variable. -*/ -class Item_splocal : public Item +/***************************************************************************** + The class is a base class for representation of stored routine variables in + the Item-hierarchy. There are the following kinds of SP-vars: + - local variables (Item_splocal); + - CASE expression (Item_case_expr); +*****************************************************************************/ + +class Item_sp_variable :public Item { - uint m_offset; +protected: + /* + THD, which is stored in fix_fields() and is used in this_item() to avoid + current_thd use. + */ + THD *m_thd; + +public: + LEX_STRING m_name; + + /* + Buffer, pointing to the string value of the item. We need it to + protect internal buffer from changes. See comment to analogous + member in Item_param for more details. + */ + String str_value_ptr; public: #ifndef DBUG_OFF @@ -770,11 +779,74 @@ public: Routine to which this Item_splocal belongs. Used for checking if correct runtime context is used for variable handling. */ - sp_head *owner; + sp_head *m_sp; #endif - LEX_STRING m_name; - THD *thd; +public: + Item_sp_variable(char *sp_var_name_str, uint sp_var_name_length); + +public: + bool fix_fields(THD *thd, Item **); + + double val_real(); + longlong val_int(); + String *val_str(String *sp); + my_decimal *val_decimal(my_decimal *decimal_value); + bool is_null(); + +public: + inline void make_field(Send_field *field); + + inline bool const_item() const; + + inline int save_in_field(Field *field, bool no_conversions); + inline bool send(Protocol *protocol, String *str); +}; + +/***************************************************************************** + Item_sp_variable inline implementation. +*****************************************************************************/ + +inline void Item_sp_variable::make_field(Send_field *field) +{ + Item *it= this_item(); + + if (name) + it->set_name(name, (uint) strlen(name), system_charset_info); + else + it->set_name(m_name.str, m_name.length, system_charset_info); + it->make_field(field); +} + +inline bool Item_sp_variable::const_item() const +{ + return TRUE; +} + +inline int Item_sp_variable::save_in_field(Field *field, bool no_conversions) +{ + return this_item()->save_in_field(field, no_conversions); +} + +inline bool Item_sp_variable::send(Protocol *protocol, String *str) +{ + return this_item()->send(protocol, str); +} + + +/***************************************************************************** + A reference to local SP variable (incl. reference to SP parameter), used in + runtime. +*****************************************************************************/ + +class Item_splocal :public Item_sp_variable +{ + uint m_var_idx; + + Type m_type; + Item_result m_result_type; + +public: /* Position of this reference to SP variable in the statement (the statement itself is in sp_instr_stmt::m_query). @@ -787,78 +859,94 @@ public: */ uint pos_in_query; - Item_splocal(LEX_STRING name, uint offset, uint pos_in_q=0) - : m_offset(offset), m_name(name), thd(0), pos_in_query(pos_in_q) - { - maybe_null= TRUE; - } - - /* For error printing */ - inline LEX_STRING *my_name(LEX_STRING *get_name) - { - if (!get_name) - return &m_name; - (*get_name)= m_name; - return get_name; - } + Item_splocal(const LEX_STRING &sp_var_name, uint sp_var_idx, + enum_field_types sp_var_type, uint pos_in_q= 0); bool is_splocal() { return 1; } /* Needed for error checking */ Item *this_item(); + const Item *this_item() const; Item **this_item_addr(THD *thd, Item **); - Item *this_const_item() const; - bool fix_fields(THD *, Item **); - void cleanup(); + void print(String *str); - inline uint get_offset() - { - return m_offset; - } +public: + inline const LEX_STRING *my_name() const; - // Abstract methods inherited from Item. Just defer the call to - // the item in the frame - enum Type type() const; + inline uint get_var_idx() const; - double val_real(); - longlong val_int(); - String *val_str(String *sp); - my_decimal *val_decimal(my_decimal *); - bool is_null(); - void print(String *str); + inline enum Type type() const; + inline Item_result result_type() const; +}; - void make_field(Send_field *field) - { - Item *it= this_item(); +/***************************************************************************** + Item_splocal inline implementation. +*****************************************************************************/ - if (name) - it->set_name(name, (uint) strlen(name), system_charset_info); - else - it->set_name(m_name.str, m_name.length, system_charset_info); - it->make_field(field); - } +inline const LEX_STRING *Item_splocal::my_name() const +{ + return &m_name; +} - Item_result result_type() const - { - return this_const_item()->result_type(); - } +inline uint Item_splocal::get_var_idx() const +{ + return m_var_idx; +} - bool const_item() const - { - return TRUE; - } +inline enum Item::Type Item_splocal::type() const +{ + return m_type; +} - int save_in_field(Field *field, bool no_conversions) - { - return this_item()->save_in_field(field, no_conversions); - } +inline Item_result Item_splocal::result_type() const +{ + return m_result_type; +} - bool send(Protocol *protocol, String *str) - { - return this_item()->send(protocol, str); - } + +/***************************************************************************** + A reference to case expression in SP, used in runtime. +*****************************************************************************/ + +class Item_case_expr :public Item_sp_variable +{ +public: + Item_case_expr(int case_expr_id); + +public: + Item *this_item(); + const Item *this_item() const; + Item **this_item_addr(THD *thd, Item **); + + inline enum Type type() const; + inline Item_result result_type() const; + +public: + /* + NOTE: print() is intended to be used from views and for debug. + Item_case_expr can not occur in views, so here it is only for debug + purposes. + */ + void print(String *str); + +private: + int m_case_expr_id; }; +/***************************************************************************** + Item_case_expr inline implementation. +*****************************************************************************/ + +inline enum Item::Type Item_case_expr::type() const +{ + return this_item()->type(); +} + +inline Item_result Item_case_expr::result_type() const +{ + return this_item()->result_type(); +} + /* NAME_CONST(given_name, const_value). @@ -885,7 +973,6 @@ public: } bool fix_fields(THD *, Item **); - void cleanup(); enum Type type() const; double val_real(); diff --git a/sql/item_func.cc b/sql/item_func.cc index 89561e8eb17..0d3bd08b230 100644 --- a/sql/item_func.cc +++ b/sql/item_func.cc @@ -4716,7 +4716,7 @@ Item_func_sp::sp_result_field(void) const share->table_cache_key = empty_name; share->table_name = empty_name; } - field= m_sp->make_field(max_length, name, dummy_table); + field= m_sp->create_result_field(max_length, name, dummy_table); DBUG_RETURN(field); } @@ -4729,17 +4729,17 @@ Item_func_sp::sp_result_field(void) const 1 value = NULL or error */ -int +bool Item_func_sp::execute(Field **flp) { - Item *it; + THD *thd= current_thd; Field *f; - if (execute(&it)) - { - null_value= 1; - context->process_error(current_thd); - return 1; - } + + /* + Get field in virtual tmp table to store result. Create the field if + invoked first time. + */ + if (!(f= *flp)) { *flp= f= sp_result_field(); @@ -4748,20 +4748,33 @@ Item_func_sp::execute(Field **flp) f->null_ptr= (uchar *)&null_value; f->null_bit= 1; } - it->save_in_field(f, 1); - return null_value= f->is_null(); + + /* Execute function and store the return value in the field. */ + + if (execute_impl(thd, f)) + { + null_value= 1; + context->process_error(thd); + return TRUE; + } + + /* Check that the field (the value) is not NULL. */ + + null_value= f->is_null(); + + return null_value; } -int -Item_func_sp::execute(Item **itp) +bool +Item_func_sp::execute_impl(THD *thd, Field *return_value_fld) { - DBUG_ENTER("Item_func_sp::execute"); - THD *thd= current_thd; - int res= -1; + bool err_status= TRUE; Sub_statement_state statement_state; Security_context *save_security_ctx= thd->security_ctx, *save_ctx_func; + DBUG_ENTER("Item_func_sp::execute_impl"); + #ifndef NO_EMBEDDED_ACCESS_CHECKS if (context->security_ctx) { @@ -4778,7 +4791,7 @@ Item_func_sp::execute(Item **itp) function call into binlog. */ thd->reset_sub_statement_state(&statement_state, SUB_STMT_FUNCTION); - res= m_sp->execute_function(thd, args, arg_count, itp); + err_status= m_sp->execute_function(thd, args, arg_count, return_value_fld); thd->restore_sub_statement_state(&statement_state); #ifndef NO_EMBEDDED_ACCESS_CHECKS @@ -4788,7 +4801,7 @@ error: #else error: #endif - DBUG_RETURN(res); + DBUG_RETURN(err_status); } @@ -4884,7 +4897,7 @@ Item_func_sp::tmp_table_field(TABLE *t_arg) DBUG_ENTER("Item_func_sp::tmp_table_field"); if (m_sp) - res= m_sp->make_field(max_length, (const char *)name, t_arg); + res= m_sp->create_result_field(max_length, (const char*) name, t_arg); if (!res) res= Item_func::tmp_table_field(t_arg); diff --git a/sql/item_func.h b/sql/item_func.h index 76647fd5cb2..d81eb5f6ebf 100644 --- a/sql/item_func.h +++ b/sql/item_func.h @@ -1374,8 +1374,8 @@ private: Field *result_field; char result_buf[64]; - int execute(Item **itp); - int execute(Field **flp); + bool execute(Field **flp); + bool execute_impl(THD *thd, Field *return_value_fld); Field *sp_result_field(void) const; public: diff --git a/sql/mysql_priv.h b/sql/mysql_priv.h index 44deaf1823a..3f4ed137a59 100644 --- a/sql/mysql_priv.h +++ b/sql/mysql_priv.h @@ -660,6 +660,7 @@ bool mysql_assign_to_keycache(THD* thd, TABLE_LIST* table_list, bool mysql_preload_keys(THD* thd, TABLE_LIST* table_list); int reassign_keycache_tables(THD* thd, KEY_CACHE *src_cache, KEY_CACHE *dst_cache); +TABLE *create_virtual_tmp_table(THD *thd, List<create_field> &field_list); bool mysql_xa_recover(THD *thd); @@ -1099,8 +1100,8 @@ void unhex_type2(TYPELIB *lib); uint check_word(TYPELIB *lib, const char *val, const char *end, const char **end_of_word); -bool is_keyword(const char *name, uint len); +bool is_keyword(const char *name, uint len); #define MY_DB_OPT_FILE "db.opt" bool load_db_opt(THD *thd, const char *path, HA_CREATE_INFO *create); diff --git a/sql/sp.cc b/sql/sp.cc index 1cc4091dd56..d5b93f0d2f2 100644 --- a/sql/sp.cc +++ b/sql/sp.cc @@ -467,7 +467,7 @@ sp_returns_type(THD *thd, String &result, sp_head *sp) bzero(&table, sizeof(table)); table.in_use= thd; table.s = &table.share_not_to_be_used; - field= sp->make_field(0, 0, &table); + field= sp->create_result_field(0, 0, &table); field->sql_type(result); delete field; } diff --git a/sql/sp_head.cc b/sql/sp_head.cc index 6814bdfa8f2..d1f1115ee4e 100644 --- a/sql/sp_head.cc +++ b/sql/sp_head.cc @@ -27,8 +27,7 @@ Item_result sp_map_result_type(enum enum_field_types type) { - switch (type) - { + switch (type) { case MYSQL_TYPE_TINY: case MYSQL_TYPE_SHORT: case MYSQL_TYPE_LONG: @@ -46,6 +45,81 @@ sp_map_result_type(enum enum_field_types type) } } + +Item::Type +sp_map_item_type(enum enum_field_types type) +{ + switch (type) { + case MYSQL_TYPE_TINY: + case MYSQL_TYPE_SHORT: + case MYSQL_TYPE_LONG: + case MYSQL_TYPE_LONGLONG: + case MYSQL_TYPE_INT24: + return Item::INT_ITEM; + case MYSQL_TYPE_DECIMAL: + case MYSQL_TYPE_NEWDECIMAL: + return Item::DECIMAL_ITEM; + case MYSQL_TYPE_FLOAT: + case MYSQL_TYPE_DOUBLE: + return Item::REAL_ITEM; + default: + return Item::STRING_ITEM; + } +} + + +/* + Return a string representation of the Item value. + + NOTE: this is a legacy-compatible implementation. It fails if the value + contains non-ordinary symbols, which should be escaped. + + SYNOPSIS + item a pointer to the Item + str string buffer for representation of the value + + RETURN + NULL on error + a pointer to valid a valid string on success +*/ + +static String * +sp_get_item_value(Item *item, String *str) +{ + Item_result result_type= item->result_type(); + + switch (item->result_type()) { + case REAL_RESULT: + case INT_RESULT: + case DECIMAL_RESULT: + return item->val_str(str); + + case STRING_RESULT: + { + char buf_holder[STRING_BUFFER_USUAL_SIZE]; + String buf(buf_holder, sizeof(buf_holder), &my_charset_latin1); + String *result= item->val_str(str); + + if (!result) + return NULL; + + buf.append('_'); + buf.append(result->charset()->csname); + buf.append('\''); + buf.append(*result); + buf.append('\''); + str->copy(buf); + + return str; + } + + case ROW_RESULT: + default: + return NULL; + } +} + + /* SYNOPSIS sp_get_flags_for_command() @@ -177,7 +251,7 @@ sp_get_flags_for_command(LEX *lex) /* - Prepare Item for execution (call of fix_fields) + Prepare an Item for evaluation (call of fix_fields). SYNOPSIS sp_prepare_func_item() @@ -189,14 +263,15 @@ sp_get_flags_for_command(LEX *lex) prepared item */ -static Item * +Item * sp_prepare_func_item(THD* thd, Item **it_addr) { - Item *it= *it_addr; DBUG_ENTER("sp_prepare_func_item"); - it_addr= it->this_item_addr(thd, it_addr); + it_addr= (*it_addr)->this_item_addr(thd, it_addr); - if (!it->fixed && (*it_addr)->fix_fields(thd, it_addr)) + if (!(*it_addr)->fixed && + ((*it_addr)->fix_fields(thd, it_addr) || + (*it_addr)->check_cols(1))) { DBUG_PRINT("info", ("fix_fields() failed")); DBUG_RETURN(NULL); @@ -205,202 +280,62 @@ sp_prepare_func_item(THD* thd, Item **it_addr) } -/* Macro to switch arena in sp_eval_func_item */ -#define CREATE_ON_CALLERS_ARENA(new_command, condition, backup_arena) \ - do \ - { \ - if (condition) \ - thd->set_n_backup_active_arena(thd->spcont->callers_arena, \ - backup_arena); \ - new_command; \ - if (condition) \ - thd->restore_active_arena(thd->spcont->callers_arena, \ - backup_arena); \ - } while(0) - /* - Evaluate an item and store it in the returned item + Evaluate an expression and store the result in the field. SYNOPSIS - sp_eval_func_item() - name - current thread object - it_addr - pointer to the item to evaluate - type - type of the item we evaluating - reuse - used if we would like to reuse existing item - instead of allocation of the new one - use_callers_arena - TRUE if we want to use caller's arena - rather then current one. - DESCRIPTION - We use this function to evaluate result for stored functions - and stored procedure parameters. It is also used to evaluate and - (re) allocate variables. + sp_eval_expr() + thd - current thread object + expr_item - the root item of the expression + result_field - the field to store the result RETURN VALUES - Evaluated item is returned + FALSE on success + TRUE on error */ -Item * -sp_eval_func_item(THD *thd, Item **it_addr, enum enum_field_types type, - Item *reuse, bool use_callers_arena) +bool +sp_eval_expr(THD *thd, Field *result_field, Item *expr_item) { - DBUG_ENTER("sp_eval_func_item"); - Item *it= sp_prepare_func_item(thd, it_addr); - uint rsize; - Query_arena backup_arena; - Item *old_item_next, *old_free_list, **p_free_list; - DBUG_PRINT("info", ("type: %d", type)); + DBUG_ENTER("sp_eval_expr"); - if (!it) - DBUG_RETURN(NULL); + if (!(expr_item= sp_prepare_func_item(thd, &expr_item))) + DBUG_RETURN(TRUE); - if (reuse) - { - old_item_next= reuse->next; - p_free_list= use_callers_arena ? &thd->spcont->callers_arena->free_list : - &thd->free_list; - old_free_list= *p_free_list; - } + bool err_status= FALSE; - switch (sp_map_result_type(type)) { - case INT_RESULT: - { - longlong i= it->val_int(); - - if (it->null_value) - { - DBUG_PRINT("info", ("INT_RESULT: null")); - goto return_null_item; - } - DBUG_PRINT("info", ("INT_RESULT: %d", i)); - CREATE_ON_CALLERS_ARENA(it= new(reuse, &rsize) Item_int(i), - use_callers_arena, &backup_arena); - break; - } - case REAL_RESULT: - { - double d= it->val_real(); - uint8 decimals; - uint32 max_length; - - if (it->null_value) - { - DBUG_PRINT("info", ("REAL_RESULT: null")); - goto return_null_item; - } + /* + Set THD flags to emit warnings/errors in case of overflow/type errors + during saving the item into the field. - /* - There's some difference between Item::new_item() and the - constructor; the former crashes, the latter works... weird. - */ - decimals= it->decimals; - max_length= it->max_length; - DBUG_PRINT("info", ("REAL_RESULT: %g", d)); - CREATE_ON_CALLERS_ARENA(it= new(reuse, &rsize) Item_float(d), - use_callers_arena, &backup_arena); - it->decimals= decimals; - it->max_length= max_length; - break; - } - case DECIMAL_RESULT: - { - my_decimal value, *val= it->val_decimal(&value); - if (it->null_value) - goto return_null_item; - CREATE_ON_CALLERS_ARENA(it= new(reuse, &rsize) Item_decimal(val), - use_callers_arena, &backup_arena); -#ifndef DBUG_OFF - { - char dbug_buff[DECIMAL_MAX_STR_LENGTH+1]; - DBUG_PRINT("info", ("DECIMAL_RESULT: %s", - dbug_decimal_as_string(dbug_buff, val))); - } -#endif - break; - } - case STRING_RESULT: - { - char buffer[MAX_FIELD_WIDTH]; - String tmp(buffer, sizeof(buffer), it->collation.collation); - String *s= it->val_str(&tmp); + Save original values and restore them after save. + */ + + enum_check_fields save_count_cuted_fields= thd->count_cuted_fields; + bool save_abort_on_warning= thd->abort_on_warning; + bool save_no_trans_update= thd->no_trans_update; - if (type == MYSQL_TYPE_NULL || it->null_value) - { - DBUG_PRINT("info", ("STRING_RESULT: null")); - goto return_null_item; - } - DBUG_PRINT("info",("STRING_RESULT: %.*s", - s->length(), s->c_ptr_quick())); - /* - Reuse mechanism in sp_eval_func_item() is only employed for assignments - to local variables and OUT/INOUT SP parameters repsesented by - Item_splocal. Usually we have some expression, which needs - to be calculated and stored into the local variable. However in the - case if "it" equals to "reuse", there is no "calculation" step. So, - no reason to employ reuse mechanism to save variable into itself. - */ - if (it == reuse) - DBUG_RETURN(it); + thd->count_cuted_fields= CHECK_FIELD_ERROR_FOR_NULL; + thd->abort_on_warning= + thd->variables.sql_mode & + (MODE_STRICT_TRANS_TABLES | MODE_STRICT_ALL_TABLES); + thd->no_trans_update= 0; - /* - For some functions, 's' is now pointing to an argument of the - function, which might be a local variable that is to be reused. - In this case, new(reuse, &rsize) below will call the destructor - and 's' ends up pointing to freed memory. - A somewhat ugly fix is to simply copy the string to our local one - (which is unused by most functions anyway), but only if 's' is - pointing somewhere else than to 'tmp' or 'it->str_value'. - */ - if (reuse && s != &tmp && s != &it->str_value) - { - if (tmp.copy((const String)(*s))) - DBUG_RETURN(NULL); - s= &tmp; - } + /* Save the value in the field. Convert the value if needed. */ - CREATE_ON_CALLERS_ARENA(it= new(reuse, &rsize) - Item_string(it->collation.collation), - use_callers_arena, &backup_arena); - /* - We have to use special constructor and allocate string - on system heap here. This is because usual Item_string - constructor would allocate memory in the callers arena. - This would lead to the memory leak in SP loops. - See Bug #11333 "Stored Procedure: Memory blow up on - repeated SELECT ... INTO query" for sample of such SP. - TODO: Usage of the system heap gives significant overhead, - however usual "reuse" mechanism does not work here, as - Item_string has no max size. That is, if we have a loop, which - has string variable with constantly increasing size, we would have - to allocate new pieces of memory again and again on each iteration. - In future we should probably reserve some area of memory for - not-very-large strings and reuse it. But for large strings - we would have to use system heap anyway. - */ - ((Item_string*) it)->set_str_with_copy(s->ptr(), s->length()); - break; - } - case ROW_RESULT: - default: - DBUG_ASSERT(0); - } - goto end; + expr_item->save_in_field(result_field, 0); -return_null_item: - CREATE_ON_CALLERS_ARENA(it= new(reuse, &rsize) Item_null(), - use_callers_arena, &backup_arena); -end: - it->rsize= rsize; + thd->count_cuted_fields= save_count_cuted_fields; + thd->abort_on_warning= save_abort_on_warning; + thd->no_trans_update= save_no_trans_update; - if (reuse && it == reuse) + if (thd->net.report_error) { - /* - The Item constructor registered itself in the arena free list, - while the item slot is reused, so we have to restore the list. - */ - it->next= old_item_next; - *p_free_list= old_free_list; + /* Return error status if something went wrong. */ + err_status= TRUE; } - DBUG_RETURN(it); + + DBUG_RETURN(err_status); } @@ -485,9 +420,11 @@ sp_head::operator delete(void *ptr, size_t size) sp_head::sp_head() :Query_arena(&main_mem_root, INITIALIZED_FOR_SP), - m_flags(0), m_returns_cs(NULL), m_recursion_level(0), m_next_cached_sp(0), + m_flags(0), m_recursion_level(0), m_next_cached_sp(0), m_first_instance(this), m_first_free_instance(this), m_last_cached_sp(this) { + m_return_field_def.charset = NULL; + extern byte * sp_table_key(const byte *ptr, uint *plen, my_bool first); DBUG_ENTER("sp_head::sp_head"); @@ -506,6 +443,7 @@ sp_head::init(LEX *lex) DBUG_ENTER("sp_head::init"); lex->spcont= m_pcont= new sp_pcontext(NULL); + /* Altough trg_table_fields list is used only in triggers we init for all types of stored procedures to simplify reset_lex()/restore_lex() code. @@ -517,7 +455,7 @@ sp_head::init(LEX *lex) m_body.str= m_defstr.str= 0; m_qname.length= m_db.length= m_name.length= m_params.length= m_body.length= m_defstr.length= 0; - m_returns_cs= NULL; + m_return_field_def.charset= NULL; DBUG_VOID_RETURN; } @@ -576,12 +514,13 @@ sp_head::init_strings(THD *thd, LEX *lex, sp_name *name) DBUG_VOID_RETURN; } -TYPELIB * -sp_head::create_typelib(List<String> *src) + +static TYPELIB * +create_typelib(MEM_ROOT *mem_root, create_field *field_def, List<String> *src) { TYPELIB *result= NULL; - CHARSET_INFO *cs= m_returns_cs; - DBUG_ENTER("sp_head::clone_typelib"); + CHARSET_INFO *cs= field_def->charset; + DBUG_ENTER("create_typelib"); if (src->elements) { result= (TYPELIB*) alloc_root(mem_root, sizeof(TYPELIB)); @@ -626,6 +565,7 @@ sp_head::create_typelib(List<String> *src) return result; } + int sp_head::create(THD *thd) { @@ -713,17 +653,30 @@ sp_head::destroy() */ Field * -sp_head::make_field(uint max_length, const char *name, TABLE *dummy) +sp_head::create_result_field(uint field_max_length, const char *field_name, + TABLE *table) { + uint field_length; Field *field; - DBUG_ENTER("sp_head::make_field"); - - field= ::make_field((char *)0, - !m_returns_len ? max_length : m_returns_len, - (uchar *)"", 0, m_returns_pack, m_returns, m_returns_cs, - m_geom_returns, Field::NONE, - m_returns_typelib, - name ? name : (const char *)m_name.str, dummy); + + DBUG_ENTER("sp_head::create_result_field"); + + field_length= !m_return_field_def.length ? + field_max_length : m_return_field_def.length; + + field= ::make_field((char*) 0, /* field ptr */ + field_length, /* field [max] length */ + (uchar*) "", /* null ptr */ + 0, /* null bit */ + m_return_field_def.pack_flag, + m_return_field_def.sql_type, + m_return_field_def.charset, + m_return_field_def.geom_type, + Field::NONE, /* unreg check */ + m_return_field_def.interval, + field_name ? field_name : (const char *) m_name.str, + table); + DBUG_RETURN(field); } @@ -821,12 +774,14 @@ int cmp_splocal_locations(Item_splocal * const *a, Item_splocal * const *b) variables with NAME_CONST('sp_var_name', value) calls. RETURN - 0 Ok, thd->query{_length} either has been appropriately replaced or - there is no need for replacements. - 1 Out of memory error. + FALSE on success + thd->query{_length} either has been appropriately replaced or there + is no need for replacements. + TRUE out of memory error. */ -static bool subst_spvars(THD *thd, sp_instr *instr, LEX_STRING *query_str) +static bool +subst_spvars(THD *thd, sp_instr *instr, LEX_STRING *query_str) { DBUG_ENTER("subst_spvars"); if (thd->prelocked_mode == NON_PRELOCKED && mysql_bin_log.is_open()) @@ -836,7 +791,7 @@ static bool subst_spvars(THD *thd, sp_instr *instr, LEX_STRING *query_str) String qbuf(buffer, sizeof(buffer), &my_charset_bin); int prev_pos, res; - /* Find all instances of item_splocal used in this statement */ + /* Find all instances of Item_splocal used in this statement */ for (Item *item= instr->free_list; item; item= item->next) { if (item->is_splocal()) @@ -847,7 +802,7 @@ static bool subst_spvars(THD *thd, sp_instr *instr, LEX_STRING *query_str) } } if (!sp_vars_uses.elements()) - DBUG_RETURN(0); + DBUG_RETURN(FALSE); /* Sort SP var refs by their occurences in the query */ sp_vars_uses.sort(cmp_splocal_locations); @@ -863,7 +818,12 @@ static bool subst_spvars(THD *thd, sp_instr *instr, LEX_STRING *query_str) splocal < sp_vars_uses.back(); splocal++) { Item *val; - (*splocal)->thd= thd; // fix_fields() is not yet done + + char str_buffer[STRING_BUFFER_USUAL_SIZE]; + String str_value_holder(str_buffer, sizeof(str_buffer), + &my_charset_latin1); + String *str_value; + /* append the text between sp ref occurences */ res|= qbuf.append(cur + prev_pos, (*splocal)->pos_in_query - prev_pos); prev_pos= (*splocal)->pos_in_query + (*splocal)->m_name.length; @@ -872,24 +832,33 @@ static bool subst_spvars(THD *thd, sp_instr *instr, LEX_STRING *query_str) res|= qbuf.append(STRING_WITH_LEN(" NAME_CONST('")); res|= qbuf.append((*splocal)->m_name.str, (*splocal)->m_name.length); res|= qbuf.append(STRING_WITH_LEN("',")); + res|= (*splocal)->fix_fields(thd, (Item **) splocal); + + if (res) + break; + val= (*splocal)->this_item(); DBUG_PRINT("info", ("print %p", val)); - val->print(&qbuf); + str_value= sp_get_item_value(val, &str_value_holder); + if (str_value) + res|= qbuf.append(*str_value); + else + res|= qbuf.append(STRING_WITH_LEN("NULL")); res|= qbuf.append(')'); if (res) break; } res|= qbuf.append(cur + prev_pos, query_str->length - prev_pos); if (res) - DBUG_RETURN(1); + DBUG_RETURN(TRUE); if (!(pbuf= thd->strmake(qbuf.ptr(), qbuf.length()))) - DBUG_RETURN(1); + DBUG_RETURN(TRUE); thd->query= pbuf; thd->query_length= qbuf.length(); } - DBUG_RETURN(0); + DBUG_RETURN(FALSE); } @@ -923,17 +892,19 @@ void sp_head::recursion_level_error() Assume the parameters already set. RETURN - -1 on error + FALSE on success + TRUE on error */ -int sp_head::execute(THD *thd) +bool +sp_head::execute(THD *thd) { DBUG_ENTER("sp_head::execute"); char olddb[128]; bool dbchanged; sp_rcontext *ctx; - int ret= 0; + bool err_status= FALSE; uint ip= 0; ulong save_sql_mode; Query_arena *old_arena; @@ -949,9 +920,7 @@ int sp_head::execute(THD *thd) /* Use some extra margin for possible SP recursion and functions */ if (check_stack_overrun(thd, 8 * STACK_MIN_SIZE, (char*)&old_packet)) - { - DBUG_RETURN(-1); - } + DBUG_RETURN(TRUE); /* init per-instruction memroot */ init_alloc_root(&execute_mem_root, MEM_ROOT_BLOCK_SIZE, 0); @@ -979,7 +948,8 @@ int sp_head::execute(THD *thd) dbchanged= FALSE; if (m_db.length && - (ret= sp_use_new_db(thd, m_db.str, olddb, sizeof(olddb), 0, &dbchanged))) + (err_status= sp_use_new_db(thd, m_db.str, olddb, sizeof(olddb), 0, + &dbchanged))) goto done; if ((ctx= thd->spcont)) @@ -1057,7 +1027,7 @@ int sp_head::execute(THD *thd) if (thd->prelocked_mode == NON_PRELOCKED) thd->user_var_events_alloc= thd->mem_root; - ret= i->execute(thd, &ip); + err_status= i->execute(thd, &ip); /* If this SP instruction have sent eof, it has caused no_send_error to be @@ -1085,11 +1055,10 @@ int sp_head::execute(THD *thd) /* Check if an exception has occurred and a handler has been found - Note: We havo to check even if ret==0, since warnings (and some - errors don't return a non-zero value. - We also have to check even if thd->killed != 0, since some - errors return with this even when a handler has been found - (e.g. "bad data"). + Note: We have to check even if err_status == FALSE, since warnings (and + some errors) don't return a non-zero value. We also have to check even + if thd->killed != 0, since some errors return with this even when a + handler has been found (e.g. "bad data"). */ if (ctx) { @@ -1100,13 +1069,12 @@ int sp_head::execute(THD *thd) break; case SP_HANDLER_CONTINUE: thd->restore_active_arena(&execute_arena, &backup_arena); - ctx->save_variables(hf); thd->set_n_backup_active_arena(&execute_arena, &backup_arena); ctx->push_hstack(ip); // Fall through default: ip= hip; - ret= 0; + err_status= FALSE; ctx->clear_handler(); ctx->enter_handler(hip); thd->clear_error(); @@ -1114,7 +1082,7 @@ int sp_head::execute(THD *thd) continue; } } - } while (ret == 0 && !thd->killed); + } while (!err_status && !thd->killed); thd->restore_active_arena(&execute_arena, &backup_arena); @@ -1135,11 +1103,11 @@ int sp_head::execute(THD *thd) state= EXECUTED; done: - DBUG_PRINT("info", ("ret=%d killed=%d query_error=%d", - ret, thd->killed, thd->query_error)); + DBUG_PRINT("info", ("err_status=%d killed=%d query_error=%d", + err_status, thd->killed, thd->query_error)); if (thd->killed) - ret= -1; + err_status= TRUE; /* If the DB has changed, the pointer has changed too, but the original thd->db will then have been freed */ if (dbchanged) @@ -1149,7 +1117,7 @@ int sp_head::execute(THD *thd) (It would generate an error from mysql_change_db() when olddb=="") */ if (! thd->killed) - ret|= (int) mysql_change_db(thd, olddb, 1); + err_status|= mysql_change_db(thd, olddb, 1); } m_flags&= ~IS_INVOKED; DBUG_PRINT("info", ("first free for 0x%lx --: 0x%lx->0x%lx, level: %lu, flags %x", @@ -1175,7 +1143,7 @@ int sp_head::execute(THD *thd) m_first_instance->m_first_free_instance->m_recursion_level == m_recursion_level + 1)); m_first_instance->m_first_free_instance= this; - DBUG_RETURN(ret); + DBUG_RETURN(err_status); } @@ -1187,33 +1155,41 @@ int sp_head::execute(THD *thd) SYNOPSIS sp_head::execute_function() - thd Thread handle - argp Passed arguments (these are items from containing statement?) - argcount Number of passed arguments. We need to check if this is - correct. - resp OUT Put result item here (q: is it a constant Item always?) + thd Thread handle + argp Passed arguments (these are items from containing + statement?) + argcount Number of passed arguments. We need to check if this is + correct. + return_value_fld Save result here. RETURN - 0 on OK - other on error + FALSE on success + TRUE on error */ -int -sp_head::execute_function(THD *thd, Item **argp, uint argcount, Item **resp) +bool +sp_head::execute_function(THD *thd, Item **argp, uint argcount, + Field *return_value_fld) { - Item **param_values; + Item_cache **param_values; ulonglong binlog_save_options; bool need_binlog_call; - DBUG_ENTER("sp_head::execute_function"); - DBUG_PRINT("info", ("function %s", m_name.str)); - uint csize = m_pcont->max_pvars(); - uint params = m_pcont->current_pvars(); - uint hmax = m_pcont->max_handlers(); - uint cmax = m_pcont->max_cursors(); + uint params; sp_rcontext *octx = thd->spcont; sp_rcontext *nctx = NULL; - uint i; - int ret= -1; // Assume error + bool err_status= FALSE; + + DBUG_ENTER("sp_head::execute_function"); + DBUG_PRINT("info", ("function %s", m_name.str)); + + params = m_pcont->context_pvars(); + + /* + Check that the function is called with all specified arguments. + + If it is not, use my_error() to report an error, or it will not terminate + the invoking query properly. + */ if (argcount != params) { @@ -1223,37 +1199,56 @@ sp_head::execute_function(THD *thd, Item **argp, uint argcount, Item **resp) */ my_error(ER_SP_WRONG_NO_OF_ARGS, MYF(0), "FUNCTION", m_qname.str, params, argcount); - goto end; + DBUG_RETURN(TRUE); } - if (!(param_values= (Item**)thd->alloc(sizeof(Item*)*argcount))) - DBUG_RETURN(-1); + /* Allocate param_values to be used for dumping the call into binlog. */ + + if (!(param_values= (Item_cache**)thd->alloc(sizeof(Item_cache*)*argcount))) + DBUG_RETURN(TRUE); // QQ Should have some error checking here? (types, etc...) - if (!(nctx= new sp_rcontext(octx, csize, hmax, cmax))) - goto end; + + if (!(nctx= new sp_rcontext(m_pcont, return_value_fld, octx)) || + nctx->init(thd)) + { + delete nctx; /* Delete nctx if it was init() that failed. */ + DBUG_RETURN(TRUE); + } + #ifndef DBUG_OFF - nctx->owner= this; + nctx->sp= this; #endif - for (i= 0 ; i < argcount ; i++) + + /* Pass arguments. */ + { - sp_pvar_t *pvar = m_pcont->find_pvar(i); - Item *it= sp_eval_func_item(thd, argp++, pvar->type, NULL, FALSE); - param_values[i]= it; + uint i; + + for (i= 0 ; i < argcount ; i++) + { + if (!argp[i]->fixed && argp[i]->fix_fields(thd, &argp[i])) + { + err_status= TRUE; + break; + } - if (!it) - goto end; // EOM error - nctx->push_item(it); - } + param_values[i]= Item_cache::get_cache(argp[i]->result_type()); + param_values[i]->store(argp[i]); + if (nctx->set_variable(thd, i, param_values[i])) + { + err_status= TRUE; + break; + } + } + } - /* - The rest of the frame are local variables which are all IN. - Push NULLs to get the right size (and make the reuse mechanism work) - - the will be initialized by set instructions in each frame. - */ - for (; i < csize ; i++) - nctx->push_item(NULL); + if (err_status) + { + delete nctx; + DBUG_RETURN(TRUE); + } thd->spcont= nctx; @@ -1266,7 +1261,7 @@ sp_head::execute_function(THD *thd, Item **argp, uint argcount, Item **resp) } thd->options&= ~OPTION_BIN_LOG; - ret= execute(thd); + err_status= execute(thd); thd->options= binlog_save_options; if (need_binlog_call) @@ -1282,9 +1277,18 @@ sp_head::execute_function(THD *thd, Item **argp, uint argcount, Item **resp) bufstr.append('('); for (uint i=0; i < argcount; i++) { + String str_value_holder; + String *str_value; + if (i) bufstr.append(','); - param_values[i]->print(&bufstr); + + str_value= sp_get_item_value(param_values[i], &str_value_holder); + + if (str_value) + bufstr.append(*str_value); + else + bufstr.append(STRING_WITH_LEN("NULL")); } bufstr.append(')'); @@ -1300,26 +1304,22 @@ sp_head::execute_function(THD *thd, Item **argp, uint argcount, Item **resp) reset_dynamic(&thd->user_var_events); } - if (m_type == TYPE_ENUM_FUNCTION && ret == 0) + if (m_type == TYPE_ENUM_FUNCTION && !err_status) { /* We need result only in function but not in trigger */ - Item *it= nctx->get_result(); - if (it) - *resp= sp_eval_func_item(thd, &it, m_returns, NULL, FALSE); - else + if (!nctx->is_return_value_set()) { my_error(ER_SP_NORETURNEND, MYF(0), m_name.str); - ret= -1; + err_status= TRUE; } } nctx->pop_all_cursors(); // To avoid memory leaks after an error - delete nctx; // Doesn't do anything + delete nctx; thd->spcont= octx; -end: - DBUG_RETURN(ret); + DBUG_RETURN(err_status); } @@ -1351,17 +1351,15 @@ static Item_func_get_user_var *item_is_user_var(Item *it) - copy back values of INOUT and OUT parameters RETURN - 0 Ok - -1 Error + FALSE on success + TRUE on error */ -int sp_head::execute_procedure(THD *thd, List<Item> *args) +bool +sp_head::execute_procedure(THD *thd, List<Item> *args) { - int ret= 0; - uint csize = m_pcont->max_pvars(); - uint params = m_pcont->current_pvars(); - uint hmax = m_pcont->max_handlers(); - uint cmax = m_pcont->max_cursors(); + bool err_status= FALSE; + uint params = m_pcont->context_pvars(); sp_rcontext *save_spcont, *octx; sp_rcontext *nctx = NULL; DBUG_ENTER("sp_head::execute_procedure"); @@ -1371,16 +1369,21 @@ int sp_head::execute_procedure(THD *thd, List<Item> *args) { my_error(ER_SP_WRONG_NO_OF_ARGS, MYF(0), "PROCEDURE", m_qname.str, params, args->elements); - DBUG_RETURN(-1); + DBUG_RETURN(TRUE); } save_spcont= octx= thd->spcont; if (! octx) { // Create a temporary old context - if (!(octx= new sp_rcontext(octx, csize, hmax, cmax))) - DBUG_RETURN(-1); + if (!(octx= new sp_rcontext(m_pcont, NULL, octx)) || + octx->init(thd)) + { + delete octx; /* Delete octx if it was init() that failed. */ + DBUG_RETURN(TRUE); + } + #ifndef DBUG_OFF - octx->owner= 0; + octx->sp= 0; #endif thd->spcont= octx; @@ -1388,63 +1391,62 @@ int sp_head::execute_procedure(THD *thd, List<Item> *args) thd->spcont->callers_arena= thd; } - if (!(nctx= new sp_rcontext(octx, csize, hmax, cmax))) + if (!(nctx= new sp_rcontext(m_pcont, NULL, octx)) || + nctx->init(thd)) { + delete nctx; /* Delete nctx if it was init() that failed. */ thd->spcont= save_spcont; - DBUG_RETURN(-1); + DBUG_RETURN(TRUE); } #ifndef DBUG_OFF - nctx->owner= this; + nctx->sp= this; #endif - if (csize > 0 || hmax > 0 || cmax > 0) + if (params > 0) { - Item_null *nit= NULL; // Re-use this, and only create if needed - uint i; - List_iterator<Item> li(*args); - Item *it; + List_iterator<Item> it_args(*args); - /* Evaluate SP arguments (i.e. get the values passed as parameters) */ - // QQ: Should do type checking? DBUG_PRINT("info",(" %.*s: eval args", m_name.length, m_name.str)); - for (i = 0 ; (it= li++) && i < params ; i++) + + for (uint i= 0 ; i < params ; i++) { + Item *arg_item= it_args++; sp_pvar_t *pvar= m_pcont->find_pvar(i); - if (pvar) + if (!arg_item) + break; + + if (!pvar) + continue; + + if (pvar->mode != sp_param_in) { - if (pvar->mode != sp_param_in) - { - if (!it->is_splocal() && !item_is_user_var(it)) - { - my_error(ER_SP_NOT_VAR_ARG, MYF(0), i+1, m_qname.str); - ret= -1; - break; - } - } - if (pvar->mode == sp_param_out) - { - if (! nit) - { - if (!(nit= new Item_null())) - { - ret= -1; - break; - } - } - nctx->push_item(nit); // OUT - } - else - { - Item *it2= sp_eval_func_item(thd, li.ref(), pvar->type, NULL, FALSE); - - if (!it2) - { - ret= -1; // Eval failed - break; - } - nctx->push_item(it2); // IN or INOUT - } + if (!arg_item->is_splocal() && !item_is_user_var(arg_item)) + { + my_error(ER_SP_NOT_VAR_ARG, MYF(0), i+1, m_qname.str); + err_status= TRUE; + break; + } + } + + if (pvar->mode == sp_param_out) + { + Item_null *null_item= new Item_null(); + + if (!null_item || + nctx->set_variable(thd, i, null_item)) + { + err_status= TRUE; + break; + } + } + else + { + if (nctx->set_variable(thd, i, *it_args.ref())) + { + err_status= TRUE; + break; + } } } @@ -1457,20 +1459,12 @@ int sp_head::execute_procedure(THD *thd, List<Item> *args) close_thread_tables(thd, 0, 0); DBUG_PRINT("info",(" %.*s: eval args done", m_name.length, m_name.str)); - - /* - The rest of the frame are local variables which are all IN. - Push NULLs to get the right size (and make the reuse mechanism work) - - the will be initialized by set instructions in each frame. - */ - for (; i < csize ; i++) - nctx->push_item(NULL); } thd->spcont= nctx; - if (! ret) - ret= execute(thd); + if (!err_status) + err_status= execute(thd); /* In the case when we weren't able to employ reuse mechanism for @@ -1480,74 +1474,67 @@ int sp_head::execute_procedure(THD *thd, List<Item> *args) */ thd->spcont->callers_arena= octx->callers_arena; - if (!ret && csize > 0) + if (!err_status && params > 0) { - List_iterator<Item> li(*args); - Item *it; + List_iterator<Item> it_args(*args); /* Copy back all OUT or INOUT values to the previous frame, or set global user variables */ - for (uint i = 0 ; (it= li++) && i < params ; i++) + for (uint i= 0 ; i < params ; i++) { + Item *arg_item= it_args++; + + if (!arg_item) + break; + sp_pvar_t *pvar= m_pcont->find_pvar(i); - if (pvar->mode != sp_param_in) + if (pvar->mode == sp_param_in) + continue; + + if (arg_item->is_splocal()) { - if (it->is_splocal()) - { - // Have to copy the item to the caller's mem_root - Item *copy; - uint offset= static_cast<Item_splocal *>(it)->get_offset(); - Item *val= nctx->get_item(i); - Item *orig= octx->get_item(offset); - - /* - We might need to allocate new item if we weren't able to - employ reuse mechanism. Then we should do it on the callers arena. - */ - copy= sp_eval_func_item(thd, &val, pvar->type, orig, TRUE); // Copy - - if (!copy) - { - ret= -1; - break; - } - if (copy != orig) - octx->set_item(offset, copy); - } - else + if (octx->set_variable(thd, + ((Item_splocal*) arg_item)->get_var_idx(), + nctx->get_item(i))) + { + err_status= TRUE; + break; + } + } + else + { + Item_func_get_user_var *guv= item_is_user_var(arg_item); + + if (guv) { - Item_func_get_user_var *guv= item_is_user_var(it); - - if (guv) - { - Item *item= nctx->get_item(i); - Item_func_set_user_var *suv; - - suv= new Item_func_set_user_var(guv->get_name(), item); - /* - Item_func_set_user_var is not fixed after construction, - call fix_fields(). - */ - if ((ret= test(!suv || suv->fix_fields(thd, &item) || - suv->check() || suv->update()))) - break; - } + Item *item= nctx->get_item(i); + Item_func_set_user_var *suv; + + suv= new Item_func_set_user_var(guv->get_name(), item); + /* + Item_func_set_user_var is not fixed after construction, + call fix_fields(). + */ + if ((ret= test(!suv || suv->fix_fields(thd, &item) || + suv->check() || suv->update()))) + break; } } + } } if (!save_spcont) - delete octx; // Does nothing + delete octx; nctx->pop_all_cursors(); // To avoid memory leaks after an error - delete nctx; // Does nothing + delete nctx; thd->spcont= save_spcont; - DBUG_RETURN(ret); + DBUG_RETURN(err_status); } @@ -1583,6 +1570,15 @@ sp_head::reset_lex(THD *thd) sublex->trg_chistics= oldlex->trg_chistics; sublex->trg_table_fields.empty(); sublex->sp_lex_in_use= FALSE; + + /* Reset type info. */ + + sublex->charset= NULL; + sublex->length= NULL; + sublex->dec= NULL; + sublex->interval_list.empty(); + sublex->type= 0; + DBUG_VOID_RETURN; } @@ -1677,6 +1673,55 @@ sp_head::check_backpatch(THD *thd) return 0; } + +/* + Prepare an instance of create_field for field creation (fill all necessary + attributes). + + SYNOPSIS + sp_head::fill_field_definition() + thd [IN] Thread handle + lex [IN] Yacc parsing context + field_type [IN] Field type + field_def [OUT] An instance of create_field to be filled + + RETURN + FALSE on success + TRUE on error +*/ + +bool +sp_head::fill_field_definition(THD *thd, LEX *lex, + enum enum_field_types field_type, + create_field *field_def) +{ + LEX_STRING cmt = { 0, 0 }; + uint unused1= 0; + int unused2= 0; + + if (field_def->init(thd, (char*) "", field_type, lex->length, lex->dec, + lex->type, (Item*) 0, (Item*) 0, &cmt, 0, + &lex->interval_list, + (lex->charset ? lex->charset : default_charset_info), + lex->uint_geom_type)) + return TRUE; + + if (field_def->interval_list.elements) + field_def->interval= create_typelib(mem_root, field_def, + &field_def->interval_list); + + sp_prepare_create_field(thd, field_def); + + if (prepare_create_field(field_def, &unused1, &unused2, &unused2, + HA_CAN_GEOMETRY)) + { + return TRUE; + } + + return FALSE; +} + + void sp_head::set_info(longlong created, longlong modified, st_sp_chistics *chistics, ulong sql_mode) @@ -2219,28 +2264,28 @@ sp_instr_set::execute(THD *thd, uint *nextp) int sp_instr_set::exec_core(THD *thd, uint *nextp) { - int res= thd->spcont->set_item_eval(thd, m_offset, &m_value, m_type); + int res= thd->spcont->set_variable(thd, m_offset, m_value); - if (res < 0 && - thd->spcont->get_item(m_offset) == NULL && - thd->spcont->found_handler_here()) + if (res && thd->spcont->found_handler_here()) { /* - Failed to evaluate the value, the variable is still not initialized, - and a handler has been found. Set to null so we can continue. + Failed to evaluate the value, and a handler has been found. Reset the + variable to NULL. */ - Item *it= new Item_null(); - if (!it || thd->spcont->set_item_eval(thd, m_offset, &it, m_type) < 0) - { /* If this also failed, we have to abort */ - sp_rcontext *spcont= thd->spcont; + if (thd->spcont->set_variable(thd, m_offset, 0)) + { + /* If this also failed, let's abort. */ + sp_rcontext *spcont= thd->spcont; + thd->spcont= 0; /* Avoid handlers */ my_error(ER_OUT_OF_RESOURCES, MYF(0)); spcont->clear_handler(); thd->spcont= spcont; } } + *nextp = m_ip+1; return res; } @@ -2509,20 +2554,22 @@ sp_instr_freturn::execute(THD *thd, uint *nextp) int sp_instr_freturn::exec_core(THD *thd, uint *nextp) { - Item *it; - int res; + /* + Change <next instruction pointer>, so that this will be the last + instruction in the stored function. + */ - it= sp_eval_func_item(thd, &m_value, m_type, NULL, TRUE); - if (! it) - res= -1; - else - { - res= 0; - thd->spcont->set_result(it); - } *nextp= UINT_MAX; - return res; + /* + Evaluate the value of return expression and store it in current runtime + context. + + NOTE: It's necessary to evaluate result item right here, because we must + do it in scope of execution the current context/block. + */ + + return thd->spcont->set_return_value(thd, m_value); } void @@ -2643,7 +2690,6 @@ sp_instr_hreturn::execute(THD *thd, uint *nextp) *nextp= m_dest; else { - thd->spcont->restore_variables(m_frame); *nextp= thd->spcont->pop_hstack(); } thd->spcont->exit_handler(); @@ -2961,6 +3007,65 @@ sp_instr_error::print(String *str) } +/************************************************************************** + sp_instr_set_case_expr class implementation +**************************************************************************/ + +int +sp_instr_set_case_expr::execute(THD *thd, uint *nextp) +{ + DBUG_ENTER("sp_instr_set_case_expr::execute"); + + DBUG_RETURN(m_lex_keeper.reset_lex_and_exec_core(thd, nextp, TRUE, this)); +} + + +int +sp_instr_set_case_expr::exec_core(THD *thd, uint *nextp) +{ + int res= thd->spcont->set_case_expr(thd, m_case_expr_id, m_case_expr); + + if (res && + !thd->spcont->get_case_expr(m_case_expr_id) && + thd->spcont->found_handler_here()) + { + /* + Failed to evaluate the value, the case expression is still not + initialized, and a handler has been found. Set to NULL so we can continue. + */ + + Item *null_item= new Item_null(); + + if (!null_item || + thd->spcont->set_case_expr(thd, m_case_expr_id, null_item)) + { + /* If this also failed, we have to abort. */ + + sp_rcontext *spcont= thd->spcont; + + thd->spcont= 0; /* Avoid handlers */ + my_error(ER_OUT_OF_RESOURCES, MYF(0)); + spcont->clear_handler(); + thd->spcont= spcont; + } + } + + *nextp = m_ip+1; + + return res; /* no error */ +} + + +void +sp_instr_set_case_expr::print(String *str) +{ + str->append(STRING_WITH_LEN("set_case_expr ")); + str->qs_append(m_case_expr_id); + str->append(' '); + m_case_expr->print(str); +} + + /* ------------------------------------------------------------------ */ /* diff --git a/sql/sp_head.h b/sql/sp_head.h index 734442724fb..ad747b3466f 100644 --- a/sql/sp_head.h +++ b/sql/sp_head.h @@ -33,6 +33,9 @@ Item_result sp_map_result_type(enum enum_field_types type); +Item::Type +sp_map_item_type(enum enum_field_types type); + uint sp_get_flags_for_command(LEX *lex); @@ -123,12 +126,9 @@ public: /* TYPE_ENUM_FUNCTION, TYPE_ENUM_PROCEDURE or TYPE_ENUM_TRIGGER */ int m_type; uint m_flags; // Boolean attributes of a stored routine - enum enum_field_types m_returns; // For FUNCTIONs only - Field::geometry_type m_geom_returns; - CHARSET_INFO *m_returns_cs; // For FUNCTIONs only - TYPELIB *m_returns_typelib; // For FUNCTIONs only - uint m_returns_len; // For FUNCTIONs only - uint m_returns_pack; // For FUNCTIONs only + + create_field m_return_field_def; /* This is used for FUNCTIONs only. */ + uchar *m_tmp_query; // Temporary pointer to sub query string uint m_old_cmq; // Old CLIENT_MULTI_QUERIES value st_sp_chistics *m_chistics; @@ -202,9 +202,6 @@ public: void init_strings(THD *thd, LEX *lex, sp_name *name); - TYPELIB * - create_typelib(List<String> *src); - int create(THD *thd); @@ -214,10 +211,10 @@ public: void destroy(); - int - execute_function(THD *thd, Item **args, uint argcount, Item **resp); + bool + execute_function(THD *thd, Item **args, uint argcount, Field *return_fld); - int + bool execute_procedure(THD *thd, List<Item> *args); int @@ -278,7 +275,12 @@ public: char *create_string(THD *thd, ulong *lenp); - Field *make_field(uint max_length, const char *name, TABLE *dummy); + Field *create_result_field(uint field_max_length, const char *field_name, + TABLE *table); + + bool fill_field_definition(THD *thd, LEX *lex, + enum enum_field_types field_type, + create_field *field_def); void set_info(longlong created, longlong modified, st_sp_chistics *chistics, ulong sql_mode); @@ -364,7 +366,7 @@ private: */ HASH m_sptabs; - int + bool execute(THD *thd); /* @@ -1075,6 +1077,31 @@ private: }; // class sp_instr_error : public sp_instr +class sp_instr_set_case_expr :public sp_instr +{ +public: + + sp_instr_set_case_expr(uint ip, sp_pcontext *ctx, uint case_expr_id, + Item *case_expr, LEX *lex) + :sp_instr(ip, ctx), m_case_expr_id(case_expr_id), m_case_expr(case_expr), + m_lex_keeper(lex, TRUE) + {} + + virtual int execute(THD *thd, uint *nextp); + + virtual int exec_core(THD *thd, uint *nextp); + + virtual void print(String *str); + +private: + + uint m_case_expr_id; + Item *m_case_expr; + sp_lex_keeper m_lex_keeper; + +}; // class sp_instr_set_case_expr : public sp_instr + + #ifndef NO_EMBEDDED_ACCESS_CHECKS bool sp_change_security_context(THD *thd, sp_head *sp, @@ -1087,8 +1114,10 @@ TABLE_LIST * sp_add_to_query_tables(THD *thd, LEX *lex, const char *db, const char *name, thr_lock_type locktype); +Item * +sp_prepare_func_item(THD* thd, Item **it_addr); -Item *sp_eval_func_item(THD *thd, Item **it, enum_field_types type, - Item *reuse, bool use_callers_arena); +bool +sp_eval_expr(THD *thd, Field *result_field, Item *expr_item); #endif /* _SP_HEAD_H_ */ diff --git a/sql/sp_pcontext.cc b/sql/sp_pcontext.cc index 6f3a9cb04aa..a8bd8cd2aa0 100644 --- a/sql/sp_pcontext.cc +++ b/sql/sp_pcontext.cc @@ -51,21 +51,26 @@ sp_cond_check(LEX_STRING *sqlstate) } sp_pcontext::sp_pcontext(sp_pcontext *prev) - : Sql_alloc(), m_psubsize(0), m_csubsize(0), m_hsubsize(0), - m_handlers(0), m_parent(prev), m_pboundary(0) + :Sql_alloc(), m_total_pvars(0), m_csubsize(0), m_hsubsize(0), + m_handlers(0), m_parent(prev), m_pboundary(0) { VOID(my_init_dynamic_array(&m_pvar, sizeof(sp_pvar_t *), 16, 8)); + VOID(my_init_dynamic_array(&m_case_expr_id_lst, sizeof(int), 16, 8)); VOID(my_init_dynamic_array(&m_cond, sizeof(sp_cond_type_t *), 16, 8)); VOID(my_init_dynamic_array(&m_cursor, sizeof(LEX_STRING), 16, 8)); VOID(my_init_dynamic_array(&m_handler, sizeof(sp_cond_type_t *), 16, 8)); m_label.empty(); m_children.empty(); if (!prev) + { m_poffset= m_coffset= 0; + m_num_case_exprs= 0; + } else { - m_poffset= prev->current_pvars(); + m_poffset= prev->m_poffset + prev->m_total_pvars; m_coffset= prev->current_cursors(); + m_num_case_exprs= prev->get_num_case_exprs(); } } @@ -81,6 +86,7 @@ sp_pcontext::destroy() m_children.empty(); m_label.empty(); delete_dynamic(&m_pvar); + delete_dynamic(&m_case_expr_id_lst); delete_dynamic(&m_cond); delete_dynamic(&m_cursor); delete_dynamic(&m_handler); @@ -99,16 +105,19 @@ sp_pcontext::push_context() sp_pcontext * sp_pcontext::pop_context() { - uint submax= max_pvars(); + m_parent->m_total_pvars= m_parent->m_total_pvars + m_total_pvars; - if (submax > m_parent->m_psubsize) - m_parent->m_psubsize= submax; - submax= max_handlers(); + uint submax= max_handlers(); if (submax > m_parent->m_hsubsize) m_parent->m_hsubsize= submax; + submax= max_cursors(); if (submax > m_parent->m_csubsize) m_parent->m_csubsize= submax; + + if (m_num_case_exprs > m_parent->m_num_case_exprs) + m_parent->m_num_case_exprs= m_num_case_exprs; + return m_parent; } @@ -191,26 +200,29 @@ sp_pcontext::find_pvar(uint offset) return NULL; // index out of bounds } -void +sp_pvar_t * sp_pcontext::push_pvar(LEX_STRING *name, enum enum_field_types type, sp_param_mode_t mode) { sp_pvar_t *p= (sp_pvar_t *)sql_alloc(sizeof(sp_pvar_t)); - if (p) - { - if (m_pvar.elements == m_psubsize) - m_psubsize+= 1; - p->name.str= name->str; - p->name.length= name->length; - p->type= type; - p->mode= mode; - p->offset= current_pvars(); - p->dflt= NULL; - insert_dynamic(&m_pvar, (gptr)&p); - } + if (!p) + return NULL; + + ++m_total_pvars; + + p->name.str= name->str; + p->name.length= name->length; + p->type= type; + p->mode= mode; + p->offset= current_pvars(); + p->dflt= NULL; + insert_dynamic(&m_pvar, (gptr)&p); + + return p; } + sp_label_t * sp_pcontext::push_label(char *name, uint ip) { @@ -354,6 +366,29 @@ sp_pcontext::find_cursor(LEX_STRING *name, uint *poff, my_bool scoped) return FALSE; } + +void +sp_pcontext::retrieve_field_definitions(List<create_field> *field_def_lst) +{ + /* Put local/context fields in the result list. */ + + for (uint i = 0; i < m_pvar.elements; ++i) + { + sp_pvar_t *var_def; + get_dynamic(&m_pvar, (gptr) &var_def, i); + + field_def_lst->push_back(&var_def->field_def); + } + + /* Put the fields of the enclosed contexts in the result list. */ + + List_iterator_fast<sp_pcontext> li(m_children); + sp_pcontext *ctx; + + while ((ctx = li++)) + ctx->retrieve_field_definitions(field_def_lst); +} + /* Find a cursor by offset from the top. This is only used for debugging. diff --git a/sql/sp_pcontext.h b/sql/sp_pcontext.h index 5c5890f82cd..6d803362d86 100644 --- a/sql/sp_pcontext.h +++ b/sql/sp_pcontext.h @@ -34,8 +34,16 @@ typedef struct sp_pvar LEX_STRING name; enum enum_field_types type; sp_param_mode_t mode; - uint offset; // Offset in current frame + + /* + offset -- basically, this is an index of variable in the scope of root + parsing context. This means, that all variables in a stored routine + have distinct indexes/offsets. + */ + uint offset; + Item *dflt; + create_field field_def; } sp_pvar_t; @@ -114,9 +122,9 @@ class sp_pcontext : public Sql_alloc // inline uint - max_pvars() + total_pvars() { - return m_psubsize + m_pvar.elements; + return m_total_pvars; } inline uint @@ -155,16 +163,15 @@ class sp_pcontext : public Sql_alloc p->dflt= it; } - void + sp_pvar_t * push_pvar(LEX_STRING *name, enum enum_field_types type, sp_param_mode_t mode); - // Pop the last 'num' slots of the frame - inline void - pop_pvar(uint num = 1) - { - while (num--) - pop_dynamic(&m_pvar); - } + /* + Retrieve definitions of fields from the current context and its + children. + */ + void + retrieve_field_definitions(List<create_field> *field_def_lst); // Find by name sp_pvar_t * @@ -175,7 +182,7 @@ class sp_pcontext : public Sql_alloc find_pvar(uint offset); /* - Set the current scope boundary (for default values) + Set the current scope boundary (for default values). The argument is the number of variables to skip. */ inline void @@ -184,6 +191,45 @@ class sp_pcontext : public Sql_alloc m_pboundary= n; } + /* + CASE expressions support. + */ + + inline int + register_case_expr() + { + return m_num_case_exprs++; + } + + inline int + get_num_case_exprs() const + { + return m_num_case_exprs; + } + + inline bool + push_case_expr_id(int case_expr_id) + { + return insert_dynamic(&m_case_expr_id_lst, (gptr) &case_expr_id); + } + + inline void + pop_case_expr_id() + { + pop_dynamic(&m_case_expr_id_lst); + } + + inline int + get_current_case_expr_id() const + { + int case_expr_id; + + get_dynamic((DYNAMIC_ARRAY*)&m_case_expr_id_lst, (gptr) &case_expr_id, + m_case_expr_id_lst.elements - 1); + + return case_expr_id; + } + // // Labels // @@ -280,8 +326,18 @@ class sp_pcontext : public Sql_alloc protected: + /* + m_total_pvars -- number of variables (including all types of arguments) + in this context including all children contexts. + + m_total_pvars >= m_pvar.elements. + + m_total_pvars of the root parsing context contains number of all + variables (including arguments) in all enclosed contexts. + */ + uint m_total_pvars; + // The maximum sub context's framesizes - uint m_psubsize; uint m_csubsize; uint m_hsubsize; uint m_handlers; // No. of handlers in this context @@ -290,8 +346,19 @@ private: sp_pcontext *m_parent; // Parent context - uint m_poffset; // Variable offset for this context + /* + m_poffset -- basically, this is an index of the first variable in this + parsing context. + + m_poffset is 0 for root context. + + Since now each variable is stored in separate place, no reuse is done, + so m_poffset is different for all enclosed contexts. + */ + uint m_poffset; + uint m_coffset; // Cursor offset for this context + /* Boundary for finding variables in this context. This is the number of variables currently "invisible" to default clauses. @@ -300,7 +367,10 @@ private: */ uint m_pboundary; + int m_num_case_exprs; + DYNAMIC_ARRAY m_pvar; // Parameters/variables + DYNAMIC_ARRAY m_case_expr_id_lst; /* Stack of CASE expression ids. */ DYNAMIC_ARRAY m_cond; // Conditions DYNAMIC_ARRAY m_cursor; // Cursors DYNAMIC_ARRAY m_handler; // Handlers, for checking of duplicates diff --git a/sql/sp_rcontext.cc b/sql/sp_rcontext.cc index ccb38358049..eca87e69f8e 100644 --- a/sql/sp_rcontext.cc +++ b/sql/sp_rcontext.cc @@ -29,41 +29,137 @@ #include "sp_rcontext.h" #include "sp_pcontext.h" -sp_rcontext::sp_rcontext(sp_rcontext *prev, uint fsize, uint hmax, uint cmax) - : m_count(0), m_fsize(fsize), m_result(NULL), m_hcount(0), m_hsp(0), - m_ihsp(0), m_hfound(-1), m_ccount(0), m_prev_ctx(prev) + +sp_rcontext::sp_rcontext(sp_pcontext *root_parsing_ctx, + Field *return_value_fld, + sp_rcontext *prev_runtime_ctx) + :m_root_parsing_ctx(root_parsing_ctx), + m_var_table(0), + m_var_items(0), + m_return_value_fld(return_value_fld), + m_return_value_set(FALSE), + m_hcount(0), + m_hsp(0), + m_ihsp(0), + m_hfound(-1), + m_ccount(0), + m_case_expr_holders(0), + m_prev_runtime_ctx(prev_runtime_ctx) { - m_frame= (Item **)sql_alloc(fsize * sizeof(Item*)); - m_handler= (sp_handler_t *)sql_alloc(hmax * sizeof(sp_handler_t)); - m_hstack= (uint *)sql_alloc(hmax * sizeof(uint)); - m_in_handler= (uint *)sql_alloc(hmax * sizeof(uint)); - m_cstack= (sp_cursor **)sql_alloc(cmax * sizeof(sp_cursor *)); - m_saved.empty(); } -int -sp_rcontext::set_item_eval(THD *thd, uint idx, Item **item_addr, - enum_field_types type) +sp_rcontext::~sp_rcontext() +{ + if (m_var_table) + free_blobs(m_var_table); +} + + +/* + Initialize sp_rcontext instance. + + SYNOPSIS + thd Thread handle + RETURN + FALSE on success + TRUE on error +*/ + +bool sp_rcontext::init(THD *thd) +{ + if (init_var_table(thd) || init_var_items()) + return TRUE; + + return + !(m_handler= + (sp_handler_t*)thd->alloc(m_root_parsing_ctx->max_handlers() * + sizeof(sp_handler_t))) || + !(m_hstack= + (uint*)thd->alloc(m_root_parsing_ctx->max_handlers() * + sizeof(uint))) || + !(m_in_handler= + (uint*)thd->alloc(m_root_parsing_ctx->max_handlers() * + sizeof(uint))) || + !(m_cstack= + (sp_cursor**)thd->alloc(m_root_parsing_ctx->max_cursors() * + sizeof(sp_cursor*))) || + !(m_case_expr_holders= + (Item_cache**)thd->calloc(m_root_parsing_ctx->get_num_case_exprs() * + sizeof (Item_cache*))); +} + + +/* + Create and initialize a table to store SP-vars. + + SYNOPSIS + thd Thread handler. + RETURN + FALSE on success + TRUE on error +*/ + +bool +sp_rcontext::init_var_table(THD *thd) { - Item *it; - Item *reuse_it; - /* sp_eval_func_item will use callers_arena */ - int res; - - reuse_it= get_item(idx); - it= sp_eval_func_item(thd, item_addr, type, reuse_it, TRUE); - if (! it) - res= -1; - else + List<create_field> field_def_lst; + + if (!m_root_parsing_ctx->total_pvars()) + return FALSE; + + m_root_parsing_ctx->retrieve_field_definitions(&field_def_lst); + + DBUG_ASSERT(field_def_lst.elements == m_root_parsing_ctx->total_pvars()); + + if (!(m_var_table= create_virtual_tmp_table(thd, field_def_lst))) + return TRUE; + + m_var_table->copy_blobs= TRUE; + m_var_table->alias= ""; + + return FALSE; +} + + +/* + Create and initialize an Item-adapter (Item_field) for each SP-var field. + + RETURN + FALSE on success + TRUE on error +*/ + +bool +sp_rcontext::init_var_items() +{ + uint idx; + uint num_vars= m_root_parsing_ctx->total_pvars(); + + if (!(m_var_items= (Item**) sql_alloc(num_vars * sizeof (Item *)))) + return TRUE; + + for (idx = 0; idx < num_vars; ++idx) { - res= 0; - set_item(idx, it); + if (!(m_var_items[idx]= new Item_field(m_var_table->field[idx]))) + return TRUE; } - return res; + return FALSE; } + +bool +sp_rcontext::set_return_value(THD *thd, Item *return_value_item) +{ + DBUG_ASSERT(m_return_value_fld); + + m_return_value_set = TRUE; + + return sp_eval_expr(thd, m_return_value_fld, return_value_item); +} + + bool sp_rcontext::find_handler(uint sql_errno, MYSQL_ERROR::enum_warning_level level) @@ -117,32 +213,14 @@ sp_rcontext::find_handler(uint sql_errno, } if (found < 0) { - if (m_prev_ctx) - return m_prev_ctx->find_handler(sql_errno, level); + if (m_prev_runtime_ctx) + return m_prev_runtime_ctx->find_handler(sql_errno, level); return FALSE; } m_hfound= found; return TRUE; } -void -sp_rcontext::save_variables(uint fp) -{ - while (fp < m_count) - { - m_saved.push_front(m_frame[fp]); - m_frame[fp++]= NULL; // Prevent reuse - } -} - -void -sp_rcontext::restore_variables(uint fp) -{ - uint i= m_count; - - while (i-- > fp) - m_frame[i]= m_saved.pop(); -} void sp_rcontext::push_cursor(sp_lex_keeper *lex_keeper, sp_instr_cpush *i) @@ -150,6 +228,7 @@ sp_rcontext::push_cursor(sp_lex_keeper *lex_keeper, sp_instr_cpush *i) m_cstack[m_ccount++]= new sp_cursor(lex_keeper, i); } + void sp_rcontext::pop_cursors(uint count) { @@ -160,6 +239,40 @@ sp_rcontext::pop_cursors(uint count) } +int +sp_rcontext::set_variable(THD *thd, uint var_idx, Item *value) +{ + return set_variable(thd, m_var_table->field[var_idx], value); +} + + +int +sp_rcontext::set_variable(THD *thd, Field *field, Item *value) +{ + if (!value) + { + field->set_null(); + return 0; + } + + return sp_eval_expr(thd, field, value); +} + + +Item * +sp_rcontext::get_item(uint var_idx) +{ + return m_var_items[var_idx]; +} + + +Item ** +sp_rcontext::get_item_addr(uint var_idx) +{ + return m_var_items + var_idx; +} + + /* * * sp_cursor @@ -263,6 +376,102 @@ sp_cursor::fetch(THD *thd, List<struct sp_pvar> *vars) } +/* + Create an instance of appropriate Item_cache class depending on the + specified type in the callers arena. + + SYNOPSIS + thd thread handler + result_type type of the expression + + RETURN + Pointer to valid object on success + NULL on error + + NOTE + We should create cache items in the callers arena, as they are used + between in several instructions. +*/ + +Item_cache * +sp_rcontext::create_case_expr_holder(THD *thd, Item_result result_type) +{ + Item_cache *holder; + Query_arena current_arena; + + thd->set_n_backup_active_arena(thd->spcont->callers_arena, ¤t_arena); + + holder= Item_cache::get_cache(result_type); + + thd->restore_active_arena(thd->spcont->callers_arena, ¤t_arena); + + return holder; +} + + +/* + Set CASE expression to the specified value. + + SYNOPSIS + thd thread handler + case_expr_id identifier of the CASE expression + case_expr_item a value of the CASE expression + + RETURN + FALSE on success + TRUE on error + + NOTE + The idea is to reuse Item_cache for the expression of the one CASE + statement. This optimization takes place when there is CASE statement + inside of a loop. So, in other words, we will use the same object on each + iteration instead of creating a new one for each iteration. + + TODO + Hypothetically, a type of CASE expression can be different for each + iteration. For instance, this can happen if the expression contains a + session variable (something like @@VAR) and its type is changed from one + iteration to another. + + In order to cope with this problem, we check type each time, when we use + already created object. If the type does not match, we re-create Item. + This also can (should?) be optimized. +*/ + +int +sp_rcontext::set_case_expr(THD *thd, int case_expr_id, Item *case_expr_item) +{ + if (!(case_expr_item= sp_prepare_func_item(thd, &case_expr_item))) + return TRUE; + + if (!m_case_expr_holders[case_expr_id] || + m_case_expr_holders[case_expr_id]->result_type() != + case_expr_item->result_type()) + { + m_case_expr_holders[case_expr_id]= + create_case_expr_holder(thd, case_expr_item->result_type()); + } + + m_case_expr_holders[case_expr_id]->store(case_expr_item); + + return FALSE; +} + + +Item * +sp_rcontext::get_case_expr(int case_expr_id) +{ + return m_case_expr_holders[case_expr_id]; +} + + +Item ** +sp_rcontext::get_case_expr_addr(int case_expr_id) +{ + return (Item**) m_case_expr_holders + case_expr_id; +} + + /*************************************************************************** Select_fetch_into_spvars ****************************************************************************/ @@ -294,11 +503,8 @@ bool Select_fetch_into_spvars::send_data(List<Item> &items) */ for (; pv= pv_iter++, item= item_iter++; ) { - Item *reuse= thd->spcont->get_item(pv->offset); - /* Evaluate a new item on the arena of the calling instruction */ - Item *it= sp_eval_func_item(thd, &item, pv->type, reuse, TRUE); - - thd->spcont->set_item(pv->offset, it); + if (thd->spcont->set_variable(thd, pv->offset, item)) + return TRUE; } return FALSE; } diff --git a/sql/sp_rcontext.h b/sql/sp_rcontext.h index d54188aa96f..c3c05228eef 100644 --- a/sql/sp_rcontext.h +++ b/sql/sp_rcontext.h @@ -43,12 +43,22 @@ typedef struct /* - This is a run context? of one SP ? - THis is - - a stack of cursors? - - a stack of handlers? - - a stack of Items ? - - a stack of instruction locations in SP? + This class is a runtime context of a Stored Routine. It is used in an + execution and is intended to contain all dynamic objects (i.e. objects, which + can be changed during execution), such as: + - stored routine variables; + - cursors; + - handlers; + + Runtime context is used with sp_head class. sp_head class is intended to + contain all static things, related to the stored routines (code, for example). + sp_head instance creates runtime context for the execution of a stored + routine. + + There is a parsing context (an instance of sp_pcontext class), which is used + on parsing stage. However, now it contains some necessary for an execution + things, such as definition of used stored routine variables. That's why + runtime context needs a reference to the parsing context. */ class sp_rcontext : public Sql_alloc @@ -68,62 +78,34 @@ class sp_rcontext : public Sql_alloc #ifndef DBUG_OFF /* - Routine to which this Item_splocal belongs. Used for checking if correct - runtime context is used for variable handling. + The routine for which this runtime context is created. Used for checking + if correct runtime context is used for variable handling. */ - sp_head *owner; + sp_head *sp; #endif - sp_rcontext(sp_rcontext *prev, uint fsize, uint hmax, uint cmax); + sp_rcontext(sp_pcontext *root_parsing_ctx, Field *return_value_fld, + sp_rcontext *prev_runtime_ctx); + bool init(THD *thd); - ~sp_rcontext() - { - // Not needed? - //sql_element_free(m_frame); - //m_saved.empty(); - } - - inline void - push_item(Item *i) - { - if (m_count < m_fsize) - m_frame[m_count++]= i; - } + ~sp_rcontext(); - inline void - set_item(uint idx, Item *i) - { - if (idx < m_count) - m_frame[idx]= i; - } - - /* Returns 0 on success, -1 on (eval) failure */ int - set_item_eval(THD *thd, uint idx, Item **i, enum_field_types type); - - inline Item * - get_item(uint idx) - { - return m_frame[idx]; - } + set_variable(THD *thd, uint var_idx, Item *value); - inline Item ** - get_item_addr(uint idx) - { - return m_frame + idx; - } + Item * + get_item(uint var_idx); + Item ** + get_item_addr(uint var_idx); - inline void - set_result(Item *it) - { - m_result= it; - } + bool + set_return_value(THD *thd, Item *return_value_item); - inline Item * - get_result() + inline bool + is_return_value_set() const { - return m_result; + return m_return_value_set; } inline void @@ -195,14 +177,6 @@ class sp_rcontext : public Sql_alloc m_ihsp-= 1; } - // Save variables starting at fp and up - void - save_variables(uint fp); - - // Restore variables down to fp - void - restore_variables(uint fp); - void push_cursor(sp_lex_keeper *lex_keeper, sp_instr_cpush *i); @@ -221,13 +195,42 @@ class sp_rcontext : public Sql_alloc return m_cstack[i]; } + /* + CASE expressions support. + */ + + int + set_case_expr(THD *thd, int case_expr_id, Item *case_expr_item); + + Item * + get_case_expr(int case_expr_id); + + Item ** + get_case_expr_addr(int case_expr_id); + private: + sp_pcontext *m_root_parsing_ctx; - uint m_count; - uint m_fsize; - Item **m_frame; + /* Virtual table for storing variables. */ + TABLE *m_var_table; - Item *m_result; // For FUNCTIONs + /* + Collection of Item_field proxies, each of them points to the corresponding + field in m_var_table. + */ + Item **m_var_items; + + /* + This is a pointer to a field, which should contain return value for stored + functions (only). For stored procedures, this pointer is NULL. + */ + Field *m_return_value_fld; + + /* + Indicates whether the return value (in m_return_value_fld) has been set + during execution. + */ + bool m_return_value_set; sp_handler_t *m_handler; // Visible handlers uint m_hcount; // Stack pointer for m_handler @@ -236,13 +239,22 @@ private: uint *m_in_handler; // Active handler, for recursion check uint m_ihsp; // Stack pointer for m_in_handler int m_hfound; // Set by find_handler; -1 if not found - List<Item> m_saved; // Saved variables during handler exec. sp_cursor **m_cstack; uint m_ccount; - sp_rcontext *m_prev_ctx; // Previous context (NULL if none) + Item_cache **m_case_expr_holders; + + /* Previous runtime context (NULL if none) */ + sp_rcontext *m_prev_runtime_ctx; + +private: + bool init_var_table(THD *thd); + bool init_var_items(); + + Item_cache *create_case_expr_holder(THD *thd, Item_result result_type); + int set_variable(THD *thd, Field *field, Item *value); }; // class sp_rcontext : public Sql_alloc diff --git a/sql/sql_class.cc b/sql/sql_class.cc index 989b8142202..f24d8ed4e50 100644 --- a/sql/sql_class.cc +++ b/sql/sql_class.cc @@ -1503,10 +1503,10 @@ int select_dumpvar::prepare(List<Item> &list, SELECT_LEX_UNIT *u) my_var *mv= gl++; if (mv->local) { - Item_splocal *var; - (void)local_vars.push_back(var= new Item_splocal(mv->s, mv->offset)); + Item_splocal *var= new Item_splocal(mv->s, mv->offset, mv->type); + (void)local_vars.push_back(var); #ifndef DBUG_OFF - var->owner= mv->owner; + var->m_sp= mv->sp; #endif } else @@ -1779,8 +1779,8 @@ bool select_dumpvar::send_data(List<Item> &items) { if ((yy=var_li++)) { - if (thd->spcont->set_item_eval(current_thd, - yy->get_offset(), it.ref(), zz->type)) + if (thd->spcont->set_variable(current_thd, yy->get_var_idx(), + *it.ref())) DBUG_RETURN(1); } } diff --git a/sql/sql_class.h b/sql/sql_class.h index e4e024cbb09..108e4723070 100644 --- a/sql/sql_class.h +++ b/sql/sql_class.h @@ -2100,7 +2100,7 @@ public: Routine to which this Item_splocal belongs. Used for checking if correct runtime context is used for variable handling. */ - sp_head *owner; + sp_head *sp; #endif bool local; uint offset; diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc index 6a453407cd0..1428e50253a 100644 --- a/sql/sql_parse.cc +++ b/sql/sql_parse.cc @@ -2617,7 +2617,7 @@ mysql_execute_command(THD *thd) goto error; /* purecov: inspected */ thd->enable_slow_log= opt_log_slow_admin_statements; res = mysql_backup_table(thd, first_table); - (TABLE_LIST*) select_lex->table_list.first=first_table; + select_lex->table_list.first= (byte*) first_table; lex->query_tables=all_tables; break; } @@ -2630,7 +2630,7 @@ mysql_execute_command(THD *thd) goto error; /* purecov: inspected */ thd->enable_slow_log= opt_log_slow_admin_statements; res = mysql_restore_table(thd, first_table); - (TABLE_LIST*) select_lex->table_list.first=first_table; + select_lex->table_list.first= (byte*) first_table; lex->query_tables=all_tables; break; } @@ -3134,7 +3134,7 @@ end_with_restore_list: mysql_bin_log.write(&qinfo); } } - (TABLE_LIST*) select_lex->table_list.first=first_table; + select_lex->table_list.first= (byte*) first_table; lex->query_tables=all_tables; break; } @@ -3146,7 +3146,7 @@ end_with_restore_list: goto error; /* purecov: inspected */ thd->enable_slow_log= opt_log_slow_admin_statements; res = mysql_check_table(thd, first_table, &lex->check_opt); - (TABLE_LIST*) select_lex->table_list.first=first_table; + select_lex->table_list.first= (byte*) first_table; lex->query_tables=all_tables; break; } @@ -3168,7 +3168,7 @@ end_with_restore_list: mysql_bin_log.write(&qinfo); } } - (TABLE_LIST*) select_lex->table_list.first=first_table; + select_lex->table_list.first= (byte*) first_table; lex->query_tables=all_tables; break; } @@ -3193,7 +3193,7 @@ end_with_restore_list: mysql_bin_log.write(&qinfo); } } - (TABLE_LIST*) select_lex->table_list.first=first_table; + select_lex->table_list.first= (byte*) first_table; lex->query_tables=all_tables; break; } @@ -5770,9 +5770,10 @@ bool add_field_to_list(THD *thd, char *field_name, enum_field_types type, buf, "TIMESTAMP"); } - if (!(new_field= new_create_field(thd, field_name, type, length, decimals, - type_modifier, default_value, on_update_value, - comment, change, interval_list, cs, uint_geom_type))) + if (!(new_field= new create_field()) || + new_field->init(thd, field_name, type, length, decimals, type_modifier, + default_value, on_update_value, comment, change, + interval_list, cs, uint_geom_type)) DBUG_RETURN(1); lex->create_list.push_back(new_field); @@ -5780,327 +5781,6 @@ bool add_field_to_list(THD *thd, char *field_name, enum_field_types type, DBUG_RETURN(0); } -/***************************************************************************** -** Create field definition for create -** Return 0 on failure, otherwise return create_field instance -******************************************************************************/ - -create_field * -new_create_field(THD *thd, char *field_name, enum_field_types type, - char *length, char *decimals, - uint type_modifier, - Item *default_value, Item *on_update_value, - LEX_STRING *comment, - char *change, List<String> *interval_list, CHARSET_INFO *cs, - uint uint_geom_type) -{ - register create_field *new_field; - uint sign_len, allowed_type_modifier=0; - ulong max_field_charlength= MAX_FIELD_CHARLENGTH; - DBUG_ENTER("new_create_field"); - - if (!(new_field=new create_field())) - DBUG_RETURN(NULL); - new_field->field=0; - new_field->field_name=field_name; - new_field->def= default_value; - new_field->flags= type_modifier; - new_field->unireg_check= (type_modifier & AUTO_INCREMENT_FLAG ? - Field::NEXT_NUMBER : Field::NONE); - new_field->decimals= decimals ? (uint)atoi(decimals) : 0; - if (new_field->decimals >= NOT_FIXED_DEC) - { - my_error(ER_TOO_BIG_SCALE, MYF(0), new_field->decimals, field_name, - NOT_FIXED_DEC-1); - DBUG_RETURN(NULL); - } - - new_field->sql_type=type; - new_field->length=0; - new_field->change=change; - new_field->interval=0; - new_field->pack_length= new_field->key_length= 0; - new_field->charset=cs; - new_field->geom_type= (Field::geometry_type) uint_geom_type; - - new_field->comment=*comment; - /* - Set flag if this field doesn't have a default value - */ - if (!default_value && !(type_modifier & AUTO_INCREMENT_FLAG) && - (type_modifier & NOT_NULL_FLAG) && type != FIELD_TYPE_TIMESTAMP) - new_field->flags|= NO_DEFAULT_VALUE_FLAG; - - if (length && !(new_field->length= (uint) atoi(length))) - length=0; /* purecov: inspected */ - sign_len=type_modifier & UNSIGNED_FLAG ? 0 : 1; - - switch (type) { - case FIELD_TYPE_TINY: - if (!length) new_field->length=MAX_TINYINT_WIDTH+sign_len; - allowed_type_modifier= AUTO_INCREMENT_FLAG; - break; - case FIELD_TYPE_SHORT: - if (!length) new_field->length=MAX_SMALLINT_WIDTH+sign_len; - allowed_type_modifier= AUTO_INCREMENT_FLAG; - break; - case FIELD_TYPE_INT24: - if (!length) new_field->length=MAX_MEDIUMINT_WIDTH+sign_len; - allowed_type_modifier= AUTO_INCREMENT_FLAG; - break; - case FIELD_TYPE_LONG: - if (!length) new_field->length=MAX_INT_WIDTH+sign_len; - allowed_type_modifier= AUTO_INCREMENT_FLAG; - break; - case FIELD_TYPE_LONGLONG: - if (!length) new_field->length=MAX_BIGINT_WIDTH; - allowed_type_modifier= AUTO_INCREMENT_FLAG; - break; - case FIELD_TYPE_NULL: - break; - case FIELD_TYPE_NEWDECIMAL: - if (!length && !new_field->decimals) - new_field->length= 10; - if (new_field->length > DECIMAL_MAX_PRECISION) - { - my_error(ER_TOO_BIG_PRECISION, MYF(0), new_field->length, field_name, - DECIMAL_MAX_PRECISION); - DBUG_RETURN(NULL); - } - if (new_field->length < new_field->decimals) - { - my_error(ER_M_BIGGER_THAN_D, MYF(0), field_name); - DBUG_RETURN(NULL); - } - new_field->length= - my_decimal_precision_to_length(new_field->length, new_field->decimals, - type_modifier & UNSIGNED_FLAG); - new_field->pack_length= - my_decimal_get_binary_size(new_field->length, new_field->decimals); - break; - case MYSQL_TYPE_VARCHAR: - /* - Long VARCHAR's are automaticly converted to blobs in mysql_prepare_table - if they don't have a default value - */ - max_field_charlength= MAX_FIELD_VARCHARLENGTH; - break; - case MYSQL_TYPE_STRING: - break; - case FIELD_TYPE_BLOB: - case FIELD_TYPE_TINY_BLOB: - case FIELD_TYPE_LONG_BLOB: - case FIELD_TYPE_MEDIUM_BLOB: - case FIELD_TYPE_GEOMETRY: - if (default_value) // Allow empty as default value - { - String str,*res; - res=default_value->val_str(&str); - if (res->length()) - { - my_error(ER_BLOB_CANT_HAVE_DEFAULT, MYF(0), - field_name); /* purecov: inspected */ - DBUG_RETURN(NULL); - } - new_field->def=0; - } - new_field->flags|=BLOB_FLAG; - break; - case FIELD_TYPE_YEAR: - if (!length || new_field->length != 2) - new_field->length=4; // Default length - new_field->flags|= ZEROFILL_FLAG | UNSIGNED_FLAG; - break; - case FIELD_TYPE_FLOAT: - /* change FLOAT(precision) to FLOAT or DOUBLE */ - allowed_type_modifier= AUTO_INCREMENT_FLAG; - if (length && !decimals) - { - uint tmp_length=new_field->length; - if (tmp_length > PRECISION_FOR_DOUBLE) - { - my_error(ER_WRONG_FIELD_SPEC, MYF(0), field_name); - DBUG_RETURN(NULL); - } - else if (tmp_length > PRECISION_FOR_FLOAT) - { - new_field->sql_type=FIELD_TYPE_DOUBLE; - new_field->length=DBL_DIG+7; // -[digits].E+### - } - else - new_field->length=FLT_DIG+6; // -[digits].E+## - new_field->decimals= NOT_FIXED_DEC; - break; - } - if (!length && !decimals) - { - new_field->length = FLT_DIG+6; - new_field->decimals= NOT_FIXED_DEC; - } - if (new_field->length < new_field->decimals && - new_field->decimals != NOT_FIXED_DEC) - { - my_error(ER_M_BIGGER_THAN_D, MYF(0), field_name); - DBUG_RETURN(NULL); - } - break; - case FIELD_TYPE_DOUBLE: - allowed_type_modifier= AUTO_INCREMENT_FLAG; - if (!length && !decimals) - { - new_field->length = DBL_DIG+7; - new_field->decimals=NOT_FIXED_DEC; - } - if (new_field->length < new_field->decimals && - new_field->decimals != NOT_FIXED_DEC) - { - my_error(ER_M_BIGGER_THAN_D, MYF(0), field_name); - DBUG_RETURN(NULL); - } - break; - case FIELD_TYPE_TIMESTAMP: - if (!length) - new_field->length= 14; // Full date YYYYMMDDHHMMSS - else if (new_field->length != 19) - { - /* - We support only even TIMESTAMP lengths less or equal than 14 - and 19 as length of 4.1 compatible representation. - */ - new_field->length=((new_field->length+1)/2)*2; /* purecov: inspected */ - new_field->length= min(new_field->length,14); /* purecov: inspected */ - } - new_field->flags|= ZEROFILL_FLAG | UNSIGNED_FLAG; - if (default_value) - { - /* Grammar allows only NOW() value for ON UPDATE clause */ - if (default_value->type() == Item::FUNC_ITEM && - ((Item_func*)default_value)->functype() == Item_func::NOW_FUNC) - { - new_field->unireg_check= (on_update_value?Field::TIMESTAMP_DNUN_FIELD: - Field::TIMESTAMP_DN_FIELD); - /* - We don't need default value any longer moreover it is dangerous. - Everything handled by unireg_check further. - */ - new_field->def= 0; - } - else - new_field->unireg_check= (on_update_value?Field::TIMESTAMP_UN_FIELD: - Field::NONE); - } - else - { - /* - If we have default TIMESTAMP NOT NULL column without explicit DEFAULT - or ON UPDATE values then for the sake of compatiblity we should treat - this column as having DEFAULT NOW() ON UPDATE NOW() (when we don't - have another TIMESTAMP column with auto-set option before this one) - or DEFAULT 0 (in other cases). - So here we are setting TIMESTAMP_OLD_FIELD only temporary, and will - replace this value by TIMESTAMP_DNUN_FIELD or NONE later when - information about all TIMESTAMP fields in table will be availiable. - - If we have TIMESTAMP NULL column without explicit DEFAULT value - we treat it as having DEFAULT NULL attribute. - */ - new_field->unireg_check= (on_update_value ? - Field::TIMESTAMP_UN_FIELD : - (new_field->flags & NOT_NULL_FLAG ? - Field::TIMESTAMP_OLD_FIELD: - Field::NONE)); - } - break; - case FIELD_TYPE_DATE: // Old date type - if (protocol_version != PROTOCOL_VERSION-1) - new_field->sql_type=FIELD_TYPE_NEWDATE; - /* fall trough */ - case FIELD_TYPE_NEWDATE: - new_field->length=10; - break; - case FIELD_TYPE_TIME: - new_field->length=10; - break; - case FIELD_TYPE_DATETIME: - new_field->length=19; - break; - case FIELD_TYPE_SET: - { - if (interval_list->elements > sizeof(longlong)*8) - { - my_error(ER_TOO_BIG_SET, MYF(0), field_name); /* purecov: inspected */ - DBUG_RETURN(NULL); - } - new_field->pack_length= get_set_pack_length(interval_list->elements); - - List_iterator<String> it(*interval_list); - String *tmp; - while ((tmp= it++)) - new_field->interval_list.push_back(tmp); - /* - Set fake length to 1 to pass the below conditions. - Real length will be set in mysql_prepare_table() - when we know the character set of the column - */ - new_field->length= 1; - break; - } - case FIELD_TYPE_ENUM: - { - // Should be safe - new_field->pack_length= get_enum_pack_length(interval_list->elements); - - List_iterator<String> it(*interval_list); - String *tmp; - while ((tmp= it++)) - new_field->interval_list.push_back(tmp); - new_field->length= 1; // See comment for FIELD_TYPE_SET above. - break; - } - case MYSQL_TYPE_VAR_STRING: - DBUG_ASSERT(0); // Impossible - break; - case MYSQL_TYPE_BIT: - { - if (!length) - new_field->length= 1; - if (new_field->length > MAX_BIT_FIELD_LENGTH) - { - my_error(ER_TOO_BIG_DISPLAYWIDTH, MYF(0), field_name, - MAX_BIT_FIELD_LENGTH); - DBUG_RETURN(NULL); - } - new_field->pack_length= (new_field->length + 7) / 8; - break; - } - case FIELD_TYPE_DECIMAL: - DBUG_ASSERT(0); /* Was obsolete */ - } - - if (!(new_field->flags & BLOB_FLAG) && - ((new_field->length > max_field_charlength && type != FIELD_TYPE_SET && - type != FIELD_TYPE_ENUM && - (type != MYSQL_TYPE_VARCHAR || default_value)) || - (!new_field->length && - type != MYSQL_TYPE_STRING && - type != MYSQL_TYPE_VARCHAR && type != FIELD_TYPE_GEOMETRY))) - { - my_error((type == MYSQL_TYPE_VAR_STRING || type == MYSQL_TYPE_VARCHAR || - type == MYSQL_TYPE_STRING) ? ER_TOO_BIG_FIELDLENGTH : - ER_TOO_BIG_DISPLAYWIDTH, - MYF(0), - field_name, max_field_charlength); /* purecov: inspected */ - DBUG_RETURN(NULL); - } - type_modifier&= AUTO_INCREMENT_FLAG; - if ((~allowed_type_modifier) & type_modifier) - { - my_error(ER_WRONG_FIELD_SPEC, MYF(0), field_name); - DBUG_RETURN(NULL); - } - DBUG_RETURN(new_field); -} - /* Store position for column in ALTER TABLE .. ADD column */ diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 17ce6c629f1..8538372d607 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -8911,6 +8911,7 @@ err: TABLE *create_virtual_tmp_table(THD *thd, List<create_field> &field_list) { uint field_count= field_list.elements; + uint blob_count= 0; Field **field; create_field *cdef; /* column definition */ uint record_length= 0; @@ -8927,6 +8928,12 @@ TABLE *create_virtual_tmp_table(THD *thd, List<create_field> &field_list) table->s= s= &table->share_not_to_be_used; s->fields= field_count; + if (!(s->blob_field= (uint*)thd->alloc((field_list.elements + 1) * + sizeof(uint)))) + return 0; + + s->blob_ptr_size= mi_portable_sizeof_char_ptr; + /* Create all fields and calculate the total length of record */ List_iterator_fast<create_field> it(field_list); while ((cdef= it++)) @@ -8942,9 +8949,15 @@ TABLE *create_virtual_tmp_table(THD *thd, List<create_field> &field_list) record_length+= (**field).pack_length(); if (! ((**field).flags & NOT_NULL_FLAG)) ++null_count; + + if ((*field)->flags & BLOB_FLAG) + s->blob_field[blob_count++]= (uint) (field - table->field); + ++field; } *field= NULL; /* mark the end of the list */ + s->blob_field[blob_count]= 0; /* mark the end of the list */ + s->blob_fields= blob_count; null_pack_length= (null_count + 7)/8; s->reclength= record_length + null_pack_length; diff --git a/sql/sql_select.h b/sql/sql_select.h index b3abd59f2b4..4aa238641e5 100644 --- a/sql/sql_select.h +++ b/sql/sql_select.h @@ -406,7 +406,6 @@ TABLE *create_tmp_table(THD *thd,TMP_TABLE_PARAM *param,List<Item> &fields, ORDER *group, bool distinct, bool save_sum_fields, ulonglong select_options, ha_rows rows_limit, char* alias); -TABLE *create_virtual_tmp_table(THD *thd, List<create_field> &field_list); void free_tmp_table(THD *thd, TABLE *entry); void count_field_types(TMP_TABLE_PARAM *param, List<Item> &fields, bool reset_with_sum_func); diff --git a/sql/sql_trigger.cc b/sql/sql_trigger.cc index 296b55679a3..779b044696e 100644 --- a/sql/sql_trigger.cc +++ b/sql/sql_trigger.cc @@ -1123,7 +1123,7 @@ bool Table_triggers_list::process_triggers(THD *thd, trg_event_type event, trg_action_time_type time_type, bool old_row_is_record1) { - int res= 0; + bool err_status= FALSE; sp_head *sp_trigger= bodies[event][time_type]; if (sp_trigger) @@ -1183,7 +1183,7 @@ bool Table_triggers_list::process_triggers(THD *thd, trg_event_type event, #endif // NO_EMBEDDED_ACCESS_CHECKS thd->reset_sub_statement_state(&statement_state, SUB_STMT_TRIGGER); - res= sp_trigger->execute_function(thd, 0, 0, 0); + err_status= sp_trigger->execute_function(thd, 0, 0, 0); thd->restore_sub_statement_state(&statement_state); #ifndef NO_EMBEDDED_ACCESS_CHECKS @@ -1191,7 +1191,7 @@ bool Table_triggers_list::process_triggers(THD *thd, trg_event_type event, #endif // NO_EMBEDDED_ACCESS_CHECKS } - return res; + return err_status; } diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy index 3111bd7060e..10ba5e8b271 100644 --- a/sql/sql_yacc.yy +++ b/sql/sql_yacc.yy @@ -1350,41 +1350,11 @@ create_function_tail: { LEX *lex= Lex; sp_head *sp= lex->sphead; - LEX_STRING cmt = { 0, 0 }; - create_field *new_field; - uint unused1= 0; - int unused2= 0; - - if (!(new_field= new_create_field(YYTHD, (char*) "", - (enum enum_field_types)$8, - lex->length, lex->dec, lex->type, - (Item *)0, (Item *) 0, &cmt, 0, - &lex->interval_list, - (lex->charset ? lex->charset : - default_charset_info), - lex->uint_geom_type))) - YYABORT; - - sp->m_returns_cs= new_field->charset; - - if (new_field->interval_list.elements) - { - new_field->interval= - sp->create_typelib(&new_field->interval_list); - } - sp_prepare_create_field(YYTHD, new_field); - - if (prepare_create_field(new_field, &unused1, &unused2, &unused2, - HA_CAN_GEOMETRY)) - YYABORT; - sp->m_returns= new_field->sql_type; - sp->m_returns_cs= new_field->charset; - sp->m_returns_len= new_field->length; - sp->m_returns_pack= new_field->pack_flag; - sp->m_returns_typelib= new_field->interval; - sp->m_geom_returns= new_field->geom_type; - new_field->interval= NULL; + if (sp->fill_field_definition(YYTHD, lex, + (enum enum_field_types) $8, + &sp->m_return_field_def)) + YYABORT; bzero((char *)&lex->sp_chistics, sizeof(st_sp_chistics)); } @@ -1506,8 +1476,28 @@ sp_fdparams: | sp_fdparam ; +sp_init_param: + /* Empty */ + { + LEX *lex= Lex; + + lex->length= 0; + lex->dec= 0; + lex->type= 0; + + lex->default_value= 0; + lex->on_update_value= 0; + + lex->comment= null_lex_str; + lex->charset= NULL; + + lex->interval_list.empty(); + lex->uint_geom_type= 0; + } + ; + sp_fdparam: - ident type + ident sp_init_param type { LEX *lex= Lex; sp_pcontext *spc= lex->spcont; @@ -1517,7 +1507,17 @@ sp_fdparam: my_error(ER_SP_DUP_PARAM, MYF(0), $1.str); YYABORT; } - spc->push_pvar(&$1, (enum enum_field_types)$2, sp_param_in); + sp_pvar_t *pvar= spc->push_pvar(&$1, (enum enum_field_types)$3, + sp_param_in); + + if (lex->sphead->fill_field_definition(YYTHD, lex, + (enum enum_field_types) $3, + &pvar->field_def)) + { + YYABORT; + } + pvar->field_def.field_name= pvar->name.str; + pvar->field_def.pack_flag |= FIELDFLAG_MAYBE_NULL; } ; @@ -1533,18 +1533,27 @@ sp_pdparams: ; sp_pdparam: - sp_opt_inout ident type + sp_opt_inout sp_init_param ident type { LEX *lex= Lex; sp_pcontext *spc= lex->spcont; - if (spc->find_pvar(&$2, TRUE)) + if (spc->find_pvar(&$3, TRUE)) { - my_error(ER_SP_DUP_PARAM, MYF(0), $2.str); + my_error(ER_SP_DUP_PARAM, MYF(0), $3.str); YYABORT; } - spc->push_pvar(&$2, (enum enum_field_types)$3, - (sp_param_mode_t)$1); + sp_pvar_t *pvar= spc->push_pvar(&$3, (enum enum_field_types)$4, + (sp_param_mode_t)$1); + + if (lex->sphead->fill_field_definition(YYTHD, lex, + (enum enum_field_types) $4, + &pvar->field_def)) + { + YYABORT; + } + pvar->field_def.field_name= pvar->name.str; + pvar->field_def.pack_flag |= FIELDFLAG_MAYBE_NULL; } ; @@ -1596,45 +1605,60 @@ sp_decls: ; sp_decl: - DECLARE_SYM sp_decl_idents type + DECLARE_SYM sp_decl_idents { LEX *lex= Lex; lex->sphead->reset_lex(YYTHD); lex->spcont->declare_var_boundary($2); } + type sp_opt_default { LEX *lex= Lex; - sp_pcontext *ctx= lex->spcont; - uint max= ctx->context_pvars(); - enum enum_field_types type= (enum enum_field_types)$3; - Item *it= $5; - bool has_default= (it != NULL); - - for (uint i = max-$2 ; i < max ; i++) + sp_pcontext *pctx= lex->spcont; + uint num_vars= pctx->context_pvars(); + enum enum_field_types var_type= (enum enum_field_types) $4; + Item *dflt_value_item= $5; + create_field *create_field_op; + + if (!dflt_value_item) { - sp_instr_set *in; - uint off= ctx->pvar_context2index(i); - - ctx->set_type(off, type); - if (! has_default) - it= new Item_null(); /* QQ Set to the type with null_value? */ - in = new sp_instr_set(lex->sphead->instructions(), - ctx, - off, - it, type, lex, - (i == max - 1)); - - /* - The last instruction is assigned to be responsible for - freeing LEX. - */ - lex->sphead->add_instr(in); - ctx->set_default(off, it); + dflt_value_item= new Item_null(); + /* QQ Set to the var_type with null_value? */ + } + + for (uint i = num_vars-$2 ; i < num_vars ; i++) + { + uint var_idx= pctx->pvar_context2index(i); + sp_pvar_t *pvar= pctx->find_pvar(var_idx); + + if (!pvar) + YYABORT; + + pvar->type= var_type; + pvar->dflt= dflt_value_item; + + if (lex->sphead->fill_field_definition(YYTHD, lex, var_type, + &pvar->field_def)) + { + YYABORT; + } + + pvar->field_def.field_name= pvar->name.str; + pvar->field_def.pack_flag |= FIELDFLAG_MAYBE_NULL; + + /* The last instruction is responsible for freeing LEX. */ + + lex->sphead->add_instr( + new sp_instr_set(lex->sphead->instructions(), pctx, var_idx, + dflt_value_item, var_type, lex, + (i == num_vars - 1))); } - ctx->declare_var_boundary(0); + + pctx->declare_var_boundary(0); lex->sphead->restore_lex(YYTHD); + $$.vars= $2; $$.conds= $$.hndlrs= $$.curs= 0; } @@ -1857,6 +1881,8 @@ sp_hcond: sp_decl_idents: ident { + /* NOTE: field definition is filled in sp_decl section. */ + LEX *lex= Lex; sp_pcontext *spc= lex->spcont; @@ -1870,6 +1896,8 @@ sp_decl_idents: } | sp_decl_idents ',' ident { + /* NOTE: field definition is filled in sp_decl section. */ + LEX *lex= Lex; sp_pcontext *spc= lex->spcont; @@ -1947,8 +1975,8 @@ sp_proc_stmt: { sp_instr_freturn *i; - i= new sp_instr_freturn(sp->instructions(), lex->spcont, - $3, sp->m_returns, lex); + i= new sp_instr_freturn(sp->instructions(), lex->spcont, $3, + sp->m_return_field_def.sql_type, lex); sp->add_instr(i); sp->m_flags|= sp_head::HAS_RETURN; } @@ -1964,25 +1992,27 @@ sp_proc_stmt: { Lex->sphead->reset_lex(YYTHD); } expr WHEN_SYM { - /* We "fake" this by using an anonymous variable which we - set to the expression. Note that all WHENs are evaluate - at the same frame level, so we then know that it's the - top-most variable in the frame. */ LEX *lex= Lex; - uint offset= lex->spcont->current_pvars(); - sp_instr_set *i = new sp_instr_set(lex->sphead->instructions(), - lex->spcont, offset, $3, - MYSQL_TYPE_STRING, lex, TRUE); - LEX_STRING dummy={(char*)"", 0}; - - lex->spcont->push_pvar(&dummy, MYSQL_TYPE_STRING, sp_param_in); - lex->sphead->add_instr(i); - lex->sphead->m_flags|= sp_head::IN_SIMPLE_CASE; - lex->sphead->restore_lex(YYTHD); + sp_head *sp= lex->sphead; + sp_pcontext *parsing_ctx= lex->spcont; + int case_expr_id= parsing_ctx->register_case_expr(); + + if (parsing_ctx->push_case_expr_id(case_expr_id)) + YYABORT; + + sp->add_instr( + new sp_instr_set_case_expr(sp->instructions(), + parsing_ctx, + case_expr_id, + $3, + lex)); + + sp->m_flags|= sp_head::IN_SIMPLE_CASE; + sp->restore_lex(YYTHD); } sp_case END CASE_SYM { - Lex->spcont->pop_pvar(); + Lex->spcont->pop_case_expr_id(); } | sp_labeled_control {} @@ -2293,20 +2323,20 @@ sp_case: i= new sp_instr_jump_if_not(ip, ctx, $2, lex); else { /* Simple case: <caseval> = <whenval> */ - LEX_STRING ivar; - ivar.str= (char *)"_tmp_"; - ivar.length= 5; - Item_splocal *var= new Item_splocal(ivar, - ctx->current_pvars()-1); + Item_case_expr *var; + Item *expr; + + var= new Item_case_expr(ctx->get_current_case_expr_id()); + #ifndef DBUG_OFF if (var) - var->owner= sp; + var->m_sp= sp; #endif - Item *expr= new Item_func_eq(var, $2); + + expr= new Item_func_eq(var, $2); i= new sp_instr_jump_if_not(ip, ctx, expr, lex); - lex->variables_used= 1; } sp->push_backpatch(i, ctx->push_label((char *)"", 0)); sp->add_instr(i); @@ -4401,11 +4431,9 @@ simple_expr: { if ($3->is_splocal()) { - LEX_STRING *name; Item_splocal *il= static_cast<Item_splocal *>($3); - name= il->my_name(NULL); - my_error(ER_WRONG_COLUMN_NAME, MYF(0), name->str); + my_error(ER_WRONG_COLUMN_NAME, MYF(0), il->my_name()->str); YYABORT; } $$= new Item_default_value(Lex->current_context(), $3); @@ -5873,7 +5901,7 @@ select_var_ident: var_list.push_back(var= new my_var($1,1,t->offset,t->type)); #ifndef DBUG_OFF if (var) - var->owner= lex->sphead; + var->sp= lex->sphead; #endif } } @@ -7175,11 +7203,12 @@ simple_ident: { /* We're compiling a stored procedure and found a variable */ Item_splocal *splocal; - splocal= new Item_splocal($1, spv->offset, lex->tok_start_prev - + splocal= new Item_splocal($1, spv->offset, spv->type, + lex->tok_start_prev - lex->sphead->m_tmp_query); #ifndef DBUG_OFF if (splocal) - splocal->owner= lex->sphead; + splocal->m_sp= lex->sphead; #endif $$ = (Item*) splocal; lex->variables_used= 1; |