Re: order of evaluation for filters in the query

[Sergey Shelukhin <se...@hortonworks.com>]

I am trying to keep the code ANSI SQL for some purposes, so I will probably
have to have some different workaround... thanks!


On Mon, Sep 30, 2013 at 6:48 AM, Rick Hillegas <ri...@oracle.com>wrote:

> Hi Sergey,
>
> This looks like a bug to me. I have logged https://issues.apache.org/**
> jira/browse/DERBY-6358 <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
> table
> (
>     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,
> -Rick
>
>
>
>
> 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.
>>
>> CONFIDENTIALITY NOTICE
>> 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.
>>
>
>

-- 
CONFIDENTIALITY NOTICE
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.