4493d40a-92aa-4162-9dfc-bfc4ec0996e6
/
polardbxengine
2
0
Fork 0
Code Issues Pull Requests Packages Projects Releases Wiki Activity
polardbxengine/mysql-test/t/opt_hints_set_var.test

478 lines
19 KiB
Plaintext
Raw Permalink Blame History

CREATE TABLE t1(f1 INT);
# Parser tests
SELECT /*+ SET_VAR(foo = 10) */ 1;
SELECT /*+ SET_VAR(foo = 1K) */ 1;
SELECT /*+ SET_VAR(foo = 21M) */ 1;
SELECT /*+ SET_VAR(foo = 321G) */ 1;
SELECT /*+ SET_VAR(foo = 9000100500G) */ 1;
SELECT /*+ SET_VAR(foo = 'test') */ 1;
SELECT /*+ SET_VAR(foo = "test""test") */ 1;
SELECT /*+ SET_VAR(foo = 900010050018247362846826482468) */ 1;
SELECT /*+ QB_NAME(1KLMN) */ 1;
SELECT /*+ SET_VAR(foo = 900010050018247362846826482468000) */ 1;
# Testing various warning issued due to incorrect hint arguments/non-updateble sys variables
# Check duplicated hints, second hint is ignored with warning
explain SELECT /*+ SET_VAR(optimizer_switch = 'batched_key_access=on') SET_VAR(optimizer_switch = 'batched_key_access=off')*/ * FROM t1;
# Check duplicated hints, second hint is ignored with warning
explain SELECT /*+ SET_VAR(max_join_size=1) SET_VAR(max_join_size=1) */ * FROM t1;
# Check duplicated hints, last hint is ignored with warning
explain SELECT /*+ SET_VAR(optimizer_switch = 'batched_key_access=on')
SET_VAR(big_tables=on)
SET_VAR(big_tables=off) */ * FROM t1;
# Check incorrect variable name, hint is ignored with warning
explain SELECT /*+ SET_VAR(optimizer_switc = 'batched_key_access=off') */ * FROM t1;
# Check incorrect value of the system variable, hint is ignored with warning
explain SELECT /*+ SET_VAR(optimizer_switch = 'batched_key_access=yes') */ * FROM t1;
# Check incorrect value of the system variable, hint is ignored with warning
explain SELECT /*+ SET_VAR(optimizer_switch = 'batched_key_acces=off') */ * FROM t1;
# Check hint behavior with non-updateable system variable, hint is ignored with warning
explain SELECT /*+ SET_VAR(max_allowed_packet = 1M) */ * FROM t1;
# Testing warning messages for the variables with incorrect values
explain SELECT /*+ SET_VAR(optimizer_switch = 'batched_key_acces=off') SET_VAR(range_alloc_block_size=amba)*/ * FROM t1;
# Testing warning message for the variables with truncated value
SELECT /*+ SET_VAR(range_alloc_block_size=7000) */ 1;
DROP TABLE t1;
# Testing SET_VAR hint with prepared statements, variable is restored after PS execution.
PREPARE stmt FROM "SELECT /*+ SET_VAR(big_tables=on) */ VARIABLE_VALUE FROM performance_schema.session_variables where VARIABLE_NAME = 'big_tables'";
SELECT VARIABLE_VALUE FROM performance_schema.session_variables where VARIABLE_NAME = 'big_tables';
EXECUTE stmt;
SELECT VARIABLE_VALUE FROM performance_schema.session_variables where VARIABLE_NAME = 'big_tables';
EXECUTE stmt;
SELECT VARIABLE_VALUE FROM performance_schema.session_variables where VARIABLE_NAME = 'big_tables';
DEALLOCATE PREPARE stmt;
# Auxiliary SP which returns variable value before hint, with hint and after hint applying
DELIMITER \\;
CREATE PROCEDURE test_hint (hint_str VARCHAR(255), var_str VARCHAR(64))
BEGIN
SET @orig_q= CONCAT("SELECT
VARIABLE_VALUE
FROM performance_schema.session_variables where VARIABLE_NAME = '", var_str, "'");
SET @hint_q= CONCAT("SELECT /*+ ", hint_str,
"*/ VARIABLE_VALUE" ,
" FROM performance_schema.session_variables where VARIABLE_NAME = '", var_str, "'");
PREPARE orig_stmt FROM @orig_q;
PREPARE hint_stmt FROM @hint_q;
EXECUTE orig_stmt;
EXECUTE hint_stmt;
EXECUTE orig_stmt;
DEALLOCATE PREPARE orig_stmt;
DEALLOCATE PREPARE hint_stmt;
END\\
DELIMITER ;\\
# Tetsing auto_increment_increment variable.
CREATE TABLE t1 (col INT NOT NULL AUTO_INCREMENT PRIMARY KEY);
INSERT /*+ SET_VAR(auto_increment_increment=10) */ INTO t1 VALUES (NULL), (NULL), (NULL), (NULL);
SELECT * FROM t1;
DROP TABLE t1;
CALL test_hint("SET_VAR(auto_increment_increment=10)", "auto_increment_increment");
# Testing auto_increment_offset variable.
CREATE TABLE t1 (col INT NOT NULL AUTO_INCREMENT PRIMARY KEY);
INSERT /*+ SET_VAR(auto_increment_increment=10) SET_VAR(auto_increment_offset=5) */ INTO t1 VALUES (NULL), (NULL), (NULL), (NULL);
SELECT * FROM t1;
DROP TABLE t1;
CALL test_hint("SET_VAR(auto_increment_offset=5)", "auto_increment_offset");
# Testing big_tables variable, 'tmp_disk_tables' status variable changes its value with the hint.
CREATE TABLE t1 (a CHAR PRIMARY KEY);
INSERT INTO t1 VALUES (1),(2);
FLUSH STATUS;
SELECT COUNT(DISTINCT t1.a) FROM t1,t1 t2 WHERE t1.a = 1;
SHOW STATUS LIKE '%tmp_disk_tables%';
FLUSH STATUS;
SELECT /*+ SET_VAR(big_tables=on) */ COUNT(DISTINCT t1.a) FROM t1,t1 t2 WHERE t1.a = 1;
SHOW STATUS LIKE '%tmp_disk_tables%';
DROP TABLE t1;
CALL test_hint("SET_VAR(big_tables=on)", "big_tables");
# Testing bulk_insert_buffer_size variable, no functional test.
CALL test_hint("SET_VAR(bulk_insert_buffer_size=0)", "bulk_insert_buffer_size");
# Testing default_tmp_storage_engine variable.
CREATE TEMPORARY TABLE t1 SELECT 1;
SHOW CREATE TABLE t1;
CREATE TEMPORARY TABLE t2 SELECT /*+ SET_VAR(default_tmp_storage_engine=InnoDB)*/ 1;
SHOW CREATE TABLE t2;
DROP TABLE t1, t2;
CALL test_hint("SET_VAR(default_tmp_storage_engine=InnoDB)", "default_tmp_storage_engine");
# Testing div_precision_increment
SELECT /*+ SET_VAR(div_precision_increment=12) */ 1/2;
CALL test_hint("SET_VAR(div_precision_increment=12)", "div_precision_increment");
# Testing end_markers_in_json variable, no functional test.
CALL test_hint("SET_VAR(end_markers_in_json=ON)", "end_markers_in_json");
# Testing eq_range_index_dive_limit variable, no functional test.
CALL test_hint("SET_VAR(eq_range_index_dive_limit=0)", "eq_range_index_dive_limit");
# Testing foreign_key_checks variable, no error for INSERT due to disabled foreign key checks.
CREATE TABLE t1
(
id INT PRIMARY KEY
) ENGINE=InnoDB;
CREATE TABLE t2
(
v INT,
CONSTRAINT c1 FOREIGN KEY (v) REFERENCES t1(id)
) ENGINE=InnoDB;
INSERT /*+ SET_VAR(foreign_key_checks=0) */INTO t2 VALUES(2);
DROP TABLE t2, t1;
CALL test_hint("SET_VAR(foreign_key_checks=0)", "foreign_key_checks");
# Testing group_concat_max_len variable, result has the length of 20 characters,
# truncation warning is issued.
CREATE TABLE t1( a VARCHAR( 10 ), b INT );
INSERT INTO t1 VALUES ( repeat( 'a', 10 ), 1),
( repeat( 'b', 10 ), 2);
SELECT /*+ SET_VAR(group_concat_max_len=20) */ GROUP_CONCAT(a) FROM t1;
DROP TABLE t1;
CALL test_hint("SET_VAR(group_concat_max_len=20)", "group_concat_max_len");
# Testing insert_id variable, first inserted value is 10.
CREATE TABLE t1 (col INT NOT NULL AUTO_INCREMENT PRIMARY KEY);
INSERT /*+ SET_VAR(insert_id=10) */ INTO t1 VALUES (NULL), (NULL), (NULL), (NULL);
SELECT * FROM t1;
DROP TABLE t1;
CALL test_hint("SET_VAR(insert_id=10)", "insert_id");
# Testing join_buffer_size variable, no functional test.
CALL test_hint("SET_VAR(join_buffer_size=128)", "join_buffer_size");
# Testing lock_wait_timeout variable, ER_LOCK_WAIT_TIMEOUT error should be issued.
CREATE TABLE t1(i INT) ENGINE InnoDB;
LOCK TABLES t1 WRITE;
connect (con1,localhost,root,,);
--error ER_LOCK_WAIT_TIMEOUT
INSERT /*+ SET_VAR(lock_wait_timeout=1) */INTO t1 VALUES (1);
connection default;
disconnect con1;
UNLOCK TABLES;
DROP TABLE t1;
CALL test_hint("SET_VAR(lock_wait_timeout=1)", "lock_wait_timeout");
# Testing max_error_count variable, no warning should be issued.
CREATE TABLE t1(f1 INT);
EXPLAIN SELECT /*+ SET_VAR(max_error_count=0) SET_VAR(optimizer_switch = 'batched_key_acces=off') SET_VAR(range_alloc_block_size=amba)*/ * FROM t1;
DROP TABLE t1;
CALL test_hint("SET_VAR(max_error_count=0)", "max_error_count");
# Testing max_execution_time variable, ER_QUERY_TIMEOUT should be issued.
CREATE TABLE t1 (a INT, b VARCHAR(300));
INSERT INTO t1 VALUES (1, 'string');
INSERT INTO t1 SELECT * FROM t1;
INSERT INTO t1 SELECT * FROM t1;
INSERT INTO t1 SELECT * FROM t1;
INSERT INTO t1 SELECT * FROM t1;
INSERT INTO t1 SELECT * FROM t1;
INSERT INTO t1 SELECT * FROM t1;
INSERT INTO t1 SELECT * FROM t1;
INSERT INTO t1 SELECT * FROM t1;
INSERT INTO t1 SELECT * FROM t1;
INSERT INTO t1 SELECT * FROM t1;
INSERT INTO t1 SELECT * FROM t1;
-- disable_query_log
-- disable_result_log
analyze table t1;
-- enable_result_log
-- enable_query_log
--error ER_QUERY_TIMEOUT
SELECT /*+ SET_VAR(max_execution_time=1) */ * FROM t1 a, t1 b;
DROP TABLE t1;
CALL test_hint("SET_VAR(max_execution_time=1)", "max_execution_time");
# Testing max_heap_table_size, internal_tmp_mem_storage_engine variables,
# 'Handler_write' status variable changes its value with the hint.
CREATE TABLE t1(f1 CHAR(255) CHARSET utf8);
INSERT INTO t1 VALUES('1'),('2'),('3'),('4'),('5'),('6'),('7'),('8'),('9'),('0');
SET @optimizer_switch_saved= @@optimizer_switch;
SET @@optimizer_switch="derived_merge=off";
FLUSH STATUS;
SELECT /*+ SET_VAR(max_heap_table_size=16384) SET_VAR(internal_tmp_mem_storage_engine=MEMORY)*/ count(*)
FROM t1 JOIN (
SELECT t1.f1 FROM t1 JOIN t1 AS t2 JOIN t1 AS t3) tt ON t1.f1 = tt.f1;
SHOW STATUS LIKE 'Handler_write';
FLUSH STATUS;
SELECT count(*)
FROM t1 JOIN (
SELECT t1.f1 FROM t1 JOIN t1 AS t2 JOIN t1 AS t3) tt ON t1.f1 = tt.f1;
SHOW STATUS LIKE 'Handler_write';
SET @@optimizer_switch= @optimizer_switch_saved;
DROP TABLE t1;
CALL test_hint("SET_VAR(max_heap_table_size=16384)", "max_heap_table_size");
CALL test_hint("SET_VAR(internal_tmp_mem_storage_engine=MEMORY)", "internal_tmp_mem_storage_engine");
# Testing max_length_for_sort_data variable, no functional test.
CALL test_hint("SET_VAR(max_length_for_sort_data=4)", "max_length_for_sort_data");
# Testing max_points_in_geometry variable, the value in error message is 1048576.
--error ER_GIS_MAX_POINTS_IN_GEOMETRY_OVERFLOWED
SELECT /*+ SET_VAR(max_points_in_geometry=1048576) */
st_astext(st_buffer(point(-5,0),8772, st_buffer_strategy( 'point_circle',1024*1024*1024))) as result;
CALL test_hint("SET_VAR(max_points_in_geometry=1048576)", "max_points_in_geometry");
# Testing max_seeks_for_key variable, no functional test.
--replace_result 18446744073709551615 DEFAULT 4294967295 DEFAULT
CALL test_hint("SET_VAR(max_seeks_for_key=1)", "max_seeks_for_key");
# Testing optimizer_prune_level, optimizer_search_depth variables,
# EXPLAIN output is different depending on SET_VAR hint use.
CREATE TABLE t1 (a INT, b INT, PRIMARY KEY (a));
CREATE TABLE t2 (a INT, INDEX a (a));
CREATE TABLE t3 (a INT, b INT, INDEX a (a,b));
INSERT INTO t1 VALUES (1,10), (2,20), (3,30), (4,40);
INSERT INTO t2 VALUES (2), (3), (4), (5);
INSERT INTO t3 VALUES (10,3), (20,4), (30,5);
ANALYZE TABLE t1, t2, t3;
EXPLAIN SELECT * FROM t1 JOIN t2 ON t1.b = t2.a WHERE
t2.a IN (SELECT t3.b FROM t3 JOIN t1 t4 ON t3.b = t4.b);
EXPLAIN SELECT /*+ SET_VAR(optimizer_search_depth=1) */ * FROM t1 JOIN t2 ON t1.b = t2.a WHERE
t2.a IN (SELECT t3.b FROM t3 JOIN t1 t4 ON t3.b = t4.b);
EXPLAIN SELECT /*+ SET_VAR(optimizer_prune_level=0) */ * FROM t1 JOIN t2 ON t1.b = t2.a WHERE
t2.a IN (SELECT t3.b FROM t3 JOIN t1 t4 ON t3.b = t4.b);
CALL test_hint("SET_VAR(optimizer_search_depth=1)", "optimizer_search_depth");
CALL test_hint("SET_VAR(optimizer_prune_level=0)", "optimizer_prune_level");
DROP TABLE t1, t2, t3;
# Testing optimizer_switch variable,
# 'handler_read%' variables change their values depending on hint use.
CREATE TABLE t1
(
f1 int NOT NULL DEFAULT '0',
f2 int NOT NULL DEFAULT '0',
f3 int NOT NULL DEFAULT '0',
INDEX idx1(f2, f3), INDEX idx2(f3)
);
INSERT INTO t1(f1) VALUES (1), (2), (3), (4), (5), (6), (7), (8);
INSERT INTO t1(f2, f3) VALUES (3,4), (3,4);
ANALYZE TABLE t1;
set optimizer_switch='mrr=on,mrr_cost_based=off';
--echo # Check statistics without hint
FLUSH STATUS;
SELECT * FROM t1 WHERE f2 <= 3 AND 3 <= f3;
SHOW STATUS LIKE 'handler_read%';
--echo # Check statistics with hint
FLUSH STATUS;
SELECT /*+ SET_VAR(optimizer_switch='mrr=off') */ * FROM t1 WHERE f2 <= 3 AND 3 <= f3;
SHOW STATUS LIKE 'handler_read%';
set optimizer_switch=default;
DROP TABLE t1;
CALL test_hint("SET_VAR(optimizer_switch='mrr=off')", "optimizer_switch");
# Testing range_alloc_block_size variable, no functional test.
CALL test_hint("SET_VAR(range_alloc_block_size=8192)", "range_alloc_block_size");
# Testing range_optimizer_max_mem_size variable, warning is issued with the hint.
CREATE TABLE t1 (f1 INT, KEY(f1));
INSERT INTO t1 VALUES (1), (2), (3), (4), (5), (6), (7);
SELECT /*+ SET_VAR(range_optimizer_max_mem_size=1) */ f1 FROM t1 WHERE f1 = 1 OR f1 = 2 OR f1 = 6;
DROP TABLE t1;
CALL test_hint("SET_VAR(range_optimizer_max_mem_size=1)", "range_optimizer_max_mem_size");
# Testing read_buffer_size, no functional test.
CALL test_hint("SET_VAR(read_buffer_size=8200)", "read_buffer_size");
# Testing read_rnd_buffer_size, no functional test.
CALL test_hint("SET_VAR(read_rnd_buffer_size=1)", "read_rnd_buffer_size");
# Testing sort_buffer_size variable,
# 'Sort_merge_passes' status variable changes its value with the hint.
CREATE TABLE t1 (f1 CHAR(255));
INSERT INTO t1 VALUES ('aaa'), ('bbb'), ('ccc'), ('ddd'), ('eee');
INSERT INTO t1 SELECT * FROM t1;
INSERT INTO t1 SELECT * FROM t1;
INSERT INTO t1 SELECT * FROM t1;
INSERT INTO t1 SELECT * FROM t1;
INSERT INTO t1 SELECT * FROM t1;
FLUSH STATUS;
--disable_result_log
SELECT /*+ SET_VAR(sort_buffer_size=32768) */f1 FROM t1 ORDER BY f1;
--enable_result_log
SHOW STATUS LIKE 'Sort_merge_passes';
FLUSH STATUS;
--disable_result_log
SELECT f1 FROM t1 ORDER BY f1;
--enable_result_log
SHOW STATUS LIKE 'Sort_merge_passes';
DROP TABLE t1;
CALL test_hint("SET_VAR(sort_buffer_size=32768)", "sort_buffer_size");
# Testing sql_auto_is_null variable, SELECT should return one row.
CREATE TABLE t1 (a INT AUTO_INCREMENT PRIMARY KEY);
SET insert_id = 1;
INSERT INTO t1 VALUES (NULL);
SET insert_id = 0;
SELECT /*+ SET_VAR(sql_auto_is_null=1) */ a FROM t1 WHERE a IS NULL;
DROP TABLE t1;
CALL test_hint("SET_VAR(sql_auto_is_null=1)", "sql_auto_is_null");
# Testing sql_big_selects, max_join_size variables,
# ER_TOO_BIG_SELECT error should be issued with the hint
CREATE TABLE t1 (a INT, b VARCHAR(300));
INSERT INTO t1 VALUES (1, 'string');
INSERT INTO t1 SELECT * FROM t1;
INSERT INTO t1 SELECT * FROM t1;
INSERT INTO t1 SELECT * FROM t1;
--error ER_TOO_BIG_SELECT
SELECT /*+ SET_VAR(sql_big_selects=0) SET_VAR(max_join_size=1) */ * FROM t1 a, t1 b;
DROP TABLE t1;
CALL test_hint("SET_VAR(sql_big_selects=0)", "sql_big_selects");
CALL test_hint("SET_VAR(max_join_size=1)", "max_join_size");
# Testing sql_buffer_result variable,
# EXPLAIN output shows 'Using temporary' with the hint.
CREATE TABLE t1 (
a int,
b varchar(1),
KEY (b,a)
) charset utf8mb4;
INSERT INTO t1 VALUES (1,NULL),(0,'a'),(1,NULL),(0,'a');
INSERT INTO t1 VALUES (1,'a'),(0,'a'),(1,'a'),(0,'a');
ANALYZE TABLE t1;
EXPLAIN SELECT /*+ SET_VAR(sql_buffer_result=1) */ MIN(a), b FROM t1 WHERE t1.b = 'a' GROUP BY b;
DROP TABLE t1;
CALL test_hint("SET_VAR(sql_buffer_result=1)", "sql_buffer_result");
# Testing sql_mode variable, zero date is inserted without error with the hint.
CREATE TABLE t1 (f1 DATE);
INSERT /*+ SET_VAR(sql_mode='ALLOW_INVALID_DATES') */ INTO t1 VALUES ('00-00-00');
DROP TABLE t1;
CALL test_hint("SET_VAR(sql_mode='ALLOW_INVALID_DATES')", "sql_mode");
# Testing sql_safe_updates variable, UPDATE fails with the hint.
CREATE TABLE t1 ( a INT, KEY( a ) );
INSERT INTO t1 VALUES (0), (1);
CREATE VIEW v1 AS SELECT t11.a, t12.a AS b FROM t1 t11, t1 t12;
--error ER_UPDATE_WITHOUT_KEY_IN_SAFE_MODE
UPDATE /*+ SET_VAR(sql_safe_updates=1) */ IGNORE v1 SET a = 1;
DROP TABLE t1;
DROP VIEW v1;
CALL test_hint("SET_VAR(sql_safe_updates=1)", "sql_safe_updates");
# Testing sql_select_limit variable, SELECT returns one row with the hint.
CREATE TABLE t1 (f1 INT);
INSERT INTO t1 VALUES (1), (2);
SELECT /*+ SET_VAR(sql_select_limit=1) */* FROM t1;
DROP TABLE t1;
CALL test_hint("SET_VAR(sql_select_limit=1)", "sql_select_limit");
# Testing timestamp variable, 'Truncated incorrect time value: '1322115328'' warning is issued.
SELECT /*+ SET_VAR(timestamp=1322115328) */ CAST(UNIX_TIMESTAMP() AS TIME);
# Testing tmp_table_size variable,
# 'Created_tmp_files' status variable changes its value with the hint.
CREATE TABLE t1 (a INT);
INSERT INTO t1 VALUES (1),(2),(3),(4),(5),(6),(7),(8);
INSERT INTO t1 SELECT a+8 FROM t1;
INSERT INTO t1 SELECT a+16 FROM t1;
INSERT INTO t1 SELECT a+32 FROM t1;
INSERT INTO t1 SELECT a+64 FROM t1;
INSERT INTO t1 VALUE(NULL);
FLUSH STATUS;
SELECT /*+ SET_VAR(tmp_table_size=1024) */ COUNT(DISTINCT a) FROM t1;
SHOW STATUS LIKE 'Created_tmp_files';
DROP TABLE t1;
CALL test_hint("SET_VAR(tmp_table_size=1024)", "tmp_table_size");
# Testing updatable_views_with_limit variable.
CALL test_hint("SET_VAR(updatable_views_with_limit=NO)", "updatable_views_with_limit");
# Testing unique_checks variable, no functional test.
CALL test_hint("SET_VAR(unique_checks=OFF)", "unique_checks");
# Testing windowing_use_high_precision variable,
# second EXPLAIN should return the same plan with windowing_use_high_precision disabled.
CREATE TABLE t1 (d DOUBLE, id INT, sex CHAR(1), n INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(n));
INSERT INTO t1(d, id, sex) VALUES (1.0, 1, 'M'),
(2.0, 2, 'F'),
(3.0, 3, 'F'),
(4.0, 4, 'F'),
(5.0, 5, 'M'),
(NULL, NULL, 'M'),
(10.0, 10, NULL),
(10.0, 10, NULL),
(11.0, 11, NULL);
ANALYZE TABLE t1;
EXPLAIN FORMAT=JSON SELECT
id, AVG(id) over w `avg`, SUM(id) OVER w `sum`, COUNT(*) OVER w cnt
FROM t1 WINDOW w as (ORDER BY id RANGE BETWEEN 1 FOLLOWING AND 2 FOLLOWING);
EXPLAIN FORMAT=JSON SELECT /*+ SET_VAR(windowing_use_high_precision=OFF)*/
d, AVG(d) over w `avg`, SUM(d) OVER w `sum`, COUNT(*) OVER w cnt
FROM t1 WINDOW w as (ORDER BY d RANGE BETWEEN 1 FOLLOWING AND 2 FOLLOWING);
DROP TABLE t1;
CALL test_hint("SET_VAR(windowing_use_high_precision=OFF)", "windowing_use_high_precision");
# Testing cte_max_recursion_depth
--error ER_CTE_MAX_RECURSION_DEPTH
WITH RECURSIVE qn AS (
SELECT 1 AS a UNION ALL
SELECT 1+a FROM qn WHERE a<=10)
SELECT /*+ SET_VAR(cte_max_recursion_depth=5) */ COUNT(*) FROM qn;
CALL test_hint("SET_VAR(cte_max_recursion_depth=5)", "cte_max_recursion_depth");
DROP PROCEDURE test_hint;
--echo #
--echo # Bug#26482684 WL681: RESULT DIFF SEEN FOR SQL_SELECT_LIMIT HINT.
--echo #
SELECT /*+ SET_VAR(sql_select_limit = 18446744073709551616) */ 1;
SELECT /*+ SET_VAR(sql_select_limit = 18446744073709551615) */ 1;
--echo #
--echo # Bug#29776464 MAKE 'TIME_ZONE' VARIABLE OPTIMIZER HINT SETTABLE.
--echo #
SET time_zone = '-01:00';
SELECT TIMEDIFF(NOW(), UTC_TIMESTAMP);
SELECT /*+ SET_VAR(time_zone = '+04:00') */ TIMEDIFF(NOW(), UTC_TIMESTAMP);
SELECT @@time_zone;
SELECT /*+ SET_VAR(time_zone = 'UTC') */ TIMEDIFF(NOW(), UTC_TIMESTAMP);
SELECT @@time_zone;
CREATE TABLE t1(f1 VARCHAR(10));
INSERT INTO t1 VALUES (@@time_zone);
SELECT * FROM t1;
UPDATE /*+ SET_VAR(time_zone = 'UTC') */ t1 SET f1 = TIMEDIFF(NOW(), UTC_TIMESTAMP);
SELECT * FROM t1;
INSERT /*+ SET_VAR(time_zone = 'UTC') */ t1 VALUES (TIMEDIFF(NOW(), UTC_TIMESTAMP));
SELECT * FROM t1;
DELETE /*+ SET_VAR(time_zone = 'UTC') */ FROM t1 WHERE f1 = TIMEDIFF(NOW(), UTC_TIMESTAMP);
SELECT * FROM t1;
SELECT @@time_zone;
DROP TABLE t1;
SET time_zone = default;
Reference in New Issue View Git Blame Copy Permalink