Column names in cursor.description contain unnecessary characters

[meih]

Driver version

2.0.876

Redshift version

Redshift 1.0.23412

Client Operating System

Mac OS 10.15.7

Python version

3.9.1

Table schema

bookname varchar
author‎ varchar

Problem description

1. Expected behaviour:
   When the table has columns bookname and author, column names in cursor.description are also available as bookname and author.

2. Actual behaviour:
   What I got from cursor.description were b'bookname' and b'author\xe2\x80\x8e' .

3. Error message/stack trace:
   No error message for this issue.

4. Any other details that can be helpful:
   See the reproduction code.

Python Driver trace logs

No trace for this issue.

Reproduction code

Code

# This is the same as the sample code on README
cursor: redshift_connector.Cursor = conn.cursor()
cursor.execute("create Temp table book(bookname varchar,author‎ varchar)")
cursor.executemany("insert into book (bookname, author‎) values (%s, %s)",
                    [
                      ('One Hundred Years of Solitude', 'Gabriel García Márquez'),
                      ('A Brief History of Time', 'Stephen Hawking')
                    ]
                  )
cursor.execute("select * from book")

result: tuple = cursor.fetchall()
print(result)

#  This is the piece of code that I added:
for field in cursor.description:
  print(field)

Output

(['A Brief History of Time', 'Stephen Hawking'], ['One Hundred Years of Solitude', 'Gabriel García Márquez'])
(b'bookname', 1043, None, None, None, None, None)
(b'author\xe2\x80\x8e', 1043, None, None, None, None, None)

[Brooke-white]

Hi @meih ,

Thanks for reaching out regarding this issue. The team will investigate this behavior and get back to you with our findings :)

[meih]

Great, thank you!

[meih]

@Brooke-white
Hi, is this reproduced on your end? I would like to know if this could be an issue on my end

[Brooke-white]

Hi @meih ,

Yes I have been able to consistently reproduce this issue on my end. From what I see, the second column name is always impacted, even when a table with more than two columns is used.

We are investigating this issue internally, I'll provide an update here with our findings.

The issue seems related to the parsing of the row description. redshift_connector's implementation depends on a null terminator byte occurring after the column name, which is present in the byte stream after these "extra" bytes \xe2\x80\x8e, causing them to be included in the column name.

[Brooke-white]

Hi @meih ,

I was playing around with this issue a bit and pasted the queries in question into the Redshift query editor, which showed there is a "hidden" unicode character \u200e present after author. This is the cause of this issue. I've included a screenshot below which highlights this character.

If you try executing the statements pasted below, which do not contain this character, you should see no issue:

 with redshift_connector.connect(**db_kwargs) as conn:
        cursor: redshift_connector.Cursor = conn.cursor()
        cursor.execute("create Temp table book(bookname varchar, author varchar)");
        cursor.executemany("insert into book(bookname, author) values (%s, %s)",
                           [
                               ('One Hundred Years of Solitude', 'Gabriel García Márquez'),
                               ('A Brief History of Time', 'Stephen Hawking')
                           ]
                           )
        cursor.execute("select * from book")
        result: tuple = cursor.fetchall()
        print(result)

        #  This is the piece of code that I added:
        for field in cursor.description:
            print(field)

(['A Brief History of Time', 'Stephen Hawking'], ['One Hundred Years of Solitude', 'Gabriel García Márquez'])
(b'bookname', 1043, None, None, None, None, None)
(b'author', 1043, None, None, None, None, None)

I will update the project README with the corrected queries.

[meih]

Hi @Brooke-white ,
Thanks for your investigation, it was nice to find out a code-specific issue and not a driver's.
And I've just figured out that the type of the column names was bytes and I needed to decode() them before using them as strings.

Problem solved. Thank you so much for your help!