Re: order of evaluation for filters in the query
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
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.
Re: Proper configuration for a very busy DB?
Do you open new connection every time or do you have a pool? How often does Derby checkpoint/switch log file? Peter - Original Message - From: Jerry Lampi j...@sdsusa.com To: Derby User Group derby-user@db.apache.org Cc: Sent: Monday, 30 September 2013, 21:28 Subject: Proper configuration for a very busy DB? We have about 30 clients that connect to our version 10.8.2.2 Derby DB. The clients are programs that gather data from the operating system of their host and then store that data in the DB, including FTP activity. Sometimes, the clients get huge flurries of data all at once and Derby is unable to handle the influx of requests; inserts, updates, etc. In addition, the clients are written so that if they are unable to talk to the DB, they queue up as much data as possible and then write it to the DB when the DB becomes available. This client queuing is a poor design, and places greater stress on the DB, as when the 30 clients finally do talk to the DB, they all dump data at once. The clients do not know about one another and therefore do not attempt any throttling or cooperation when dumping on the DB. The net effect of all this is that the DB is too slow to keep up with the clients. As clients try to feed data to the DB, it cannot accept it as fast as desired and this results in the clients queueing more data, exacerbating the issue. So the DB is very busy. The only significant thing we have done thus far is change the derby.storage.pageCacheSize=5000 and increase Java heap to 1536m. Is there a configuration considered optimal for a VERY busy Derby DB? Thanks, Jerry --- avast! Antivirus: Outbound message clean. Virus Database (VPS): 130930-0, 09/30/2013 Tested on: 9/30/2013 2:28:40 PM avast! - copyright (c) 1988-2013 AVAST Software. http://www.avast.com
Re: order of evaluation for filters in the query
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 rick.hille...@oracle.comwrote:
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-6358to
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.
