MySQL Bugs: #75402: set types are not translated correctly in the case of the empty set

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

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

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

Hello Mike Bayer,

Thank you for the report.

Thanks,
Umesh

Noted in 2.0.4 changelog.

Values of the SET data type were not translated correctly if empty.

Noted in 2.1.2 changelog.