[
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
when column with type `CHAR(N)` as value lesser than N bytes. .
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 ('00000000000000');
UPSERT INTO TEST.TABLE_WITH_CHAR(ID) VALUES ('00000000000000');
select * from test.table_with_varchar where id not in (select id from
test.table_with_char);
{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:52919> UPSERT INTO TEST.TABLE_WITH_VARCHAR(ID) VALUES
('00000000000000');
1 row affected (0.041 seconds)
0: jdbc:phoenix:localhost:52919> UPSERT INTO TEST.TABLE_WITH_CHAR(ID) VALUES
('00000000000000');
1 row affected (0.004 seconds)
0: jdbc:phoenix:localhost:52919> select * from test.table_with_varchar where id
not in (select id from test.table_with_char);
+----------------+
| ID |
+----------------+
| 00000000000000 |
+----------------+
1 row selected (0.109 seconds)
0: jdbc:phoenix:localhost:52919> UPSERT INTO TEST.TABLE_WITH_CHAR(ID) VALUES
('ABC');
1 row affected (0.005 seconds)
0: jdbc:phoenix:localhost:52919> UPSERT INTO TEST.TABLE_WITH_VARCHAR(ID) VALUES
('ABC');
1 row affected (0.006 seconds)
0: jdbc:phoenix:localhost:52919> select * from test.table_with_varchar where id
not in (select id from test.table_with_char);
+----------------+
| ID |
+----------------+
| 00000000000000 |
| ABC |
+----------------+
2 rows selected (0.026 seconds)
0: jdbc:phoenix:localhost:52919> select * from test.table_with_char;
+----------------+
| ID |
+----------------+
| 00000000000000 |
| ABC |
+----------------+
2 rows selected (0.013 seconds)
0: jdbc:phoenix:localhost:52919> select * from test.table_with_varchar;
+----------------+
| ID |
+----------------+
| 00000000000000 |
| ABC |
+----------------+
2 rows selected (0.011 seconds)
0: jdbc:phoenix:localhost:52919> select * from test.table_with_varchar where id
in (select id from test.table_with_char);
+----+
| ID |
+----+
+----+{code}
This returns empty.
was:
Phoenix select subquery doesn't work on VARCHAR and CHAR types in two tables
when column with type CHAR(n) as value lesser than n bytes. .
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 ('00000000000000');
UPSERT INTO TEST.TABLE_WITH_CHAR(ID) VALUES ('00000000000000');
select * from test.table_with_varchar where id not in (select id from
test.table_with_char);
{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:52919> UPSERT INTO TEST.TABLE_WITH_VARCHAR(ID) VALUES
('00000000000000');
1 row affected (0.041 seconds)
0: jdbc:phoenix:localhost:52919> UPSERT INTO TEST.TABLE_WITH_CHAR(ID) VALUES
('00000000000000');
1 row affected (0.004 seconds)
0: jdbc:phoenix:localhost:52919> select * from test.table_with_varchar where id
not in (select id from test.table_with_char);
+----------------+
| ID |
+----------------+
| 00000000000000 |
+----------------+
1 row selected (0.109 seconds)
0: jdbc:phoenix:localhost:52919> UPSERT INTO TEST.TABLE_WITH_CHAR(ID) VALUES
('ABC');
1 row affected (0.005 seconds)
0: jdbc:phoenix:localhost:52919> UPSERT INTO TEST.TABLE_WITH_VARCHAR(ID) VALUES
('ABC');
1 row affected (0.006 seconds)
0: jdbc:phoenix:localhost:52919> select * from test.table_with_varchar where id
not in (select id from test.table_with_char);
+----------------+
| ID |
+----------------+
| 00000000000000 |
| ABC |
+----------------+
2 rows selected (0.026 seconds)
0: jdbc:phoenix:localhost:52919> select * from test.table_with_char;
+----------------+
| ID |
+----------------+
| 00000000000000 |
| ABC |
+----------------+
2 rows selected (0.013 seconds)
0: jdbc:phoenix:localhost:52919> select * from test.table_with_varchar;
+----------------+
| ID |
+----------------+
| 00000000000000 |
| ABC |
+----------------+
2 rows selected (0.011 seconds)
0: jdbc:phoenix:localhost:52919> select * from test.table_with_varchar where id
in (select id from test.table_with_char);
+----+
| ID |
+----+
+----+{code}
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
> when column with type `CHAR(N)` as value lesser than N bytes. .
> 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 ('00000000000000');
> UPSERT INTO TEST.TABLE_WITH_CHAR(ID) VALUES ('00000000000000');
>
> select * from test.table_with_varchar where id not in (select id from
> test.table_with_char);
>
> {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:52919> UPSERT INTO TEST.TABLE_WITH_VARCHAR(ID)
> VALUES ('00000000000000');
> 1 row affected (0.041 seconds)
> 0: jdbc:phoenix:localhost:52919> UPSERT INTO TEST.TABLE_WITH_CHAR(ID) VALUES
> ('00000000000000');
> 1 row affected (0.004 seconds)
> 0: jdbc:phoenix:localhost:52919> select * from test.table_with_varchar where
> id not in (select id from test.table_with_char);
> +----------------+
> | ID |
> +----------------+
> | 00000000000000 |
> +----------------+
> 1 row selected (0.109 seconds)
> 0: jdbc:phoenix:localhost:52919> UPSERT INTO TEST.TABLE_WITH_CHAR(ID) VALUES
> ('ABC');
> 1 row affected (0.005 seconds)
> 0: jdbc:phoenix:localhost:52919> UPSERT INTO TEST.TABLE_WITH_VARCHAR(ID)
> VALUES ('ABC');
> 1 row affected (0.006 seconds)
> 0: jdbc:phoenix:localhost:52919> select * from test.table_with_varchar where
> id not in (select id from test.table_with_char);
> +----------------+
> | ID |
> +----------------+
> | 00000000000000 |
> | ABC |
> +----------------+
> 2 rows selected (0.026 seconds)
> 0: jdbc:phoenix:localhost:52919> select * from test.table_with_char;
> +----------------+
> | ID |
> +----------------+
> | 00000000000000 |
> | ABC |
> +----------------+
> 2 rows selected (0.013 seconds)
> 0: jdbc:phoenix:localhost:52919> select * from test.table_with_varchar;
> +----------------+
> | ID |
> +----------------+
> | 00000000000000 |
> | ABC |
> +----------------+
> 2 rows selected (0.011 seconds)
> 0: jdbc:phoenix:localhost:52919> select * from test.table_with_varchar where
> id in (select id from test.table_with_char);
> +----+
> | ID |
> +----+
> +----+{code}
> This returns empty.
>
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
