[PHOENIX-3051] IN predicate cannot compare CHAR columns of differing length

• Type: Bug
• Status: Reopened
• Priority: Minor
• Resolution: Unresolved
• Affects Version/s: 4.7.0
• Labels: char in sql
• Assignee: Unassigned
• Reporter: Kevin Liew
• Created: 06/Jul/16 18:57
• Updated: 07/Jul/16 17:57

Description

create table testchar1 (pk char(10) primary key)
create table testchar2 (pk char(20) primary key)

upsert into testchar1 values('a')
upsert into testchar2 values('a')

upsert into testchar1 values('b')
upsert into testchar2 values('b')

select testchar1.pk from testchar1 where testchar1.pk not in (select testchar2.pk from testchar2)

`testchar1` and `testchar2` contain the same data so the result of the select query should return an empty result set. Instead, all of the rows of `testchar1` are returned.

Note that the following queries will return an empty result set as expected:

select testchar1.pk from testchar1 where testchar1.pk not in ('a', 'b')
select testchar1.pk from testchar1 where testchar1.pk not in ('a  ', 'b  ')

Issue Links

• is related to: PHOENIX-3052 Cannot JOIN on comparing CHAR columns of differing length
• requires: PHOENIX-2767 Support columns for IN list