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.
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.