db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "A B (JIRA)" <j...@apache.org>
Subject [jira] Commented: (DERBY-716) Re-enable VTIs
Date Wed, 25 Apr 2007 19:07:15 GMT

    [ https://issues.apache.org/jira/browse/DERBY-716?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12491734

A B commented on DERBY-716:

I took a look at the functional spec for this issue and had the following questions.  Apologies
in advance for anything painfully obvious that I may have missed...

Under the "New SELECT Syntax" section:

 - Syntax for "TableFunctionInvocation" is missing parentheses that are required according
to SQL 2003 7.6 <table function derived table>.  Need a left paren before "function-name"
and a right paren at the end of the line.

   I think the example at the end of this section needs to be updated, as well.

 - "Value - A Value is an expression which could appear as a column in a standalone VALUES
    statement. That is, it is built out of literals, '?' parameters, and function calls."

 A standalone VALUES clause can include other types of expressions, as well.  For example:

     ij> values (select distinct 2 from sys.systables);
 Would this kind of expression be allowed within a TableFunctionInvocation? I'm assuming not,
but just thought I'd ask.  Note that such an expression is *not* currently allowed with the
SYSCS_DIAG table functions (DERBY-2152).

 Also, what does "function calls" mean here?  Is this just referring to the SQL functions
supported by Derby?  Or are we talking about JDBC escape functions and/or other user-defined
functions ("CREATE FUNCTION") as well?

Under "System Tables":

 - "Each column in the returned table is represented by a row in SYSCOLUMNS just as each
    function parameter is."

 It's not clear to me why we need to store information about the function parameters in SYSCOLUMNS.
 The Derby documentation says that SYSCOLUMNS "Describes the columns within all tables in
the current database."  I'm not sure how function parameter info fits that description.  Can
you elaborate on this?

 Note that based on some simple testing it would appear that we do *not* include info about
parameters for other Derby functions.  Ex:

    ij version 10.3
    ij> connect 'dbdb;create=true';
    ij> select count(*) from sys.syscolumns;

    1 row selected
    ij> create function myf (i int, vc varchar(200)) returns integer
        parameter style java language java external name 'hmm.myFunction';
    0 rows inserted/updated/deleted
    ij> select count(*) from sys.syscolumns;

    1 row selected

 We can see that no rows are added for the function parameters "i" nor for "vc".  And that
seems fine since information about the parameters is, as the spec says, available from the
DBMD.getFunctionColumns() method.  So what is the need for having rows in SYSCOLUMNS for parameters
defined in a table function?

 And going one step further, it even seems odd to me to keep the return columns themselves
in SYSCOLUMNs.  For one thing, a table function is ultimately a *function*, not a *table*,
and hence doesn't fall into the category of "all tables in the current database".  The argument
here may be that the function ultimately _returns_ a table, and that the resultant table _does_
fit the description of "all tables in the current database". But in that case I think it's
worth mentioning that the result is a *derived* table and is therefore neither persistent
(the function is persistent but the table is not) nor referenceable outside of a specific
query.  So it seems odd to me to add rows for such a table into SYSCOLUMNS.

 But it's quite possible I'm missing something obvious, so feel free to correct me :)

Under "Type System"

 - "We introduce a new Derby type RowMultisetImpl. This is the return type of Table Functions.
   A RowMultisetImpl  contains all of the column names in the returned table as well as their

 Okay, sorry for the really dumb question, but...how does this new type fit into the picture?

 From the spec the only use I can see is that this is what will be returned from a call to
the proposed "getReturnType()" method on RoutineAliasInfo.

 In particular, I'm wondering if this is something the user will see, or is this just internal
to Derby?  If it's internal to Derby then what is the need for this type here? The reason
I ask is that we already have diagnostic table functions working in Derby (see DERBY-1852)
and those functions do not depend on any new types; so what is it about the proposed DERBY-716
changes that require the new RowMultisetImpl type?  Is this related to the fact that we want
the table function to map to a static Java method (which is different from the diagnostic

 Also, it's not immediately clear to me why we need a new Formatable id for this type.  My
underlying assumption here is that a formatable id is only required for serialization of the
object (typically for writing/reading to/from disk).  This is based on the following javadoc
from iapi/services/io/StoredFormatIds:

    A format id identifies a stored form of an object for the
    purposes of locating a class which can read the stored form and
    reconstruct the object using the java.io.Externalizable interface.

So can you envision any places where we would need to store/reconstruct a RowMultisetImpl
via (de-)serialization? It seems like if we want to store the derived table result persistently
we would just create an actual table and then select into it.  Which is, for example, exactly
what the example shown in the "New SELECT Syntax" section shows:

    INSERT INTO employees
      SELECT s.*
        FROM TABLE (externalEmployees('jdbc:mysql://localhost/hr')) s;

Or as an alternate example, Derby currently supports the following:

   ij> create table st as select * from
          table (syscs_diag.space_table('T1')) s with no data;

   ij> insert into st select * from table (syscs_diag.space_table('T1')) s;

There are of course other internal reasons to serialize an object, ex. iapi/types/SqlXmlUtil
is serialized (and therefore needs a format id) because it used as a "saved object".  Do you
foresee a scenario where this could happen with user-defined table functions?

> Re-enable VTIs
> --------------
>                 Key: DERBY-716
>                 URL: https://issues.apache.org/jira/browse/DERBY-716
>             Project: Derby
>          Issue Type: New Feature
>          Components: SQL
>            Reporter: Rick Hillegas
>         Attachments: functionTables.html
> Cloudscape used to expose Virtual Table Interfaces, by which any class which implemented
ResultSet could be included in a query's FROM list. Derby still exposes a number of these
VTIs as diagnostic tools. However, Derby now prevents customers from declaring their own VTIs.
The parser raises an error if a VTI's package isn't one of the Derby diagnostic packages.
> This is a very powerful feature which customers can use to solve many problems. We should
discuss the reasons that it was disabled and come up with a plan for putting this power back
into our customers' hands.

This message is automatically generated by JIRA.
You can reply to this email to add a comment to the issue online.

View raw message