- edited description
postgresql dialect misinterprets non-column expressions in indexes
Issue #3174
resolved
this works in general, but fails on at least the Postgresql dialect because it is linking each expression to a column. See https://bitbucket.org/zzzeek/alembic/issue/222/support-functional-indexes-with for how alembic works around this.
from sqlalchemy import *
from sqlalchemy.schema import CreateIndex
from sqlalchemy.dialects import postgresql
m = MetaData()
t = Table('x', m)
idx = Index('foo', text("lower(c)"))
idx._set_parent(t)
print CreateIndex(idx).compile(dialect=postgresql.dialect())
output:
CREATE INDEX foo ON x ()
Index should be a lot more forgiving for indexes that have non-column expressions in it, and we should also provide for a "table" keyword.
Comments (7)
-
reporter -
reporter this is a straight up bug and should be fixed in 0.9 as well. it is conflating index.expressions with index.columns:
from sqlalchemy import * from sqlalchemy.schema import CreateIndex from sqlalchemy.dialects import postgresql m = MetaData() t = Table('x', m, Column('q', Integer)) idx = Index('foo', text("lower(c)"), t.c.q) idx._set_parent(t) print CreateIndex(idx).compile() print CreateIndex(idx).compile(dialect=postgresql.dialect())
-
reporter this a bugfix, should backport to 0.9:
diff --git a/lib/sqlalchemy/dialects/postgresql/base.py b/lib/sqlalchemy/dialects/postgresql/base.py index fa9a2cf..0817fe8 100644 --- a/lib/sqlalchemy/dialects/postgresql/base.py +++ b/lib/sqlalchemy/dialects/postgresql/base.py @@ -1477,8 +1477,13 @@ class PGDDLCompiler(compiler.DDLCompiler): if not isinstance(expr, expression.ColumnClause) else expr, include_table=False, literal_binds=True) + - (c.key in ops and (' ' + ops[c.key]) or '') - for expr, c in zip(index.expressions, index.columns)]) + ( + (' ' + ops[expr.key]) + if hasattr(expr, 'key') + and expr.key in ops else '' + ) + for expr in index.expressions + ]) ) whereclause = index.dialect_options["postgresql"]["where"]
-
reporter - changed milestone to 0.9.9
- changed component to postgres
- changed title to postgresql dialect misinterprets non-column expressions in indexes
-
reporter - changed status to resolved
- Fixed bug where Postgresql dialect would fail to render an
expression in an :class:
.Index
that did not correspond directly to a table-bound column; typically when a :func:.text
construct was one of the expressions within the index; or could misinterpret the list of expressions if one or more of them were such an expression. fixes#3174
→ <<cset 79fa69f1f37f>>
-
reporter - Fixed bug where Postgresql dialect would fail to render an
expression in an :class:
.Index
that did not correspond directly to a table-bound column; typically when a :func:.text
construct was one of the expressions within the index; or could misinterpret the list of expressions if one or more of them were such an expression. fixes#3174
(cherry picked from commit 79fa69f1f37fdbc0dfec6bdea1e07f52bfe18f7b)
→ <<cset 63b2c466d12f>>
- Fixed bug where Postgresql dialect would fail to render an
expression in an :class:
-
reporter Issue
#3432was marked as a duplicate of this issue. - Log in to comment