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 (
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 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
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?