Skip to content

SQL-expression is not wrapped by brackets when 'type_coerce()' is used #5344

Closed
@theosotr

Description

@theosotr
Contributor

Describe the bug

It seems to me that sqlalchemy does not wrap an SQL expression with brackets when the function type_coerce() is used.

I have the following query

mulExpr = type_coerce((Model.a * Model.b), types.Float).label('mulExpr')
divExpr = (Model.c / mulExpr).label('divExpr')
session.query(divExpr, mulExpr).select_from(Model)

The code generates the following SQL query when I ran it on sqlite.

SELECT Model.c / Model.a * Model.b AS "divExpr", Model.a * Model.b AS "mulExpr"
FROM listing

However, I would expect the following query instead where the sub-expression Model.a * Model.b is wrapped by brackets.

SELECT Model.c / (Model.a * Model.b) AS "divExpr", Model.a * Model.b AS "mulExpr"
FROM listing

Note that this issue only occurs when the function type_coerce() is applied to the sub-expression.

Have a nice day!

Activity

CaselIT

CaselIT commented on May 21, 2020

@CaselIT
Member

Hi,
You can use self_group to ensure parenthesis are used.

added
questionissue where a "fix" on the SQLAlchemy side is unlikely, hence more of a usage question
and removed
requires triageNew issue that requires categorization
on May 21, 2020
zzzeek

zzzeek commented on May 21, 2020

@zzzeek
Member

self_group is likely what's broken here.

For the moment work around as:

from sqlalchemy.sql.elements import Grouping
expr = Grouping(type_coerce(...))
zzzeek

zzzeek commented on May 21, 2020

@zzzeek
Member

here's a test case that shows where this should be consistent:

from sqlalchemy import column, type_coerce, Integer, cast


a = column("a") / (column("b") * column("c"))
b = column("a") / cast(column("b") * column("c"), Integer)
c = column("a") / type_coerce(column("b") * column("c"), Integer)


print(a)
print(b)
print(c)

proposed fix, needs to defer to the inner expression for correct grouped behavior:

diff --git a/lib/sqlalchemy/sql/elements.py b/lib/sqlalchemy/sql/elements.py
index 43115f117..059b054d4 100644
--- a/lib/sqlalchemy/sql/elements.py
+++ b/lib/sqlalchemy/sql/elements.py
@@ -2893,6 +2893,12 @@ class TypeCoerce(WrapsColumnExpression, ColumnElement):
     def wrapped_column_expression(self):
         return self.clause
 
+    def self_group(self, against=None):
+        grouped = self.clause.self_group(against=against)
+        if grouped is not self.clause:
+            return TypeCoerce(grouped, self.type)
+        else:
+            return self
 
 class Extract(ColumnElement):
     """Represent a SQL EXTRACT clause, ``extract(field FROM expr)``."""

added
bugSomething isn't working
and removed
questionissue where a "fix" on the SQLAlchemy side is unlikely, hence more of a usage question
on May 21, 2020
added this to the 1.3.x milestone on May 21, 2020
CaselIT

CaselIT commented on May 21, 2020

@CaselIT
Member

I'll look into this, since the documentation could also be improved for type_coerce, since it's not super clear why one should use it

self-assigned this
on May 21, 2020
CaselIT

CaselIT commented on May 22, 2020

@CaselIT
Member

The documentation is not correct regarding the anon_1 label:

stmt = select([
                type_coerce(log_table.date_string, StringDateTime())
            ])

now renders

SELECT date_string AS date_string FROM log

not anon_1 as stated in the docs

Edit: only in master, 1_3 still has anon_1

CaselIT

CaselIT commented on May 22, 2020

@CaselIT
Member

Interestingly

stmt = select([
                type_coerce(log_table.date_string, StringDateTime())
                type_coerce(log_table.date_string, Time())
            ])

renders

SELECT date_string AS date_string, date_string AS date_string FROM log

but the processing happens correctly

zzzeek

zzzeek commented on May 22, 2020

@zzzeek
Member

yeah those anon_1 / label things change quite often, if you don't set an explicit label it's not defined what the format of an anonymous label will be so this is not critical

7 remaining items

Loading
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

Labels

bugSomething isn't workingsql

Type

No type

Projects

No projects

Relationships

None yet

    Development

    No branches or pull requests

      Participants

      @zzzeek@theosotr@sqla-tester@CaselIT

      Issue actions

        SQL-expression is not wrapped by brackets when 'type_coerce()' is used · Issue #5344 · sqlalchemy/sqlalchemy