postgresql dialect misinterprets non-column expressions in indexes

Issue #3174 resolved
Mike Bayer repo owner created an issue

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)

  1. Mike Bayer 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())
    
  2. Mike Bayer 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"]
    
  3. Mike Bayer 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

    → <<cset 79fa69f1f37f>>

  4. Mike Bayer 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>>

  5. Log in to comment