mirror of
https://github.com/MariaDB/server.git
synced 2025-02-13 17:05:35 +01:00
9bfde897c3
This patch adds DEFAULT as a possible dynamic SQL parameter, e.g.: EXECUTE IMMEDIATE 'INSERT INTO t1 (column) VALUES(?)' USING DEFAULT; EXECUTE IMMEDIATE 'UPDATE t1 SET column=?' USING DEFAULT; and for similar PREPARE..EXECUTE queries. This is done for symmetry with the STMT_INDICATOR_DEFAULT indicator in the client-server PS protocol. The changes include: - Allowing DEFAULT as a possible option in execute USING clause (sql_yacc.yy) - Adding "virtual bool Item::save_in_param(THD *thd, Item_param *param)", because "normal" items (that have real values) and Item_default_value have now different actions when assigning itself as an Item_param value. - Fixing switch() statements in a few Item_param methods not to have "default", because it was easy to forget to add a new "case" when adding a new XXX_VALUE value into the enum Item_param::enum_item_param_state. This is important, as we'll be adding new values soon, e.g. for MDEV-11359. Removing "default" helped to find and report bugs MDEV-11361 and MDEV-11362, because DECIMAL_VALUE is obviously not properly handled in some cases.
187 lines
7.7 KiB
Text
187 lines
7.7 KiB
Text
drop table if exists t1;
|
|
reset master;
|
|
create table t1 (a int);
|
|
prepare s from "insert into t1 values (@a),(?)";
|
|
set @a=98;
|
|
execute s using @a;
|
|
prepare s from "insert into t1 values (?)";
|
|
set @a=99;
|
|
execute s using @a;
|
|
prepare s from "insert into t1 select 100 limit ?";
|
|
set @a=100;
|
|
execute s using @a;
|
|
Warnings:
|
|
Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses a LIMIT clause. This is unsafe because the set of rows included cannot be predicted
|
|
include/show_binlog_events.inc
|
|
Log_name Pos Event_type Server_id End_log_pos Info
|
|
master-bin.000001 # Gtid # # GTID #-#-#
|
|
master-bin.000001 # Query # # use `test`; create table t1 (a int)
|
|
master-bin.000001 # Gtid # # BEGIN GTID #-#-#
|
|
master-bin.000001 # User var # # @`a`=98
|
|
master-bin.000001 # Query # # use `test`; insert into t1 values (@a),(98)
|
|
master-bin.000001 # Query # # COMMIT
|
|
master-bin.000001 # Gtid # # BEGIN GTID #-#-#
|
|
master-bin.000001 # Query # # use `test`; insert into t1 values (99)
|
|
master-bin.000001 # Query # # COMMIT
|
|
master-bin.000001 # Gtid # # BEGIN GTID #-#-#
|
|
master-bin.000001 # Query # # use `test`; insert into t1 select 100 limit 100
|
|
master-bin.000001 # Query # # COMMIT
|
|
drop table t1;
|
|
#
|
|
# MDEV-10709 Expressions as parameters to Dynamic SQL
|
|
#
|
|
FLUSH LOGS;
|
|
SET TIMESTAMP=UNIX_TIMESTAMP('2001-01-02 10:20:30.123456');
|
|
CREATE TABLE t1 (a DECIMAL(30,8));
|
|
PREPARE stmt FROM 'INSERT INTO t1 VALUES (?)';
|
|
EXECUTE stmt USING 10;
|
|
EXECUTE stmt USING 11e0;
|
|
EXECUTE stmt USING 12.1;
|
|
EXECUTE stmt USING '13';
|
|
EXECUTE stmt USING CURRENT_DATE;
|
|
EXECUTE stmt USING MAKETIME(10,20,30);
|
|
EXECUTE stmt USING CURRENT_TIME;
|
|
EXECUTE stmt USING CURRENT_TIME(3);
|
|
EXECUTE stmt USING CURRENT_TIME(6);
|
|
EXECUTE stmt USING CURRENT_TIMESTAMP;
|
|
EXECUTE stmt USING CURRENT_TIMESTAMP(3);
|
|
EXECUTE stmt USING CURRENT_TIMESTAMP(6);
|
|
SELECT * FROM t1;
|
|
a
|
|
10.00000000
|
|
11.00000000
|
|
12.10000000
|
|
13.00000000
|
|
20010102.00000000
|
|
102030.00000000
|
|
102030.00000000
|
|
102030.12300000
|
|
102030.12345600
|
|
20010102102030.00000000
|
|
20010102102030.12300000
|
|
20010102102030.12345600
|
|
include/show_binlog_events.inc
|
|
Log_name Pos Event_type Server_id End_log_pos Info
|
|
master-bin.000002 # Binlog_checkpoint # # master-bin.000002
|
|
master-bin.000002 # Gtid # # GTID #-#-#
|
|
master-bin.000002 # Query # # use `test`; CREATE TABLE t1 (a DECIMAL(30,8))
|
|
master-bin.000002 # Gtid # # BEGIN GTID #-#-#
|
|
master-bin.000002 # Query # # use `test`; INSERT INTO t1 VALUES (10)
|
|
master-bin.000002 # Query # # COMMIT
|
|
master-bin.000002 # Gtid # # BEGIN GTID #-#-#
|
|
master-bin.000002 # Query # # use `test`; INSERT INTO t1 VALUES (11)
|
|
master-bin.000002 # Query # # COMMIT
|
|
master-bin.000002 # Gtid # # BEGIN GTID #-#-#
|
|
master-bin.000002 # Query # # use `test`; INSERT INTO t1 VALUES (12.1)
|
|
master-bin.000002 # Query # # COMMIT
|
|
master-bin.000002 # Gtid # # BEGIN GTID #-#-#
|
|
master-bin.000002 # Query # # use `test`; INSERT INTO t1 VALUES ('13')
|
|
master-bin.000002 # Query # # COMMIT
|
|
master-bin.000002 # Gtid # # BEGIN GTID #-#-#
|
|
master-bin.000002 # Query # # use `test`; INSERT INTO t1 VALUES (DATE'2001-01-02')
|
|
master-bin.000002 # Query # # COMMIT
|
|
master-bin.000002 # Gtid # # BEGIN GTID #-#-#
|
|
master-bin.000002 # Query # # use `test`; INSERT INTO t1 VALUES (TIME'10:20:30')
|
|
master-bin.000002 # Query # # COMMIT
|
|
master-bin.000002 # Gtid # # BEGIN GTID #-#-#
|
|
master-bin.000002 # Query # # use `test`; INSERT INTO t1 VALUES (TIME'10:20:30')
|
|
master-bin.000002 # Query # # COMMIT
|
|
master-bin.000002 # Gtid # # BEGIN GTID #-#-#
|
|
master-bin.000002 # Query # # use `test`; INSERT INTO t1 VALUES (TIME'10:20:30.123')
|
|
master-bin.000002 # Query # # COMMIT
|
|
master-bin.000002 # Gtid # # BEGIN GTID #-#-#
|
|
master-bin.000002 # Query # # use `test`; INSERT INTO t1 VALUES (TIME'10:20:30.123456')
|
|
master-bin.000002 # Query # # COMMIT
|
|
master-bin.000002 # Gtid # # BEGIN GTID #-#-#
|
|
master-bin.000002 # Query # # use `test`; INSERT INTO t1 VALUES (TIMESTAMP'2001-01-02 10:20:30')
|
|
master-bin.000002 # Query # # COMMIT
|
|
master-bin.000002 # Gtid # # BEGIN GTID #-#-#
|
|
master-bin.000002 # Query # # use `test`; INSERT INTO t1 VALUES (TIMESTAMP'2001-01-02 10:20:30.123')
|
|
master-bin.000002 # Query # # COMMIT
|
|
master-bin.000002 # Gtid # # BEGIN GTID #-#-#
|
|
master-bin.000002 # Query # # use `test`; INSERT INTO t1 VALUES (TIMESTAMP'2001-01-02 10:20:30.123456')
|
|
master-bin.000002 # Query # # COMMIT
|
|
DROP TABLE t1;
|
|
SET TIMESTAMP=DEFAULT;
|
|
#
|
|
# MDEV-10585 EXECUTE IMMEDIATE statement
|
|
#
|
|
FLUSH LOGS;
|
|
CREATE TABLE t1 (a INT);
|
|
EXECUTE IMMEDIATE 'INSERT INTO t1 VALUES (101)';
|
|
SET @a=102;
|
|
EXECUTE IMMEDIATE 'INSERT INTO t1 VALUES (?)' USING @a;
|
|
SET @a=103;
|
|
SET @stmt='INSERT INTO t1 VALUES (?)';
|
|
EXECUTE IMMEDIATE @stmt USING @a;
|
|
include/show_binlog_events.inc
|
|
Log_name Pos Event_type Server_id End_log_pos Info
|
|
master-bin.000003 # Binlog_checkpoint # # master-bin.000003
|
|
master-bin.000003 # Gtid # # GTID #-#-#
|
|
master-bin.000003 # Query # # use `test`; CREATE TABLE t1 (a INT)
|
|
master-bin.000003 # Gtid # # BEGIN GTID #-#-#
|
|
master-bin.000003 # Query # # use `test`; INSERT INTO t1 VALUES (101)
|
|
master-bin.000003 # Query # # COMMIT
|
|
master-bin.000003 # Gtid # # BEGIN GTID #-#-#
|
|
master-bin.000003 # Query # # use `test`; INSERT INTO t1 VALUES (102)
|
|
master-bin.000003 # Query # # COMMIT
|
|
master-bin.000003 # Gtid # # BEGIN GTID #-#-#
|
|
master-bin.000003 # Query # # use `test`; INSERT INTO t1 VALUES (103)
|
|
master-bin.000003 # Query # # COMMIT
|
|
DROP TABLE t1;
|
|
#
|
|
# MDEV-11360 Dynamic SQL: DEFAULT as a bind parameter
|
|
#
|
|
FLUSH LOGS;
|
|
CREATE TABLE t1 (a INT DEFAULT 10);
|
|
EXECUTE IMMEDIATE 'INSERT INTO t1 VALUES (Default)';
|
|
# The output of this query in 'Note' is a syntactically incorrect query.
|
|
# But as it's never logged, it's ok. It should be human readable only.
|
|
EXECUTE IMMEDIATE 'EXPLAIN EXTENDED SELECT ?' USING Default;
|
|
id select_type table type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used
|
|
Warnings:
|
|
Note 1003 select default AS `?`
|
|
EXECUTE IMMEDIATE 'INSERT INTO t1 VALUES (?)' USING Default;
|
|
CREATE PROCEDURE p1 ()
|
|
BEGIN
|
|
INSERT INTO t1 VALUES (Default);
|
|
# EXPLAIN should not be logged
|
|
EXECUTE IMMEDIATE 'EXPLAIN EXTENDED SELECT ?' USING Default;
|
|
EXECUTE IMMEDIATE 'INSERT INTO t1 VALUES (?)' USING Default;
|
|
END;
|
|
$$
|
|
CALL p1;
|
|
id select_type table type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used
|
|
DROP PROCEDURE p1;
|
|
DROP TABLE t1;
|
|
include/show_binlog_events.inc
|
|
Log_name Pos Event_type Server_id End_log_pos Info
|
|
master-bin.000004 # Binlog_checkpoint # # master-bin.000004
|
|
master-bin.000004 # Gtid # # GTID #-#-#
|
|
master-bin.000004 # Query # # use `test`; CREATE TABLE t1 (a INT DEFAULT 10)
|
|
master-bin.000004 # Gtid # # BEGIN GTID #-#-#
|
|
master-bin.000004 # Query # # use `test`; INSERT INTO t1 VALUES (Default)
|
|
master-bin.000004 # Query # # COMMIT
|
|
master-bin.000004 # Gtid # # BEGIN GTID #-#-#
|
|
master-bin.000004 # Query # # use `test`; INSERT INTO t1 VALUES (DEFAULT)
|
|
master-bin.000004 # Query # # COMMIT
|
|
master-bin.000004 # Gtid # # GTID #-#-#
|
|
master-bin.000004 # Query # # use `test`; CREATE DEFINER=`root`@`localhost` PROCEDURE `p1`()
|
|
BEGIN
|
|
INSERT INTO t1 VALUES (Default);
|
|
# EXPLAIN should not be logged
|
|
EXECUTE IMMEDIATE 'EXPLAIN EXTENDED SELECT ?' USING Default;
|
|
EXECUTE IMMEDIATE 'INSERT INTO t1 VALUES (?)' USING Default;
|
|
END
|
|
master-bin.000004 # Gtid # # BEGIN GTID #-#-#
|
|
master-bin.000004 # Query # # use `test`; INSERT INTO t1 VALUES (Default)
|
|
master-bin.000004 # Query # # COMMIT
|
|
master-bin.000004 # Gtid # # BEGIN GTID #-#-#
|
|
master-bin.000004 # Query # # use `test`; INSERT INTO t1 VALUES (DEFAULT)
|
|
master-bin.000004 # Query # # COMMIT
|
|
master-bin.000004 # Gtid # # GTID #-#-#
|
|
master-bin.000004 # Query # # use `test`; DROP PROCEDURE p1
|
|
master-bin.000004 # Gtid # # GTID #-#-#
|
|
master-bin.000004 # Query # # use `test`; DROP TABLE `t1` /* generated by server */
|