Zhengqiang Duan created CALCITE-7229:
----------------------------------------
Summary: Support PostgreSQL ROW constructor alias reference from
LATERAL subquery
Key: CALCITE-7229
URL: https://issues.apache.org/jira/browse/CALCITE-7229
Project: Calcite
Issue Type: Improvement
Components: core
Reporter: Zhengqiang Duan
In https://github.com/apache/calcite/pull/4553, @dssysolyatin provided a SQL
example of a constructor alias reference in PostgreSQL.
{code:java}
SELECT e.empno,r.r
FROM emp AS e, LATERAL (SELECT ROW(e.empno, e.ename) AS r) AS r
WHERE ROW(e.empno, e.ename) IN (
r.r,
ROW(?, ?)
); {code}
I did some testing and found that this SQL statement cannot be executed in
Calcite. The following is the execution error:
{code:java}
java.lang.AssertionError at
org.apache.calcite.sql.validate.implicit.TypeCoercionImpl.inOperationCoercion(TypeCoercionImpl.java:571)
at
org.apache.calcite.sql.fun.SqlInOperator.deriveType(SqlInOperator.java:156) at
org.apache.calcite.sql.validate.SqlValidatorImpl$DeriveTypeVisitor.visit(SqlValidatorImpl.java:7094)
at
org.apache.calcite.sql.validate.SqlValidatorImpl$DeriveTypeVisitor.visit(SqlValidatorImpl.java:7081)
at org.apache.calcite.sql.SqlCall.accept(SqlCall.java:175) at
org.apache.calcite.sql.validate.SqlValidatorImpl.deriveTypeImpl(SqlValidatorImpl.java:1998)
at
org.apache.calcite.sql.validate.SqlValidatorImpl.deriveType(SqlValidatorImpl.java:1985)
at
org.apache.calcite.sql.validate.SqlValidatorImpl.validateWhereOrOn(SqlValidatorImpl.java:5068)
{code}
Calcite should support reference ROW constructor alias from LATERAL subquery to
improve support for PostgreSQL.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
