misleading documentation for query.exists()

[Leo Hemsted]

Using:

• Python 2.6.6
• sqlalchemy 0.9.7
• pyodbc 3.0.6
• Microsoft SQL Server 2008 (version 10.50.4000.0)

query.exists produces SELECT EXISTS (...) AS anon_1, that's bad syntax for SQL Server. SQL Server only accepts it as part of a where clause, ie the equivalent query would be SELECT 1 WHERE EXISTS (...)

import sqlalchemy
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import scoped_session, sessionmaker
from sqlalchemy import Column, Integer

engine = sqlalchemy.create_engine('mssql+pyodbc://*****:*****@*****/*****')
session = scoped_session(sessionmaker(bind=engine))

Base = declarative_base()

class User(Base):
    __tablename__ = 'User'
    id = Column('Id', Integer, primary_key=True)

session.query(session.query(User).exists()).scalar()

Traceback (most recent call last):
  File "test.py", line 15, in <module>
    session.query(session.query(User).exists()).scalar()
  File "c:\Python26\lib\site-packages\sqlalchemy\orm\query.py", line 2400, in scalar
    ret = self.one()
  File "c:\Python26\lib\site-packages\sqlalchemy\orm\query.py", line 2369, in one
    ret = list(self)
  File "c:\Python26\lib\site-packages\sqlalchemy\orm\query.py", line 2412, in __iter__
    return self._execute_and_instances(context)
  File "c:\Python26\lib\site-packages\sqlalchemy\orm\query.py", line 2427, in _execute_and_instances
    result = conn.execute(querycontext.statement, self._params)
  File "c:\Python26\lib\site-packages\sqlalchemy\engine\base.py", line 729, in execute
    return meth(self, multiparams, params)
  File "c:\Python26\lib\site-packages\sqlalchemy\sql\elements.py", line 321, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File "c:\Python26\lib\site-packages\sqlalchemy\engine\base.py", line 826, in _execute_clauseelement
    compiled_sql, distilled_params
  File "c:\Python26\lib\site-packages\sqlalchemy\engine\base.py", line 958, in _execute_context
    context)
  File "c:\Python26\lib\site-packages\sqlalchemy\engine\base.py", line 1160, in _handle_dbapi_exception
    exc_info
  File "c:\Python26\lib\site-packages\sqlalchemy\util\compat.py", line 199, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb)
  File "c:\Python26\lib\site-packages\sqlalchemy\engine\base.py", line 951, in _execute_context
    context)
  File "c:\Python26\lib\site-packages\sqlalchemy\engine\default.py", line 436, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.ProgrammingError: (ProgrammingError) ('42000', "[42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'EXISTS'. (156) (SQLExecDirectW); [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'AS'. (156)") u'SELECT EXISTS (SELECT 1 \nFROM [User]) AS anon_1' ()

[Mike Bayer]

There's not a solution to this I can see other than documentation.

If you have an exists() construct, you can either as you say put it into a WHERE clause:

q = sess.query(literal(1)).filter(sess.query(User).exists()).scalar()

or you can SELECT from it directly, which SQL server doesn't allow even if the expression has no name; as you say, it won't even allow "select exists (select 1 from foo)".

So given that I don't see why you'd want to say session.query(session.query(User).exists()).scalar() ? SQL server doesn't support that form. SQLAlchemy isn't in the business of designing the structure of SQL, you have to use the structure you want.

I'll add an alternate form to the docstring.

[Mike Bayer]

• clarify documentation on exists() that it is preferred to be in the WHERE clause. fixes #3212

→ <<cset 42837f4bca6a>>

[Mike Bayer]

• clarify documentation on exists() that it is preferred to be in the WHERE clause. fixes #3212

→ <<cset 98f457a5e2f8>>

[Mike Bayer]

this works fine:

from sqlalchemy import Column, Integer, create_engine, literal
from sqlalchemy.orm import Session
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()


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

e = create_engine("mssql+pyodbc://scott:tiger@ms_2008", echo=True)
Base.metadata.create_all(e)

s = Session(e)
s.add(A(id=5))

exists = s.query(A).exists()

print s.query(literal(True)).filter(exists).scalar()