[
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
-----------
119
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
-----------
119
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
datatypes."
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 VTIs)?
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.
