Cast(NULL AS CLOB) fails on oracle, doesnt work w current polymorphic_union

[Former user]

I found an issue w/casting NULLs as CLOBs with the oracle compiler. Oracle does not support this operation, you get "ORA-00932: inconsistent datatypes: expected - got CLOB"

I made the following fix to get around the issue. I was seeing this behavior in the polymorphic join generator.

# FIX FOR CAST(NULL AS CLOB)
def visit_cast(self, cast, **kwargs):
    if cast.clause.type == sqltypes.NULLTYPE \
       and isinstance(cast.type,OracleText) :
        return "NULL"
    else :
        return "CAST(%s AS %s)" % \
               (self.process(cast.clause),self.process(cast.typeclause))

[Mike Bayer]

running tests against sqlite, pg, mysql, I see that this works:

diff -r 445afb56c14c71db90ace859a37404642226e49c lib/sqlalchemy/orm/util.py
--- a/lib/sqlalchemy/orm/util.py    Sat May 07 19:41:10 2011 -0400
+++ b/lib/sqlalchemy/orm/util.py    Mon May 09 15:58:19 2011 -0400
@@ -95,7 +95,7 @@
         try:
             return colnamemaps[table](table)[name](name)
         except KeyError:
-            return sql.cast(sql.null(), types[name](name)).label(name)
+            return sql.type_coerce(sql.null(), types[name](name)).label(name)

     result = [    for type, table in table_map.iteritems():

and even this works:

diff -r 445afb56c14c71db90ace859a37404642226e49c lib/sqlalchemy/orm/util.py
--- a/lib/sqlalchemy/orm/util.py    Sat May 07 19:41:10 2011 -0400
+++ b/lib/sqlalchemy/orm/util.py    Mon May 09 15:59:00 2011 -0400
@@ -95,7 +95,7 @@
         try:
             return colnamemaps[table](]
)[name](name)
         except KeyError:
-            return sql.cast(sql.null(), types[name](name)).label(name)
+            return sql.null().label(name)

     result = []
     for type, table in table_map.iteritems():

so need to figure out the rationale for the cast() in the first place. I'd like to just change polymorphic_union here instead.

[Mike Bayer]

here is the comment in the extremely old changeset that introduced the "cast":

then, polymorphic_union() function now CASTs null()s to the type corresponding to the columns in the UNION,
since postgres doesnt like mixing NULL with integer types

so need to poke around and see what that's about. was it an old version of PG, or do we just not have tests for integer polymorphic_on.

[Mike Bayer]

shrugs, these all work with psycopg2 and pg8000 too:

from sqlalchemy import *

e = create_engine('postgresql://scott:tiger@localhost/test', echo=True)

print e.execute("select * from (select null union select cast(5 as decimal)) as foo").fetchall()
print e.execute("select * from (select null union select cast(5 as int)) as foo").fetchall()
print e.execute("select * from (select null union select 5) as foo").fetchall()

[Mike Bayer]

i.e. the "decimal" cast gets picked up and we get Decimal("5") back, otherwise int, etc.

[Mike Bayer]

even as far back as 8.1 they document the behavior this way: http://www.postgresql.org/docs/8.1/static/typeconv-union-case.html.

I would propose use type_coerce(), make sure a test for poly union exists by itself using varchar, char, int, and text, run the test on s/p/m/f/o backends, we're done.

[Mike Bayer]

its too late for me to make a bigger change here so I've added a flag that turns of the CAST in 9826ba73a6a614a92be7104474428dde40c2d773.

[Mike Bayer]

Removing milestone: 0.7.0 (automated comment)