is_ and isnot in conjunction with boolean values / MySQL
MySQL does not have native support for boolean types. Instead they are mimicked using tinyints. From the docs (http://dev.mysql.com/doc/refman/5.5/en/numeric-type-overview.html):
''BOOL, BOOLEAN
These types are synonyms for TINYINT(1). A value of zero is considered false. Nonzero values are considered true''
Probably wrong-footed by the integer type, SA converts Python boolean values into integers in queries. This works for filters based on ==, but not for filters using the is_ or isnot operators:
query = DBSession.query(Test).filter(Test.flag.isnot(False))
result = query.all()
yields the following error:
sqlalchemy.exc.ProgrammingError: (ProgrammingError) (1064, "You have an error in your SQL syntax; check the
manual that corresponds to your MySQL server version for the right syntax to use near '0' at line 3") 'SELECT test.id AS
test_id, test.flag AS test_flag \nFROM test \nWHERE test.flag IS NOT %s' (0,)
The same result is obtained from the MySQL prompt:
mysql> SELECT test.id AS test_id, test.flag AS test_flag FROM test WHERE test.flag IS NOT 0;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to
your MySQL server version for the right syntax to use near '0' at line 1
But using boolean syntax, the query passes:
mysql> SELECT test.id AS test_id, test.flag AS test_flag FROM test WHERE test.flag IS NOT False;
Empty set (0.00 sec)
Complete script to reproduce:
from sqlalchemy import Column, Integer, create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.types import Boolean
DeclarativeBase = declarative_base()
Session = sessionmaker()
class Test(DeclarativeBase):
__tablename__ = 'test'
id = Column('id', Integer, primary_key=True)
flag = Column('flag', Boolean, default=None)
def __repr__(self):
return "<Test({}, {})>".format(self.id, self.flag)
def configure_session(database_uri, echo=True):
engine = create_engine(database_uri, echo=True)
DeclarativeBase.metadata.create_all(engine)
Session.configure(bind=engine)
if __name__ == "__main__":
configure_session(...)
DBSession = Session()
DBSession.add_all([Test(flag=False), Test(flag=True](Test(),))
query = DBSession.query(Test).filter(Test.flag == True)
query = DBSession.query(Test).filter(Test.flag.is_(True))
query = DBSession.query(Test).filter(Test.flag.isnot(False))
r = query.all()
Comments (9)
-
Account Deleted -
repo owner - changed title to is_ and isnot in conjunction with boolean values / MySQL
- changed milestone to 0.8.xx
the gist I'm seeing here is that while MySQL doesn't have a boolean type, it does have "True"/ "False" constants ? If that's the case we can seek a solution using that.
-
Account Deleted The MySQL docs state that ''the values TRUE and FALSE are merely aliases for 1 and 0''. So it seems this is more a MySQL bug than a SA bug. It's this kind of, hum, 'feature', that makes the database universally loved among developers.
-
repo owner - changed status to resolved
-
repo owner see also 0a5737d9a85a28236a68080d699ef01ac7aed5ee where we added true/false conversion for oracle, SQL Server is also broken and that's coming up...
-
repo owner cfb0f9ca14f82c9ba06c4d5cbc992f821eb2234e for SQL server
-
repo owner - removed milestone
Removing milestone: 0.8.xx (automated comment)
-
Hi, looks like it is regression. I get same error in versions > 0.8.3. Just checked it on 0.9.7
Here is my MRE: https://gist.github.com/peterdemin/7afff3d66b657de3fb40
-
repo owner its something that was never expected to work previously. MySQL doesnt have a "boolean" type. but apparently it tries hard to pretend it does.
#3186is added. no fix for 0.9, it's too late in the game. - Log in to comment
SA version 0.7.10 MySQL version 5.5