db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Sergey Shelukhin <ser...@hortonworks.com>
Subject order of evaluation for filters in the query
Date Fri, 27 Sep 2013 23:52:47 GMT
Is it a bug that Derby seems to evaluate the cast in "where" before
evaluating the join conditions that would make the cast valid, and is there
any way to avoid that?

I have tables T, T2 and T3; all of them can be joined together by id, for
simplicity let's say one-to-one.
T2 stores an application-specific type name in a column.
T3.value is a varchar column; if T2.t3_type is integral, then these values
would also be integral (e.g. string "5").
I am trying to cast T3.value to decimal for integral values for some purpose

"select ... from T inner join T2 on T.id=T2.id and T2.t3_type = integral
inner join T3 on T2.id=T3.id where cast(T3.value as decimal(...)) > 5"

I get: "ERROR 22018: Invalid character string format for type DECIMAL."

When I rjust return all the T3.value-s to be tested ("select T3.value from
T inner join T2 on T.id=T2.id and T2.t3_type = integral inner join T3 on
T2.id=T3.id"), I get all number strings, no spaces or anything (like "3",
"11", etc.).
Just to make sure, for each value returned, I do select cast(T3.val as
decimal(...)) from T3 where T3.value = (that value as string) - they all
are returned, casting successfully.

Why, and what, does it fail to cast then? It appears that cast may be
attempted before joining that would filter it?
Could this be happening? This sounds like a bug to me.

NOTICE: This message is intended for the use of the individual or entity to 
which it is addressed and may contain information that is confidential, 
privileged and exempt from disclosure under applicable law. If the reader 
of this message is not the intended recipient, you are hereby notified that 
any printing, copying, dissemination, distribution, disclosure or 
forwarding of this communication is strictly prohibited. If you have 
received this communication in error, please contact the sender immediately 
and delete it from your system. Thank You.

View raw message