Skip to content

figure out if type can be inferred for elements of IN #6222

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom
Closed
jcampbell opened this issue Apr 7, 2021 · 6 comments
Closed

figure out if type can be inferred for elements of IN #6222

jcampbell opened this issue Apr 7, 2021 · 6 comments
Labels
bug Something isn't working regression something worked and was broken by a change sql
Milestone
1.4.x

Comments

@jcampbell
Copy link

Describe the bug
When I compile a SqlAlchemy statement with an IN clause using 1.4, I am unable to compile an IN statement using literal_binds.

Expected behavior
I believe that I should be able to compile to a valid text SQL statement using literal_binds; I have observed this (excellent!) description of new behavior described in the recent changelog:
https://docs.sqlalchemy.org/en/14/changelog/migration_14.html#all-in-expressions-render-parameters-for-each-value-in-the-list-on-the-fly-e-g-expanding-parameters; however, it seems to explicitly suggest that this formulation should still work.

To Reproduce

eng = sa.create_engine("sqlite://")

eng.execute("CREATE TABLE demo (id INTEGER PRIMARY KEY);")
eng.execute("INSERT INTO demo (id) VALUES (1),(2),(3);")
stmt = sa.select("*").select_from(sa.table("demo", schema=None)).where(sa.column("id").in_([1,2])).limit(2)
res = stmt.compile(dialect = eng.dialect, compile_kwargs={"literal_binds": True})
print(res)

Error

Traceback (most recent call last):
  File "/Users/james/Library/Application Support/JetBrains/PyCharm2020.3/scratches/scratch_10.py", line 15, in <module>
    res = stmt.compile(dialect = eng.dialect, compile_kwargs={"literal_binds": True})
  File "/Users/james/miniconda3/envs/quagga/lib/python3.8/site-packages/sqlalchemy/sql/elements.py", line 506, in compile
    return self._compiler(dialect, **kw)
  File "/Users/james/miniconda3/envs/quagga/lib/python3.8/site-packages/sqlalchemy/sql/elements.py", line 570, in _compiler
    return dialect.statement_compiler(dialect, self, **kw)
  File "/Users/james/miniconda3/envs/quagga/lib/python3.8/site-packages/sqlalchemy/sql/compiler.py", line 766, in __init__
    Compiled.__init__(self, dialect, statement, **kwargs)
  File "/Users/james/miniconda3/envs/quagga/lib/python3.8/site-packages/sqlalchemy/sql/compiler.py", line 455, in __init__
    self.string = self.process(self.statement, **compile_kwargs)
  File "/Users/james/miniconda3/envs/quagga/lib/python3.8/site-packages/sqlalchemy/sql/compiler.py", line 490, in process
    return obj._compiler_dispatch(self, **kwargs)
  File "/Users/james/miniconda3/envs/quagga/lib/python3.8/site-packages/sqlalchemy/sql/visitors.py", line 81, in _compiler_dispatch
    return meth(self, **kw)
  File "/Users/james/miniconda3/envs/quagga/lib/python3.8/site-packages/sqlalchemy/sql/compiler.py", line 3086, in visit_select
    text = self._compose_select_body(
  File "/Users/james/miniconda3/envs/quagga/lib/python3.8/site-packages/sqlalchemy/sql/compiler.py", line 3239, in _compose_select_body
    t = self._generate_delimited_and_list(
  File "/Users/james/miniconda3/envs/quagga/lib/python3.8/site-packages/sqlalchemy/sql/compiler.py", line 1591, in _generate_delimited_and_list
    return clauses[0]._compiler_dispatch(self, **kw)
  File "/Users/james/miniconda3/envs/quagga/lib/python3.8/site-packages/sqlalchemy/sql/visitors.py", line 81, in _compiler_dispatch
    return meth(self, **kw)
  File "/Users/james/miniconda3/envs/quagga/lib/python3.8/site-packages/sqlalchemy/sql/compiler.py", line 2030, in visit_binary
    return self._generate_generic_binary(
  File "/Users/james/miniconda3/envs/quagga/lib/python3.8/site-packages/sqlalchemy/sql/compiler.py", line 2086, in _generate_generic_binary
    + binary.right._compiler_dispatch(
  File "/Users/james/miniconda3/envs/quagga/lib/python3.8/site-packages/sqlalchemy/sql/visitors.py", line 81, in _compiler_dispatch
    return meth(self, **kw)
  File "/Users/james/miniconda3/envs/quagga/lib/python3.8/site-packages/sqlalchemy/sql/compiler.py", line 2254, in visit_bindparam
    ret = self.render_literal_bindparam(
  File "/Users/james/miniconda3/envs/quagga/lib/python3.8/site-packages/sqlalchemy/sql/compiler.py", line 2345, in render_literal_bindparam
    to_update, replacement_expr = leep(bindparam, value)
  File "/Users/james/miniconda3/envs/quagga/lib/python3.8/site-packages/sqlalchemy/sql/compiler.py", line 1928, in _literal_execute_expanding_parameter_literal_binds
    replacement_expression = ", ".join(
  File "/Users/james/miniconda3/envs/quagga/lib/python3.8/site-packages/sqlalchemy/sql/compiler.py", line 1929, in <genexpr>
    self.render_literal_value(value, parameter.type)
  File "/Users/james/miniconda3/envs/quagga/lib/python3.8/site-packages/sqlalchemy/sql/compiler.py", line 2363, in render_literal_value
    return processor(value)
  File "/Users/james/miniconda3/envs/quagga/lib/python3.8/site-packages/sqlalchemy/sql/sqltypes.py", line 3135, in process
    raise exc.CompileError(
sqlalchemy.exc.CompileError: Don't know how to render literal SQL value: 1```

**Versions.**
 - OS: OSX (11.2.3)
 - Python: 3.8
 - SQLAlchemy: 1.4.5
 - Database: sqlite (in-memory)
 - DBAPI:

**Additional context**
This *does* work as expected in SqlAlchemy 1.3.x

**Have a nice day!**
@jcampbell jcampbell added the requires triage New issue that requires categorization label Apr 7, 2021
@zzzeek zzzeek added bug Something isn't working regression something worked and was broken by a change sql and removed requires triage New issue that requires categorization labels Apr 7, 2021
@zzzeek zzzeek added this to the 1.4.x milestone Apr 7, 2021
@zzzeek zzzeek changed the title literal_binds fails with list type in version >= 1.4 figure out if type can be inferred for elements of IN Apr 7, 2021
@zzzeek
Copy link
Member

zzzeek commented Apr 7, 2021

I don't have a fix for this one right now, as the mechanics of IN are very different. if you give your column a type then it will know these are integers:

stmt = (
    select("*")
    .select_from(table("demo", schema=None))
    .where(column("id", Integer).in_([1, 2]))
    .limit(2)
)

@jcampbell
Copy link
Author

Thanks for the quick response. In my case, I don't have independent information about the types of the columns, but I do have an extant engine. Could it be appropriate to use introspection as the mechanism for inferring types?

@zzzeek
Copy link
Member

zzzeek commented Apr 8, 2021

people typically use Table reflection when they have an engine because that will give you a structure that creates the most appropriate SQL given the type information, sure. The whole "table()/column()" thing is more for testing and one-offs.

@sqla-tester
Copy link
Collaborator

Mike Bayer has proposed a fix for this issue in the master branch:

Infer types in BindParameter when expanding=True https://gerrit.sqlalchemy.org/c/sqlalchemy/sqlalchemy/+/2726

@zzzeek
Copy link
Member

zzzeek commented Apr 8, 2021

the above patch fixes

@Shinnnyshinshin Shinnnyshinshin mentioned this issue Apr 8, 2021
Merged
9 tasks
@jcampbell
Copy link
Author

Thank you for the quick update!

@b0urb0n b0urb0n mentioned this issue Apr 28, 2021
Closed
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
bug Something isn't working regression something worked and was broken by a change sql
Projects
None yet
Milestone
1.4.x
Development

No branches or pull requests
3 participants
@zzzeek @jcampbell @sqla-tester