[jira] [Comment Edited] (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.

["Richard Huddleston (JIRA)" <ji...@apache.org>]

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

Richard Huddleston edited comment on DERBY-6036 at 12/13/13 8:25 AM:
---------------------------------------------------------------------

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)
that VTIContext for now could have just getFunctionName, but it would be also nice to have things like getFunctionComment so that instead of params to a function, we could also dynamically change a function's behaviour by altering JUST the comments of a regstered function, and NOT having to change all the queries which call the function and pass arguments.

because that VTIContext is an 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 ?



was (Author: rhuddusa):
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)