randgen team mailing list archive

[John H Embretsen <john.embretsen@xxxxxxxxxx>]

Hi,


It is probably known to those of you using the Transformer validator 
that some query transforms are not comparable to the original in every 
case. One such case is the ExecuteAsUnion transforms when using 
TRANSFORM_OUTCOME_DISTINCT against queries using varchar columns.

With this transform, results from the original query (e.g. SELECT 
`col_varchar_10_latin1`  FROM L) are compared to results of transformed 
queries using UNION. For example,

            (  SELECT `col_varchar_10_latin1`  FROM L    )
  UNION ALL (  SELECT `col_varchar_10_latin1`  FROM L    )
  /* TRANSFORM_OUTCOME_SUPERSET */;

where the /* TRANFORM_OUTCOME_SUPERSET */ signals to the validator that 
results are OK if results from the transformed query is a superset of 
the original query. This is all well.

On the other hand, one other type of transformed UNION query is:

        (  SELECT `col_varchar_10_latin1`  FROM L    )
  UNION (  SELECT `col_varchar_10_latin1`  FROM L    )
  /* TRANSFORM_OUTCOME_DISTINCT */;

where the /* TRANFORM_OUTCOME_DISTINCT */ signals to the validator that 
results are OK if results from the transformed query after removing 
duplicates is equal to the results of the original query.

This works OK in most cases, but when dealing with text values in a 
column with a case-insensitive collation (default in MySQL) the 
validator reports false positives. This is because the validator's idea 
of "DISTINCT" is not equal to the the database server's idea of 
DISTINCT. The issue is visible with transforms such as "ExecuteAsUnion" 
and "Distinct".

I see this for example when running the RQG against MySQL with 
--validator=Transform, --grammar=conf/optimizer/range_access.yy and 
--gendata=conf/optimizer/range_access.zz.

I am interested in your opinions on what is the best way to deal with 
this, especially if you too are affected by this. For example:

a) Implement permanent support for case insensitive duplicate removal 
(if possible) in the Transform validator's DISTINCT checking?

b) Automatically skip (return STATUS_WONT_HANDLE) this transform 
altogether if columns in query are varchar or char?

c) Implement temporary hacks to disable e.g. the ExecuteAsUnion 
transform in the affected test runs?



To make the issue more clear, consider the following MySQL table:

CREATE TABLE t9 (a VARCHAR(10), b VARCHAR(10) COLLATE latin1_bin);

-- Here, column a has default collation (|latin1_swedish_ci|), while 
column b has case sensitive collation (latin1_bin).
-- Let's insert some data:

insert into t9 values ('hello', 'hello');
insert into t9 values ('v', 'v');
insert into t9 values ('i', 'i');
insert into t9 values ('I', 'I');
insert into t9 values ('m', 'm');

-- Mimicking the Transform validator, we execute first the original 
query and then the transformed query:

mysql> SELECT * FROM t9;
+-------+-------+
| a     | b     |
+-------+-------+
| hello | hello |
| v     | v     |
| i     | i     |
| I     | I     |
| m     | m     |
+-------+-------+
5 rows in set (0.00 sec)

-- Using the column with default collation, we get:

mysql> (SELECT a FROM t9) UNION (SELECT a FROM t9);
+-------+
| a     |
+-------+
| hello |
| v     |
| i     |
| m     |
+-------+
4 rows in set (0.00 sec)

-- The validator thinks this is a bad result, because the value "I" is 
missing.
-- However, the result is correct, since duplicate rows were removed 
with case insensitivity in mind.

-- Using the column with the case sensitive collation:

mysql> (SELECT b FROM t9) UNION (SELECT b FROM t9);
+-------+
| b     |
+-------+
| hello |
| v     |
| i     |
| I     |
| m     |
+-------+
5 rows in set (0.00 sec)

-- The validator agrees this is an OK result, since it is equal to the 
original.
-- The result is correct, since this was a column with case sensitive 
collation (no duplicates).


The problem here is that although the validator's isDistinct() check 
tries to remove duplicates from the result before comparing, it does 
this in a case sensitive manner ("i" is not the same as "I"). The UNION 
operator of MySQL, however, regards "i" the same as "I" and removes one 
of them from the result, as long as the column has default collation.

Let me know if you have suggestions or opinions.


--
John