postgresql dialect misinterprets non-column expressions in indexes

[Mike Bayer]

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.

[Mike Bayer]

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

[Mike Bayer]

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"]

[Mike Bayer]

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

[Mike Bayer]

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

[Mike Bayer]

Issue #3432 was marked as a duplicate of this issue.