I am having an issue at work with a query that is passing in all of our unit tests against SQLite and PostgreSQL, but is failing in MySQL. Here is a short test case that will show the problem. Any help would be much appreciated!
#---------------------------------------------------------------------# from sqlalchemy import * metadata = MetaData() # # tables # p_table = Table('p', metadata, Column('id', Integer, primary_key=True), Column('foo', Unicode) ) v_table = Table('v', metadata, Column('id', Integer, primary_key=True), Column('p_id', Integer, ForeignKey('p.id')), Column('a_id', Integer, ForeignKey('a.id')), Column('bar', Unicode) ) a_table = Table('a', metadata, Column('id', Integer, primary_key=True), Column('baz', Unicode) ) # # test case # if __name__ == '__main__': metadata.connect('mysql://user:[EMAIL PROTECTED]/some_test_database') metadata.create_all() p_table.insert().execute(foo='foo1') a_table.insert().execute(baz='baz1') v_table.insert().execute(bar='bar1', p_id=1, a_id=1) p_table.insert().execute(foo='foo2') a_table.insert().execute(baz='baz2') v_table.insert().execute(bar='bar2', p_id=2, a_id=2) p_table.insert().execute(foo='foo3') a_table.insert().execute(baz='baz3') v_table.insert().execute(bar='bar3', p_id=3, a_id=3) query = select( [p_table.c.foo, v_table.c.bar, a_table.c.baz], and_( p_table.c.id==v_table.c.p_id, v_table.c.a_id==a_table.c.id, not_(p_table.c.id.in_( select( [p_table.c.id], and_( p_table.c.id==v_table.c.p_id, v_table.c.a_id==a_table.c.id, or_( p_table.c.foo=='foo2', v_table.c.bar=='bar3' ) ) ) )) ) ) try: for result in query.execute(): print result finally: metadata.drop_all() #---------------------------------------------------------------------# It appears to me that MySQL doesn't like it when you don't specify a FROM in the subselect, whereas PostgreSQL and SQLite don't care. -- Jonathan LaCour http://cleverdevil.org --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~----------~----~----~----~------~----~------~--~---