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:

Below are the diffs between actual and expected results:
-------------------------------------------------------
*** r/foo.result        Thu Jan 20 18:38:37 2005
--- r/foo.reject        Thu Jan 20 18:39:00 2005
*** 16,32 ****
  SELECT period FROM t1;
  period
  9410
! 0034
  affected rows: 2
  SELECT * FROM t1;
  Period	Varor_period
  9410	9412
! 0034	9999
  affected rows: 2
  SELECT t1.* FROM t1;
  Period	Varor_period
  9410	9412
! 0034	9999
  affected rows: 2
  SELECT * FROM t1 INNER JOIN t2 USING (Period);
  Period	Varor_period
--- 16,32 ----
  SELECT period FROM t1;
  period
  9410
! 0038
  affected rows: 2
  SELECT * FROM t1;
  Period	Varor_period
  9410	9412
! 0038	9999
  affected rows: 2
  SELECT t1.* FROM t1;
  Period	Varor_period
  9410	9412
! 0038	9999
  affected rows: 2
  SELECT * FROM t1 INNER JOIN t2 USING (Period);
  Period	Varor_period
-------------------------------------------------------

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