Bug #83256 date in multi-column IN clause with multi-values clause is parsed incorrectly
Submitted: 4 Oct 2016 16:17 Modified: 4 Oct 2016 16:48
Reporter: Navin Kumar Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Parser Severity:S3 (Non-critical)
Version:5.5/5.6/5.7 OS:Red Hat (rhel6)
Assigned to: CPU Architecture:Any
Tags: date, IN clause, inconsistency, multiple values, SELECT, SQL, tuple

[4 Oct 2016 16:17] Navin Kumar
Description:
The parsing for date with IN clauses is generally correct.

1. It is correct in the case where an expression is (date) IN (values...), and handles one or multiple values and handles int and varchar comparisons with date. E.g the following correctly succeeds:
a. select cast(20160901 as date) in (20160901, 20160902);

2. It is correct in the case where an expression is (date, other_val) in ((val1, other_val1)). E.g. the following all correctly succeed:
a. select (cast(20160901 as date), 123) in ((20160901, 123));
b. select (cast(20160901 as date), 123) in (('20160901', 123));
c. select (cast(20160901 as date), '123') in ((20160901, '123'));

3. It is correct in the case where an expression is (date, other_val) in ((val1, other_val1), (val2, other_val2), ...)  IF val1/val2/etc are Y-m-d formatted strings.  E.g. the following all correctly succeed:
a. select (cast(20160901 as date), 123) in (('20160901', 123), ('20160902', 123));
b. select (cast(20160901 as date), '123') in (('20160901', '123'), ('20160902', '123'));

4. It is INCORRECT in the case where an expression in (date, other_val) in ((val1, other_val1), (val2, other_val2), ...) IF val1/val2/etc are Ymd formatted integers.
a. select (cast(20160901 as date), 123) in ((20160901, 123), (20160902, 123));
b. select (cast(20160901 as date), '123') in ((20160901, '123'), (20160902, '123'));

How to repeat:
The following give incorrect results:

i.  select (cast(20160901 as date), 123) in ((20160901, 123), (20160902, 123));

ii. select (cast(20160901 as date), '123') in ((20160901, '123'), (20160902, '123'));

These two, (i) and (ii), both return "FALSE"

However, the following are correct:
iii. select (cast(20160901 as date), 123) in ((20160901, 123));

iv.  select (cast(20160901 as date), '123') in ((20160901, '123'));

These two, (iii) and (iv), both return "TRUE".  The only difference is that (i) and (ii) have an extra pair of values in the 'IN' clause to relax the constraint.  If (iii) and (iv) are TRUE, then (i) and (ii) must also be TRUE.

Suggested fix:
Properly handle (date, other_val) IN ((val1, other_val1), (val2, other_val2), ...) to behave consistently with (date, other_val) IN ((val1, other_val1)).  If the latter is TRUE, the former *must* also be TRUE.
[4 Oct 2016 16:48] Miguel Solorzano
Thank you for the bug report. Verified as described.