mirror/mariadb
1
0
Fork 0
mirror of https://github.com/MariaDB/server.git synced 2025-10-21 23:34:22 +02:00
Code Issues Projects Releases Packages Wiki Activity
mariadb/storage/spider/mysql-test/spider/t/timestamp.test
Yuchen Pei 8866fba00a
 MDEV-36454 MDEV-35452 Fix spider view protocol test failures caused by different queries constructed without GBH 
With view protocol, often during optimization, the GBH is not created
because join->tables_list is the view mysqltest_tmp_v which has MEMORY
as engine which does not have GBH implemented.

In such cases, if without view protocol the test takes a path that
does create a spider GBH, the resulting queries sent to the data node
often differ.

Therefore we disable view protocol for these statements.
2025-04-08 11:26:01 +10:00

570 lines
12 KiB
Text
Raw Permalink Blame History

--source timestamp_init.inc
--echo
--echo Initialize Time Zone
--connection master_1
SET GLOBAL time_zone='MET';
SET time_zone='MET';
if ($USE_CHILD_GROUP2)
{
--connection child2_1
SET GLOBAL time_zone='MET';
SET time_zone='MET';
}
--echo
--echo drop and create databases
--connection master_1
--disable_warnings
DROP DATABASE IF EXISTS ts_test_local;
CREATE DATABASE ts_test_local;
USE ts_test_local;
if ($USE_CHILD_GROUP2)
{
--connection child2_1
if ($USE_GENERAL_LOG)
{
SET @old_log_output = @@global.log_output;
SET GLOBAL log_output = 'TABLE,FILE';
}
DROP DATABASE IF EXISTS ts_test_remote;
CREATE DATABASE ts_test_remote;
USE ts_test_remote;
}
--enable_warnings
--echo
--echo test select 1
--connection master_1
SELECT 1;
if ($USE_CHILD_GROUP2)
{
if (!$OUTPUT_CHILD_GROUP2)
{
--disable_query_log
--disable_result_log
}
--connection child2_1
SELECT 1;
if (!$OUTPUT_CHILD_GROUP2)
{
--enable_query_log
--enable_result_log
}
}
--echo
--echo create table
if ($USE_CHILD_GROUP2)
{
if (!$OUTPUT_CHILD_GROUP2)
{
--disable_query_log
--disable_result_log
}
--connection child2_1
if ($OUTPUT_CHILD_GROUP2)
{
--disable_query_log
echo CHILD2_1_DROP_TABLE;
echo CHILD2_1_DROP_TABLE_F;
echo CHILD2_1_CREATE_TABLE;
echo CHILD2_1_CREATE_TABLE_F;
}
--disable_warnings
eval $CHILD2_1_DROP_TABLE;
eval $CHILD2_1_DROP_TABLE_F;
--enable_warnings
eval $CHILD2_1_CREATE_TABLE;
eval $CHILD2_1_CREATE_TABLE_F;
if ($OUTPUT_CHILD_GROUP2)
{
--enable_query_log
}
if ($USE_GENERAL_LOG)
{
TRUNCATE TABLE mysql.general_log;
}
if (!$OUTPUT_CHILD_GROUP2)
{
--enable_query_log
--enable_result_log
}
}
--connection master_1
--disable_warnings
DROP TABLE IF EXISTS tbl_a;
--enable_warnings
--disable_query_log
echo CREATE TABLE tbl_a (
col_a INT UNSIGNED NOT NULL AUTO_INCREMENT,
col_dt DATETIME,
col_ts TIMESTAMP NOT NULL
DEFAULT current_timestamp() ON UPDATE current_timestamp(),
PRIMARY KEY(col_a),
UNIQUE INDEX i_ts (col_ts)
) MASTER_1_ENGINE MASTER_1_AUTO_INCREMENT_2_1 MASTER_1_COMMENT_2_1;
eval CREATE TABLE tbl_a (
col_a INT UNSIGNED NOT NULL AUTO_INCREMENT,
col_dt DATETIME,
col_ts TIMESTAMP NOT NULL
DEFAULT current_timestamp() ON UPDATE current_timestamp(),
PRIMARY KEY(col_a),
UNIQUE INDEX i_ts (col_ts)
) $MASTER_1_ENGINE $MASTER_1_AUTO_INCREMENT_2_1 $MASTER_1_COMMENT_2_1;
echo CREATE TABLE tbl_f (
col_d DATE,
col_t TIME
) $MASTER_1_ENGINE $MASTER_1_COMMENT_2_1_F;
eval CREATE TABLE tbl_f (
col_d DATE,
col_t TIME
) $MASTER_1_ENGINE $MASTER_1_COMMENT_2_1_F;
--enable_query_log
SHOW CREATE TABLE tbl_a;
SHOW CREATE TABLE tbl_f;
--echo
--echo Set a different time zone that has DST
SET time_zone='+01:00';
--echo
--echo Insert Rows
--connection master_1
--echo Min value
SET @@timestamp=1;
INSERT INTO tbl_a VALUES (1, now(), now());
SET @@timestamp=0;
--echo Ambiguous DST values for MET time zone that result in the
--echo same UTC timestamp
INSERT INTO tbl_a VALUES (2, '2018-03-25 02:00:00', '2018-03-25 02:00:00');
INSERT INTO tbl_a VALUES (3, '2018-03-25 02:30:00', '2018-03-25 02:30:00');
--echo Ambiguous DST values for MET time zone in the 2:00 am to 3:00 am hour
--echo that occur twice when transitioning from DST to standard time
SET @@timestamp=1540686600;
INSERT INTO tbl_a VALUES (4, now(), now());
SET @@timestamp=1540690200;
INSERT INTO tbl_a VALUES (5, now(), now());
--echo Max value
SET @@timestamp=2147483647;
INSERT INTO tbl_a VALUES (6, now(), now());
SET @@timestamp=0;
--echo
--echo SELECTs
if ($USE_CHILD_GROUP2)
{
if (!$OUTPUT_CHILD_GROUP2)
{
--disable_query_log
--disable_result_log
}
--connection child2_1
if ($USE_GENERAL_LOG)
{
TRUNCATE TABLE mysql.general_log;
}
if (!$OUTPUT_CHILD_GROUP2)
{
--enable_query_log
--enable_result_log
}
}
--connection master_1
--disable_ps2_protocol
--disable_view_protocol
SELECT *, unix_timestamp(col_ts) FROM tbl_a;
--enable_view_protocol
if ($USE_CHILD_GROUP2)
{
if (!$OUTPUT_CHILD_GROUP2)
{
--disable_query_log
--disable_result_log
}
--connection child2_1
if ($USE_GENERAL_LOG)
{
--disable_view_protocol
eval $CHILD2_1_SELECT_ARGUMENT1;
--enable_view_protocol
}
eval $CHILD2_1_SELECT_TABLE;
if (!$OUTPUT_CHILD_GROUP2)
{
--enable_query_log
--enable_result_log
}
}
--enable_ps2_protocol
--echo
--echo DELETEs
if ($USE_CHILD_GROUP2)
{
if (!$OUTPUT_CHILD_GROUP2)
{
--disable_query_log
--disable_result_log
}
--connection child2_1
if ($USE_GENERAL_LOG)
{
TRUNCATE TABLE mysql.general_log;
}
if (!$OUTPUT_CHILD_GROUP2)
{
--enable_query_log
--enable_result_log
}
}
--connection master_1
DELETE FROM tbl_a WHERE col_ts='1970-01-01 01:00:01';
--disable_ps2_protocol
--disable_view_protocol
SELECT *, unix_timestamp(col_ts) FROM tbl_a;
--enable_view_protocol
if ($USE_CHILD_GROUP2)
{
if (!$OUTPUT_CHILD_GROUP2)
{
--disable_query_log
--disable_result_log
}
--connection child2_1
if ($USE_GENERAL_LOG)
{
--disable_view_protocol
eval $CHILD2_1_SELECT_ARGUMENT1;
--enable_view_protocol
}
eval $CHILD2_1_SELECT_TABLE;
if (!$OUTPUT_CHILD_GROUP2)
{
--enable_query_log
--enable_result_log
}
}
--enable_ps2_protocol
if ($USE_CHILD_GROUP2)
{
if (!$OUTPUT_CHILD_GROUP2)
{
--disable_query_log
--disable_result_log
}
--connection child2_1
if ($USE_GENERAL_LOG)
{
TRUNCATE TABLE mysql.general_log;
}
if (!$OUTPUT_CHILD_GROUP2)
{
--enable_query_log
--enable_result_log
}
}
--connection master_1
SET @@timestamp=1;
INSERT INTO tbl_a VALUES (1, now(), now());
SET @@timestamp=0;
--disable_ps2_protocol
--disable_view_protocol
SELECT *, unix_timestamp(col_ts) FROM tbl_a;
--enable_view_protocol
if ($USE_CHILD_GROUP2)
{
if (!$OUTPUT_CHILD_GROUP2)
{
--disable_query_log
--disable_result_log
}
--connection child2_1
if ($USE_GENERAL_LOG)
{
--disable_view_protocol
eval $CHILD2_1_SELECT_ARGUMENT1;
--enable_view_protocol
}
eval $CHILD2_1_SELECT_TABLE;
if (!$OUTPUT_CHILD_GROUP2)
{
--enable_query_log
--enable_result_log
}
}
--enable_ps2_protocol
--echo
--echo UPDATEs
if ($USE_CHILD_GROUP2)
{
if (!$OUTPUT_CHILD_GROUP2)
{
--disable_query_log
--disable_result_log
}
--connection child2_1
if ($USE_GENERAL_LOG)
{
TRUNCATE TABLE mysql.general_log;
}
if (!$OUTPUT_CHILD_GROUP2)
{
--enable_query_log
--enable_result_log
}
}
--connection master_1
UPDATE tbl_a SET col_ts=col_dt;
--disable_ps2_protocol
--disable_view_protocol
SELECT *, unix_timestamp(col_ts) FROM tbl_a;
--enable_view_protocol
if ($USE_CHILD_GROUP2)
{
if (!$OUTPUT_CHILD_GROUP2)
{
--disable_query_log
--disable_result_log
}
--connection child2_1
if ($USE_GENERAL_LOG)
{
--disable_view_protocol
eval $CHILD2_1_SELECT_ARGUMENT1;
--enable_view_protocol
}
eval $CHILD2_1_SELECT_TABLE;
if (!$OUTPUT_CHILD_GROUP2)
{
--enable_query_log
--enable_result_log
}
}
--enable_ps2_protocol
--echo
--echo Lookups
if ($USE_CHILD_GROUP2)
{
if (!$OUTPUT_CHILD_GROUP2)
{
--disable_query_log
--disable_result_log
}
--connection child2_1
if ($USE_GENERAL_LOG)
{
TRUNCATE TABLE mysql.general_log;
}
if (!$OUTPUT_CHILD_GROUP2)
{
--enable_query_log
--enable_result_log
}
}
--connection master_1
--disable_ps2_protocol
--disable_view_protocol
SELECT *, unix_timestamp(col_ts) FROM tbl_a WHERE col_ts > '2018-01-01';
SELECT *, unix_timestamp(col_ts) FROM tbl_a WHERE col_ts < '2018-10-28 02:30:00';
SELECT *, unix_timestamp(col_ts) FROM tbl_a WHERE '2018-10-28 02:30:00' > col_ts;
SELECT *, unix_timestamp(col_ts) FROM tbl_a WHERE col_ts BETWEEN '2018-10-28 01:30:00' AND '2018-10-28 02:30:00';
SELECT *, unix_timestamp(col_ts) FROM tbl_a WHERE col_ts >= '2018-10-28 01:30:00' AND col_ts <= '2018-10-28 02:30:00';
SELECT *, unix_timestamp(col_ts) FROM tbl_a WHERE col_ts > 180325020000;
SELECT *, unix_timestamp(col_ts) FROM tbl_a WHERE col_ts > 19700101010001;
--enable_view_protocol
if ($USE_CHILD_GROUP2)
{
if (!$OUTPUT_CHILD_GROUP2)
{
--disable_query_log
--disable_result_log
}
--connection child2_1
if ($USE_GENERAL_LOG)
{
--disable_view_protocol
eval $CHILD2_1_SELECT_ARGUMENT1;
--enable_view_protocol
}
eval $CHILD2_1_SELECT_TABLE;
if (!$OUTPUT_CHILD_GROUP2)
{
--enable_query_log
--enable_result_log
}
}
--enable_ps2_protocol
--echo
--echo Drop the index on the timestamp column
if ($USE_CHILD_GROUP2)
{
if (!$OUTPUT_CHILD_GROUP2)
{
--disable_query_log
--disable_result_log
}
--connection child2_1
eval $CHILD2_1_DROP_INDEX;
eval $CHILD2_1_SHOW_CREATE_TABLE;
if ($OUTPUT_CHILD_GROUP2)
{
--enable_query_log
}
if ($USE_GENERAL_LOG)
{
TRUNCATE TABLE mysql.general_log;
}
if (!$OUTPUT_CHILD_GROUP2)
{
--enable_query_log
--enable_result_log
}
}
--connection master_1
DROP INDEX i_ts ON tbl_a;
SHOW CREATE TABLE tbl_a;
--echo
--echo Retry lookups on unindexed timestamp column
if ($USE_CHILD_GROUP2)
{
if (!$OUTPUT_CHILD_GROUP2)
{
--disable_query_log
--disable_result_log
}
--connection child2_1
if ($USE_GENERAL_LOG)
{
TRUNCATE TABLE mysql.general_log;
}
if (!$OUTPUT_CHILD_GROUP2)
{
--enable_query_log
--enable_result_log
}
}
--connection master_1
--disable_ps2_protocol
--disable_view_protocol
SELECT *, unix_timestamp(col_ts) FROM tbl_a WHERE col_ts > '2018-01-01';
SELECT *, unix_timestamp(col_ts) FROM tbl_a WHERE col_ts < '2018-10-28 02:30:00';
SELECT *, unix_timestamp(col_ts) FROM tbl_a WHERE '2018-10-28 02:30:00' > col_ts;
SELECT *, unix_timestamp(col_ts) FROM tbl_a WHERE col_ts BETWEEN '2018-10-28 01:30:00' AND '2018-10-28 02:30:00';
SELECT *, unix_timestamp(col_ts) FROM tbl_a WHERE col_ts >= '2018-10-28 01:30:00' AND col_ts <= '2018-10-28 02:30:00';
SELECT *, unix_timestamp(col_ts) FROM tbl_a WHERE col_ts > 180325020000;
SELECT *, unix_timestamp(col_ts) FROM tbl_a WHERE col_ts > 19700101010001;
--enable_view_protocol
if ($USE_CHILD_GROUP2)
{
if (!$OUTPUT_CHILD_GROUP2)
{
--disable_query_log
--disable_result_log
}
--connection child2_1
if ($USE_GENERAL_LOG)
{
--disable_view_protocol
eval $CHILD2_1_SELECT_ARGUMENT1;
--enable_view_protocol
}
eval $CHILD2_1_SELECT_TABLE;
if (!$OUTPUT_CHILD_GROUP2)
{
--enable_query_log
--enable_result_log
}
}
--enable_ps2_protocol
--echo
--echo Test the TIMESTAMP function
--connection master_1
INSERT INTO tbl_f VALUES ('2018-06-24', '01:23:45'),
('2018-06-24', '01:23:45'),
('2018-08-01', '12:34:56');
if ($USE_CHILD_GROUP2)
{
if (!$OUTPUT_CHILD_GROUP2)
{
--disable_query_log
--disable_result_log
}
--connection child2_1
if ($USE_GENERAL_LOG)
{
TRUNCATE TABLE mysql.general_log;
}
if (!$OUTPUT_CHILD_GROUP2)
{
--enable_query_log
--enable_result_log
}
}
--connection master_1
--disable_ps2_protocol
--disable_view_protocol
SELECT * FROM tbl_f;
SELECT TIMESTAMP(col_d, col_t) FROM tbl_f;
SELECT TIMESTAMP('2018-06-25', col_t) FROM tbl_f;
SELECT TIMESTAMP(col_d, '10:43:21') FROM tbl_f;
SELECT TIMESTAMP('2018-06-25', '10:43:21') FROM tbl_f;
--enable_view_protocol
if ($USE_CHILD_GROUP2)
{
if (!$OUTPUT_CHILD_GROUP2)
{
--disable_query_log
--disable_result_log
}
--connection child2_1
if ($USE_GENERAL_LOG)
{
--disable_view_protocol
eval $CHILD2_1_SELECT_ARGUMENT1;
--enable_view_protocol
}
eval $CHILD2_1_SELECT_TABLE_F;
if (!$OUTPUT_CHILD_GROUP2)
{
--enable_query_log
--enable_result_log
}
}
--enable_ps2_protocol
--echo
--echo Restore Time Zone settings
--connection master_1
SET GLOBAL time_zone=DEFAULT;
SET time_zone=DEFAULT;
if ($USE_CHILD_GROUP2)
{
--connection child2_1
SET GLOBAL time_zone=DEFAULT;
SET time_zone=DEFAULT;
}
--echo
--echo deinit
--disable_warnings
--connection master_1
DROP DATABASE IF EXISTS ts_test_local;
if ($USE_CHILD_GROUP2)
{
--connection child2_1
DROP DATABASE IF EXISTS ts_test_remote;
if ($USE_GENERAL_LOG)
{
SET GLOBAL log_output = @old_log_output;
}
}
--enable_warnings
--source timestamp_deinit.inc
--echo
--echo end of test
Reference in a new issue View git blame Copy permalink