.all_ condition doesn't act as expected, documentation is unclear

Issue #4093 resolved
Lars Wikman
created an issue

Unclear if this causes issues on other engines. Test case and my issue is postgres.

It does not match up with what I'd expect from PostgreSQL ALL: https://www.postgresql.org/docs/9.1/static/functions-subquery.html#FUNCTIONS-SUBQUERY-ALL

The code below throws a TypeError:

Traceback (most recent call last): File "all_repro.py", line 32, in <module> posts = session.query(Post).filter(Post.title.all_(['test', 'test2'])).all() TypeError: all_() takes exactly 1 argument (2 given)

I would expect it work similarly to .in_ where you can add a subquery and it will AND them rather than OR them.

This part of the docs does show that it doesn't take any arguments, which fits with the error: http://docs.sqlalchemy.org/en/latest/core/sqlelement.html#sqlalchemy.sql.operators.ColumnOperators.all_

but it links to a part of the docs where it takes an argument though I think they should be the same? http://docs.sqlalchemy.org/en/latest/core/sqlelement.html#sqlalchemy.sql.expression.all_

# Issue against: SQLAlchemy==1.2.0b2
# Using psycopg2: psycopg2==2.7.3.1

# It does not match up with what I'd expect from PostgreSQL ALL:
#   https://www.postgresql.org/docs/9.1/static/functions-subquery.html#FUNCTIONS-SUBQUERY-ALL

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
import sys

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column
from sqlalchemy import String
from sqlalchemy.orm import relationship

Base = declarative_base()

connection_string = sys.argv[1] # Just add it as an argument

class Post(Base):
    __tablename__ = 'post'

    uuid = Column(String(72), primary_key=True)
    title = Column(String(200), nullable=True)

engine = create_engine(connection_string)
Base.metadata.create_all(engine)
SessionClass = sessionmaker(bind=engine)

session = SessionClass()

posts = session.query(Post).filter(Post.title.all_(['test', 'test2'])).all()

# Throws a TyperError:
"""
Traceback (most recent call last):
  File "all_repro.py", line 32, in <module>
    posts = session.query(Post).filter(Post.title.all_(['test', 'test2'])).all()
TypeError: all_() takes exactly 1 argument (2 given)
"""

# I would expect it work similarly to .in_ where you can add a subquery

# This part of the docs does show that it doesn't take any arguments:
# http://docs.sqlalchemy.org/en/latest/core/sqlelement.html#sqlalchemy.sql.operators.ColumnOperators.all_
# but it links to a part of the docs where it takes and argument though I think they should be the same?
# http://docs.sqlalchemy.org/en/latest/core/sqlelement.html#sqlalchemy.sql.expression.all_

I hope this fits with the information you need. I couldn't find the issue previously reported.

Comments (8)

  1. Lars Wikman reporter

    Trying to use the ALL thing in PostgreSQL makes me think I've severely misunderstood how it is supposed to work. So it might turn out that I simply find the documentation confusing concerning all_().

  2. Michael Bayer repo owner

    here's how to use it like "IN":

    session = SessionClass()
    session.add_all([
        Post(title="test"),
        Post(title="test2"),
        Post(title="test3"),
    ])
    
    from sqlalchemy import any_, literal, ARRAY
    
    
    posts = session.query(Post).filter(
        Post.title == any_(literal(['test', 'test2'], ARRAY(String)))).all()
    print(posts)
    
  3. Michael Bayer repo owner

    that looks like...

    SELECT post.id AS post_id, post.title AS post_title 
    FROM post 
    WHERE post.title = ANY (%(param_1)s)
    2017-09-27 10:17:32,160 INFO sqlalchemy.engine.base.Engine {'param_1': ['test', 'test2']}
    
  4. Lars Wikman reporter

    Thanks a lot.

    I figured out that it is probably not what I wanted to do in the end for my use-case. And I ended up not needing it. But very pleased to see that the issue was real so I didn't waste your time with that :)

  5. Michael Bayer repo owner

    Support method form of any_(), all_()

    Fixed bug where the recently added :meth:.ColumnOperators.any_ and :meth:.ColumnOperators.all_ methods didn't work when called as methods, as opposed to using the standalone functions :func:~.expression.any_ and :func:~.expression.all_. Also added documentation examples for these relatively unintuitive SQL operators.

    Change-Id: I3e56b463e9fd146a077b9970624f50cba27f9811 Fixes: #4093

    → <<cset 944c662d8add>>

  6. Michael Bayer repo owner

    Support method form of any_(), all_()

    Fixed bug where the recently added :meth:.ColumnOperators.any_ and :meth:.ColumnOperators.all_ methods didn't work when called as methods, as opposed to using the standalone functions :func:~.expression.any_ and :func:~.expression.all_. Also added documentation examples for these relatively unintuitive SQL operators.

    Change-Id: I3e56b463e9fd146a077b9970624f50cba27f9811 Fixes: #4093 (cherry picked from commit 944c662d8add498577d6359251d4b94cd84d4011)

    → <<cset 3a8a8b9bc581>>

  7. Log in to comment