Skip to content

look into using inline=true for bindparam() inside of DDL sequences #2742

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
sqlalchemy-bot opened this issue Jun 5, 2013 · 8 comments
Closed

look into using inline=true for bindparam() inside of DDL sequences #2742

sqlalchemy-bot opened this issue Jun 5, 2013 · 8 comments
Labels
bug Something isn't working postgresql
Milestone
0.8.xx

Comments

@sqlalchemy-bot
Copy link
Collaborator

Migrated issue, originally created by Andrey Cizov (@andreycizov)

The bug is reproducible on 0.8.1 and the git master version (!version! == 0.9.0)

I am trying to add an index on an expression for PostgreSQL:

Index('heuristic', (1.) / (Foo.a + Foo.b))

The SQL generated by SQLA is correct: 2013-06-05 09:45:06,668 INFO sqlalchemy.engine.base.Engine CREATE INDEX heuristic ON foos (%(param_1)s / (a + b)), but param_1 is never passed to the query executor so the query fails.

Traceback:

Traceback (most recent call last):
  File "\sqla_bug\bugreport.py", line 24, in <module> 
    Base.metadata.create_all(engine)
  File "\sqla_bug\sqlalchemy\schema.py", line 2781,in create_all 
    tables=tables)
  File "\sqla_bug\sqlalchemy\engine\base.py", line 1475, in _run_visitor
    conn._run_visitor(visitorcallable, element, **kwargs)
  File "\sqla_bug\sqlalchemy\engine\base.py", line 1118, in _run_visitor
    **kwargs).traverse_single(element)
  File "\sqla_bug\sqlalchemy\sql\visitors.py", line 108, in traverse_single
    return meth(obj, **kw)
  File "\sqla_bug\sqlalchemy\engine\ddl.py", line 70, in visit_metadata
    self.traverse_single(table, create_ok=True)
  File "\sqla_bug\sqlalchemy\sql\visitors.py", line 108, in traverse_single
    return meth(obj, **kw)
  File "\sqla_bug\sqlalchemy\engine\ddl.py", line 93, in visit_table
    self.traverse_single(index)
  File "\sqla_bug\sqlalchemy\sql\visitors.py", line 108, in traverse_single
    return meth(obj, **kw)
  File "\sqla_bug\sqlalchemy\engine\ddl.py", line 105, in visit_index
    self.connection.execute(schema.CreateIndex(index))
  File "\sqla_bug\sqlalchemy\engine\base.py", line662, in execute
    params)
  File "\sqla_bug\sqlalchemy\engine\base.py", line720, in _execute_ddl
    compiled
  File "\sqla_bug\sqlalchemy\engine\base.py", line876, in _execute_context
    context)
  File "\sqla_bug\sqlalchemy\engine\base.py", line1023, in _handle_dbapi_exception
    util.reraise(*exc_info)
  File "\sqla_bug\sqlalchemy\engine\base.py", line869, in _execute_context
    context)
  File "\sqla_bug\sqlalchemy\engine\default.py", line 326, in do_execute
    cursor.execute(statement, parameters)
KeyError: 'param_1'

I've attached to code to reproduce the bug.

Attachments: bugreport.py
@sqlalchemy-bot
Copy link
Collaborator Author

Andrey Cizov (@andreycizov) wrote:

Code to reproduce

@sqlalchemy-bot
Copy link
Collaborator Author

Changes by Andrey Cizov (@andreycizov):

  • attached file bugreport.py

@sqlalchemy-bot
Copy link
Collaborator Author

Andrey Cizov (@andreycizov) wrote:

I have used the wrong Component for this ticket and I can't seem to be able to edit it, so please note that.

@sqlalchemy-bot
Copy link
Collaborator Author

Michael Bayer (@zzzeek) wrote:

well you can't use a bound parameter in an Index. The system here should tell the compiler to convert bound parameters to literals, so that can be fixed, but in the meantime just use text() or literal_column() (btw this index still returns an error for me on PG 9.1.4):

    Index('heuristic',
          (text("1")) / (Foo.a + Foo.b))

error:

ProgrammingError: (ProgrammingError) syntax error at or near "1"
LINE 1: CREATE INDEX heuristic ON foos (1 / (a + b))

@sqlalchemy-bot
Copy link
Collaborator Author

Changes by Michael Bayer (@zzzeek):

  • removed labels: cextensions
  • added labels: schema
  • set milestone to "0.8.xx"
  • changed title from "Base.metadata.create_all() fails to create express" to "look into using inline=true for bindparam() inside"

@sqlalchemy-bot
Copy link
Collaborator Author

Andrey Cizov (@andreycizov) wrote:

This works on PG 9.2.3:

CREATE INDEX heuristic ON foos ((1 / (a + b)))

That doesn't as well:

CREATE INDEX heuristic ON foos (1 / (a + b))

@sqlalchemy-bot
Copy link
Collaborator Author

Michael Bayer (@zzzeek) wrote:

two different issues:

  1. need to render literals inline, not bound - I applied this individually
    to the default compiler as well as to the compilers for postgresql, mssql,
    mysql even though I'm not sure if expressions are valid for those other two:

9bc9d5c master

d8c17e2 0.8

  1. Postgresql requires parenthesis around any SQL expression that isn't a straight
    column inside the list of cols in CREATE INDEX (http://www.postgresql.org/docs/9.1/static/sql-createindex.html)

a2cce1b master

70fdd3e 0.8

with these two changes your test case passes.

@sqlalchemy-bot
Copy link
Collaborator Author

Changes by Michael Bayer (@zzzeek):

  • removed labels: schema
  • added labels: postgres
  • changed status to closed

@sqlalchemy-bot sqlalchemy-bot added postgresql bug Something isn't working labels Nov 27, 2018
@sqlalchemy-bot sqlalchemy-bot added this to the 0.8.xx milestone Nov 27, 2018
This was referenced Nov 27, 2018
Closed
Closed
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 postgresql
Projects
None yet
Milestone
0.8.xx
Development

No branches or pull requests
1 participant
@sqlalchemy-bot