Uploaded image for project: 'Phoenix'
  1. Phoenix
  2. PHOENIX-3051

IN predicate cannot compare CHAR columns of differing length

    XMLWordPrintableJSON

    Details

    • Type: Bug
    • Status: Reopened
    • Priority: Minor
    • Resolution: Unresolved
    • Affects Version/s: 4.7.0
    • Fix Version/s: None
    • Labels:

      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  ')
      

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                Unassigned
                Reporter:
                kliew Kevin Liew
              • Votes:
                0 Vote for this issue
                Watchers:
                1 Start watching this issue

                Dates

                • Created:
                  Updated: