Bug #75402 set types are not translated correctly in the case of the empty set
Submitted: 4 Jan 2015 3:06 Modified: 17 Mar 2015 17:26
Reporter: Mike Bayer Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / Python Severity:S3 (Non-critical)
Version:2.0.2 OS:Any
Assigned to: CPU Architecture:Any

[4 Jan 2015 3:06] Mike Bayer
Description:
On line 526 of mysql/connector/conversion.py, we have:

    set_type = set(val.split(','))

This fails for the empty set, which per http://dev.mysql.com/doc/refman/5.0/en/set.html is documented as returned in string form as '':

    >>> set("A,B".split(','))
    set(['A', 'B'])   # works

    >>> set("".split(','))
    set([''])    # bzzt

How to repeat:
    from mysql import connector

    conn = connector.connect(
        user='scott', password='tiger', host='localhost', db='test')
    cursor = conn.cursor()
    cursor.execute("drop table if exists data")
    cursor.execute("create table data(set_data set('a', 'b'))")
    cursor.execute("insert into data (set_data) values ('a,b')")
    cursor.execute("insert into data (set_data) values ('')")
    cursor.execute("select set_data from data")

    result = cursor.fetchone()[0]
    assert result == set(['a', 'b']), result

    result = cursor.fetchone()[0]
    assert result == set([]), result

Suggested fix:
A fix here is to use an expression such as:

    set(re.findall(r'[^,]+', value))
[4 Jan 2015 3:14] Mike Bayer
here's a variant on that test, which illustrates the difference in MySQL between a set that contains the blank value '' as one of its values vs. the empty set; the integer version of this differentiates.  From a string perspective, there is no reliable way to differentiate on the return value side.   But I think the vast majority of use cases would expect '' to be the empty set, given that 'A', is set(['A']) and 'A,B' is set(['A', 'B']).   In SQLAlchemy I've added a "bitwise" flag that allows the type to be queried using the int, but this requires that the SQL be expressed differently (http://docs.sqlalchemy.org/en/latest/changelog/migration_10.html#dialect-improvements-and-...) so is not really an option for MySQL-connector.

from mysql import connector

conn = connector.connect(
    user='scott', password='tiger', host='localhost', db='test')
cursor = conn.cursor()
cursor.execute("drop table if exists data")
cursor.execute("create table data(set_data set('a', 'b', ''))")

cursor.execute("insert into data (set_data) values (',')")
cursor.execute("insert into data (set_data) values ('')")

cursor.execute("select set_data + 0 from data")
result = cursor.fetchone()[0]
assert result == 4

result = cursor.fetchone()[0]
assert result == 0

cursor.execute("select set_data from data")
result = cursor.fetchone()[0]
assert result == set([''])

result = cursor.fetchone()[0]
assert result == set(), result
[5 Jan 2015 7:29] MySQL Verification Team
Hello Mike Bayer,

Thank you for the report.

Thanks,
Umesh
[17 Mar 2015 17:26] Paul DuBois
Noted in 2.0.4 changelog.

Values of the SET data type were not translated correctly if empty.
[30 Mar 2015 15:54] Paul DuBois
Noted in 2.1.2 changelog.