db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Tim Dudgeon <tdudg...@informaticsmatters.com>
Subject Re: Limitations of org.apache.derby.vti.Restriction classes
Date Fri, 13 May 2011 13:49:43 GMT
Hi Rick,

Thanks for quick response.
Thoughts below.


On 13/05/2011 14:22, Rick Hillegas wrote:
> Thanks for the comments on Restriction, Tim. Some responses inline..
> On 5/13/11 3:22 AM, Tim Dudgeon wrote:
>> Hi, I was looking at writing restricted table functions to retrieve
>> data from a foreign database, and think I have identified some
>> limitations of the org.apache.derby.vti.Restriction classes. I would
>> welcome some advice or comments on this.
>> 1. Constructor of Restriction.AND and Restriction.OR only allows two
>> terms to be added. In reality and AND or OR expression could take any
>> number of elements, so really there should be ability to add List
>> (and/or Array) of elements. When Derby passes through simple
>> expression like
>> where a < 1 and b < 2 and c < 3
>> it ends up as a nested expression and the generated SQL starts to look
>> ugly.
> I think there are two issues in here:
> i) the usability of the Restriction constructors
> ii) the readability of the toSQL() output
> The constructors were designed to be used internally by Derby when
> invoking a RestrictedVTI. Do you find that you are calling these
> constructors directly? If so, can you tell me something more about the
> use case so that we can design a friendlier constructor api?

No, I'm not using these directly. Just assuming that Derby could do it 
more efficiently e.g. in the case of
a < 1 and b < 2 and c < 3
type where clauses.
These unnecessarily cause a nesting of Restriction elements, and result 
in unnecessary brackets being applied. I expect this does not have any 
impact on query execution, but it just looks ugly!

> Similarly, the output of toSQL() was meant to be consumed by a foreign
> RDBMS. The output was designed to be correct ANSI/ISO SQL. It was not
> designed to be friendly to read and it was not expected that it would be
> parseable by non-ANSI/ISO dialects. For some foreign databases, your
> RestrictedVTI may need to hand-construct a WHERE clause by walking the
> Restriction tree, calling getLeftChild() and getRightChild() as you go.
> But again, can you tell me more about your use case which would benefit
> from more readable toSQL() output?
>> 2. When using Restiction.toSQL() braces are not put in the rigth
>> places for AND and OR restictions. Generated SQL is like this:
>> select x, y, x from foo where ( a = 1 ) AND ( b = 2 )
>> Surely should be:
>> select x, y, x from foo where ( a = 1 AND b = 2 )
>> Although generated SQL seems to be legal (even when nested) its
>> difficult to read.
> I believe that the parentheses are placed correctly. Please let me know
> if you can identify a case where the generated SQL has the wrong
> meaning. The scheme you suggest may work too, but it may encounter
> problems in foreign databases whose operators bind in a non-ANSI/ISO order.

I think they are correct, but unnecessary.
No real problem with this. Just seems a little untidy.

>> 3. Then using text (e.g. for varchar columns) comparisons (and
>> presumably date) the values passed through by Derby are not quoted,
>> and the Restiction.toSQL() function does not add quotes. This results
>> in SQL like this:
>> select x, y, x from foo where ( name = bar)
>> not
>> select x, y, x from foo where ( name = 'bar' )
>> and so SQL is invalid
> This is an ugly bug. Thanks for finding it. I have logged
> https://issues.apache.org/jira/browse/DERBY-5231 to track this one.

OK, good. Thanks.

>> 4. Column names etc. are double quoted when using Restiction.toSQL().
>> This is fine for most DBs, but is going to give problems with some.
>> e.g. MySQL which uses backticks.
> Right. Here you have to fall back on walking the Restriction tree
> yourself, hand-generating a WHERE clause which will work for the SQL
> dialect in your foreign RDBMS. I can see the value of having a library
> of WHERE clause generators targeted at different SQL dialects. This is
> something which the user community could help assemble. If there is
> interest in donating these implementations, we could discuss where this
> library should live.

I think the obvious case is MySQL that needs to use backticks for 
quoting identifiers?
I haven't tested, but I imagine the generated SQL will not work on MySQL.

A library of translators seems like a good plan. Visitor pattern springs 
to mind.
Something to handle an entire select statement would be even nicer.
Does derby have similar classes for handling things other than the where 

> Thanks,
> -Rick
>> Tim

View raw message