[jira] [Commented] (PHOENIX-6428) Phoenix select subquery doesn't work on VARCHAR and CHAR types

["Lars Hofhansl (Jira)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/PHOENIX-6428?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17309117#comment-17309117 ] 

Lars Hofhansl commented on PHOENIX-6428:
----------------------------------------

Huh? Isn't that what should happen? You're asking a NOT IN query. There is indeed no ID in table_with_varchar that is *not* in table_with_char.

This works fine as it should:
{code}
> select * from test.table_with_varchar where id in (select id from test.table_with_char)
+-----------------+
|       ID        |
+-----------------+
| 000000000000000 |
+-----------------+
1 row selected (0.138 seconds)
{code}




> 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_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: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)