[Sqlalchemy-tickets] Issue #3079: select statement returning 0 rows generates: sqlalchemy.exc.ResourceClosedError: This result object does not return rows. It has been closed automatically. (zzzeek/sqlalchemy)

[mike_solomon <iss...@bi...>]

New issue 3079: select statement returning 0 rows generates: sqlalchemy.exc.ResourceClosedError: This result object does not return rows. It has been closed automatically.
https://bitbucket.org/zzzeek/sqlalchemy/issue/3079/select-statement-returning-0-rows

mike_solomon:

Below is code that generates what is, IMO, a bug.  To see the bug, comment out the line towards the end with the comment saying COMMENT ME OUT.  That line will result in the issuing of a select request that, given how I understand SQL and sqlalchemy, should generate 0 rows.  Instead, it causes a ResourceClosedError.  I can use this select statement, however, to be inserted into other tables (see http://stackoverflow.com/questions/24094857/subqueries-for-filters-in-joined-sqlalchemy-statements/24159702#24159702).  I think that the statement should return 0 rows instead of raising the error.

```
#!python

from sqlalchemy import Table, Column, String, Integer, MetaData, \
    select, func, ForeignKey, text
import sys
from functools import reduce

from sqlalchemy import create_engine
engine = create_engine('sqlite:///:memory:', echo=False)

metadata = MetaData()

linked_list = Table('linked_list', metadata,
    Column('id', Integer, primary_key = True),
    Column('at', Integer, nullable=False),
    Column('val', Integer, nullable=False),
    Column('next', Integer, ForeignKey('linked_list.at'))
)

refs = Table('refs', metadata,
    Column('id', Integer, primary_key = True),
    Column('ref', Integer, ForeignKey('linked_list.at')),
)

metadata.create_all(engine)
conn = engine.connect()

refs_al = refs.alias()

linked_list_m = select([
                    linked_list.c.at,
                    linked_list.c.val,
                    linked_list.c.next]).\
                    where(linked_list.c.at==refs_al.c.ref).\
                    cte(recursive=True)

llm_alias = linked_list_m.alias()
ll_alias = linked_list.alias()

linked_list_m = linked_list_m.union_all(
    select([
        llm_alias.c.at,
        ll_alias.c.val * llm_alias.c.val,
        ll_alias.c.next
    ]).
        where(ll_alias.c.at==llm_alias.c.next)
)


llm_alias_2 = linked_list_m.alias()

sub_statement = select([
            llm_alias_2.c.at,
            llm_alias_2.c.val]).\
        order_by(llm_alias_2.c.val.desc()).\
        limit(1)

def gen_statement(v) :
  return select([refs_al.c.ref, func.max(llm_alias_2.c.val)]).\
    select_from(
     refs_al.\
       join(llm_alias_2, onclause=refs_al.c.ref == llm_alias_2.c.at)).\
     group_by(refs_al.c.ref).where(llm_alias_2.c.val > v)

LISTS = [[2,4,4,11],[3,4,5,6]]

idx = 0
for LIST in LISTS :
  start = idx
  for x in range(len(LIST)) :
    ELT = LIST[x]
    conn.execute(linked_list.insert().\
      values(at=idx, val = ELT, next=idx+1 if x != len(LIST) - 1 else None))
    idx += 1
  conn.execute(refs.insert().values(ref=start))
  
print "LISTS:"
for LIST in LISTS :
  print "  ", LIST

def PRODUCT(L) : return reduce(lambda x,y : x*y, L, 1)
print "PRODUCTS OF LISTS:"
for LIST in LISTS :
  print "  ", PRODUCT(LIST)

for x in (345,355,365) :
  if x == 365 : continue # COMMENT ME OUT TO GET sqlalchemy.exc.ResourceClosedError
  statement_ = gen_statement(x)
  print "########"
  print "Lists that are greater than:", x
  conn.execute(statement_)
  allresults = conn.execute(statement_).fetchall()
  if len(allresults) == 0 :
    print "  /no results found/"
  else :
    for res in allresults :
      print res

print "########"
```