It is best to write each test case so that the result it produces does not vary for each test run, or according to factors such as the time of day, differences in how program binaries are compiled, the operating system, and so forth.

For example, if the result contains the current date and time, the test engine has no way to verify that the result is correct.

However, sometimes a test result is inherently variable according to external factors, or perhaps there is a part of a result that you simply do not care about. mysqltest provides commands that enable you to postprocess test output into a more standard format so that output variation across test runs will not trigger a result mismatch.

One such command is replace_column, which specifies that you want to replace whatever is in a given column with a string. This makes the output for that column the same for each test run.

To see how this command works, add the following row after the first insert in the test case:

INSERT INTO t1 VALUES (DATE_FORMAT(NOW(), '%s'),9999);

Then record the test result and run the test again:

shell> ./mysql-test-run.pl --record foo
shell> ./mysql-test-run.pl foo

Most likely, a failure will occur and mysql-test-run.pl will display the difference between the expected result and what we actually got, like this (the header has been simplified):

CURRENT_TEST: main.foo
--- r/foo.result        2009-11-17 16:22:38
+++ var/log/foo.reject        2009-11-17 16:22:47
@@ -10,15 +10,15 @@
 SELECT period FROM t1;
 period
 9410
-0038
+0047
 SELECT * FROM t1;
 Period Varor_period
 9410   9412
-0038   9999
+0047   9999
 SELECT t1.* FROM t1;
 Period Varor_period
 9410   9412
-0038   9999
+0047   9999
 SELECT * FROM t1 INNER JOIN t2 USING (Period);
 Period Varor_period
 9410   9412

mysqltest: Result content mismatch

The actual numbers will likely be different for your case, and the format of the diff may also vary.

If we are not really interested in the first column, one way to eliminate this mismatch is by using the replace_column command. The duration of the effect of this command is the next SQL statement, so we need one before each select statement:

--replace_column 1 SECONDS
SELECT period FROM t1;

--replace_column 1 SECONDS
SELECT * FROM t1;

--replace_column 1 SECONDS
SELECT t1.* FROM t1;

In the replace_column commands, SECONDS could be any string. Its only purpose is to map variable output onto a constant value. If we record the test result again, we will succeed each time we run the test after that. The result file will look like this:

DROP TABLE IF EXISTS t1,t2;
CREATE TABLE t1 (
Period SMALLINT(4) UNSIGNED ZEROFILL DEFAULT '0000' NOT NULL,
Varor_period SMALLINT(4) UNSIGNED DEFAULT '0' NOT NULL
);
affected rows: 0
CREATE TABLE t2 (Period SMALLINT);
affected rows: 0
INSERT INTO t1 VALUES (9410,9412);
affected rows: 1
INSERT INTO t1 VALUES (DATE_FORMAT(NOW(), '%s'),9999);
affected rows: 1
INSERT INTO t2 VALUES (9410),(9411),(9412),(9413);
affected rows: 4
info: Records: 4  Duplicates: 0  Warnings: 0
SELECT period FROM t1;
period
SECONDS
SECONDS
affected rows: 2
SELECT * FROM t1;
Period  Varor_period
SECONDS 9412
SECONDS 9999
affected rows: 2
SELECT t1.* FROM t1;
Period  Varor_period
SECONDS 9412
SECONDS 9999
affected rows: 2
SELECT * FROM t1 INNER JOIN t2 USING (Period);
Period  Varor_period
9410    9412
affected rows: 1
DROP TABLE t1, t2;
affected rows: 0
ok