MySQL Bugs: #112397: Inconsistent results caused by JOIN tables and CHAR function

Bug #112397	Inconsistent results caused by JOIN tables and CHAR function
Submitted:	20 Sep 2023 14:55	Modified:	25 Jul 2024 22:57
Reporter:	Zuming Jiang	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	8.0	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Dear MySQL developers,

I used my new fuzzer to fuzz MySQL and found a logic bug that make MySQL server output inconsistent results.

How to repeat:
*** Set up the database ***

create table t0 (vkey int);
create table t7 (vkey int);
insert into t0 (vkey) values (6);

*** Test Case 1 ***

select
    1 as c_0
  from
    (t0 as ref_5
      left outer join t7 as ref_8
      on (ref_5.vkey = ref_8.vkey))
  where CHAR(case when false then ref_8.vkey else ref_8.vkey end) not like 'YVR';

I changed "case when false then ref_8.vkey else ref_8.vkey end" to "ref_8.vkey", and got Test Case 2:

*** Test Case 2 ***

select
    1 as c_0
  from
    (t0 as ref_5
      left outer join t7 as ref_8
      on (ref_5.vkey = ref_8.vkey))
  where CHAR(ref_8.vkey) not like 'YVR';

*** Expected results ***

Test Case 1 and Test Case 2 return the same results.

*** Actual results ***

Test Case 1 and Test Case 2 return inconsistent results.

Test Case 1 return:

+-----+
| c_0 |
+-----+
|   1 |
+-----+
1 row in set (0.00 sec)

Test Case 2 return:

Empty set (0.00 sec)

*** Note ***

The bug can be reproduced in version all MySQL release versions (5.5 - 5.7, 8.0.0 - 8.0.34).

Hi Mr. Jiang,

Thank you for your bug report. 

However , this is not a bug.

There are rules on the simplifications of the queries or transformation of the same into the ones that are expected to return the same result. There are many logical errors in your queries, but one is crucial.

Using CHAR() conversion on the non-existent value returns no results what so ever. Any expression involving non-value as considered as an empty result. Empty result is not "" , nor NULL nor anything similar.

According to the SQL standard, this leads to the undefined behaviour.

Not a bug.

Thank you for checking this issue! I thought MySQL would set the "empty result" as NULL in default (I selected ref_8.vkey from the join table, and it was NULL), so I did not realize that it is an undefined behavior. Now I know it.

Thank you for your feedback and I will then refine the tool accordingly.

Best wishes,
Zuming

Mr Jiang,

You are truly welcome.

Hi Mr. Jiang,

This is just to inform you that we discovered that there is a problem with CHAR function, after all.

It is a bit different, then what you reported, but we are verifying your report, since it will also solve this problem as well.

Verified as reported.

Since only versions 8.0 and higher are maintained, we are verifying this bug for 8.0 and higher versions.

Thanking you for verifying it!

Documented fix as follows in the MySQL 9.0.0 changelog:

    A different result was obtained when a column reference argument
    to the CHAR() function was replaced with a CASE() expression
    that was essentially the same as the column reference. This took
    place when the CHAR() function was placed in the WHERE clause of
    an outer join, and the column reference was from the inner table
    of the outer join. An example of such a query is shown here:
    
SELECT 1 AS c_0
FROM t0 LEFT JOIN t1 ON t0.vkey = t1.vkey
WHERE CHAR(CASE WHEN FALSE THEN t1.vkey ELSE t1.vkey END) NOT LIKE 'X';

    A wrong value was obtained when the column reference was used
    directly; the CHAR() function in the WHERE clause was used to
    convert the outer join to an inner join, although this is
    correct only when NULL as the argument implies a NULL result,
    which is not true of CHAR(). The implementation detail that
    enforces this conversion is that the function's
    not_null_tables() property function returns the map bit of the
    table, but when the column reference was replaced with the CASE
    expression, the CASE did not propagate the not_null_tables()
    value and keep the outer join from being optimized improperly to
    an inner join.
    
    We fix this problem by setting the CHAR() function's
    null_on_null property to false instead of true, which ensures
    that not_null_tables() returns 0 rather than the table's map
    bit, so that that the outer join is not converted to an inner
    join.

Closed.

Thank you, Jon.