db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Richard Huddleston (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (DERBY-6036) If you wrap a SELECT * view around a table, all of the columns are read from the base row even when you SELECT only a subset of the view columns.
Date Fri, 13 Dec 2013 08:09:07 GMT

    [ https://issues.apache.org/jira/browse/DERBY-6036?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13847246#comment-13847246
] 

Richard Huddleston commented on DERBY-6036:
-------------------------------------------

We are trying to work around this issue by calling the function directly instead of the view,
but have noticed that it would be much simpler if Derby VTI exposed the function name to the
VTI implementation ResultSet .  i don't believe there currently is a way to access the function
name in the VTI ResultSet class. 

We are accessing n different remote data tables, each with its own table definition.  Thus
in Derby, we must register n different functions. Note, we only need 1 implementation class
of the function to actually get the data and return our custom ResultSet implementation. We'd
prefer to NOT write a separate class and redeploy our application each time someone adds a
new remote data table, as we have many actually derby instances running this codebase.

pseudo code / sql

we have to create register n function definitions in , for i in 1 .. n, where tableName_i
is our ith table name, (tableName_i is like customers)
register derby function selectFromRemote_tableName_i('tableName_i')  java method is selectFromRemote.read
, 

then to call any specific function we have to write (pseudo sql)

select s.* from table ( selectFromRemote_customers('customers') ) s where custId = 2

it would be MUCH cleaner if we could just

register a derby function with different names and no parameters 
so instead of selectFromRemote_customers, we would just register function "customers"
and derby function customers is java method selectFromRemote.read , 

and then be able to call
select c.* from table ( customers() ) c where custId = 2

then our ResultSet implementation could access some variable about the function name, and
we would use that in lieu of a function parameters (it would also save us having to declare
function params when we register functions, which is tedious )

i think in the future you may want to add more information about VTI function context, 
would it be logical to add more stuff to VTIEnvironment and make VTIEnvironment available
/ applicable to all VTI resultsets, not just those that implement VTICosting ?  side question,
if we did implement VTICosting , is it guaranteed that its methods will be called ?

perhaps there should be a new VTIContextAware interface that has something like
init(VTIContext context)

and that VTIContext interface you could continue to add methods to in the future you expose
more information to the function., note it would have been nice if in RestrictedVTI, the initScan
method took in an interface object like InitScanParams which had currently getColumnNames
and getRestriction, that would probably have allowed this bug to be solved rather easily by
just adding "getFunctionName" on that InitScanParams interface. 

Perhaps I should stop just being a writing critic and start writing something myself how easy
would it be for me to start hacking up and contributing to the derby project myself ?


> If you wrap a SELECT * view around a table, all of the columns are read from the base
row even when you SELECT only a subset of the view columns.
> -------------------------------------------------------------------------------------------------------------------------------------------------
>
>                 Key: DERBY-6036
>                 URL: https://issues.apache.org/jira/browse/DERBY-6036
>             Project: Derby
>          Issue Type: Bug
>          Components: SQL
>    Affects Versions: 10.10.1.1
>            Reporter: Rick Hillegas
>              Labels: derby_triage10_11
>         Attachments: derby-6036-01-aa-testForRestrictionPushing.diff, derby-6036.sql,
derbyAST.xml
>
>
> This also affects SELECTs from views wrapping RestrictedVTIs. Restrictions are pushed
into a restricted VTI if you wrap it in a view. However, projections are not. I will attach
a script showing this problem.



--
This message was sent by Atlassian JIRA
(v6.1.4#6159)

Mime
View raw message