[
https://issues.apache.org/jira/browse/PHOENIX-7491?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Vaibhav Joshi updated PHOENIX-7491:
-----------------------------------
Fix Version/s: 5.2.2
> 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
> Assignee: Vaibhav Joshi
> Priority: Major
> Fix For: 5.3.0, 5.2.2
>
>
> 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)
