[ https://issues.apache.org/jira/browse/PHOENIX-6428?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Sandeep Pal updated PHOENIX-6428: --------------------------------- Description: Phoenix select subquery doesn't work on VARCHAR and CHAR types in two tables. Steps to reproduce: CREATE TABLE TEST.TABLE_WITH_VARCHAR (ID VARCHAR NOT NULL,CONSTRAINT PK PRIMARY KEY (ID)); CREATE TABLE TEST.TABLE_WITH_CHAR (ID CHAR(15) NOT NULL,CONSTRAINT PK PRIMARY KEY (ID)); UPSERT INTO TEST.TABLE_WITH_VARCHAR(ID) VALUES ('000000000000000'); UPSERT INTO TEST.TABLE_WITH_CHAR(ID) VALUES ('000000000000000'); select * from test.table_varchar as T join test.table_with_char as S on S.ID=T.ID; {code:java} 0: jdbc:phoenix:localhost:56632> CREATE TABLE TEST.TABLE_WITH_VARCHAR (ID VARCHAR NOT NULL,CONSTRAINT PK PRIMARY KEY (ID)); No rows affected (2.354 seconds) 0: jdbc:phoenix:localhost:56632> CREATE TABLE TEST.TABLE_WITH_CHAR (ID CHAR(15) NOT NULL,CONSTRAINT PK PRIMARY KEY (ID)); No rows affected (2.254 seconds) 0: jdbc:phoenix:localhost:56632> UPSERT INTO TEST.TABLE_WITH_VARCHAR(ID) VALUES ('000000000000000'); 1 row affected (0.04 seconds) 0: jdbc:phoenix:localhost:56632> UPSERT INTO TEST.TABLE_WITH_CHAR(ID) VALUES ('000000000000000'); 1 row affected (0.005 seconds) 0: jdbc:phoenix:localhost:56632> select * from test.table_with_varchar; +-----------------+ | ID | +-----------------+ | 000000000000000 | +-----------------+ 1 row selected (0.032 seconds) 0: jdbc:phoenix:localhost:56632> select * from test.table_with_char; +-----------------+ | ID | +-----------------+ | 000000000000000 | +-----------------+ 1 row selected (0.015 seconds) 0: jdbc:phoenix:localhost:56632> select * from test.table_with_varchar where id not in (select id from test.table_with_char); +----+ | ID | +----+ +----+{code} This returns empty. was: When doing the join on two tables on columns with VARCHAR and CHAR, the join condition doesn't work. Steps to reproduce: CREATE TABLE TEST.TABLE_WITH_VARCHAR (ID VARCHAR NOT NULL,CONSTRAINT PK PRIMARY KEY (ID,)); CREATE TABLE TEST.TABLE_WITH_CHAR (ID CHAR(15) NOT NULL,CONSTRAINT PK PRIMARY KEY (ID)); UPSERT INTO TEST.TABLE_WITH_VARCHAR(ID) VALUES ('000000000000000'); UPSERT INTO TEST.TABLE_WITH_CHAR(ID) VALUES ('000000000000000'); select * from test.table_varchar as T join test.table_with_char as S on S.ID=T.ID; This returns empty. > Phoenix select subquery doesn't work on VARCHAR and CHAR types > -------------------------------------------------------------- > > Key: PHOENIX-6428 > URL: https://issues.apache.org/jira/browse/PHOENIX-6428 > Project: Phoenix > Issue Type: Improvement > Affects Versions: 4.14.3, 4.16.0, 4.17.0 > Reporter: Sandeep Pal > Priority: Major > > Phoenix select subquery doesn't work on VARCHAR and CHAR types in two tables. > Steps to reproduce: > CREATE TABLE TEST.TABLE_WITH_VARCHAR (ID VARCHAR NOT NULL,CONSTRAINT PK > PRIMARY KEY (ID)); > CREATE TABLE TEST.TABLE_WITH_CHAR (ID CHAR(15) NOT NULL,CONSTRAINT PK PRIMARY > KEY (ID)); > > UPSERT INTO TEST.TABLE_WITH_VARCHAR(ID) VALUES ('000000000000000'); > UPSERT INTO TEST.TABLE_WITH_CHAR(ID) VALUES ('000000000000000'); > > select * from test.table_varchar as T join test.table_with_char as S on > S.ID=T.ID; > > {code:java} > 0: jdbc:phoenix:localhost:56632> CREATE TABLE TEST.TABLE_WITH_VARCHAR (ID > VARCHAR NOT NULL,CONSTRAINT PK PRIMARY KEY (ID)); > No rows affected (2.354 seconds) > 0: jdbc:phoenix:localhost:56632> CREATE TABLE TEST.TABLE_WITH_CHAR (ID > CHAR(15) NOT NULL,CONSTRAINT PK PRIMARY KEY (ID)); > No rows affected (2.254 seconds) > 0: jdbc:phoenix:localhost:56632> UPSERT INTO TEST.TABLE_WITH_VARCHAR(ID) > VALUES ('000000000000000'); > 1 row affected (0.04 seconds) > 0: jdbc:phoenix:localhost:56632> UPSERT INTO TEST.TABLE_WITH_CHAR(ID) VALUES > ('000000000000000'); > 1 row affected (0.005 seconds) > 0: jdbc:phoenix:localhost:56632> select * from test.table_with_varchar; > +-----------------+ > | ID | > +-----------------+ > | 000000000000000 | > +-----------------+ > 1 row selected (0.032 seconds) > 0: jdbc:phoenix:localhost:56632> select * from test.table_with_char; > +-----------------+ > | ID | > +-----------------+ > | 000000000000000 | > +-----------------+ > 1 row selected (0.015 seconds) > 0: jdbc:phoenix:localhost:56632> select * from test.table_with_varchar where > id not in (select id from test.table_with_char); > +----+ > | ID | > +----+ > +----+{code} > This returns empty. > -- This message was sent by Atlassian Jira (v8.3.4#803005)