[CALCITE-5013] JDBC adapter should retain parentheses if a query in a UNION has a LIMIT clause

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Fix Version/s: 1.31.0
• Component/s: core
• Assignee: Unassigned
• Reporter: Jiajun Xie
• Created: 19/Feb/22 10:13
• Updated: 03/Aug/22 16:21
• Resolved: 23/Jun/22 23:17

Description

The JDBC adapter, when generating SQL, should retain parentheses if one of the inputs to a set operator (e.g. UNION) has an LIMIT clause.

In standard SQL, the operand of UNION should not have LIMIT or ORDER BY. So parse will fail in SqlParserTetest#testLimitUnion and SqlParserTetest#OrderUnion.

When users use parentheses, most engines allow it. See the discussion: CALCITE-1892.

For simple example, parentheses control the scope of the limit

select "product_id" from "product"
union all
(select "product_id" from "product" limit 10)

unparseBinarySyntax will miss parentheses, this change will affect semantics

SELECT \"product_id\" FROM \"foodmart\".\"product\" 
UNION ALL 
SELECT \"product_id\" FROM \"foodmart\".\"product\" 
FETCH NEXT 10 ROWS ONLY  -- Affect semantics

Issue Links

• is duplicated by: CALCITE-1892 Relbuilder .union(...) should use parentheses after .sortLimit(...) is called