Using INTERVAL parameters for UDTF

[Patrick Angeles]

Wondering if anyone has seen this before, and has any suggestions. I have a UDTF with the following signature:

public void eval(LocalDateTime startTime, LocalDateTime endTime, Duration step) {

According to the docs, this should be mapped from the following SQL snippet:

... LATERAL TABLE func(t1, t2, INTERVAL '5' MINUTES)

However, when I run a query in sql-client I get:

Caused by: java.lang.UnsupportedOperationException: Unsupported type when convertTypeToSpec: INTERVAL_DAY_SECOND
        at org.apache.calcite.sql.type.SqlTypeUtil.convertTypeToSpec(SqlTypeUtil.java:1059) ~[flink-table-blink_2.11-1.12.0.jar:1.12.0]
        at org.apache.calcite.sql.type.SqlTypeUtil.convertTypeToSpec(SqlTypeUtil.java:1081) ~[flink-table-blink_2.11-1.12.0.jar:1.12.0]
        at org.apache.flink.table.planner.functions.inference.TypeInferenceOperandChecker.castTo(TypeInferenceOperandChecker.java:165) ~[flink-table-blink_2.11-1.12.0.jar:1.12.0]

       

I tried all sorts of DataTypeHints (including bridgedTo specification) to no avail. Any pointers?

Thanks in advance.

- P

[Patrick Angeles]

For some reason I can't get view the source so I don't have exact line numbers, but IntelliJ was kind enough to decompile this part (SqlTypeUtil.class) for me. This appears to be the exception I'm hitting.

if (!isAtomic(type) && !isNull(type)) {
    if (isCollection(type)) {
        typeNameSpec = new SqlCollectionTypeNameSpec(convertTypeToSpec(type.getComponentType()).getTypeNameSpec(), typeName, SqlParserPos.ZERO);
    } else {
        if (!isRow(type)) {
            throw new UnsupportedOperationException("Unsupported type when convertTypeToSpec: " + typeName);
        }

It got there because isAtomic returns false for interval types:

public static boolean isAtomic(RelDataType type) {
    SqlTypeName typeName = type.getSqlTypeName();
    if (typeName == null) {
        return false;
    } else {
        return isDatetime(type) || isNumeric(type) || isString(type) || isBoolean(type);
    }
}

Seems like a bug?

On Wed, Feb 17, 2021 at 5:55 PM Patrick Angeles <[hidden email]> wrote:

Wondering if anyone has seen this before, and has any suggestions. I have a UDTF with the following signature:

public void eval(LocalDateTime startTime, LocalDateTime endTime, Duration step) {

According to the docs, this should be mapped from the following SQL snippet:

... LATERAL TABLE func(t1, t2, INTERVAL '5' MINUTES)

However, when I run a query in sql-client I get:

Caused by: java.lang.UnsupportedOperationException: Unsupported type when convertTypeToSpec: INTERVAL_DAY_SECOND
        at org.apache.calcite.sql.type.SqlTypeUtil.convertTypeToSpec(SqlTypeUtil.java:1059) ~[flink-table-blink_2.11-1.12.0.jar:1.12.0]
        at org.apache.calcite.sql.type.SqlTypeUtil.convertTypeToSpec(SqlTypeUtil.java:1081) ~[flink-table-blink_2.11-1.12.0.jar:1.12.0]
        at org.apache.flink.table.planner.functions.inference.TypeInferenceOperandChecker.castTo(TypeInferenceOperandChecker.java:165) ~[flink-table-blink_2.11-1.12.0.jar:1.12.0]

       

I tried all sorts of DataTypeHints (including bridgedTo specification) to no avail. Any pointers?

Thanks in advance.

- P

[Patrick Angeles]

NVM. Found the actual source on Calcite trunk. Looks like interval type (and a few others) are not yet supported. 

https://github.com/apache/calcite/blob/master/core/src/main/java/org/apache/calcite/sql/type/SqlTypeUtil.java

On Wed, Feb 17, 2021 at 8:11 PM Patrick Angeles <[hidden email]> wrote:

For some reason I can't get view the source so I don't have exact line numbers, but IntelliJ was kind enough to decompile this part (SqlTypeUtil.class) for me. This appears to be the exception I'm hitting.

if (!isAtomic(type) && !isNull(type)) {
    if (isCollection(type)) {
        typeNameSpec = new SqlCollectionTypeNameSpec(convertTypeToSpec(type.getComponentType()).getTypeNameSpec(), typeName, SqlParserPos.ZERO);
    } else {
        if (!isRow(type)) {
            throw new UnsupportedOperationException("Unsupported type when convertTypeToSpec: " + typeName);
        }

It got there because isAtomic returns false for interval types:

public static boolean isAtomic(RelDataType type) {
    SqlTypeName typeName = type.getSqlTypeName();
    if (typeName == null) {
        return false;
    } else {
        return isDatetime(type) || isNumeric(type) || isString(type) || isBoolean(type);
    }
}

Seems like a bug?

On Wed, Feb 17, 2021 at 5:55 PM Patrick Angeles <[hidden email]> wrote:

Wondering if anyone has seen this before, and has any suggestions. I have a UDTF with the following signature:

public void eval(LocalDateTime startTime, LocalDateTime endTime, Duration step) {

According to the docs, this should be mapped from the following SQL snippet:

... LATERAL TABLE func(t1, t2, INTERVAL '5' MINUTES)

However, when I run a query in sql-client I get:

Caused by: java.lang.UnsupportedOperationException: Unsupported type when convertTypeToSpec: INTERVAL_DAY_SECOND
        at org.apache.calcite.sql.type.SqlTypeUtil.convertTypeToSpec(SqlTypeUtil.java:1059) ~[flink-table-blink_2.11-1.12.0.jar:1.12.0]
        at org.apache.calcite.sql.type.SqlTypeUtil.convertTypeToSpec(SqlTypeUtil.java:1081) ~[flink-table-blink_2.11-1.12.0.jar:1.12.0]
        at org.apache.flink.table.planner.functions.inference.TypeInferenceOperandChecker.castTo(TypeInferenceOperandChecker.java:165) ~[flink-table-blink_2.11-1.12.0.jar:1.12.0]

       

I tried all sorts of DataTypeHints (including bridgedTo specification) to no avail. Any pointers?

Thanks in advance.

- P

[Timo Walther]

Hi Patrick,

thanks for reaching out to us and investigating the problem. Could you
open an issue in the Calcite project? I think it would be nice to solve
it on both the Calcite and Flink side.

Thanks,
Timo


On 18.02.21 06:02, Patrick Angeles wrote:

> NVM. Found the actual source on Calcite trunk. Looks like interval type
> (and a few others) are not yet supported.
>
> https://github.com/apache/calcite/blob/master/core/src/main/java/org/apache/calcite/sql/type/SqlTypeUtil.java 
> <https://github.com/apache/calcite/blob/master/core/src/main/java/org/apache/calcite/sql/type/SqlTypeUtil.java>
>
>
>
> On Wed, Feb 17, 2021 at 8:11 PM Patrick Angeles
> <[hidden email] <mailto:[hidden email]>> wrote:
>
>     For some reason I can't get view the source so I don't have exact
>     line numbers, but IntelliJ was kind enough to decompile this part
>     (SqlTypeUtil.class) for me. This appears to be the exception I'm
>     hitting.
>
>         if (!isAtomic(type) && !isNull(type)) {
>              if (isCollection(type)) {
>                  typeNameSpec = new
>         SqlCollectionTypeNameSpec(convertTypeToSpec(type.getComponentType()).getTypeNameSpec(),
>         typeName, SqlParserPos.ZERO);
>              } else {
>                  if (!isRow(type)) {
>                      throw new
>         UnsupportedOperationException("Unsupported type when
>         convertTypeToSpec: " + typeName);
>                  }
>
>
>     It got there because isAtomic returns false for interval types:
>
>         public static boolean isAtomic(RelDataType type) {
>              SqlTypeName typeName = type.getSqlTypeName();
>              if (typeName == null) {
>                  return false;
>              } else {
>                  return isDatetime(type) || isNumeric(type) ||
>         isString(type) || isBoolean(type);
>              }
>         }
>
>
>     Seems like a bug?
>
>     On Wed, Feb 17, 2021 at 5:55 PM Patrick Angeles
>     <[hidden email] <mailto:[hidden email]>> wrote:
>
>         Wondering if anyone has seen this before, and has any
>         suggestions. I have a UDTF with the following signature:
>
>             public void eval(LocalDateTime startTime, LocalDateTime
>             endTime, Duration step) {
>
>
>         According to the docs, this should be mapped from the following
>         SQL snippet:
>
>             ... LATERAL TABLE func(t1, t2, INTERVAL '5' MINUTES)
>
>
>         However, when I run a query in sql-client I get:
>
>             Caused by: java.lang.UnsupportedOperationException:
>             Unsupported type when convertTypeToSpec: INTERVAL_DAY_SECOND
>                      at
>             org.apache.calcite.sql.type.SqlTypeUtil.convertTypeToSpec(SqlTypeUtil.java:1059)
>             ~[flink-table-blink_2.11-1.12.0.jar:1.12.0]
>                      at
>             org.apache.calcite.sql.type.SqlTypeUtil.convertTypeToSpec(SqlTypeUtil.java:1081)
>             ~[flink-table-blink_2.11-1.12.0.jar:1.12.0]
>                      at
>             org.apache.flink.table.planner.functions.inference.TypeInferenceOperandChecker.castTo(TypeInferenceOperandChecker.java:165)
>             ~[flink-table-blink_2.11-1.12.0.jar:1.12.0]
>
>
>         I tried all sorts of DataTypeHints (including bridgedTo
>         specification) to no avail. Any pointers?
>
>         Thanks in advance.
>
>         - P
>

[Patrick Angeles]

Opened an issue here:

https://issues.apache.org/jira/browse/CALCITE-4504

Cheers

On Thu, Feb 18, 2021 at 5:33 AM Timo Walther <[hidden email]> wrote:

Hi Patrick,

thanks for reaching out to us and investigating the problem. Could you
open an issue in the Calcite project? I think it would be nice to solve
it on both the Calcite and Flink side.

Thanks,
Timo


On 18.02.21 06:02, Patrick Angeles wrote:
> NVM. Found the actual source on Calcite trunk. Looks like interval type
> (and a few others) are not yet supported.
>
> https://github.com/apache/calcite/blob/master/core/src/main/java/org/apache/calcite/sql/type/SqlTypeUtil.java
> <https://github.com/apache/calcite/blob/master/core/src/main/java/org/apache/calcite/sql/type/SqlTypeUtil.java>
>
>
>
> On Wed, Feb 17, 2021 at 8:11 PM Patrick Angeles
> <[hidden email] <mailto:[hidden email]>> wrote:
>
>     For some reason I can't get view the source so I don't have exact
>     line numbers, but IntelliJ was kind enough to decompile this part
>     (SqlTypeUtil.class) for me. This appears to be the exception I'm
>     hitting.
>
>         if (!isAtomic(type) && !isNull(type)) {
>              if (isCollection(type)) {
>                  typeNameSpec = new
>         SqlCollectionTypeNameSpec(convertTypeToSpec(type.getComponentType()).getTypeNameSpec(),
>         typeName, SqlParserPos.ZERO);
>              } else {
>                  if (!isRow(type)) {
>                      throw new
>         UnsupportedOperationException("Unsupported type when
>         convertTypeToSpec: " + typeName);
>                  }
>
>
>     It got there because isAtomic returns false for interval types:
>
>         public static boolean isAtomic(RelDataType type) {
>              SqlTypeName typeName = type.getSqlTypeName();
>              if (typeName == null) {
>                  return false;
>              } else {
>                  return isDatetime(type) || isNumeric(type) ||
>         isString(type) || isBoolean(type);
>              }
>         }
>
>
>     Seems like a bug?
>
>     On Wed, Feb 17, 2021 at 5:55 PM Patrick Angeles
>     <[hidden email] <mailto:[hidden email]>> wrote:
>
>         Wondering if anyone has seen this before, and has any
>         suggestions. I have a UDTF with the following signature:
>
>             public void eval(LocalDateTime startTime, LocalDateTime
>             endTime, Duration step) {
>
>
>         According to the docs, this should be mapped from the following
>         SQL snippet:
>
>             ... LATERAL TABLE func(t1, t2, INTERVAL '5' MINUTES)
>
>
>         However, when I run a query in sql-client I get:
>
>             Caused by: java.lang.UnsupportedOperationException:
>             Unsupported type when convertTypeToSpec: INTERVAL_DAY_SECOND
>                      at
>             org.apache.calcite.sql.type.SqlTypeUtil.convertTypeToSpec(SqlTypeUtil.java:1059)
>             ~[flink-table-blink_2.11-1.12.0.jar:1.12.0]
>                      at
>             org.apache.calcite.sql.type.SqlTypeUtil.convertTypeToSpec(SqlTypeUtil.java:1081)
>             ~[flink-table-blink_2.11-1.12.0.jar:1.12.0]
>                      at
>             org.apache.flink.table.planner.functions.inference.TypeInferenceOperandChecker.castTo(TypeInferenceOperandChecker.java:165)
>             ~[flink-table-blink_2.11-1.12.0.jar:1.12.0]
>
>
>         I tried all sorts of DataTypeHints (including bridgedTo
>         specification) to no avail. Any pointers?
>
>         Thanks in advance.
>
>         - P
>