SA 1.0.2 throwing TypeError: Boolean value of this clause is not defined

[Bill Schindler]

This error is being thrown on code that worked with 0.9.8. It seems to be checking a comparison on something, but I can't figure out which "this clause" the exception is referring to. Here's the stripped-down code leading up to the commit:

    ancient = utcnow() - timedelta(hours=8)

    ancient_conn = (

        (LiveSession.isconnected) &

        (LiveSession.connected < ancient))

    for conn in session.query(LiveSession).filter(ancient_conn):

        conn.isconnected = False

        conn.disconnected = func.now()

    session.commit()

I've tried it without the loop and it fails about 75% of the time with the same traceback. I'm also getting this exception on another ORM object that has a string column and four timestamp columns (and isn't updated in a loop). Test updating that object also gives me the same exception about 75% of the time on flush.

Neither ORM object has any relationships or anything other than straight column definitions.

          File "/opt/certwise-lcs/eggs/lcs.web.events-1.0.0-py2.7.egg/lcs/web/events/utility.py", line 296, in _dead

            session.commit()

          File "/opt/certwise-lcs/eggs/SQLAlchemy-1.0.2-py2.7-linux-x86_64.egg/sqlalchemy/orm/session.py", line 790, in commit

            self.transaction.commit()

          File "/opt/certwise-lcs/eggs/SQLAlchemy-1.0.2-py2.7-linux-x86_64.egg/sqlalchemy/orm/session.py", line 392, in commit

            self._prepare_impl()

          File "/opt/certwise-lcs/eggs/SQLAlchemy-1.0.2-py2.7-linux-x86_64.egg/sqlalchemy/orm/session.py", line 372, in _prepare_impl

            self.session.flush()

          File "/opt/certwise-lcs/eggs/SQLAlchemy-1.0.2-py2.7-linux-x86_64.egg/sqlalchemy/orm/session.py", line 2004, in flush

            self._flush(objects)

          File "/opt/certwise-lcs/eggs/SQLAlchemy-1.0.2-py2.7-linux-x86_64.egg/sqlalchemy/orm/session.py", line 2122, in _flush

            transaction.rollback(_capture_exception=True)

          File "/opt/certwise-lcs/eggs/SQLAlchemy-1.0.2-py2.7-linux-x86_64.egg/sqlalchemy/util/langhelpers.py", line 60, in __exit__

            compat.reraise(exc_type, exc_value, exc_tb)

          File "/opt/certwise-lcs/eggs/SQLAlchemy-1.0.2-py2.7-linux-x86_64.egg/sqlalchemy/orm/session.py", line 2086, in _flush

            flush_context.execute()

          File "/opt/certwise-lcs/eggs/SQLAlchemy-1.0.2-py2.7-linux-x86_64.egg/sqlalchemy/orm/unitofwork.py", line 373, in execute

            rec.execute(self)

          File "/opt/certwise-lcs/eggs/SQLAlchemy-1.0.2-py2.7-linux-x86_64.egg/sqlalchemy/orm/unitofwork.py", line 532, in execute

            uow

          File "/opt/certwise-lcs/eggs/SQLAlchemy-1.0.2-py2.7-linux-x86_64.egg/sqlalchemy/orm/persistence.py", line 170, in save_obj

            mapper, table, update)

          File "/opt/certwise-lcs/eggs/SQLAlchemy-1.0.2-py2.7-linux-x86_64.egg/sqlalchemy/orm/persistence.py", line 613, in _emit_update_statements

            lambda rec: (

          File "/opt/certwise-lcs/eggs/SQLAlchemy-1.0.2-py2.7-linux-x86_64.egg/sqlalchemy/orm/persistence.py", line 456, in _collect_update_commands

            value, state.committed_state[propkey]):

          File "/opt/certwise-lcs/eggs/SQLAlchemy-1.0.2-py2.7-linux-x86_64.egg/sqlalchemy/sql/elements.py", line 2726, in __bool__

            raise TypeError("Boolean value of this clause is not defined")

        exceptions.TypeError: Boolean value of this clause is not defined

--

Bill

[Mike Bayer]

this error is not related to the code illustrated above; it has to do with an object that is present in session.dirty which has some kind of SQL expression clause inside of its state, but also seems related to using an odd kind of comparison function within a custom type, likely a PickleType that is using a custom "comparator" function.

I can create this stack trace exactly.  But I have to do something really weird to make it happen.   It doesn't provide the failure in 0.9.9 so is a regression.  But super curious if you have something in your mapping that looks like this:

from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

def comparator(a, b):
    return a > b

class A(Base):
    __tablename__ = 'a'
    id = Column(Integer, primary_key=True)
    data = Column(PickleType(comparator=comparator))


e = create_engine("sqlite://", echo=True)
Base.metadata.create_all(e)

s = Session(e)
s.add(A(data='some data'))
s.commit()

a1 = s.query(A).first()
a1.data = func.foo("im a SQL expression")
s.commit()


are you using PickleType, with a custom comparator that doesn't come down to using "==", and are using a SQL expression as the value to be persisted?

--

Bill

--
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.
To post to this group, send email to sqlal...@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[Bill Schindler]

No, no PickleTypes. And my manual testing shouldn't have any other objects in session.dirty -- but I'll double-check that. The two classes with a custom comparator (which uses "==" in any case) is never seen by this module -- and they're never updated by any code in this server.

There's a session.commit() just prior to the code I posted, so the session should be clean, shouldn't it?

I'll poke at session.dirty and see if anything is lurking there.

[Bill Schindler]

Here's the contents of session.dirty just prior to the commit. It looks like what I expected, with no unexpected additional objects. Is there anything in there that looks like it could cause the exception?

session.dirty = IdentitySet([

    LiveSession(id=49, prodid=u'folio', userid=u'josh_saesan', sessionid=u'7c8a0b87-0c13-455f-8d0f-9cd90f4eff38', courseid=None, endpoint=u'tcp://192.168.5.32:21992', isconnected=<sqlalchemy.sql.elements.False_ object at 0x6aed050>, connected=datetime.datetime(2015, 4, 28, 16, 18, 9, 391405, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=-300, name=None)), disconnected=<sqlalchemy.sql.functions.now at 0x6aed1d0; now>),

    LiveSession(id=45, prodid=u'pmp1', userid=u'josh_saesan', sessionid=u'57047c05-26b1-4f46-b017-0be242c3fd11', courseid=20789, endpoint=u'tcp://192.168.5.32:21992', isconnected=<sqlalchemy.sql.elements.False_ object at 0x6a95d50>, connected=datetime.datetime(2015, 4, 28, 14, 45, 18, 974124, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=-300, name=None)), disconnected=<sqlalchemy.sql.functions.now at 0x6a95dd0; now>),

    LiveSession(id=48, prodid=u'pmp1', userid=u'josh_saesan', sessionid=u'd5ced200-58b4-41c1-9142-876ac1b83f70', courseid=20789, endpoint=u'tcp://192.168.5.32:21992', isconnected=<sqlalchemy.sql.elements.False_ object at 0x6a95ed0>, connected=datetime.datetime(2015, 4, 28, 15, 1, 47, 528904, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=-300, name=None)), disconnected=<sqlalchemy.sql.functions.now at 0x6aed090; now>),

    LiveSession(id=47, prodid=u'pmp1', userid=u'steve_drees', sessionid=u'be63ba27-4c50-47b4-bdae-6ccd0c7ec3df', courseid=None, endpoint=u'tcp://192.168.5.32:21992', isconnected=<sqlalchemy.sql.elements.False_ object at 0x6a95d90>, connected=datetime.datetime(2015, 4, 28, 14, 46, 4, 879577, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=-300, name=None)), disconnected=<sqlalchemy.sql.functions.now at 0x6a95f10; now>),

    LiveSession(id=46, prodid=u'folio', userid=u'bills', sessionid=u'15947462-0c01-4aa3-8ea2-3f05e095a80a', courseid=None, endpoint=u'tcp://192.168.5.32:21992', isconnected=<sqlalchemy.sql.elements.False_ object at 0x6aed190>, connected=datetime.datetime(2015, 4, 28, 16, 18, 47, 999584, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=-300, name=None)), disconnected=<sqlalchemy.sql.functions.now at 0x6aed310; now>)])

On Wednesday, April 29, 2015 at 6:39:12 AM UTC-7, Michael Bayer wrote:

[Mike Bayer]

On 4/29/15 11:00 AM, Bill Schindler wrote:

Here's the contents of session.dirty just prior to the commit. It looks like what I expected, with no unexpected additional objects. Is there anything in there that looks like it could cause the exception?

session.dirty = IdentitySet([

    LiveSession(id=49, prodid=u'folio', userid=u'josh_saesan', sessionid=u'7c8a0b87-0c13-455f-8d0f-9cd90f4eff38', courseid=None, endpoint=u'tcp://192.168.5.32:21992', isconnected=<sqlalchemy.sql.elements.False_ object at 0x6aed050>, connected=datetime.datetime(2015, 4, 28, 16, 18, 9, 391405, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=-300, name=None)), disconnected=<sqlalchemy.sql.functions.now at 0x6aed1d0; now>),

    LiveSession(id=45, prodid=u'pmp1', userid=u'josh_saesan', sessionid=u'57047c05-26b1-4f46-b017-0be242c3fd11', courseid=20789, endpoint=u'tcp://192.168.5.32:21992', isconnected=<sqlalchemy.sql.elements.False_ object at 0x6a95d50>, connected=datetime.datetime(2015, 4, 28, 14, 45, 18, 974124, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=-300, name=None)), disconnected=<sqlalchemy.sql.functions.now at 0x6a95dd0; now>),

    LiveSession(id=48, prodid=u'pmp1', userid=u'josh_saesan', sessionid=u'd5ced200-58b4-41c1-9142-876ac1b83f70', courseid=20789, endpoint=u'tcp://192.168.5.32:21992', isconnected=<sqlalchemy.sql.elements.False_ object at 0x6a95ed0>, connected=datetime.datetime(2015, 4, 28, 15, 1, 47, 528904, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=-300, name=None)), disconnected=<sqlalchemy.sql.functions.now at 0x6aed090; now>),

    LiveSession(id=47, prodid=u'pmp1', userid=u'steve_drees', sessionid=u'be63ba27-4c50-47b4-bdae-6ccd0c7ec3df', courseid=None, endpoint=u'tcp://192.168.5.32:21992', isconnected=<sqlalchemy.sql.elements.False_ object at 0x6a95d90>, connected=datetime.datetime(2015, 4, 28, 14, 46, 4, 879577, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=-300, name=None)), disconnected=<sqlalchemy.sql.functions.now at 0x6a95f10; now>),

    LiveSession(id=46, prodid=u'folio', userid=u'bills', sessionid=u'15947462-0c01-4aa3-8ea2-3f05e095a80a', courseid=None, endpoint=u'tcp://192.168.5.32:21992', isconnected=<sqlalchemy.sql.elements.False_ object at 0x6aed190>, connected=datetime.datetime(2015, 4, 28, 16, 18, 47, 999584, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=-300, name=None)), disconnected=<sqlalchemy.sql.functions.now at 0x6aed310; now>)])

yup, its the boolean + the SQL false expression

from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class A(Base):
    __tablename__ = 'a'
    id = Column(Integer, primary_key=True)

    data = Column(Boolean)

e = create_engine("sqlite://", echo=True)
Base.metadata.create_all(e)

s = Session(e)

s.add(A(data=None))
s.commit()

a1 = s.query(A).first()
a1.data = false()
s.commit()



i will commit the patch in the next few hours.

[Bill Schindler]

Not sure if your patch also handles the case when there's no boolean, but I'm also getting the exception with this in session.dirty:

IdentitySet([UserLoginTracking(id=37, userid='bills', lastseen=datetime.datetime(2015, 4, 29, 12, 57, 52, 648042, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=-300, name=None)), login_complete=<sqlalchemy.sql.functions.now at 0x6cd5f90; now>, tutorial_seen=None, prior_lastseen=None)])

This also throws the exception, just by slightly modifying your test on a boolean to use timestamp instead:

from sqlalchemy import *

from sqlalchemy.orm import *

from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class A(Base):

    __tablename__ = 'a'

    id = Column(Integer, primary_key=True)

    data = Column(DateTime(timezone=True))

e = create_engine("sqlite://", echo=True)

Base.metadata.create_all(e)

s = Session(e)

s.add(A(data=None))

s.commit()

a1 = s.query(A).first()

a1.data = func.now()

s.commit()

[Mike Bayer]

thanks for reporting, that is https://bitbucket.org/zzzeek/sqlalchemy/issue/3402/new-stack-trace-when-combining-non-eq#comment-17769865 and will be in 1.0.3, which hopefully I can release tomorrow.

[Mike Bayer]

On 4/29/15 2:09 PM, Bill Schindler wrote:

Not sure if your patch also handles the case when there's no boolean, but I'm also getting the exception with this in session.dirty:

IdentitySet([UserLoginTracking(id=37, userid='bills', lastseen=datetime.datetime(2015, 4, 29, 12, 57, 52, 648042, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=-300, name=None)), login_complete=<sqlalchemy.sql.functions.now at 0x6cd5f90; now>, tutorial_seen=None, prior_lastseen=None)])

This also throws the exception, just by slightly modifying your test on a boolean to use timestamp instead:

yup, it's all the same thing, works in master now thanks!