[CALCITE-6115] Interval type specifier with zero fractional second precision does not pass validation

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 1.36.0
• Fix Version/s: 1.37.0
• Component/s: core
• Labels: pull-request-available
• Assignee: Leonid Chistov
• Reporter: Leonid Chistov
• Created: 13/Nov/23 10:26
• Updated: 06/May/24 22:19
• Resolved: 14/Dec/23 15:38

Description

Consider interval expression 

interval '1' second(1, 0) 

Calcite SQL validator considers it as not correct, since it uses following lower bound for fractional seconds precision:

public static final int MIN_INTERVAL_FRACTIONAL_SECOND_PRECISION = 1;

In order to reproduce this issue one can add following test cast to SqlValidatorTest.java:

@Test void testSecondIntervalExpression() {
  expr("interval '1' second(1, 0)").columnType("INTERVAL SECOND(1, 0) NOT NULL");
} 

and get an error:

Interval fractional second precision '0' out of range for INTERVAL SECOND(1, 0) 

However, SQL standard say:

An <interval fractional seconds precision>, if specified, shall be greater than or equal to 0 (zero)
and shall not be greater than the implementation-defined maximum. If SECOND is specified
and <interval fractional seconds precision> is not specified, then an <interval fractional seconds
precision> of 6 is implicit. 

Consequently, MIN_INTERVAL_FRACTIONAL_SECOND_PRECISION should be equal to 0 to make Calcite behavior consistent with SQL specification.

Issue Links

• links to: GitHub Pull Request #3563