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

[Andrey Cizov]

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.

[Andrey Cizov]

Code to reproduce

[Andrey Cizov]

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

[Mike Bayer]

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))

[Andrey Cizov]

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))

[Mike Bayer]

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:

9bc9d5c1068be878118202259add3c master

d8c17e2a6f41d0aad331678e91 0.8

2. 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)

a2cce1bf43552e699f2b master

70fdd3efa7a9a9a0fcf6ce 0.8

with these two changes your test case passes.

[Mike Bayer]

Removing milestone: 0.8.xx (automated comment)