You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
386 lines
7.2 KiB
386 lines
7.2 KiB
#
|
|
# WL#7766 Deprecate the EOF packet
|
|
# This WL can be tested by checking if session state which is sent
|
|
# as part of OK packet is displayed as part of SELECT sql statement.
|
|
#
|
|
CREATE DATABASE wl7766;
|
|
USE wl7766;
|
|
CREATE TABLE t1 (a int);
|
|
INSERT INTO t1 VALUES (1), (2);
|
|
CREATE TABLE t2 (a int);
|
|
INSERT INTO t2 VALUES (1), (2);
|
|
Turn on all trackers
|
|
SET @@session.session_track_schema=ON;
|
|
SET @@session.session_track_system_variables='*';
|
|
SET @@session.session_track_state_change=ON;
|
|
|
|
#CASE1: SELECT inside PROCEDURE
|
|
|
|
CREATE PROCEDURE t1_sel()
|
|
BEGIN
|
|
SET @var1=20;
|
|
SELECT * FROM t1 ORDER BY 1;
|
|
END |
|
|
CALL t1_sel();
|
|
a
|
|
1
|
|
2
|
|
-- Tracker : SESSION_TRACK_STATE_CHANGE
|
|
-- 1
|
|
|
|
CREATE PROCEDURE t1_inssel()
|
|
BEGIN
|
|
SET @a=1;
|
|
INSERT INTO t1 VALUES (3),(4);
|
|
SELECT * FROM t1 ORDER BY 1;
|
|
SELECT "session state sent as part of above SELECT" AS col_heading;
|
|
END |
|
|
CALL t1_inssel();
|
|
a
|
|
1
|
|
2
|
|
3
|
|
4
|
|
-- Tracker : SESSION_TRACK_STATE_CHANGE
|
|
-- 1
|
|
|
|
col_heading
|
|
session state sent as part of above SELECT
|
|
CREATE PROCEDURE t1_selins()
|
|
BEGIN
|
|
SELECT * FROM t1 ORDER BY 1;
|
|
INSERT INTO t1 VALUES (5),(6);
|
|
SELECT "no session state exists" AS col_heading;
|
|
END |
|
|
CALL t1_selins();
|
|
a
|
|
1
|
|
2
|
|
3
|
|
4
|
|
col_heading
|
|
no session state exists
|
|
CREATE PROCEDURE t2t1_sel()
|
|
BEGIN
|
|
SET @a=20;
|
|
SELECT MIN(a) FROM t2;
|
|
SET @@session.sql_mode='traditional';
|
|
SELECT MAX(2) FROM t1;
|
|
END |
|
|
session state sent for both SELECT statement
|
|
CALL t2t1_sel();
|
|
MIN(a)
|
|
1
|
|
-- Tracker : SESSION_TRACK_STATE_CHANGE
|
|
-- 1
|
|
|
|
MAX(2)
|
|
2
|
|
-- Tracker : SESSION_TRACK_SYSTEM_VARIABLES
|
|
-- sql_mode
|
|
-- STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_ENGINE_SUBSTITUTION
|
|
|
|
-- Tracker : SESSION_TRACK_STATE_CHANGE
|
|
-- 1
|
|
|
|
CREATE PROCEDURE t1_call()
|
|
BEGIN
|
|
SET @a=20;
|
|
CALL t1_sel();
|
|
SELECT "session state sent for SELECT inside t1_sel()" AS col_heading;
|
|
END |
|
|
CALL t1_call();
|
|
a
|
|
1
|
|
2
|
|
3
|
|
4
|
|
5
|
|
6
|
|
-- Tracker : SESSION_TRACK_STATE_CHANGE
|
|
-- 1
|
|
|
|
col_heading
|
|
session state sent for SELECT inside t1_sel()
|
|
CREATE PROCEDURE t1_inout(
|
|
IN v0 INT,
|
|
OUT v_str_1 CHAR(32),
|
|
OUT v_dbl_1 DOUBLE(4, 2),
|
|
OUT v_dec_1 DECIMAL(6, 3),
|
|
OUT v_int_1 INT,
|
|
IN v1 INT,
|
|
INOUT v_str_2 CHAR(64),
|
|
INOUT v_dbl_2 DOUBLE(5, 3),
|
|
INOUT v_dec_2 DECIMAL(7, 4),
|
|
INOUT v_int_2 INT)
|
|
BEGIN
|
|
SET v0 = -1;
|
|
SET v1 = -1;
|
|
SET v_str_1 = 'test_1';
|
|
SET v_dbl_1 = 12.34;
|
|
SET v_dec_1 = 567.891;
|
|
SET v_int_1 = 2345;
|
|
SET v_str_2 = 'test_2';
|
|
SET v_dbl_2 = 67.891;
|
|
SET v_dec_2 = 234.6789;
|
|
SET v_int_2 = 6789;
|
|
SET @@session.time_zone='Europe/Moscow';
|
|
SELECT * FROM t1;
|
|
SET @@session.TIMESTAMP=200;
|
|
SELECT * FROM t2;
|
|
END |
|
|
Warnings:
|
|
Warning 1681 Specifying number of digits for floating point data types is deprecated and will be removed in a future release.
|
|
Warning 1681 Specifying number of digits for floating point data types is deprecated and will be removed in a future release.
|
|
CALL t1_inout(@a,@b,@c,@d,@e,@f,@g,@h,@i,@j);
|
|
a
|
|
1
|
|
2
|
|
3
|
|
4
|
|
5
|
|
6
|
|
-- Tracker : SESSION_TRACK_SYSTEM_VARIABLES
|
|
-- time_zone
|
|
-- Europe/Moscow
|
|
|
|
-- Tracker : SESSION_TRACK_STATE_CHANGE
|
|
-- 1
|
|
|
|
a
|
|
1
|
|
2
|
|
-- Tracker : SESSION_TRACK_SYSTEM_VARIABLES
|
|
-- timestamp
|
|
-- 200.000000
|
|
|
|
-- Tracker : SESSION_TRACK_STATE_CHANGE
|
|
-- 1
|
|
|
|
SELECT @a,@b,@c,@d,@e,@f,@g,@h,@i,@j;
|
|
@a @b @c @d @e @f @g @h @i @j
|
|
20 test_1 12.34 567.891 2345 NULL test_2 67.891 234.6789 6789
|
|
|
|
#CASE2: SELECT FUNCTIONs
|
|
|
|
CREATE FUNCTION f1 () RETURNS int
|
|
BEGIN
|
|
SET NAMES 'big5';
|
|
RETURN (SELECT COUNT(*) FROM t1);
|
|
END |
|
|
SELECT f1();
|
|
f1()
|
|
6
|
|
-- Tracker : SESSION_TRACK_SYSTEM_VARIABLES
|
|
-- character_set_client
|
|
-- utf8mb4
|
|
-- character_set_connection
|
|
-- utf8mb4
|
|
-- character_set_results
|
|
-- big5
|
|
|
|
-- Tracker : SESSION_TRACK_STATE_CHANGE
|
|
-- 1
|
|
|
|
CREATE VIEW v1 AS SELECT f1();
|
|
SELECT * FROM v1;
|
|
f1()
|
|
6
|
|
-- Tracker : SESSION_TRACK_SYSTEM_VARIABLES
|
|
-- character_set_client
|
|
-- utf8mb4
|
|
-- character_set_connection
|
|
-- utf8mb4
|
|
-- character_set_results
|
|
-- big5
|
|
|
|
-- Tracker : SESSION_TRACK_STATE_CHANGE
|
|
-- 1
|
|
|
|
CREATE PROCEDURE sp1(OUT x INT)
|
|
BEGIN
|
|
SELECT MIN(a) INTO x FROM t1;
|
|
END |
|
|
CREATE FUNCTION f2() RETURNS int
|
|
BEGIN
|
|
DECLARE a int;
|
|
SET @a=20;
|
|
CALL sp1(a);
|
|
RETURN a;
|
|
END |
|
|
SELECT f2();
|
|
f2()
|
|
1
|
|
-- Tracker : SESSION_TRACK_STATE_CHANGE
|
|
-- 1
|
|
|
|
CREATE FUNCTION f3() RETURNS int
|
|
BEGIN
|
|
DECLARE a, b int;
|
|
DROP TEMPORARY TABLE IF EXISTS t3;
|
|
CREATE TEMPORARY TABLE t3 (id INT);
|
|
INSERT INTO t3 VALUES (1), (2), (3);
|
|
SET a:= (SELECT COUNT(*) FROM t3);
|
|
SET b:= (SELECT COUNT(*) FROM t3 t3_alias);
|
|
RETURN a + b;
|
|
END |
|
|
SELECT f3();
|
|
f3()
|
|
6
|
|
-- Tracker : SESSION_TRACK_STATE_CHANGE
|
|
-- 1
|
|
|
|
CREATE FUNCTION f4() RETURNS int
|
|
BEGIN
|
|
DECLARE x int;
|
|
DECLARE c CURSOR FOR SELECT * FROM t1 limit 1;
|
|
SET NAMES 'utf8';
|
|
SET @var1=20;
|
|
OPEN c;
|
|
FETCH c INTO x;
|
|
CLOSE c;
|
|
RETURN x;
|
|
END |
|
|
Warnings:
|
|
Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
|
|
SELECT f4();
|
|
f4()
|
|
1
|
|
-- Tracker : SESSION_TRACK_SYSTEM_VARIABLES
|
|
-- character_set_client
|
|
-- utf8mb4
|
|
-- character_set_connection
|
|
-- utf8mb4
|
|
-- character_set_results
|
|
-- utf8
|
|
|
|
-- Tracker : SESSION_TRACK_STATE_CHANGE
|
|
-- 1
|
|
|
|
Warnings:
|
|
Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
|
|
|
|
#CASE3: SELECT with CURSORS
|
|
|
|
CREATE PROCEDURE cursor1()
|
|
BEGIN
|
|
DECLARE v1 int;
|
|
DECLARE done INT DEFAULT FALSE;
|
|
DECLARE cur1 CURSOR FOR SELECT * FROM t1;
|
|
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
|
|
SET @@session.transaction_isolation='READ-COMMITTED';
|
|
OPEN cur1;
|
|
read_loop: LOOP
|
|
FETCH cur1 INTO v1;
|
|
IF done THEN
|
|
LEAVE read_loop;
|
|
END IF;
|
|
END LOOP;
|
|
SELECT v1;
|
|
CLOSE cur1;
|
|
END |
|
|
CALL cursor1();
|
|
v1
|
|
6
|
|
-- Tracker : SESSION_TRACK_SYSTEM_VARIABLES
|
|
-- transaction_isolation
|
|
-- READ-COMMITTED
|
|
|
|
-- Tracker : SESSION_TRACK_STATE_CHANGE
|
|
-- 1
|
|
|
|
CREATE PROCEDURE cursor2()
|
|
BEGIN
|
|
DECLARE x int;
|
|
DECLARE y int;
|
|
DECLARE c1 CURSOR FOR SELECT * FROM t1 limit 1;
|
|
DECLARE c2 CURSOR FOR SELECT * FROM t2 limit 1;
|
|
SET @@session.transaction_isolation='READ-COMMITTED';
|
|
OPEN c1;
|
|
OPEN c2;
|
|
FETCH c1 INTO x;
|
|
FETCH c2 INTO y;
|
|
SELECT (x+y);
|
|
SELECT "session state sent as part of above SELECT" AS col_heading;
|
|
CLOSE c1;
|
|
CLOSE c2;
|
|
END |
|
|
CALL cursor2();
|
|
(x+y)
|
|
2
|
|
-- Tracker : SESSION_TRACK_SYSTEM_VARIABLES
|
|
-- transaction_isolation
|
|
-- READ-COMMITTED
|
|
|
|
-- Tracker : SESSION_TRACK_STATE_CHANGE
|
|
-- 1
|
|
|
|
col_heading
|
|
session state sent as part of above SELECT
|
|
DROP DATABASE wl7766;
|
|
-- Tracker : SESSION_TRACK_SCHEMA
|
|
--
|
|
|
|
#
|
|
# Bug#19550875: SESSION STATE NOT SENT AS PART OF RESULT SETS WHEN
|
|
# QUERY CACHE IS ON
|
|
#
|
|
SET @@session.session_track_state_change=ON;
|
|
-- Tracker : SESSION_TRACK_SYSTEM_VARIABLES
|
|
-- session_track_state_change
|
|
-- ON
|
|
|
|
CREATE DATABASE bug19550875;
|
|
USE bug19550875;
|
|
-- Tracker : SESSION_TRACK_SCHEMA
|
|
-- bug19550875
|
|
|
|
-- Tracker : SESSION_TRACK_STATE_CHANGE
|
|
-- 1
|
|
|
|
CREATE TABLE t1 (a int);
|
|
INSERT INTO t1 VALUES (1), (2);
|
|
CREATE PROCEDURE t_cache()
|
|
BEGIN
|
|
SET @A= 20;
|
|
SELECT * FROM t1;
|
|
SELECT * FROM t1;
|
|
PREPARE x FROM 'SELECT 1';
|
|
SELECT * FROM t1;
|
|
SELECT * FROM t1;
|
|
END |
|
|
CALL t_cache();
|
|
a
|
|
1
|
|
2
|
|
-- Tracker : SESSION_TRACK_STATE_CHANGE
|
|
-- 1
|
|
|
|
a
|
|
1
|
|
2
|
|
a
|
|
1
|
|
2
|
|
-- Tracker : SESSION_TRACK_STATE_CHANGE
|
|
-- 1
|
|
|
|
a
|
|
1
|
|
2
|
|
CREATE PROCEDURE sel_with_session()
|
|
BEGIN
|
|
SET @var1=20;
|
|
SELECT * FROM t1 ORDER BY 1;
|
|
END |
|
|
CREATE PROCEDURE sel_with_no_session()
|
|
BEGIN
|
|
SELECT * FROM t1 ORDER BY 1;
|
|
END |
|
|
#cleanup
|
|
DROP DATABASE bug19550875;
|
|
-- Tracker : SESSION_TRACK_SCHEMA
|
|
--
|
|
|
|
|
|
End of tests
|
|
|