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
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:
./mysql-test-run.pl --record fooshell>
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 +++ r/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
--replace_column 1 SECONDS SELECT period FROM t1; --replace_column 1 SECONDS SELECT * FROM t1; --replace_column 1 SECONDS SELECT t1.* FROM t1;
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