Query handling of CAST only caters for some situations, and doesn't always add a discriminator clause

[andyjefferson]

If we have something like
class Base (SINGLE_TABLE, with DISCRIMINATOR)
class Sub extends Base (SUPERCLASS_TABLE)

and we have a query like

SELECT b FROM Base b WHERE (TREAT(b) AS Sub).someField = value

then this currently ignore the TREAT (cast) whereas it should add a DISCRIMINATOR clause.

The problem is that we can only add the discriminator clause to a BooleanExpression ... i.e let it propagate back up to the
{...}.someField = value
and add the discriminator constraint there.

[andyjefferson]

See ObjectExpression.cast(). Near the end there is a block "if (castSqlTbl == table)". We need to allow ObjectExpression to have a BooleanExpression adding (when part of FILTER), and then when we evaluate a BooleanExpression from the ObjectExpression (e.g in ObjectExpression = value) we then add the BooleanExpression the ObjectExpression has attached.

[chrisco484]

It seems possible that this might fix the issue identified by the warning message discussed here:

https://groups.io/g/datanucleus/topic/9462787

There's another scenario we use regularly where we have:

class Base (SINGLE_TABLE, with DISCRIMINATOR)
class Sub extends Base (SUPERCLASS_TABLE)

class A
  Base baseRef

and we want to find all objects of type A where:

baseRef instanceof Sub && ((Sub)baseRef).someField = value

We have this scenario extremely frequently throughout our code base. The thing is that it seems to function correctly but it produces a LOG warning

Currently do not support adding restriction on discriminator for table='tableName'

and is possibly not performed optimally compared to if it added a discriminator clause?

[chrisco484]

then this currently ignore the TREAT (cast) whereas it should add a DISCRIMINATOR clause.

By not adding a discriminator clause to the query it could result in the RDBMS having to iterate through a lot more records in any given table - possibly making the query much slower.