From e4dd3ed76e3b9fa09d48ed9ec970001f5dabc8b4 Mon Sep 17 00:00:00 2001 From: Daniel Lenski Date: Thu, 11 May 2023 14:19:48 -0700 Subject: [PATCH] Fix hard-coded timestamps in main.derived_condition_pushdown MTR test Two hard-coded timestamps were added to this test in https://github.com/mysql/mysql-server/commit/22f6ef90cae8#diff-d7e1b8c7138b94f92fbd555c44e7facc027cb50217f59f51729444ee7c3b37a4R1950 1. '2022-05-06 16:49:45' (assumed by the test's result file to be `< NOW()`) 2. '2023-05-06 16:49:45' (assumed by the test's result file to be `>= NOW()`) Because the second of these timestamps is now in the past, the test is now failing due to result mismatch. Tests should not be written to include hard-coded assumptions about the local time in their execution environment; these are essentially guaranteed to get out-of-date and cause failures. This test can very easily be rewritten to choose past/future timestamps relative to the time at which it is run, `DATE_ADD(NOW(), INTERVAL [+-] 1 DAY)`. Note that Ubuntu's build environment had to disable this test on 2022-05-09, likely due to encountering this problem: https://bugs.launchpad.net/ubuntu/+source/mysql-8.0/8.0.33-0ubuntu2 All new code of the whole pull request, including one or several files that are either new files or modified ones, are contributed under the BSD-new license. I am contributing on behalf of my employer Amazon Web Services, Inc. --- mysql-test/r/derived_condition_pushdown.result | 4 ++-- mysql-test/t/derived_condition_pushdown.test | 4 ++-- 2 files changed, 4 insertions(+), 4 deletions(-) diff --git a/mysql-test/r/derived_condition_pushdown.result b/mysql-test/r/derived_condition_pushdown.result index 405db15319a0..77f67d29ebb8 100644 --- a/mysql-test/r/derived_condition_pushdown.result +++ b/mysql-test/r/derived_condition_pushdown.result @@ -2098,7 +2098,7 @@ UNION SELECT NOW() AS time FROM t1 WHERE f1 = 0) AS dt WHERE time <= ?"; SET @a = 1; -SET @b = '2022-05-06 16:49:45'; +SET @b = DATE_FORMAT(DATE_ADD(NOW(), INTERVAL -1 DAY), '%Y-%m-%d %H:%i:%s'); EXECUTE stmt USING @a, @b; time PREPARE stmt FROM "EXPLAIN FORMAT=tree SELECT * @@ -2111,7 +2111,7 @@ EXPLAIN -> Zero rows (no matching row in const table) (rows=0) SET @a = 2; -SET @b = '2023-05-06 16:49:45'; +SET @b = DATE_FORMAT(DATE_ADD(NOW(), INTERVAL +1 DAY), '%Y-%m-%d %H:%i:%s'); EXECUTE stmt USING @a, @b; EXPLAIN -> Table scan on dt (rows=2) diff --git a/mysql-test/t/derived_condition_pushdown.test b/mysql-test/t/derived_condition_pushdown.test index cfa748f5ec08..f13b4c794b7e 100644 --- a/mysql-test/t/derived_condition_pushdown.test +++ b/mysql-test/t/derived_condition_pushdown.test @@ -1305,14 +1305,14 @@ let $query = SELECT * WHERE time <= ?; eval PREPARE stmt FROM "$query"; SET @a = 1; -SET @b = '2022-05-06 16:49:45'; +SET @b = DATE_FORMAT(DATE_ADD(NOW(), INTERVAL -1 DAY), '%Y-%m-%d %H:%i:%s'); # Date in the past eval EXECUTE stmt USING @a, @b; eval PREPARE stmt FROM "EXPLAIN FORMAT=tree $query"; --replace_regex $elide_costs --skip_if_hypergraph # Depends on the query plan. eval EXECUTE stmt USING @a, @b; SET @a = 2; -SET @b = '2023-05-06 16:49:45'; +SET @b = DATE_FORMAT(DATE_ADD(NOW(), INTERVAL +1 DAY), '%Y-%m-%d %H:%i:%s'); # Date in the future --replace_regex $elide_costs --skip_if_hypergraph # Depends on the query plan. eval EXECUTE stmt USING @a, @b;