[CALCITE-4449] Generate nicer SQL for Sarg 'x IS NULL OR x NOT IN (1, 2)'

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Fix Version/s: 1.27.0
• Assignee: Julian Hyde
• Reporter: Julian Hyde
• Created: 28/Dec/20 20:42
• Updated: 03/Jun/21 22:31
• Resolved: 06/Jan/21 00:06

Description

When converting the Sarg

x IS NULL OR x NOT IN (1, 2)

to SQL, Calcite currently generates

x IS NULL OR x < 1 OR x > 1 AND x < 2 OR x > 2

This is correct SQL, but is difficult to read unless you know that AND has higher precedence than OR. Either of the following would be better:

x IS NULL OR x <> 1 AND x <> 2

x IS NULL OR x NOT IN (1, 2)