[CALCITE-4746] Pivots with pivotAgg without alias fail with Babel Parser Implementation

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 1.27.0
• Fix Version/s: 1.31.0
• Component/s: babel
• Labels: pull-request-available
• Assignee: Jiajun Xie
• Reporter: Toni Mueller
• Created: 18/Aug/21 12:02
• Updated: 03/Aug/22 16:21
• Resolved: 30/Jun/22 22:57

Description

The following works:

SELECT * FROM STUDENTS PIVOT(SUM(AGE) AS sum_age FOR PROFESSION IN ('BIOLOGY', 'CHEMISTRY'))

The following doesn't work:

SELECT * FROM STUDENTS PIVOT(SUM(AGE) FOR PROFESSION IN ('BIOLOGY', 'CHEMISTRY'))

With error:

Caused by: java.lang.RuntimeException: org.apache.calcite.sql.parser.SqlParseException: Encountered "PROFESSION" at line 1, column 43.
Was expecting one of:
    "FOR" ...
    "," ...
    
	at org.apache.calcite.sql.parser.SqlParseException.writeReplace(SqlParseException.java:171)
	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.base/java.lang.reflect.Method.invoke(Method.java:566)
	at java.base/java.io.ObjectStreamClass.invokeWriteReplace(ObjectStreamClass.java:1235)
	at java.base/java.io.ObjectOutputStream.writeObject0(ObjectOutputStream.java:1137)
	at java.base/java.io.ObjectOutputStream.writeObject(ObjectOutputStream.java:349)
	at org.gradle.internal.serialize.ExceptionPlaceholder.<init>(ExceptionPlaceholder.java:133)
	at org.gradle.internal.serialize.ExceptionPlaceholder.convertToExceptionPlaceholderList(ExceptionPlaceholder.java:158)
	at org.gradle.internal.serialize.ExceptionPlaceholder.<init>(ExceptionPlaceholder.java:141)
	at org.gradle.internal.serialize.TopLevelExceptionPlaceholder.<init>(TopLevelExceptionPlaceholder.java:28)
	at org.gradle.internal.serialize.ExceptionReplacingObjectOutputStream.doReplaceObject(ExceptionReplacingObjectOutputStream.java:67)
	at org.gradle.internal.serialize.ExceptionReplacingObjectOutputStream$1.transform(ExceptionReplacingObjectOutputStream.java:31)
	at org.gradle.internal.serialize.ExceptionReplacingObjectOutputStream.replaceObject(ExceptionReplacingObjectOutputStream.java:62)
	at java.base/java.io.ObjectOutputStream.writeObject0(ObjectOutputStream.java:1145)
	at java.base/java.io.ObjectOutputStream.writeObject(ObjectOutputStream.java:349)
	at org.gradle.internal.serialize.Message.send(Message.java:36)
	at org.gradle.internal.serialize.BaseSerializerFactory$ThrowableSerializer.write(BaseSerializerFactory.java:321)
	at org.gradle.internal.serialize.BaseSerializerFactory$ThrowableSerializer.write(BaseSerializerFactory.java:313)
	at org.gradle.internal.remote.internal.hub.DefaultMethodArgsSerializer$ArraySerializer.write(DefaultMethodArgsSerializer.java:80)
	at org.gradle.internal.remote.internal.hub.DefaultMethodArgsSerializer$ArraySerializer.write(DefaultMethodArgsSerializer.java:61)
	at org.gradle.internal.remote.internal.hub.MethodInvocationSerializer$MethodInvocationWriter.writeArgs(MethodInvocationSerializer.java:78)
	at org.gradle.internal.remote.internal.hub.MethodInvocationSerializer$MethodInvocationWriter.write(MethodInvocationSerializer.java:74)
	at org.gradle.internal.remote.internal.hub.MethodInvocationSerializer$MethodInvocationWriter.write(MethodInvocationSerializer.java:58)
	at org.gradle.internal.serialize.kryo.TypeSafeSerializer$2.write(TypeSafeSerializer.java:47)
	at org.gradle.internal.remote.internal.hub.InterHubMessageSerializer$MessageWriter.write(InterHubMessageSerializer.java:104)
	at org.gradle.internal.remote.internal.hub.InterHubMessageSerializer$MessageWriter.write(InterHubMessageSerializer.java:88)
	at org.gradle.internal.remote.internal.inet.SocketConnection.dispatch(SocketConnection.java:122)
	at org.gradle.internal.remote.internal.hub.MessageHub$ConnectionDispatch.run(MessageHub.java:328)

According to the Language Definition both should work:
https://calcite.apache.org/docs/reference.html

Issue Links

• links to: GitHub Pull Request #2842