with_only_columns([func.count('*')]) works wrong in sqalchemy 0.7.3

[Former user]

query.with_only_columns(func.count('*'))) seems to produce invalid SQL since slqalchemy 0.7.3 (tested on fresh clone from hg too)

code to reproduce it(tested also on mysql - the same issue):

from sqlalchemy import ForeignKey, create_engine, select, func
from sqlalchemy.orm import sessionmaker, relationship
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, DateTime, Unicode


Base = declarative_base()
engine = create_engine('sqlite://', echo=False)
Session = sessionmaker(autoflush=False, bind=engine)

class Foo(Base):
    __tablename__ = 'foos'
    id = Column(Integer(), primary_key=True)
    xxx = Column(Unicode(10), nullable=False, default=u'xxx')

session = Session()
Base.metadata.create_all(engine)

session.add_all(Foo() for _ in xrange(30))
session.commit()

foos_table = Foo.__table__

query = select([foos_table](foos_table))

# doesn't work:/ (query: SELECT count(?) AS count_1)
count = query.with_only_columns([func.count('*')](func.count('*')))
# wrong result: 1
print '\tcount *: %i' % session.execute(count).scalar()

# work (query: SELECT count(foos.id) AS count_1 FROM foos)
count = query.with_only_columns([func.count(foos_table.c.id)](func.count(foos_table.c.id)))
# correct result: 30
print '\tcount id: %i' % session.execute(count).scalar()

test runs:

sqlalchemy 0.7.3

$ python bug_with_only_columns.py 
    count *: 1
    count id: 30

sqlalchemy 0.7.2

$ python bug_with_only_columns.py 
    count *: 30
    count id: 30

[Mike Bayer]

with_only_columns() has a serious documentation issue and also we have issue #2319.

the simple fact is that this:

select([x](x)).with_only_columns(y)

is intended to be equivalent to:

select([y](y))

The FROM list is derived from the columns clause. To reuse the "old" FROM list was issue #2261 which was fixed.

If you want to select FROM a table, you do this:

query = select().select_from(foos_table)

the situation is not totally ideal, in that with_only_columns() exists at all; in particular #2319 is a really thorny issue and I'd have preferred this method wasn't even introduced until these issues were understood. It works awkwardly with the "generative" paradigm since it removes state.

So from my perspective I'll likely close this on the next cycle, and/or convert to a documentation issue. its unfortunate 0.7.3 is backwards incompatible with 0.7.2 in this case but I'm not about to flip it back again; with_only_columns was originally only intended to receive a subset of table columns that were already in the original selectable. Maybe it might have been better as "remove_columns()".

[Mike Bayer]

this will be documentation, rolling into #2319