Vaibhav Joshi created PHOENIX-7491:
--------------------------------------
Summary: Mixed-cased alias doesn't work in select statement of
“INNER JOIN”
Key: PHOENIX-7491
URL: https://issues.apache.org/jira/browse/PHOENIX-7491
Project: Phoenix
Issue Type: Bug
Components: phoenix
Reporter: Vaibhav Joshi
Mixed-cased alias doesn't work in select statement of “INNER JOIN”. Following
are the repro steps.
*1. Setup: Create tables and views.*
{code:java}
CREATE TABLE T1 (ID CHAR(256) PRIMARY KEY, "F".DUMMY_COLUMN VARCHAR);
CREATE VIEW V1(ROWKEY VARCHAR PRIMARY KEY,F.COL1 VARCHAR,F.COL2 VARCHAR,F.COL3
VARCHAR) AS SELECT * FROM T1;
CREATE TABLE T2 (ID CHAR(256) PRIMARY KEY, "F2".DUMMY_COLUMN VARCHAR);
CREATE VIEW V2(ROWKEY VARCHAR PRIMARY KEY,F2.COL1 VARCHAR,F2.COL2
VARCHAR,F2.COL3 VARCHAR) AS SELECT * FROM T2; {code}
*2. Execute query - Alias name without quotes, which are treated as UPPERCASE
internally*
{code:java}
SELECT MixedCaseAlias1."COL1" as "MixedCaseAlias1_COL1",
"MixedCaseAlias2"."COL2" as "MixedCaseAlias2_COL2", "MixedCaseAlias2"."COL3" as
"MixedCaseAlias2_COL3" FROM (SELECT "COL1", "COL2" FROM V1) AS MixedCaseAlias1
INNER JOIN V2 AS "MixedCaseAlias2" on (MixedCaseAlias1."COL1" =
"MixedCaseAlias2"."COL1") {code}
{+}Result{+}: Query is executed successfully
{code:java}
jdbc:phoenix:> SELECT MixedCaseAlias1."COL1" as "MixedCaseAlias1_COL1",
"MixedCaseAlias2"."COL2" as "MixedCaseAlias2_COL2", "MixedCaseAlias2"."COL3" as
"MixedCaseAlias2_COL3" FROM (SELECT "COL1", "COL2" FROM V1) AS MixedCaseAlias1
INNER JOIN V2 AS "MixedCaseAlias2" on (MixedCaseAlias1."COL1" =
"MixedCaseAlias2"."COL1");
+----------------------+----------------------+----------------------+
| MixedCaseAlias1_COL1 | MixedCaseAlias2_COL2 | MixedCaseAlias2_COL3 |
+----------------------+----------------------+----------------------+
+----------------------+----------------------+----------------------+
No rows selected (0.258 seconds)
0: jdbc:phoenix:> {code}
*3. Execute Problematic query - Alias name with quotes, which are not converted
to UPPERCASE*
{code:java}
SELECT "MixedCaseAlias1"."COL1" as "MixedCaseAlias1_COL1",
"MixedCaseAlias2"."COL2" as "MixedCaseAlias2_COL2", "MixedCaseAlias2"."COL3" as
"MixedCaseAlias2_COL3" FROM (SELECT "COL1", "COL2" FROM V1) AS
"MixedCaseAlias1" INNER JOIN V2 AS "MixedCaseAlias2" on
("MixedCaseAlias1"."COL1" = "MixedCaseAlias2"."COL1") {code}
{+}Result{+}: Got exception
{code:java}
jdbc:phoenix:> SELECT "MixedCaseAlias1"."COL1" as "MixedCaseAlias1_COL1",
"MixedCaseAlias2"."COL2" as "MixedCaseAlias2_COL2", "MixedCaseAlias2"."COL3" as
"MixedCaseAlias2_COL3" FROM (SELECT "COL1", "COL2" FROM V1) AS
"MixedCaseAlias1" INNER JOIN V2 AS "MixedCaseAlias2" on
("MixedCaseAlias1"."COL1" = "MixedCaseAlias2"."COL1")
. . . .semicolon> ;
java.lang.NullPointerException
at
org.apache.phoenix.compile.FromCompiler.getResolverForCompiledDerivedTable(FromCompiler.java:301)
at
org.apache.phoenix.compile.JoinCompiler$Table.createProjectedTable(JoinCompiler.java:1145)
at
org.apache.phoenix.compile.QueryCompiler.compileJoinQuery(QueryCompiler.java:348)
at
org.apache.phoenix.compile.QueryCompiler.compileJoinQuery(QueryCompiler.java:302)
at
org.apache.phoenix.compile.QueryCompiler.compileSelect(QueryCompiler.java:249)
at org.apache.phoenix.compile.QueryCompiler.compile(QueryCompiler.java:178)
at
org.apache.phoenix.jdbc.PhoenixStatement$ExecutableSelectStatement.compilePlan(PhoenixStatement.java:543)
at
org.apache.phoenix.jdbc.PhoenixStatement$ExecutableSelectStatement.compilePlan(PhoenixStatement.java:506)
at
org.apache.phoenix.jdbc.PhoenixStatement$1.call(PhoenixStatement.java:311)
at
org.apache.phoenix.jdbc.PhoenixStatement$1.call(PhoenixStatement.java:300)
at org.apache.phoenix.call.CallRunner.run(CallRunner.java:53)
at
org.apache.phoenix.jdbc.PhoenixStatement.executeQuery(PhoenixStatement.java:299)
at
org.apache.phoenix.jdbc.PhoenixStatement.executeQuery(PhoenixStatement.java:292)
at
org.apache.phoenix.jdbc.PhoenixStatement.execute(PhoenixStatement.java:2021)
at sqlline.Commands.executeSingleQuery(Commands.java:1054)
at sqlline.Commands.execute(Commands.java:1003)
at sqlline.Commands.sql(Commands.java:967)
at sqlline.SqlLine.dispatch(SqlLine.java:734)
at sqlline.SqlLine.begin(SqlLine.java:541)
at sqlline.SqlLine.start(SqlLine.java:267)
at sqlline.SqlLine.main(SqlLine.java:206) {code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
