db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Rick Hillegas <rick.hille...@oracle.com>
Subject Re: order of evaluation for filters in the query
Date Mon, 30 Sep 2013 13:48:55 GMT
Hi Sergey,

This looks like a bug to me. I have logged 
https://issues.apache.org/jira/browse/DERBY-6358 to track this.

As a workaround, you could first put the results of the inner joins into 
a temp table and then select from the temp table, applying the WHERE clause.

Another solution would be to wrap the inner joins in a table function 
and then select from the table function, applying the WHERE clause. 
Here's a table function you could use. You could eliminate the arguments 
to the table function if you wanted to make your query simpler to express...

import java.sql.*;

public class ForeignQueryVTI
     public  static  ResultSet   foreignQuery( String connectionURL, 
String query )
         throws SQLException
         Connection          conn = DriverManager.getConnection( 
connectionURL );
         PreparedStatement   ps = conn.prepareStatement( query );

         return ps.executeQuery();

The following script shows how to use this table function to get the 
right results:

connect 'jdbc:derby:memory:db;create=true';

create table t1( a varchar( 10 ) );
create table t2( a varchar( 10 ) );

create function fq( url varchar( 100 ), queryString varchar( 100 ) ) returns
     b varchar( 10 ),
     c varchar( 10 )
language java parameter style derby_jdbc_result_set reads sql data
external name 'ForeignQueryVTI.foreignQuery';

insert into t1( a ) values ( 'horse' ), ( 'apple' ), ( 'star' ), ( '6' );
insert into t2( a ) values ( '6' );

-- fails because of DERBY-6358
select *
from t1 inner join t2 on t1.a = t2.a
where cast( t1.a as int ) > 5;

-- succeeds
select *
from table
     fq( 'jdbc:default:connection', 'select * from t1 inner join t2 on 
t1.a = t2.a' )
) s
where cast( s.b as int ) > 5;

Hope this helps,

On 9/27/13 4:52 PM, Sergey Shelukhin wrote:
> Hi.
> 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?
> Details:
> 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