Any way to suppress CASTs during Concrete Inheritance unions on Oracle ?

[Peter Lai]

I've implemented a Concrete inheritance model and Oracle 11g is balking on `CAST(NULL AS CLOB) as fieldn` during the pjoin union query execution with:

ORA-00932: inconsistent datatypes: expected - got CLOB

This happens when the unioned tables representing the 2 subclasses have different `Text` fields which are created as CLOBs on Oracle. This is independent of cxOracle version, since we get the same problem running the emitted query in native Oracle SQL Developer:

`select cast(null as clob) from dual`

ORA-00932: inconsistent datatypes: expected - got CLOB

00932. 00000 -  "inconsistent datatypes: expected %s got %s"

*Cause:    

*Action:

Error at Line: 1 Column: 8

The rest of the CASTs of NULL work fine, though, but generally NULL columns should have no datatype in Oracle via NULL definition. I am on SQLAlchemy 1.1.3. Does 1.2x handle this better? If yes, and I need to hotpatch, which lines would I need to patch?

[Mike Bayer]

just turn them off:

http://docs.sqlalchemy.org/en/rel_1_1/orm/mapping_api.html?highlight=polymorphic_union#sqlalchemy.orm.util.polymorphic_union.params.cast_nulls

cast_nulls¶ – if True, non-existent columns, which are represented as
labeled NULLs, will be passed into CAST. This is a legacy behavior
that is problematic on some backends such as Oracle - in which case it
can be set to False.




>
> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> To post example code, please provide an MCVE: Minimal, Complete, and
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full
> description.
> ---
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to sqlalchemy+...@googlegroups.com.
> To post to this group, send email to sqlal...@googlegroups.com.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.

[Peter Lai]

Cool! However, I used/ am using AbstractConcreteBase for this and apparently my inheritance-foo isn't good enough to avoid completely copy-pasting a whole bunch of code from sqlalchemy.ext.declarative.api just to override _create_polymorphic_union(). I tried to create a class that inherits from AbstractConcreteBase for a simple override and I got a bunch of `sqlalchemy.exc.ArgumentError: Mapper 'Mapper|FooBase|None' does not have a mapped_table specified.` errors:

Base = declarative_base()

class AbstractConcreteBase_ORA(AbstractConcreteBase):

    # override polymorphic union because of Oracle

    @classmethod

     def _create_polymorphic_union(cls, mappers):

        return polymorphic_union(OrderedDict(

            (mp.polymorphic_identity, mp.local_table)

            for mp in mappers

        ), 'type', 'pjoin', False)

class FooBase(AbstractConcreteBase_ORA, Base):

    common = Column(Integer, primary_key=True)

class Foo(FooBase, Base):

     noncommon = Column(Text)

    __mapper_args__ = {

        'polymorphic_identity': 'foo',

        'concrete': True

    }

class Bar(FooBase, Base):

    noncommon2 = Column(Text)

    __mapper_args__ = {

        'polymorphic_identity': 'bar',

        'concrete': True

    }

[Mike Bayer]

There is a bunch of brittle class logic happening in
AbstractConcreteBase that makes this more rigid when you add a class
into the hierarchy, but since your FooBase already has two classes,
you can just add one more which does not get in ABC's way:

Base = declarative_base()


class DontCastNull(object):

@classmethod
def _create_polymorphic_union(cls, mappers):
return polymorphic_union(OrderedDict(
(mp.polymorphic_identity, mp.local_table)
for mp in mappers

), 'type', 'pjoin', cast_nulls=False)


class Employee(DontCastNull, AbstractConcreteBase, Base):
pass


or put the method on your mapped class:

class Employee(AbstractConcreteBase, Base):

@classmethod
def _create_polymorphic_union(cls, mappers):
return polymorphic_union(OrderedDict(
(mp.polymorphic_identity, mp.local_table)
for mp in mappers

), 'type', 'pjoin', cast_nulls=False)


clearly we should make it easier for AbstractConcreteBase to pass in
the cast_nulls flag as well as anything else but it likely still
involve a class-level flag on the basemost mapped class or a mixin.
Getting AbstractConcreteBase to accept __abstract__ classes below it
should be fixed too but that's more complicated to make it work.

>
> Base = declarative_base()
>
> class AbstractConcreteBase_ORA(AbstractConcreteBase):
> # override polymorphic union because of Oracle
> @classmethod
> def _create_polymorphic_union(cls, mappers):
> return polymorphic_union(OrderedDict(
> (mp.polymorphic_identity, mp.local_table)
> for mp in mappers
> ), 'type', 'pjoin', False)
>
> class FooBase(AbstractConcreteBase_ORA, Base):
> common = Column(Integer, primary_key=True)
>
> class Foo(FooBase, Base):
>
> noncommon = Column(Text)
>
> __mapper_args__ = {
> 'polymorphic_identity': 'foo',
> 'concrete': True
> }
>
>
> class Bar(FooBase, Base):
> noncommon2 = Column(Text)
>
> __mapper_args__ = {
> 'polymorphic_identity': 'bar',
> 'concrete': True
> }
>