[Sqlalchemy-tickets] Issue #3212: Bad SQL syntax for query.exists in mssql (zzzeek/sqlalchemy)

[Leo H. <iss...@bi...>]

New issue 3212: Bad SQL syntax for query.exists in mssql
https://bitbucket.org/zzzeek/sqlalchemy/issue/3212/bad-sql-syntax-for-queryexists-in-mssql

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 (...)`


```
#!python

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' ()
```