Details

    • Type: Improvement Improvement
    • Status: Closed
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: 10.4.1.3
    • Component/s: SQL
    • Labels:
      None

      Description

      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.

      1. functionTables.html
        45 kB
        Rick Hillegas
      2. functionTables.html
        51 kB
        Rick Hillegas
      3. functionTables.html
        41 kB
        Rick Hillegas
      4. derby-716-11-subqueries-aa.diff
        4 kB
        Rick Hillegas
      5. derby-716-10-datatypesCollation-aa.diff
        23 kB
        Rick Hillegas
      6. derby-716-09-upgradeLocalization-aa.diff
        0.6 kB
        Rick Hillegas
      7. derby-716-08-upgrade-aa.diff
        3 kB
        Rick Hillegas
      8. derby-716-07-dblook-aa.diff
        3 kB
        Rick Hillegas
      9. derby-716-06-TestEncoding-aa.diff
        1 kB
        Rick Hillegas
      10. derby-716-05-PublicAPI-aa.diff
        11 kB
        Rick Hillegas
      11. derby-716-04-Optimizer-aa.diff
        18 kB
        Rick Hillegas
      12. derby-716-03-DatabaseMetaData-aa.diff
        3 kB
        Rick Hillegas
      13. derby-716-02-DatabaseMetaData-aa.diff
        36 kB
        Rick Hillegas
      14. derby-716-01-basic-aa.diff
        110 kB
        Rick Hillegas

        Issue Links

          Activity

          Hide
          Rick Hillegas added a comment -

          This feature is supported by other databases, including DB2 (see http://publib.boulder.ibm.com/infocenter/db2help/index.jsp?topic=/com.ibm.db2.udb.doc/ad/c0011177.htm) and Microsoft SQL Server (see http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_create_7r1l.asp).

          These databases largely hew to the ANSI 2003 solution:

          1) Declare a function which returns a table
          2) Invoke the function in your query's FROM list

          The ANSI syntax for declaring VTI-returning functions is defined in Part 2, section 11.50, under the <returns table type> production. It allows you to specify a table signature in the RETURNS clause of the function declaration:

          CREATE FUNCTION functionName ...
          RETURNS TABLE( [ [ columnName, columnDatatype ] [, columnName columnDatatype ]* ] )

          The ANSI syntax for querying a VTI is defined in Part 2, section 7.6 under the <table function derived table> production. It allows a FROM list element to be a function invocation wrapped by a TABLE constructor:

          SELECT *
          FROM TABLE( functionName( args ... ) )

          This differs from the Cloudscape approach, which was to declare the VTI on the fly at query time using a constructor. The ANSI approach seems simple, powerful, and elegant enough. Here's a sketch of what we could do:

          1) Enhance the CREATE FUNCTION syntax to support the RETURNS TABLE clause.
          2) Enhance query specifications to allow TABLE( functionName( args ... ) ) in the FROM list
          3) Expose template ResultSet and ResultSetMetaData implementations which customers can extend. We currently have versions of these in org.apache.derby.vti.VTITemplate.
          4) Raise a query-execution-time exception if a) the java function does not return a ResultSet, or b) the VTI's ResultSetMetaData does not match the signature declared by CREATE FUNCTION.

          Show
          Rick Hillegas added a comment - This feature is supported by other databases, including DB2 (see http://publib.boulder.ibm.com/infocenter/db2help/index.jsp?topic=/com.ibm.db2.udb.doc/ad/c0011177.htm ) and Microsoft SQL Server (see http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_create_7r1l.asp ). These databases largely hew to the ANSI 2003 solution: 1) Declare a function which returns a table 2) Invoke the function in your query's FROM list The ANSI syntax for declaring VTI-returning functions is defined in Part 2, section 11.50, under the <returns table type> production. It allows you to specify a table signature in the RETURNS clause of the function declaration: CREATE FUNCTION functionName ... RETURNS TABLE( [ [ columnName, columnDatatype ] [, columnName columnDatatype ] * ] ) The ANSI syntax for querying a VTI is defined in Part 2, section 7.6 under the <table function derived table> production. It allows a FROM list element to be a function invocation wrapped by a TABLE constructor: SELECT * FROM TABLE( functionName( args ... ) ) This differs from the Cloudscape approach, which was to declare the VTI on the fly at query time using a constructor. The ANSI approach seems simple, powerful, and elegant enough. Here's a sketch of what we could do: 1) Enhance the CREATE FUNCTION syntax to support the RETURNS TABLE clause. 2) Enhance query specifications to allow TABLE( functionName( args ... ) ) in the FROM list 3) Expose template ResultSet and ResultSetMetaData implementations which customers can extend. We currently have versions of these in org.apache.derby.vti.VTITemplate. 4) Raise a query-execution-time exception if a) the java function does not return a ResultSet, or b) the VTI's ResultSetMetaData does not match the signature declared by CREATE FUNCTION.
          Hide
          Daniel John Debrunner added a comment -

          I think the syntax of the RETURNS TABLE clause above is incorrect, I don't believe the column definitions are optional.

          Implementing a table function in Java using ResultsSet is non-standard, here are some notes on the official standard Java mapping:

          http://wiki.apache.org/db-derby/JavaTableFunctions

          I do think the ResultSet/PreparedStatement mechanism is much more elegant and powerful though.

          I don't believe Derby should provide templates for ResultSet and other JDBC classes for VTIs, it's a pain to support those with multiple JDBC versions, and nowadays IDE's provide easy mechanisms to create all the methods of an interface for a given class.

          Show
          Daniel John Debrunner added a comment - I think the syntax of the RETURNS TABLE clause above is incorrect, I don't believe the column definitions are optional. Implementing a table function in Java using ResultsSet is non-standard, here are some notes on the official standard Java mapping: http://wiki.apache.org/db-derby/JavaTableFunctions I do think the ResultSet/PreparedStatement mechanism is much more elegant and powerful though. I don't believe Derby should provide templates for ResultSet and other JDBC classes for VTIs, it's a pain to support those with multiple JDBC versions, and nowadays IDE's provide easy mechanisms to create all the methods of an interface for a given class.
          Hide
          Rick Hillegas added a comment -

          Thanks for helping work through the details here. You are, of course, correct that the column definitions are mandatory.

          I looked at the http://wiki.apache.org/db-derby/JavaTableFunctions wiki page. It seems to describe the layout and behavior of functions which return ARRAY datatypes. I do not understand how this applies to functions which return TABLE types. The SQL Standard sections on user-defined-routines are very long and, at least for me, hard to read. Still, as I read the SQL Standard, the return type of a table function is equivalent to a MULTISET datatype, not an ARRAY type. That, at least, is how I read Part 2, Section 11.50, Syntax Rule 4.

          Could you point me at the chapter and verse which leads you to believe that Java TABLE functions should be implemented as ARRAY returning functions?

          Show
          Rick Hillegas added a comment - Thanks for helping work through the details here. You are, of course, correct that the column definitions are mandatory. I looked at the http://wiki.apache.org/db-derby/JavaTableFunctions wiki page. It seems to describe the layout and behavior of functions which return ARRAY datatypes. I do not understand how this applies to functions which return TABLE types. The SQL Standard sections on user-defined-routines are very long and, at least for me, hard to read. Still, as I read the SQL Standard, the return type of a table function is equivalent to a MULTISET datatype, not an ARRAY type. That, at least, is how I read Part 2, Section 11.50, Syntax Rule 4. Could you point me at the chapter and verse which leads you to believe that Java TABLE functions should be implemented as ARRAY returning functions?
          Hide
          Daniel John Debrunner added a comment -

          I don't understand the comment about ARRAY returning functions, what in the wiki page leads you to believe it describes functions returning ARRAY datatypes?

          Show
          Daniel John Debrunner added a comment - I don't understand the comment about ARRAY returning functions, what in the wiki page leads you to believe it describes functions returning ARRAY datatypes?
          Hide
          Christian d'Heureuse added a comment -

          Rick, do you mean the array arguments that are used for the OUT parameters, e.g. "double[] cost"?

          You have to look at SQL/Part 13 (SQL/JRT), sections 8.5 (Execution of array-returning functions) and 8.6 (Java routine signature determination). Not only at Part 2 (Foundation)) Section 11.50 (SQL-invoked routine)..

          Show
          Christian d'Heureuse added a comment - Rick, do you mean the array arguments that are used for the OUT parameters, e.g. "double[] cost"? You have to look at SQL/Part 13 (SQL/JRT), sections 8.5 (Execution of array-returning functions) and 8.6 (Java routine signature determination). Not only at Part 2 (Foundation)) Section 11.50 (SQL-invoked routine)..
          Hide
          Rick Hillegas added a comment -

          Thanks, Dan. I think what has me confused here is the wiki page's reference to Part 13, section 8.5. That section deals with ARRAY types. I see that 8.6 addresses the case of MULTISET return types. My reading (of Part 13, Section 9.8, syntax rule 12 and Part 13, Section 8.6) is that the routine resolution on the wiki page addresses the case when PARAMETER STYLE JAVA is specified.

          My itch is to re-enable the ResultSet style VTIs, which you agree are elegant and powerful. Here's a proposal:

          1) We introduce a new kind of parameter style, say PARAMETER STYLE DERBY. We can sand down the name of this style later on.

          2) For a first rev, the customer must specify PARAMETER STYLE DERBY when they declare a table-returning function.These methods resolve to static methods which return ResultSets.

          3) Later on, someone who has the itch can implement PARAMETER STYLE JAVA and the kinds of method signatures described in the wiki page.

          4) Perhaps someone can contribute some machinery which will wrap DERBY-style methods inside JAVA-style ones and vice-versa.

          I think this proposal

          A) Enables a powerful programming model which distinguishes Derby

          B) Nevertheless allows later implementation of the ANSI method-style for people who are interested in migrating table-functions to/from other databases.

          Show
          Rick Hillegas added a comment - Thanks, Dan. I think what has me confused here is the wiki page's reference to Part 13, section 8.5. That section deals with ARRAY types. I see that 8.6 addresses the case of MULTISET return types. My reading (of Part 13, Section 9.8, syntax rule 12 and Part 13, Section 8.6) is that the routine resolution on the wiki page addresses the case when PARAMETER STYLE JAVA is specified. My itch is to re-enable the ResultSet style VTIs, which you agree are elegant and powerful. Here's a proposal: 1) We introduce a new kind of parameter style, say PARAMETER STYLE DERBY. We can sand down the name of this style later on. 2) For a first rev, the customer must specify PARAMETER STYLE DERBY when they declare a table-returning function.These methods resolve to static methods which return ResultSets. 3) Later on, someone who has the itch can implement PARAMETER STYLE JAVA and the kinds of method signatures described in the wiki page. 4) Perhaps someone can contribute some machinery which will wrap DERBY-style methods inside JAVA-style ones and vice-versa. I think this proposal A) Enables a powerful programming model which distinguishes Derby B) Nevertheless allows later implementation of the ANSI method-style for people who are interested in migrating table-functions to/from other databases.
          Hide
          Rick Hillegas added a comment -

          Thanks, Christian. I think I'm more or less tracking now.

          Show
          Rick Hillegas added a comment - Thanks, Christian. I think I'm more or less tracking now.
          Hide
          Christian d'Heureuse added a comment -

          Part 2 section 9.15 (Execution of multiset-returning functions) Rule 6 states: "The General Rules of Subclause 9.14 (Execution of array-returning functions) are applied...".
          Therefore Part 13 section 8.5 (Execution of array-returning functions), which modifies Part 1 section 9.14, indirectly also applies to functions returning MULTISETs.

          Show
          Christian d'Heureuse added a comment - Part 2 section 9.15 (Execution of multiset-returning functions) Rule 6 states: "The General Rules of Subclause 9.14 (Execution of array-returning functions) are applied...". Therefore Part 13 section 8.5 (Execution of array-returning functions), which modifies Part 1 section 9.14, indirectly also applies to functions returning MULTISETs.
          Hide
          Rick Hillegas added a comment -

          Attaching a first rev of a functional spec for this feature. I would be grateful for your feedback.

          Show
          Rick Hillegas added a comment - Attaching a first rev of a functional spec for this feature. I would be grateful for your feedback.
          Hide
          Daniel John Debrunner added a comment -

          Using ResultSet as the api to implement table functions can be somewhat awkward. When this VTI functionality was implemented in Cloudscape, ResultSet's were somewhat simple, forward only, read-only and only JDBC 1.2. Now ResultSet's need to support updates, deletes, inserts and scrolling. This means that even a simple table function must implement a large number of methods even if (I think) in the simplest case only four may be required (constructor, next, getXXX and close). While one can try to create a template that has most of the non-required methods (as Cloudscape did), this becomes a major pain if one needs to support multiple JDBC environments, JDBC 3, 4 and JSR 169. In fact using ResultSet means that the application developer will have a hard time implementing a solution in a JDBC version agnostic way.
          Also using ResultSet means that the application developer ends up implementing a class that doesn't really implement the defined contract of ResultSet, just the subset specific to Derby's functional tables, doesn't seem to be a ideal postion to be in for an app developer.

          I've been struggling with these issues while looking at the way Derby uses VTIs (ResultSets) for triggers, currently the implementation uses classes that are not in JSR169 but needs to because the code is compiled against JDBC 3. It just seems to work, but may fail on some J2ME VM's that have stricter class loading policies.

          Contrast this with the standard mechanism for writing table functions in Java that just use a single static Java method, much easier for an application developer to work with, portable to multiple JDBC versions and easier to understand (a single working method rather than 139 methods (in JDBC 3) and most of them not used).

          While the current Derby internal vti is quite powerful the SQL standard provides for much of the same functionality through SQL/MED, e.g. pushing predicates, providing optimizer costs etc.

          Thus I have reservations about introducing a non-standard feature into Derby and especially a non-standard feature that exposes a un-friendly api onto the application developer.

          I do think that Java table functions or wrappers (for updates as well) would be a great addition to Derby, I just think that the api should be clean and easy especially if it's not standard based.

          I also have some questions on the details in the spec if the ResultSet approach is taken, but here's one on the SQL. Why is the SQL for SELECT changing? Derby already supports bultin table functions, so what needs to change here?

          Show
          Daniel John Debrunner added a comment - Using ResultSet as the api to implement table functions can be somewhat awkward. When this VTI functionality was implemented in Cloudscape, ResultSet's were somewhat simple, forward only, read-only and only JDBC 1.2. Now ResultSet's need to support updates, deletes, inserts and scrolling. This means that even a simple table function must implement a large number of methods even if (I think) in the simplest case only four may be required (constructor, next, getXXX and close). While one can try to create a template that has most of the non-required methods (as Cloudscape did), this becomes a major pain if one needs to support multiple JDBC environments, JDBC 3, 4 and JSR 169. In fact using ResultSet means that the application developer will have a hard time implementing a solution in a JDBC version agnostic way. Also using ResultSet means that the application developer ends up implementing a class that doesn't really implement the defined contract of ResultSet, just the subset specific to Derby's functional tables, doesn't seem to be a ideal postion to be in for an app developer. I've been struggling with these issues while looking at the way Derby uses VTIs (ResultSets) for triggers, currently the implementation uses classes that are not in JSR169 but needs to because the code is compiled against JDBC 3. It just seems to work, but may fail on some J2ME VM's that have stricter class loading policies. Contrast this with the standard mechanism for writing table functions in Java that just use a single static Java method, much easier for an application developer to work with, portable to multiple JDBC versions and easier to understand (a single working method rather than 139 methods (in JDBC 3) and most of them not used). While the current Derby internal vti is quite powerful the SQL standard provides for much of the same functionality through SQL/MED, e.g. pushing predicates, providing optimizer costs etc. Thus I have reservations about introducing a non-standard feature into Derby and especially a non-standard feature that exposes a un-friendly api onto the application developer. I do think that Java table functions or wrappers (for updates as well) would be a great addition to Derby, I just think that the api should be clean and easy especially if it's not standard based. I also have some questions on the details in the spec if the ResultSet approach is taken, but here's one on the SQL. Why is the SQL for SELECT changing? Derby already supports bultin table functions, so what needs to change here?
          Hide
          Christian d'Heureuse added a comment -

          In H2 (http://www.h2database.com) there is a class called SimpleResultSet (http://www.h2database.com/javadoc/org/h2/tools/SimpleResultSet.html) which makes it easy to implement a simple stored procedure that returns a Resultset.
          Example (from http://www.h2database.com/html/features.html#user_defined_functions)::

          public static ResultSet simpleResultSet() throws SQLException {
          SimpleResultSet rs = new SimpleResultSet();
          rs.addColumn("ID", Types.INTEGER, 10, 0);
          rs.addColumn("NAME", Types.VARCHAR, 255, 0);
          rs.addRow(new Object[]

          { new Integer(0), "Hello" }

          );
          rs.addRow(new Object[]

          { new Integer(1), "World" }

          );
          return rs;
          }

          And the interface SimpleRowSource (http://www.h2database.com/javadoc/org/h2/tools/SimpleRowSource.html) can be used together with SimpleResultSet. to create rows on demand for dynamic result sets

          Show
          Christian d'Heureuse added a comment - In H2 ( http://www.h2database.com ) there is a class called SimpleResultSet ( http://www.h2database.com/javadoc/org/h2/tools/SimpleResultSet.html ) which makes it easy to implement a simple stored procedure that returns a Resultset. Example (from http://www.h2database.com/html/features.html#user_defined_functions):: public static ResultSet simpleResultSet() throws SQLException { SimpleResultSet rs = new SimpleResultSet(); rs.addColumn("ID", Types.INTEGER, 10, 0); rs.addColumn("NAME", Types.VARCHAR, 255, 0); rs.addRow(new Object[] { new Integer(0), "Hello" } ); rs.addRow(new Object[] { new Integer(1), "World" } ); return rs; } And the interface SimpleRowSource ( http://www.h2database.com/javadoc/org/h2/tools/SimpleRowSource.html ) can be used together with SimpleResultSet. to create rows on demand for dynamic result sets
          Hide
          Rick Hillegas added a comment -

          Thanks for your feedback, Dan and Christian.

          I agree that ResultSet has become very large. It is, however, the tabular interface defined by the JDBC standard. In a previous conversation (which I can't cite more specifically), I was persuaded that modern IDEs are very good at stubbing out these big interfaces. The H2 template which Christian cites is very interesting. If we think that stubbing out this interface for all platforms is too onerous, we could consider supplying a JDBC4 template in a demo directory-the user could prune that template back to a JSR169 compliant form if necessary. We could even provide templates for all the JDBC levels we support-that does not seem like a lot of work for us.

          I think that, over the long haul, we will want both the api which Dan suggests and the ResultSet api. I can see good use-cases for both.

          I don't know how to map the SQL/MED model onto table functions. It seems to me that chapter 9 of the SQL Standard (SQL/MED) defines an interface to foreign servers. These seem to be entire SQL servers complete with their own authentication schemes and SQL interpreters. The optimizer interface in chapter 9 applies, I believe, to "foreign tables" (defined in section 4.10.1 of that chapter) and not to the "derived tables" returned by table functions. I am having a hard time mapping the SQL/MED model onto table functions:

          1) SQL/MED seems to assume a handshake with a wrapper interface, a gateway which wraps the external server. Getting one's hands on one of these gateways implies a lot of machinery that is not present for table functions.

          2) The SQL/MED optimizer calls seem to assume that the external data source can be repositioned with repeatable-read behavior. This is probably true if you are talking to an external relational server but I don't think you can assume that is generally true for table functions. VTICosting lets you override this assumption.

          3) The SQL/MED predicate pushing is indeed powerful. However, it again assumes that you are dealing with a gateway which wraps a SQL interpreter.

          I'm eager to see alternative optimizer apis, though. At the very least, they will help us think through the issues. Maybe you could explain in greater detail how you would map this "foreign table" model onto table functions.

          Thanks for pointing out that the FROM clause already handles table functions provided that they are the diagnostic VTIs. I could not find this syntax documented in the FROM clause section of the Reference Manual but, now that you point it out, I see that it is mentioned in the section of that manual titled "SYSCS_DIAG diagnostic tables and functions". This is good news because it is another chunk of work which has been done already.

          Show
          Rick Hillegas added a comment - Thanks for your feedback, Dan and Christian. I agree that ResultSet has become very large. It is, however, the tabular interface defined by the JDBC standard. In a previous conversation (which I can't cite more specifically), I was persuaded that modern IDEs are very good at stubbing out these big interfaces. The H2 template which Christian cites is very interesting. If we think that stubbing out this interface for all platforms is too onerous, we could consider supplying a JDBC4 template in a demo directory- the user could prune that template back to a JSR169 compliant form if necessary. We could even provide templates for all the JDBC levels we support -that does not seem like a lot of work for us. I think that, over the long haul, we will want both the api which Dan suggests and the ResultSet api. I can see good use-cases for both. I don't know how to map the SQL/MED model onto table functions. It seems to me that chapter 9 of the SQL Standard (SQL/MED) defines an interface to foreign servers. These seem to be entire SQL servers complete with their own authentication schemes and SQL interpreters. The optimizer interface in chapter 9 applies, I believe, to "foreign tables" (defined in section 4.10.1 of that chapter) and not to the "derived tables" returned by table functions. I am having a hard time mapping the SQL/MED model onto table functions: 1) SQL/MED seems to assume a handshake with a wrapper interface, a gateway which wraps the external server. Getting one's hands on one of these gateways implies a lot of machinery that is not present for table functions. 2) The SQL/MED optimizer calls seem to assume that the external data source can be repositioned with repeatable-read behavior. This is probably true if you are talking to an external relational server but I don't think you can assume that is generally true for table functions. VTICosting lets you override this assumption. 3) The SQL/MED predicate pushing is indeed powerful. However, it again assumes that you are dealing with a gateway which wraps a SQL interpreter. I'm eager to see alternative optimizer apis, though. At the very least, they will help us think through the issues. Maybe you could explain in greater detail how you would map this "foreign table" model onto table functions. Thanks for pointing out that the FROM clause already handles table functions provided that they are the diagnostic VTIs. I could not find this syntax documented in the FROM clause section of the Reference Manual but, now that you point it out, I see that it is mentioned in the section of that manual titled "SYSCS_DIAG diagnostic tables and functions". This is good news because it is another chunk of work which has been done already.
          Hide
          Daniel John Debrunner added a comment -

          Rick > If we think that stubbing out this interface for all platforms is too onerous, we could consider supplying a JDBC4 template in a demo directory-the user could prune that template back to a JSR169 compliant form if necessary. We could even provide templates for all the JDBC levels we support-that does not seem like a lot of work for us.

          It's not that it's a lot of work for anyone, it moves to a model where an application that wants to support multiple JDBC environments has two unpleasant choices:

          • have a Java class with multiple versions (doesn't fit well into the Java development model)
          • have different versions of the SQL schema for different platforms (different create function statements that point to different java classes)

          Maybe that's not a huge concern, Derby could just say it has the facility to present a JDBC ResultSet as a virtual table and it's the user's problem if they want to support multiple environments.

          Show
          Daniel John Debrunner added a comment - Rick > If we think that stubbing out this interface for all platforms is too onerous, we could consider supplying a JDBC4 template in a demo directory- the user could prune that template back to a JSR169 compliant form if necessary. We could even provide templates for all the JDBC levels we support -that does not seem like a lot of work for us. It's not that it's a lot of work for anyone, it moves to a model where an application that wants to support multiple JDBC environments has two unpleasant choices: have a Java class with multiple versions (doesn't fit well into the Java development model) have different versions of the SQL schema for different platforms (different create function statements that point to different java classes) Maybe that's not a huge concern, Derby could just say it has the facility to present a JDBC ResultSet as a virtual table and it's the user's problem if they want to support multiple environments.
          Hide
          Daniel John Debrunner added a comment -

          Some comments on the spec:

          I think the parameter style should be more specific than "DERBY", say "DERBY_JDBC_RESULT_SET", there may be other Derby specific types that could be added here, e.g. RSS.

          "When you issue a query against a Table Function, Derby constructs a ResultSetMetaData for the result, based on the column names and datatypes you declared when you initially created the Table Function."
          Not sure what this is really trying to say. Why would Derby create a ResultSetMetaData based upon the functions shape, what is this used for?

          I don't see from the functional specification how VTICosting is tied in? What does the app developer do?

          How about the Pushable interface, that's useful existing functionality as well?

          Show
          Daniel John Debrunner added a comment - Some comments on the spec: I think the parameter style should be more specific than "DERBY", say "DERBY_JDBC_RESULT_SET", there may be other Derby specific types that could be added here, e.g. RSS. "When you issue a query against a Table Function, Derby constructs a ResultSetMetaData for the result, based on the column names and datatypes you declared when you initially created the Table Function." Not sure what this is really trying to say. Why would Derby create a ResultSetMetaData based upon the functions shape, what is this used for? I don't see from the functional specification how VTICosting is tied in? What does the app developer do? How about the Pushable interface, that's useful existing functionality as well?
          Hide
          Rick Hillegas added a comment -

          Dan> It's not that it's a lot of work for anyone, it moves to a model where an application that wants to support multiple JDBC environments has two unpleasant choices:

          I think that Øystein ran some experiments using Derby 10.1 (supporting JDBC3) on Java 6 (which defines JDBC4). I think that Derby's JDBC3 ResultSets worked fine on Java 6 because the testing application never called JDBC4-specific methods. I'm cautiously hopeful that an application which needs to run on multiple VM revs can succeed as long as it codes to the least-common denominator.

          I don't have any experience trying to run the same code on Java SE and Java ME. I have only very limited experience trying to code an application to run both places. My limited experience suggests that most people in this situation will end up writing a portablitliy layer, or even two implementations because desktop users will refuse to live with the limitations imposed by by the small-device environment.

          Show
          Rick Hillegas added a comment - Dan> It's not that it's a lot of work for anyone, it moves to a model where an application that wants to support multiple JDBC environments has two unpleasant choices: I think that Øystein ran some experiments using Derby 10.1 (supporting JDBC3) on Java 6 (which defines JDBC4). I think that Derby's JDBC3 ResultSets worked fine on Java 6 because the testing application never called JDBC4-specific methods. I'm cautiously hopeful that an application which needs to run on multiple VM revs can succeed as long as it codes to the least-common denominator. I don't have any experience trying to run the same code on Java SE and Java ME. I have only very limited experience trying to code an application to run both places. My limited experience suggests that most people in this situation will end up writing a portablitliy layer, or even two implementations because desktop users will refuse to live with the limitations imposed by by the small-device environment.
          Hide
          Rick Hillegas added a comment -

          Thanks again for the feedback, Dan.

          Dan> I think the parameter style should be more specific than "DERBY", say "DERBY_JDBC_RESULT_SET", there may be other Derby specific types that could be added here, e.g. RSS.

          Sounds good to me. Maybe something shorter like DERBY_JDBC.

          Dan >"When you issue a query against a Table Function, Derby constructs a ResultSetMetaData for the result, based on the column names and datatypes you declared when you initially created the Table Dan >Function."
          Dan > Not sure what this is really trying to say. Why would Derby create a ResultSetMetaData based upon the functions shape, what is this used for?

          I will clarify this in the next rev of the spec. Here's the point I was trying to make. Please let me know if this is still confusing: The user will write a VTI, say myVTI. When the user issues "select * from TABLE( myVTI( ... ) )", Derby will hand back a ResultSet, say an EmbedResultSet20. The original CREATE FUNCTION statement determines the shape of the metadata returned by EmbedResultSet20 regardless of the shape of the metadata returned by myVTI.getResultSetMetaData().

          Dan >I don't see from the functional specification how VTICosting is tied in? What does the app developer do?

          Thanks, I will explain this in the next rev of the spec.

          Dan > How about the Pushable interface, that's useful existing functionality as well?

          I don't see any implementations of Pushable in the Derby diagnostic VTIs. Was this interface ever really used or is it, like VTIEnvironment, part of someone's future plans?

          In any event, I was only spec'ing read-only table functions, that is, ones that implement ResultSet. From its javadoc, Pushable seems to apply to read-write VTIs that implement PreparedStatement.

          Show
          Rick Hillegas added a comment - Thanks again for the feedback, Dan. Dan> I think the parameter style should be more specific than "DERBY", say "DERBY_JDBC_RESULT_SET", there may be other Derby specific types that could be added here, e.g. RSS. Sounds good to me. Maybe something shorter like DERBY_JDBC. Dan >"When you issue a query against a Table Function, Derby constructs a ResultSetMetaData for the result, based on the column names and datatypes you declared when you initially created the Table Dan >Function." Dan > Not sure what this is really trying to say. Why would Derby create a ResultSetMetaData based upon the functions shape, what is this used for? I will clarify this in the next rev of the spec. Here's the point I was trying to make. Please let me know if this is still confusing: The user will write a VTI, say myVTI. When the user issues "select * from TABLE( myVTI( ... ) )", Derby will hand back a ResultSet, say an EmbedResultSet20. The original CREATE FUNCTION statement determines the shape of the metadata returned by EmbedResultSet20 regardless of the shape of the metadata returned by myVTI.getResultSetMetaData(). Dan >I don't see from the functional specification how VTICosting is tied in? What does the app developer do? Thanks, I will explain this in the next rev of the spec. Dan > How about the Pushable interface, that's useful existing functionality as well? I don't see any implementations of Pushable in the Derby diagnostic VTIs. Was this interface ever really used or is it, like VTIEnvironment, part of someone's future plans? In any event, I was only spec'ing read-only table functions, that is, ones that implement ResultSet. From its javadoc, Pushable seems to apply to read-write VTIs that implement PreparedStatement.
          Hide
          A B added a comment -

          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?

          Show
          A B added a comment - 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?
          Hide
          Rick Hillegas added a comment -

          Thanks for the great feedback, Army. Your comments will help simplify the next rev of this spec.

          > Under the "New SELECT Syntax" section:

          You are correct: I left out the parentheses needed by the TABLE constructor in both the syntax description and the example.

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

          Function Table invocations will support the same spectrum of expressions that currently work with the diagnostic VTIs. I will reword this section.

          > Under "Type System"

          Yes, I think we need a new datatype for Function Tables, which will be returned by the corresponding RoutineAliasInfo.getReturnType() method. This returned datatype, in turn, can be used:

          1) by the GetProcedureColumns diagnostic vti, which decodes the RoutineAliasInfo on behalf of java.sql.DatabaseMetaData.getFunctionColumns()

          2) by our bind() logic in order to determine the names and types of columns in the derived table

          Because this datatype is part of RoutineAliasInfo, it will be serialized to SYSALIASES.ALIASINFO.and that is why it has its own Formatable id.

          You are, of course, correct that this is mostly an internal, implementation detail. This new datatype will only appear to users accidentally since it is part of the contents of SYSALIASES.ALIASINFO, which users can select and display. We won't be documenting this in the user guides. I will explain this better in the next rev of the spec.

          This particular implementation seems like a fairly straightforward way to deliver (1) and (2). If you have another idea how to implement (1) and (2), please let me know.

          Show
          Rick Hillegas added a comment - Thanks for the great feedback, Army. Your comments will help simplify the next rev of this spec. > Under the "New SELECT Syntax" section: You are correct: I left out the parentheses needed by the TABLE constructor in both the syntax description and the example. > A standalone VALUES clause can include other types of expressions, as well. For example: Function Table invocations will support the same spectrum of expressions that currently work with the diagnostic VTIs. I will reword this section. > Under "Type System" Yes, I think we need a new datatype for Function Tables, which will be returned by the corresponding RoutineAliasInfo.getReturnType() method. This returned datatype, in turn, can be used: 1) by the GetProcedureColumns diagnostic vti, which decodes the RoutineAliasInfo on behalf of java.sql.DatabaseMetaData.getFunctionColumns() 2) by our bind() logic in order to determine the names and types of columns in the derived table Because this datatype is part of RoutineAliasInfo, it will be serialized to SYSALIASES.ALIASINFO.and that is why it has its own Formatable id. You are, of course, correct that this is mostly an internal, implementation detail. This new datatype will only appear to users accidentally since it is part of the contents of SYSALIASES.ALIASINFO, which users can select and display. We won't be documenting this in the user guides. I will explain this better in the next rev of the spec. This particular implementation seems like a fairly straightforward way to deliver (1) and (2). If you have another idea how to implement (1) and (2), please let me know.
          Hide
          Rick Hillegas added a comment -

          Continuing with Army's feedback:

          > Under "System Tables":

          You are correct. We don't currently use SYSCOLUMNS to describe the parameters and return types of functions and procedures. I can rip out this section and spare myself this chunk of work. Thanks.

          Show
          Rick Hillegas added a comment - Continuing with Army's feedback: > Under "System Tables": You are correct. We don't currently use SYSCOLUMNS to describe the parameters and return types of functions and procedures. I can rip out this section and spare myself this chunk of work. Thanks.
          Hide
          Rick Hillegas added a comment -

          Attaching second rev of the functional spec, incorporating recent feedback.

          Show
          Rick Hillegas added a comment - Attaching second rev of the functional spec, incorporating recent feedback.
          Hide
          A B added a comment -

          Thank you for answering my previous questions, Rick, and for incorporating my feedback into the second version of the spec. I took a look at the latest spec and I have the following questions...

          1) Under "Additional SELECT Syntax"

          "Value - A Value is an expression which could appear as an argument in the invocation
          of one of the diagnostic VTI functions. This includes literals and '?' parameters."

          I wonder if it wouldn't be better to just explicitly state what is allowed here, instead of referencing the diagnostic VTIs? I.e. "Value" can be any expression which evaluates to a single value whose corresponding JDBC type equals the JDBC equivalent of the relevant function parameter's declared SQL type.

          That's a mouthful (you'll probably want to wordsmith it a bit), but as an example:

          CREATE FUNCTION externalEmployees (LAST_NAME VARCHAR(50))
          RETURNS TABLE ...

          The function parameter "LAST_NAME" has a declared SQL type of VARCHAR. The JDBC equivalent to this type is String. Call this PARAM_JDBC_TYPE. Then when calling the function:

          SELECT * FROM TABLE (externalEmployees( <Value> )) as EMP

          <Value> can be any expression that evaluates to a type whose JDBC equivalent is PARAM_JDBC_TYPE. One exception here may be LOBs; I don't think Derby allows passing of LOBs as function parameters?

          In this case PARAM_JDBC_TYPE is "String", so <Value> can be any character expression. And yes, this includes literals and '?' parameters.

          Note that something like:

          SELECT * FROM TABLE (externalEmployees(SELECT DISTINCT 'hi' FROM SYS.SYSTABLES)) as EMP

          would not work because the subquery returns "a result set with a single row", which is not the same as "a single value".

          2) Under "Appendix E: Sample VTI"

          It's great to have an example, so thank you for putting this together. Some initial comments...

          A – The javadoc for the class includes:

          • 3) When you are done siphoning out the rows you need, release the
          • connection to the external database:
            *
          • EmployeeTable.close();

          I don't quite understand who the "you" is in this sentence? It sounds like it's referring to the user, but it seems odd to me to expect that the user is responsible for explicitly calling "close" on the VTI class.

          Is the assumption here that an application will typically execute code such as:

          ResultSet rs = conn.createStatement().executeQuery(
          "select * from TABLE (employeeTable()) emps");

          while (rs.next())

          { ... }

          rs.close();
          EmployeeTable.close();

          If this is not what you had in mind, can you perhaps include an example program that would call the EmployeeTable VTI, process results, and then clean up?

          Intuitively I would expect that a call to "rs.close()" internally leads Derby to call "close()" on the VTI class, sparing the user the need to do so. Which brings me to my next question...

          B – What is "rs" in the following:

          ResultSet rs = conn.createStatement().executeQuery(
          "select * from TABLE (employeeTable()) emps");

          Is it:

          a) The exact same ResultSet object that is returned from EmployeeTable.read()
          b) A Derby ResultSet that somehow wraps the the EmployeeTable VTI
          c) A Derby ResultSet that somehow wraps the ResultSet returned from EmployeeTable.read()
          d) Something else entirely?

          If it's "a" then the user/app would indeed be responsible for calling EmployeeTable.close() explicitly, which seems odd. If it's "b" then Derby can internally propagate "rs.close()" to EmployeeTable.close(), but would not have direct access to the underlying result set (or would it?). If it's "c" then Derby has more control over the behavior of the result set and can propagate calls on "rs" to the underlying (user-defined) ResultSet--but Derby would not be able to call methods on the VTI itself (such as EmployeeTable.close()). Can you say which of these, if any, correlates to your plans for VTIs?

          Thanks for your patience as I try to wrap my head around this...

          Show
          A B added a comment - Thank you for answering my previous questions, Rick, and for incorporating my feedback into the second version of the spec. I took a look at the latest spec and I have the following questions... 1) Under "Additional SELECT Syntax" "Value - A Value is an expression which could appear as an argument in the invocation of one of the diagnostic VTI functions. This includes literals and '?' parameters." I wonder if it wouldn't be better to just explicitly state what is allowed here, instead of referencing the diagnostic VTIs? I.e. "Value" can be any expression which evaluates to a single value whose corresponding JDBC type equals the JDBC equivalent of the relevant function parameter's declared SQL type. That's a mouthful (you'll probably want to wordsmith it a bit), but as an example: CREATE FUNCTION externalEmployees (LAST_NAME VARCHAR(50)) RETURNS TABLE ... The function parameter "LAST_NAME" has a declared SQL type of VARCHAR. The JDBC equivalent to this type is String. Call this PARAM_JDBC_TYPE. Then when calling the function: SELECT * FROM TABLE (externalEmployees( <Value> )) as EMP <Value> can be any expression that evaluates to a type whose JDBC equivalent is PARAM_JDBC_TYPE. One exception here may be LOBs; I don't think Derby allows passing of LOBs as function parameters? In this case PARAM_JDBC_TYPE is "String", so <Value> can be any character expression. And yes, this includes literals and '?' parameters. Note that something like: SELECT * FROM TABLE (externalEmployees(SELECT DISTINCT 'hi' FROM SYS.SYSTABLES)) as EMP would not work because the subquery returns "a result set with a single row", which is not the same as "a single value". 2) Under "Appendix E: Sample VTI" It's great to have an example, so thank you for putting this together. Some initial comments... A – The javadoc for the class includes: 3) When you are done siphoning out the rows you need, release the connection to the external database: * EmployeeTable.close(); I don't quite understand who the "you" is in this sentence? It sounds like it's referring to the user, but it seems odd to me to expect that the user is responsible for explicitly calling "close" on the VTI class. Is the assumption here that an application will typically execute code such as: ResultSet rs = conn.createStatement().executeQuery( "select * from TABLE (employeeTable()) emps"); while (rs.next()) { ... } rs.close(); EmployeeTable.close(); If this is not what you had in mind, can you perhaps include an example program that would call the EmployeeTable VTI, process results, and then clean up? Intuitively I would expect that a call to "rs.close()" internally leads Derby to call "close()" on the VTI class, sparing the user the need to do so. Which brings me to my next question... B – What is "rs" in the following: ResultSet rs = conn.createStatement().executeQuery( "select * from TABLE (employeeTable()) emps"); Is it: a) The exact same ResultSet object that is returned from EmployeeTable.read() b) A Derby ResultSet that somehow wraps the the EmployeeTable VTI c) A Derby ResultSet that somehow wraps the ResultSet returned from EmployeeTable.read() d) Something else entirely? If it's "a" then the user/app would indeed be responsible for calling EmployeeTable.close() explicitly, which seems odd. If it's "b" then Derby can internally propagate "rs.close()" to EmployeeTable.close(), but would not have direct access to the underlying result set (or would it?). If it's "c" then Derby has more control over the behavior of the result set and can propagate calls on "rs" to the underlying (user-defined) ResultSet--but Derby would not be able to call methods on the VTI itself (such as EmployeeTable.close()). Can you say which of these, if any, correlates to your plans for VTIs? Thanks for your patience as I try to wrap my head around this...
          Hide
          Daniel John Debrunner added a comment -

          Why is the VTIEnvironment class being removed from the VTICosting interface? Does it cause issues in some way?

          The separation of the creation of the VTICosting object from the creation of the VTI class means that the costing cannot take into
          account the parameters being passed to the table function. Thus it might be hard for an application developer to have any meaningful
          costing information, defeating the whole purpose of the interface.

          It also limits the any class to supporting just one static method that returns a ResultSet, unless they can all share the same exact costing information.

          Show
          Daniel John Debrunner added a comment - Why is the VTIEnvironment class being removed from the VTICosting interface? Does it cause issues in some way? The separation of the creation of the VTICosting object from the creation of the VTI class means that the costing cannot take into account the parameters being passed to the table function. Thus it might be hard for an application developer to have any meaningful costing information, defeating the whole purpose of the interface. It also limits the any class to supporting just one static method that returns a ResultSet, unless they can all share the same exact costing information.
          Hide
          Rick Hillegas added a comment -

          Thanks for the continued feedback, Army.

          > 1) Under "Additional SELECT Syntax"

          I welcome additional ideas about how to wordsmith this. I'm trying to accomplish the following:

          a) Indicate that I'm not planning to support any syntax that doesn't currently work for the diagnostic vtis--at the same time, I'm not planning to disable anything that is useful and already implemented.

          b) Sketch what can be said about this in the user manuals.

          I think I have accomplished (a) but I agree that (b) is a bit fuzzy. To me, the wording that you suggest doesn't cover the following case:

          select s.schemaName, t.tableName
          from sys.sysschemas s, sys.systables t
          where t.schemaid=s.schemaid
          and exists
          (
          select vti.*
          from table ( syscs_diag.space_table( s.schemaName, t.tableName ) ) as vti
          where vti.numfreepages > 100
          );

          Here the arguments to the VTI constructor are variables in the context of the outer query block but constants in the context of the inner block. What do you think of something like the following: "Table Function arguments must resolve to expressions which are evaluated once in the context of their query block. This includes literals and ? parameters but may also include the return values of function calls as well as correlated references to columns in outer query blocks."

          > 2) Under "Appendix E: Sample VTI"

          I am sorry that this is so confusing. In my example, I am admitedly waving my hands over the complexity of managing connections to an external database. This is not how someone would really write this VTI. In additon to the awkwardness of closing down the whole VTI, this example is simply not re-entrant: If two different connections tried to use this VTI, they would trip over one another. Writing a bullet-proof VTI like this requires some work, which I think someone will want to do (and hopefully donate to the community). I'm not taking on that task as part of writing this functional spec. I will add some words to note that I'm waving my hands here.

          Show
          Rick Hillegas added a comment - Thanks for the continued feedback, Army. > 1) Under "Additional SELECT Syntax" I welcome additional ideas about how to wordsmith this. I'm trying to accomplish the following: a) Indicate that I'm not planning to support any syntax that doesn't currently work for the diagnostic vtis--at the same time, I'm not planning to disable anything that is useful and already implemented. b) Sketch what can be said about this in the user manuals. I think I have accomplished (a) but I agree that (b) is a bit fuzzy. To me, the wording that you suggest doesn't cover the following case: select s.schemaName, t.tableName from sys.sysschemas s, sys.systables t where t.schemaid=s.schemaid and exists ( select vti.* from table ( syscs_diag.space_table( s.schemaName, t.tableName ) ) as vti where vti.numfreepages > 100 ); Here the arguments to the VTI constructor are variables in the context of the outer query block but constants in the context of the inner block. What do you think of something like the following: "Table Function arguments must resolve to expressions which are evaluated once in the context of their query block. This includes literals and ? parameters but may also include the return values of function calls as well as correlated references to columns in outer query blocks." > 2) Under "Appendix E: Sample VTI" I am sorry that this is so confusing. In my example, I am admitedly waving my hands over the complexity of managing connections to an external database. This is not how someone would really write this VTI. In additon to the awkwardness of closing down the whole VTI, this example is simply not re-entrant: If two different connections tried to use this VTI, they would trip over one another. Writing a bullet-proof VTI like this requires some work, which I think someone will want to do (and hopefully donate to the community). I'm not taking on that task as part of writing this functional spec. I will add some words to note that I'm waving my hands here.
          Hide
          A B added a comment -

          > To me, the wording that you suggest doesn't cover the following case:

          Can you explain more about why the example is not covered? Is it because of my use of the term "single value"? If so, we could take that part out:

          Let P[i] be the i-th declared parameter for some table function. Let A[i] be the i-th argument
          passed to the table function when it is called.

          A table function argument A[i] can be any expression whose corresponding JDBC type is the same
          as the JDBC type which corresponds to the SQL type of the function's declared parameter P[i].

          In the example you give, "s.schemaName" and "t.tableName" are simply expressions (in this case, column references) whose corresponding JDBC type is String, hence they are fine (because the JDBC type of the SPACE_TABLE parameters is String, too).

          > What do you think of something like the following: "Table Function
          > arguments must resolve to expressions which are evaluated once in the
          > context of their query block. This includes literals and ? parameters
          > but may also include the return values of function calls as well as
          > correlated references to columns in outer query blocks.

          This seems too concentrated on the idea of "evaluated once". The important thing here isn't how many times the expression is evaluated for a given query; it's that the expression's datatype match the datatype of the declared function parameter. Sorry if my previous suggestion made it seem otherwise...

          > I will add some words to note that I'm waving my hands here.

          On the one hand I agree, having a solid example is not the goal of the spec. On the other hand, if we can't come up with a solid example, I wonder how complete/appropriate any proposed solution will end up being? If we cannot get a good use case of how this feature might be used, it makes it hard to know whether or not the design is going to be a good one.

          An example doesn't have to do anything complex like reference an external database. It could just be something really simple that, for example, creates a 2-d array of strings and returns that as a ResultSet. Proof of concept is what I'm hoping for. As it is, I can't get a good feel for how the proposed VTIs are actually supposed to be created work from a user perspective...

          Show
          A B added a comment - > To me, the wording that you suggest doesn't cover the following case: Can you explain more about why the example is not covered? Is it because of my use of the term "single value"? If so, we could take that part out: Let P [i] be the i-th declared parameter for some table function. Let A [i] be the i-th argument passed to the table function when it is called. A table function argument A [i] can be any expression whose corresponding JDBC type is the same as the JDBC type which corresponds to the SQL type of the function's declared parameter P [i] . In the example you give, "s.schemaName" and "t.tableName" are simply expressions (in this case, column references) whose corresponding JDBC type is String, hence they are fine (because the JDBC type of the SPACE_TABLE parameters is String, too). > What do you think of something like the following: "Table Function > arguments must resolve to expressions which are evaluated once in the > context of their query block. This includes literals and ? parameters > but may also include the return values of function calls as well as > correlated references to columns in outer query blocks. This seems too concentrated on the idea of "evaluated once". The important thing here isn't how many times the expression is evaluated for a given query; it's that the expression's datatype match the datatype of the declared function parameter. Sorry if my previous suggestion made it seem otherwise... > I will add some words to note that I'm waving my hands here. On the one hand I agree, having a solid example is not the goal of the spec. On the other hand, if we can't come up with a solid example, I wonder how complete/appropriate any proposed solution will end up being? If we cannot get a good use case of how this feature might be used, it makes it hard to know whether or not the design is going to be a good one. An example doesn't have to do anything complex like reference an external database. It could just be something really simple that, for example, creates a 2-d array of strings and returns that as a ResultSet. Proof of concept is what I'm hoping for. As it is, I can't get a good feel for how the proposed VTIs are actually supposed to be created work from a user perspective...
          Hide
          Daniel John Debrunner added a comment -

          > A table function argument A[i] can be any expression whose corresponding JDBC type is the same
          > as the JDBC type which corresponds to the SQL type of the function's declared parameter P[i].

          Why is JDBC being mentioned here? Parameters for table functions are declared as SQL types
          and table functions are executed in SQL statements. There is no relationship to JDBC.

          JDBC only defines the mapping of the declared parameter type to the Java type, it does not affect
          the SQL compilation/binding.

          Show
          Daniel John Debrunner added a comment - > A table function argument A [i] can be any expression whose corresponding JDBC type is the same > as the JDBC type which corresponds to the SQL type of the function's declared parameter P [i] . Why is JDBC being mentioned here? Parameters for table functions are declared as SQL types and table functions are executed in SQL statements. There is no relationship to JDBC. JDBC only defines the mapping of the declared parameter type to the Java type, it does not affect the SQL compilation/binding.
          Hide
          A B added a comment -

          Oops, thanks. Meant "Java type" in all of these cases, not "JDBC type".

          Show
          A B added a comment - Oops, thanks. Meant "Java type" in all of these cases, not "JDBC type".
          Hide
          Daniel John Debrunner added a comment -

          Still not sure "Java type" is correct. Is that really the rule for deciding if the arguments can be mapped to the parameter type?

          For regular (non-table) functions and procedures the check is made to see if type of the SQL argument can be stored in the
          type of the SQL parameter (defined in the routine's CREATE statement). Maybe checking the java types would lead to the
          same answer, but logically it's a check of SQL types only.

          Table functions should follow the same logic as regular functions.

          See this check in StaticMethodCallNode.java

          if (! getTypeCompiler(parameterTypeId).storable(argumentTypeId, getClassFactory()))
          throw StandardException.newException(SQLState.LANG_NOT_STORABLE,
          parameterTypeId.getSQLTypeName(),
          argumentTypeId.getSQLTypeName() );

          Show
          Daniel John Debrunner added a comment - Still not sure "Java type" is correct. Is that really the rule for deciding if the arguments can be mapped to the parameter type? For regular (non-table) functions and procedures the check is made to see if type of the SQL argument can be stored in the type of the SQL parameter (defined in the routine's CREATE statement). Maybe checking the java types would lead to the same answer, but logically it's a check of SQL types only. Table functions should follow the same logic as regular functions. See this check in StaticMethodCallNode.java if (! getTypeCompiler(parameterTypeId).storable(argumentTypeId, getClassFactory())) throw StandardException.newException(SQLState.LANG_NOT_STORABLE, parameterTypeId.getSQLTypeName(), argumentTypeId.getSQLTypeName() );
          Hide
          Rick Hillegas added a comment -

          Thanks for the continued feedback, Dan.

          > Why is the VTIEnvironment class being removed from the VTICosting interface? Does it cause issues in some way?

          I had a couple issues with this class:

          1) I don't know what to tell users who want to exploit this argument in their costing logic. The javadoc is not very helpful. In addition I couldn't find any explanation of this class in the Cloudscape 3.5 documentation which I consulted: There the class is mentioned as having been added for future expansion, but the methods are not explained. I could not find any examples of its actually being used by our diagnostic VTIs.

          2) One of the methods in this class, isCompileTime(), seems geared toward the old Cloudscape VTIs, which were instantiated twice: at compile-time in order to bind() against the signature of the ResultSet, and at run-time in order to actually loop through the rows. This doesn't fit the ANSI scheme in which the bind() time information is declared when you CREATE the Function Table.

          > The separation of the creation of the VTICosting object from the creation of the VTI class means that the costing cannot take into
          > account the parameters being passed to the table function. Thus it might be hard for an application developer to have any meaningful
          > costing information, defeating the whole purpose of the interface.
          >
          > It also limits the any class to supporting just one static method that returns a ResultSet, unless they can all share the same exact costing information.

          I'm not happy with the flexibility of VTICosting and I welcome brainstorming on this topic.

          The signature of the VTICosting instantiator is a tricky issue. Consider the example in my reply to Army above. Here the arguments to the VTI are not known until run time. In fact, they change as the query runs and the VTI is re-instantiated for each row in the outer query block. In this case, what would be the signature of the VTICosting instantiator?

          For the moment, let's not worry about where we find the VTICosting instantiator. This could be a constructor in the Function Table's class, a distinctively named static method in that class, some other class or method bound to the Function Table via a system procedure, etc..

          Instead, let's focus on the signature of the VTICosting instantiator. Here are some possibilities:

          A) A 0-arg signature. This is essentially what the current spec proposes.

          B) Some Derby interface like VTIEnvironment. The interface would have to be documented extensively.

          C) The same signature as the Function Table itself. We would substitute conventional defaults for arguments which could not be computed at bind() time--like ? parameters and correlated column references from outer query blocks.

          D) A leading prefix of the Function Table's signature. Here we would raise an exception if one of the leading arguments could not be computed at bind() time.

          What are your thoughts?

          Show
          Rick Hillegas added a comment - Thanks for the continued feedback, Dan. > Why is the VTIEnvironment class being removed from the VTICosting interface? Does it cause issues in some way? I had a couple issues with this class: 1) I don't know what to tell users who want to exploit this argument in their costing logic. The javadoc is not very helpful. In addition I couldn't find any explanation of this class in the Cloudscape 3.5 documentation which I consulted: There the class is mentioned as having been added for future expansion, but the methods are not explained. I could not find any examples of its actually being used by our diagnostic VTIs. 2) One of the methods in this class, isCompileTime(), seems geared toward the old Cloudscape VTIs, which were instantiated twice: at compile-time in order to bind() against the signature of the ResultSet, and at run-time in order to actually loop through the rows. This doesn't fit the ANSI scheme in which the bind() time information is declared when you CREATE the Function Table. > The separation of the creation of the VTICosting object from the creation of the VTI class means that the costing cannot take into > account the parameters being passed to the table function. Thus it might be hard for an application developer to have any meaningful > costing information, defeating the whole purpose of the interface. > > It also limits the any class to supporting just one static method that returns a ResultSet, unless they can all share the same exact costing information. I'm not happy with the flexibility of VTICosting and I welcome brainstorming on this topic. The signature of the VTICosting instantiator is a tricky issue. Consider the example in my reply to Army above. Here the arguments to the VTI are not known until run time. In fact, they change as the query runs and the VTI is re-instantiated for each row in the outer query block. In this case, what would be the signature of the VTICosting instantiator? For the moment, let's not worry about where we find the VTICosting instantiator. This could be a constructor in the Function Table's class, a distinctively named static method in that class, some other class or method bound to the Function Table via a system procedure, etc.. Instead, let's focus on the signature of the VTICosting instantiator. Here are some possibilities: A) A 0-arg signature. This is essentially what the current spec proposes. B) Some Derby interface like VTIEnvironment. The interface would have to be documented extensively. C) The same signature as the Function Table itself. We would substitute conventional defaults for arguments which could not be computed at bind() time--like ? parameters and correlated column references from outer query blocks. D) A leading prefix of the Function Table's signature. Here we would raise an exception if one of the leading arguments could not be computed at bind() time. What are your thoughts?
          Hide
          Rick Hillegas added a comment -

          Thanks, Dan and Army, for the continued discussion of the parameters to Function Tables.

          Army and I seem to be concerned about different issues here. I am not concerned about the type resolution of arguments to the Function Tables. This seems to me to be exactly the same resolution logic which applies to existing (non-table) functions. I am not proposing to change that logic. If the user guides don't adequately describe the type resolution of function arguments, then that is another issue and it is someone else's itch.

          I am concerned about the fact that certain expressions can appear in the arguments to non-table functions but those expressions can not appear in the arguments to Function Tables. For instance,

          select *
          from T, TABLE( foo( T.a ) )
          where bar( T.a ) = 3;

          Here the expression T.a is a legal argument to bar() but not to foo().

          I hope we are not talking past one another.

          Show
          Rick Hillegas added a comment - Thanks, Dan and Army, for the continued discussion of the parameters to Function Tables. Army and I seem to be concerned about different issues here. I am not concerned about the type resolution of arguments to the Function Tables. This seems to me to be exactly the same resolution logic which applies to existing (non-table) functions. I am not proposing to change that logic. If the user guides don't adequately describe the type resolution of function arguments, then that is another issue and it is someone else's itch. I am concerned about the fact that certain expressions can appear in the arguments to non-table functions but those expressions can not appear in the arguments to Function Tables. For instance, select * from T, TABLE( foo( T.a ) ) where bar( T.a ) = 3; Here the expression T.a is a legal argument to bar() but not to foo(). I hope we are not talking past one another.
          Hide
          Daniel John Debrunner added a comment -

          VTIEnvironment was (is?) meant to be a general purpose class that provides some control over & information for virtual tables, it was meant to be applicable to more than costing, despite it's class javadoc. see how it is used on the other interfaces that are applicable to virtual tables. Keeping the class may allow future expansion, removing it would hinder future expansion.

          isCompileTime() may provide useful state information if use of this class is expanded in the future, though it may be ok to remove the method, since it could be added back to the class at any time. Removing the class from the methods passed to VTICosting would make it harder to add back in the future.

          Show
          Daniel John Debrunner added a comment - VTIEnvironment was (is?) meant to be a general purpose class that provides some control over & information for virtual tables, it was meant to be applicable to more than costing, despite it's class javadoc. see how it is used on the other interfaces that are applicable to virtual tables. Keeping the class may allow future expansion, removing it would hinder future expansion. isCompileTime() may provide useful state information if use of this class is expanded in the future, though it may be ok to remove the method, since it could be added back to the class at any time. Removing the class from the methods passed to VTICosting would make it harder to add back in the future.
          Hide
          Rick Hillegas added a comment -

          I searched the Derby codeline for all of the method names in VTIEnvironment. They do not appear to be invoked anywhere although they are declared in the VTIEnvironment interface itself and in FromVTI and in VTIResultSet.

          There may be some value in exposing a vacuous VTIEnvironment interface, which has no methods in it but which would be a placeholder for future expansion. I don't see the value in retaining the existing, unused methods.

          The unused getSharedState() and setSharedState() methods suggest that the whole thing might be replaced with a Hashtable.

          Show
          Rick Hillegas added a comment - I searched the Derby codeline for all of the method names in VTIEnvironment. They do not appear to be invoked anywhere although they are declared in the VTIEnvironment interface itself and in FromVTI and in VTIResultSet. There may be some value in exposing a vacuous VTIEnvironment interface, which has no methods in it but which would be a placeholder for future expansion. I don't see the value in retaining the existing, unused methods. The unused getSharedState() and setSharedState() methods suggest that the whole thing might be replaced with a Hashtable.
          Hide
          A B added a comment -

          Dan> Maybe checking the java types would lead to the same answer, but logically it's a check of
          Dan> SQL types only. Table functions should follow the same logic as regular functions.

          Okay.

          Rick> I hope we are not talking past one another.

          Oops, I think we were.

          Thank you both for clarifying...

          Show
          A B added a comment - Dan> Maybe checking the java types would lead to the same answer, but logically it's a check of Dan> SQL types only. Table functions should follow the same logic as regular functions. Okay. Rick> I hope we are not talking past one another. Oops, I think we were. Thank you both for clarifying...
          Hide
          Rick Hillegas added a comment -

          Maybe the VTICosting instantiator could have a signature like this and it would be up to the implementation to throw a SQLException if there isn't enough information to cost the VTI:

          public static VTICosting getVTICosting
          (
          String schemaName, // table function's schema as declared at CREATE FUNCTION time
          String tableFunctionName, // table function's name as declared at CREATE FUNCTION time
          HashMap functionArguments // key = arg name from CREATE FUNCTION, value = bind() time value, possibly null if bind() can't figure it out
          )
          throws SQLException;

          Show
          Rick Hillegas added a comment - Maybe the VTICosting instantiator could have a signature like this and it would be up to the implementation to throw a SQLException if there isn't enough information to cost the VTI: public static VTICosting getVTICosting ( String schemaName, // table function's schema as declared at CREATE FUNCTION time String tableFunctionName, // table function's name as declared at CREATE FUNCTION time HashMap functionArguments // key = arg name from CREATE FUNCTION, value = bind() time value, possibly null if bind() can't figure it out ) throws SQLException;
          Hide
          Rick Hillegas added a comment -

          Thanks for helping me puzzle through the usage of VTIEnvironment, Dan. How does the following sound:

          1) Leave the VTIEnvironment arguments in the VTICosting methods.

          2) Remove isCompileTime() from VTIEnvironment.

          3) Beef up the javadoc for VTIEnvironment:

          a) Note that it is information used only by user-written costing logic.
          b) It allows user-written costing methods to share information with each other.
          c) Its lifetime is the lifetime of a statement plan in Derby's statement cache. If the invoking statement has to be recompiled, then the VTIEnvironment variable can be used to share information across re-compilations.

          If we are going to use VTIEnvironment, then I think the user guides should give an example of how to exploit this variable.

          Show
          Rick Hillegas added a comment - Thanks for helping me puzzle through the usage of VTIEnvironment, Dan. How does the following sound: 1) Leave the VTIEnvironment arguments in the VTICosting methods. 2) Remove isCompileTime() from VTIEnvironment. 3) Beef up the javadoc for VTIEnvironment: a) Note that it is information used only by user-written costing logic. b) It allows user-written costing methods to share information with each other. c) Its lifetime is the lifetime of a statement plan in Derby's statement cache. If the invoking statement has to be recompiled, then the VTIEnvironment variable can be used to share information across re-compilations. If we are going to use VTIEnvironment, then I think the user guides should give an example of how to exploit this variable.
          Hide
          Rick Hillegas added a comment -

          Attaching 3rd rev of the functional spec. This incorporates feedback from Army and Dan.

          Notably, I have restored the VTIEnvironment argument to the signatures of the VTICosting methods. I think that we could use VTIEnvironment to carry the function-invocation args whose values are known at optimize() time. This deserves more discussion and would be, I think, a related, follow-on JIRA.

          Show
          Rick Hillegas added a comment - Attaching 3rd rev of the functional spec. This incorporates feedback from Army and Dan. Notably, I have restored the VTIEnvironment argument to the signatures of the VTICosting methods. I think that we could use VTIEnvironment to carry the function-invocation args whose values are known at optimize() time. This deserves more discussion and would be, I think, a related, follow-on JIRA.
          Hide
          Rick Hillegas added a comment -

          Attaching a patch which adds basic support for Derby-style Table Functions. This patch allows you to declare user-defined Table Functions and to invoke them in simple SELECT statements. I've included a JUnit test for this functionality. The existing regression tests pass cleanly for me.

          A description of the contents of this patch follows:

          -----------------------------------------------------------

          (1) Created a new datatype, RowMultiSetImpl, to represent the return type of a Table Function.

          M java/engine/org/apache/derby/iapi/services/io/RegisteredFormatIds.java
          M java/engine/org/apache/derby/iapi/services/io/StoredFormatIds.java
          M java/engine/org/apache/derby/iapi/types/TypeId.java
          M java/engine/org/apache/derby/iapi/types/DataTypeDescriptor.java
          M java/engine/org/apache/derby/iapi/types/DTSClassInfo.java
          M java/engine/org/apache/derby/catalog/TypeDescriptor.java
          A java/engine/org/apache/derby/catalog/types/RowMultiSetImpl.java
          M java/engine/org/apache/derby/catalog/types/TypeDescriptorImpl.java
          M java/engine/org/apache/derby/catalog/types/BaseTypeIdImpl.java

          -----------------------------------------------------------

          (2) Added new syntax for declaring Derby-style Table Functions.

          M java/engine/org/apache/derby/iapi/sql/dictionary/DataDictionary.java
          M java/engine/org/apache/derby/impl/sql/catalog/DataDictionaryImpl.java
          M java/storeless/org/apache/derby/impl/storeless/EmptyDictionary.java

          Factored out the check for builtin VTIs so that the parser can know whether to build nodes for a constructor-based VTI or a static-method-based VTI.

          M java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj
          M java/engine/org/apache/derby/iapi/sql/dictionary/AliasDescriptor.java
          M java/engine/org/apache/derby/catalog/types/RoutineAliasInfo.java

          Parser support for Table Function DDL.

          -----------------------------------------------------------

          (3) Supported the invocation of Table Functions in simple SELECTs.

          M java/engine/org/apache/derby/impl/sql/compile/MethodCallNode.java
          M java/engine/org/apache/derby/impl/sql/compile/VTIDeferModPolicy.java
          M java/engine/org/apache/derby/impl/sql/compile/NewInvocationNode.java
          M java/engine/org/apache/derby/impl/sql/compile/FromVTI.java

          Moved some bind() logic from NewInvocationNode into its superclass, MethodCallNode. The previous scheme allowed the invocation of VTIs which are constructors. The new scheme also allows the invocation of VTIs which are static methods.

          M java/engine/org/apache/derby/impl/sql/compile/ResultColumnList.java

          Factored out some code which builds ResultColumns. The newly factored-out method is used to bind() the shape of the return value of a Table Function.

          M java/engine/org/apache/derby/impl/sql/compile/StaticMethodCallNode.java

          Don't try to cast returned ResultSets.

          M java/engine/org/apache/derby/iapi/sql/execute/ResultSetFactory.java
          M java/engine/org/apache/derby/impl/sql/execute/GenericResultSetFactory.java
          M java/engine/org/apache/derby/impl/sql/execute/VTIResultSet.java

          Accounted for the fact that the returned columns of Table Functions are nullable according to the SQL standard.

          -----------------------------------------------------------

          (4) Added new error messages.

          M java/engine/org/apache/derby/loc/messages.xml
          M java/shared/org/apache/derby/shared/common/reference/SQLState.java

          -----------------------------------------------------------

          (5) Added a JUnit test for this functionality.

          A java/testing/org/apache/derbyTesting/functionTests/tests/lang/TableFunctionTest.java
          A java/testing/org/apache/derbyTesting/functionTests/tests/lang/StringColumnVTI.java
          M java/testing/org/apache/derbyTesting/functionTests/tests/lang/_Suite.java
          M java/testing/org/apache/derbyTesting/functionTests/tests/lang/SysDiagVTIMappingTest.java
          A java/testing/org/apache/derbyTesting/functionTests/tests/lang/StringArrayVTI.java
          M java/testing/org/apache/derbyTesting/junit/BaseTestCase.java

          Show
          Rick Hillegas added a comment - Attaching a patch which adds basic support for Derby-style Table Functions. This patch allows you to declare user-defined Table Functions and to invoke them in simple SELECT statements. I've included a JUnit test for this functionality. The existing regression tests pass cleanly for me. A description of the contents of this patch follows: ----------------------------------------------------------- (1) Created a new datatype, RowMultiSetImpl, to represent the return type of a Table Function. M java/engine/org/apache/derby/iapi/services/io/RegisteredFormatIds.java M java/engine/org/apache/derby/iapi/services/io/StoredFormatIds.java M java/engine/org/apache/derby/iapi/types/TypeId.java M java/engine/org/apache/derby/iapi/types/DataTypeDescriptor.java M java/engine/org/apache/derby/iapi/types/DTSClassInfo.java M java/engine/org/apache/derby/catalog/TypeDescriptor.java A java/engine/org/apache/derby/catalog/types/RowMultiSetImpl.java M java/engine/org/apache/derby/catalog/types/TypeDescriptorImpl.java M java/engine/org/apache/derby/catalog/types/BaseTypeIdImpl.java ----------------------------------------------------------- (2) Added new syntax for declaring Derby-style Table Functions. M java/engine/org/apache/derby/iapi/sql/dictionary/DataDictionary.java M java/engine/org/apache/derby/impl/sql/catalog/DataDictionaryImpl.java M java/storeless/org/apache/derby/impl/storeless/EmptyDictionary.java Factored out the check for builtin VTIs so that the parser can know whether to build nodes for a constructor-based VTI or a static-method-based VTI. M java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj M java/engine/org/apache/derby/iapi/sql/dictionary/AliasDescriptor.java M java/engine/org/apache/derby/catalog/types/RoutineAliasInfo.java Parser support for Table Function DDL. ----------------------------------------------------------- (3) Supported the invocation of Table Functions in simple SELECTs. M java/engine/org/apache/derby/impl/sql/compile/MethodCallNode.java M java/engine/org/apache/derby/impl/sql/compile/VTIDeferModPolicy.java M java/engine/org/apache/derby/impl/sql/compile/NewInvocationNode.java M java/engine/org/apache/derby/impl/sql/compile/FromVTI.java Moved some bind() logic from NewInvocationNode into its superclass, MethodCallNode. The previous scheme allowed the invocation of VTIs which are constructors. The new scheme also allows the invocation of VTIs which are static methods. M java/engine/org/apache/derby/impl/sql/compile/ResultColumnList.java Factored out some code which builds ResultColumns. The newly factored-out method is used to bind() the shape of the return value of a Table Function. M java/engine/org/apache/derby/impl/sql/compile/StaticMethodCallNode.java Don't try to cast returned ResultSets. M java/engine/org/apache/derby/iapi/sql/execute/ResultSetFactory.java M java/engine/org/apache/derby/impl/sql/execute/GenericResultSetFactory.java M java/engine/org/apache/derby/impl/sql/execute/VTIResultSet.java Accounted for the fact that the returned columns of Table Functions are nullable according to the SQL standard. ----------------------------------------------------------- (4) Added new error messages. M java/engine/org/apache/derby/loc/messages.xml M java/shared/org/apache/derby/shared/common/reference/SQLState.java ----------------------------------------------------------- (5) Added a JUnit test for this functionality. A java/testing/org/apache/derbyTesting/functionTests/tests/lang/TableFunctionTest.java A java/testing/org/apache/derbyTesting/functionTests/tests/lang/StringColumnVTI.java M java/testing/org/apache/derbyTesting/functionTests/tests/lang/_Suite.java M java/testing/org/apache/derbyTesting/functionTests/tests/lang/SysDiagVTIMappingTest.java A java/testing/org/apache/derbyTesting/functionTests/tests/lang/StringArrayVTI.java M java/testing/org/apache/derbyTesting/junit/BaseTestCase.java
          Hide
          Rick Hillegas added a comment -

          Committed derby-716-01-basic-aa.diff at subversion revision 555032. I merged again today. All of the regression tests ran cleanly for me except for the wisconsin test. That test shows the following diff. It shows this diff even in a clean subversion client without my patch:

          28712a28713
          > 0
          28715a28717
          > 0
          Test Failed.

          Show
          Rick Hillegas added a comment - Committed derby-716-01-basic-aa.diff at subversion revision 555032. I merged again today. All of the regression tests ran cleanly for me except for the wisconsin test. That test shows the following diff. It shows this diff even in a clean subversion client without my patch: 28712a28713 > 0 28715a28717 > 0 Test Failed.
          Hide
          Daniel John Debrunner added a comment - - edited

          Is there a need to add isRowMultiSet() to TypeDescriptor? It looks out of place in that interface.
          All the other isXXXX() methods are on TypeId, and in fact the DataTypeDescriptor.isRowMultiSet() just calls the TypeId method.
          Also the TypeDescriptorImpl version just returns false which looks wrong, or an indication the method should not be on TypeDescriptor.

          Show
          Daniel John Debrunner added a comment - - edited Is there a need to add isRowMultiSet() to TypeDescriptor? It looks out of place in that interface. All the other isXXXX() methods are on TypeId, and in fact the DataTypeDescriptor.isRowMultiSet() just calls the TypeId method. Also the TypeDescriptorImpl version just returns false which looks wrong, or an indication the method should not be on TypeDescriptor.
          Hide
          Rick Hillegas added a comment -

          Thanks for taking a look at this patch, Dan. I think that isRowMultiSet() could be removed from TypeDescriptor. It's in there so that AliasDescriptor doesn't have to cast a TypeDescriptor to DataTypeDescriptor. I'm comfortable with that cast and agree that isRowMutliSet() looks awkward in TypeDescriptorImpl.

          Show
          Rick Hillegas added a comment - Thanks for taking a look at this patch, Dan. I think that isRowMultiSet() could be removed from TypeDescriptor. It's in there so that AliasDescriptor doesn't have to cast a TypeDescriptor to DataTypeDescriptor. I'm comfortable with that cast and agree that isRowMutliSet() looks awkward in TypeDescriptorImpl.
          Hide
          Daniel John Debrunner added a comment -

          I see that isRowMultiSet is used to indicate the function is a table function. Would it not be clearer to have an explict state in RoutineAliasInfo that the function is a table function, rather than overloading the return type to indicate this?

          Also look at DERBY-2917, I'm trying to separate TypeDescriptor & DataTypeDescriptor, thus the cast you didn't want to add won't be valid soon.

          Show
          Daniel John Debrunner added a comment - I see that isRowMultiSet is used to indicate the function is a table function. Would it not be clearer to have an explict state in RoutineAliasInfo that the function is a table function, rather than overloading the return type to indicate this? Also look at DERBY-2917 , I'm trying to separate TypeDescriptor & DataTypeDescriptor, thus the cast you didn't want to add won't be valid soon.
          Hide
          Daniel John Debrunner added a comment -

          I see the type system details in the functional spec, but it's lacking some details. With a multiset type

          • What infomation will be stored in TypeDescriptorImpl, e.g. scale, precision, type name etc.
          • how does code access the types & names in the multiset?
          Show
          Daniel John Debrunner added a comment - I see the type system details in the functional spec, but it's lacking some details. With a multiset type What infomation will be stored in TypeDescriptorImpl, e.g. scale, precision, type name etc. how does code access the types & names in the multiset?
          Hide
          Rick Hillegas added a comment -

          Hi Dan. I have added a comment to derby-2917. Thanks for tackling that project. I am very interested in this conversation. Unfortunately, tomorrow is my last day before I go on vacation (and then a conference) for two and a half weeks. So, please don't be put off by my impending radio-silence.

          > I see that isRowMultiSet is used to indicate the function is a table function. Would it not be clearer to have an explict state in RoutineAliasInfo that the function is a table function, rather than overloading the return type to indicate this?

          It seemed to me that a Table Function was just a function which returned a Row Multi Set. I think it would certainly be reasonable to add an isTableFunction() method to RoutineAliasInfo. However, to avoid duplicating state, I think that that method would just turn around and inspect the return type to see if it were a Row Multi Set.

          > - What infomation will be stored in TypeDescriptorImpl, e.g. scale, precision, type name etc.

          I'm not sure that a TypeDescriptorImpl would ever be built for a Row Multi Set as part of implementing Table Functions. The return type is never used at runtime and is only briefly inspected at compilation time in order to build the shape of the returned Table. I think you have created derby-2917 because it seems to you, too, that it's hard to understand how behavior is divided between the types in the catalog package and the types which actually are persisted to the catalogs.

          > how does code access the types & names in the multiset?

          This is done in FromVTI.createResultColumnsForTableFunction().

          Show
          Rick Hillegas added a comment - Hi Dan. I have added a comment to derby-2917. Thanks for tackling that project. I am very interested in this conversation. Unfortunately, tomorrow is my last day before I go on vacation (and then a conference) for two and a half weeks. So, please don't be put off by my impending radio-silence. > I see that isRowMultiSet is used to indicate the function is a table function. Would it not be clearer to have an explict state in RoutineAliasInfo that the function is a table function, rather than overloading the return type to indicate this? It seemed to me that a Table Function was just a function which returned a Row Multi Set. I think it would certainly be reasonable to add an isTableFunction() method to RoutineAliasInfo. However, to avoid duplicating state, I think that that method would just turn around and inspect the return type to see if it were a Row Multi Set. > - What infomation will be stored in TypeDescriptorImpl, e.g. scale, precision, type name etc. I'm not sure that a TypeDescriptorImpl would ever be built for a Row Multi Set as part of implementing Table Functions. The return type is never used at runtime and is only briefly inspected at compilation time in order to build the shape of the returned Table. I think you have created derby-2917 because it seems to you, too, that it's hard to understand how behavior is divided between the types in the catalog package and the types which actually are persisted to the catalogs. > how does code access the types & names in the multiset? This is done in FromVTI.createResultColumnsForTableFunction().
          Hide
          Rick Hillegas added a comment -

          Attaching derby-716-02-DatabaseMetaData-aa.diff. This patch adds TableFunction support to our implementation of DatabaseMetaData.getFunctions() and getFunctionColumns(). Touches the following files:

          M java/engine/org/apache/derby/catalog/types/RoutineAliasInfo.java

          Add a new method so that metadata queries can determine whether a function is a TableFunction.

          M java/engine/org/apache/derby/impl/jdbc/metadata.properties
          M java/engine/org/apache/derby/catalog/GetProcedureColumns.java

          Amend the queries and the dbproc which provide the results for getFunctions() and getFunctionColumns().

          M java/shared/org/apache/derby/shared/common/reference/JDBC40Translation.java
          M java/testing/org/apache/derbyTesting/functionTests/tests/lang/TableFunctionTest.java
          M java/testing/org/apache/derbyTesting/functionTests/tests/jdbc4/TestDbMetaData.java
          M java/testing/org/apache/derbyTesting/junit/JDBC.java

          Regrssion tests.

          The regression tests ran cleanly for me under Java 6 except for the outstanding orphaned ResultSet problem in ProcedureInTriggerTest. That test runs cleanly standalone. This is the same regression test behavior seen in DERBY-2983.

          Show
          Rick Hillegas added a comment - Attaching derby-716-02-DatabaseMetaData-aa.diff. This patch adds TableFunction support to our implementation of DatabaseMetaData.getFunctions() and getFunctionColumns(). Touches the following files: M java/engine/org/apache/derby/catalog/types/RoutineAliasInfo.java Add a new method so that metadata queries can determine whether a function is a TableFunction. M java/engine/org/apache/derby/impl/jdbc/metadata.properties M java/engine/org/apache/derby/catalog/GetProcedureColumns.java Amend the queries and the dbproc which provide the results for getFunctions() and getFunctionColumns(). M java/shared/org/apache/derby/shared/common/reference/JDBC40Translation.java M java/testing/org/apache/derbyTesting/functionTests/tests/lang/TableFunctionTest.java M java/testing/org/apache/derbyTesting/functionTests/tests/jdbc4/TestDbMetaData.java M java/testing/org/apache/derbyTesting/junit/JDBC.java Regrssion tests. The regression tests ran cleanly for me under Java 6 except for the outstanding orphaned ResultSet problem in ProcedureInTriggerTest. That test runs cleanly standalone. This is the same regression test behavior seen in DERBY-2983 .
          Hide
          Rick Hillegas added a comment -

          Committed derby-716-02-DatabaseMetaData-aa.diff at subversion revision 564208.

          Show
          Rick Hillegas added a comment - Committed derby-716-02-DatabaseMetaData-aa.diff at subversion revision 564208.
          Hide
          Rick Hillegas added a comment - - edited

          Lance Andersen, JDBC spec lead, tells me that for a Table Function, DatabaseMetaData.getFunctionColumns() should not return a first row which describes the overall shape of the returned ResultSet, that is the ROW MULTISET. I will change the output of this metadata function accordingly.

          Show
          Rick Hillegas added a comment - - edited Lance Andersen, JDBC spec lead, tells me that for a Table Function, DatabaseMetaData.getFunctionColumns() should not return a first row which describes the overall shape of the returned ResultSet, that is the ROW MULTISET. I will change the output of this metadata function accordingly.
          Hide
          Rick Hillegas added a comment -

          Committed derby-03-DatabaseMetaData-aa.diff at subversion revision 564800. This removes the summary row 0 (describing the whole ROW MULTISET) from the ResultSet returned by DatabaseMetaData.getFunctionColumns() as Lance said is required. Touches the following files:

          M java/engine/org/apache/derby/catalog/GetProcedureColumns.java
          M java/testing/org/apache/derbyTesting/functionTests/tests/lang/TableFunctionTest.java

          Show
          Rick Hillegas added a comment - Committed derby-03-DatabaseMetaData-aa.diff at subversion revision 564800. This removes the summary row 0 (describing the whole ROW MULTISET) from the ResultSet returned by DatabaseMetaData.getFunctionColumns() as Lance said is required. Touches the following files: M java/engine/org/apache/derby/catalog/GetProcedureColumns.java M java/testing/org/apache/derbyTesting/functionTests/tests/lang/TableFunctionTest.java
          Hide
          Rick Hillegas added a comment -

          Attaching derby-716-Optimizer-aa.diff. This is the work described in the functional spec section titled "Optimizer Support". Touches the following files:

          M java/engine/org/apache/derby/impl/sql/compile/FromVTI.java

          Adds optimizer support for Derby Style Table Functions.

          M java/engine/org/apache/derby/loc/messages.xml
          M java/shared/org/apache/derby/shared/common/reference/SQLState.java

          Adds a new error message.

          M java/testing/org/apache/derbyTesting/functionTests/tests/lang/TableFunctionTest.java
          M java/testing/org/apache/derbyTesting/functionTests/tests/lang/StringArrayVTI.java

          Adds test cases for this work.

          Show
          Rick Hillegas added a comment - Attaching derby-716-Optimizer-aa.diff. This is the work described in the functional spec section titled "Optimizer Support". Touches the following files: M java/engine/org/apache/derby/impl/sql/compile/FromVTI.java Adds optimizer support for Derby Style Table Functions. M java/engine/org/apache/derby/loc/messages.xml M java/shared/org/apache/derby/shared/common/reference/SQLState.java Adds a new error message. M java/testing/org/apache/derbyTesting/functionTests/tests/lang/TableFunctionTest.java M java/testing/org/apache/derbyTesting/functionTests/tests/lang/StringArrayVTI.java Adds test cases for this work.
          Hide
          Rick Hillegas added a comment -

          Regression tests passed cleanly on this patch except for the testTriggerNegative heisenbug documented in DERBY-1585.

          Show
          Rick Hillegas added a comment - Regression tests passed cleanly on this patch except for the testTriggerNegative heisenbug documented in DERBY-1585 .
          Hide
          Rick Hillegas added a comment -

          Committed derby-716-04-Optimizer-aa.diff at subversion revision 574276.

          Show
          Rick Hillegas added a comment - Committed derby-716-04-Optimizer-aa.diff at subversion revision 574276.
          Hide
          Rick Hillegas added a comment -

          Attaching derby-716-05-PublicAPI-aa.diff. This exposes VTICosting and VTIEnvironment in Derby's public API as described in the "Public API" section of the functional spec.

          Show
          Rick Hillegas added a comment - Attaching derby-716-05-PublicAPI-aa.diff. This exposes VTICosting and VTIEnvironment in Derby's public API as described in the "Public API" section of the functional spec.
          Hide
          Daniel John Debrunner added a comment -

          In the squeezeString() method in TableFunctionsTest this method call is made:

          new String( bytes );

          That constructor for String is platform dependent, thus this test will most likely fail on platforms that have a different default encoding.

          See:

          http://wiki.apache.org/db-derby/AvoidNonPortableMethods

          Show
          Daniel John Debrunner added a comment - In the squeezeString() method in TableFunctionsTest this method call is made: new String( bytes ); That constructor for String is platform dependent, thus this test will most likely fail on platforms that have a different default encoding. See: http://wiki.apache.org/db-derby/AvoidNonPortableMethods
          Hide
          Rick Hillegas added a comment -

          Thanks for pointing this out, Dan. I'll take a look at this.

          Show
          Rick Hillegas added a comment - Thanks for pointing this out, Dan. I'll take a look at this.
          Hide
          Rick Hillegas added a comment -

          Committed derby-716-05-PublicAPI-aa.diff at subversion revision 574945.

          Show
          Rick Hillegas added a comment - Committed derby-716-05-PublicAPI-aa.diff at subversion revision 574945.
          Hide
          Rick Hillegas added a comment -

          Attaching derby-716-06-TestEncoding-aa.diff. This makes TableFunctionTest create strings from byte arrays using UTF8 encoding.

          Show
          Rick Hillegas added a comment - Attaching derby-716-06-TestEncoding-aa.diff. This makes TableFunctionTest create strings from byte arrays using UTF8 encoding.
          Hide
          Rick Hillegas added a comment -

          Committed derby-716-06-TestEncoding-aa.diff at subversion revision 575060.

          Show
          Rick Hillegas added a comment - Committed derby-716-06-TestEncoding-aa.diff at subversion revision 575060.
          Hide
          Rick Hillegas added a comment -

          Attaching derby-716-07-dblook-aa.diff. This patch changes the type name returned by the RowMultiSetImpl class. The type name has been changed so that dblook will emit re-playable DDL for Table Functions. The type name has been changed from 'ROW( col1 type1, ... ) MULTISET' to 'TABLE( "COL1" type1, ... )'. Touches the following files:

          M java/engine/org/apache/derby/catalog/types/RowMultiSetImpl.java

          Changes the type name.

          M java/testing/org/apache/derbyTesting/functionTests/tests/lang/TableFunctionTest.java

          Adjusts this test to account for the new type name.

          Show
          Rick Hillegas added a comment - Attaching derby-716-07-dblook-aa.diff. This patch changes the type name returned by the RowMultiSetImpl class. The type name has been changed so that dblook will emit re-playable DDL for Table Functions. The type name has been changed from 'ROW( col1 type1, ... ) MULTISET' to 'TABLE( "COL1" type1, ... )'. Touches the following files: M java/engine/org/apache/derby/catalog/types/RowMultiSetImpl.java Changes the type name. M java/testing/org/apache/derbyTesting/functionTests/tests/lang/TableFunctionTest.java Adjusts this test to account for the new type name.
          Hide
          Rick Hillegas added a comment -

          Committed derby-716-07-dblook-aa.diff at subversion revision 575660.

          Show
          Rick Hillegas added a comment - Committed derby-716-07-dblook-aa.diff at subversion revision 575660.
          Hide
          Rick Hillegas added a comment -

          Attaching derby-716-08-upgrade-aa.diff. This prevents table functions from being created after soft-upgrade. Added a unit test to verify this behavior. Touches the following files:

          M java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj

          Reject table function creation if we have only soft-upgraded.

          M java/testing/org/apache/derbyTesting/functionTests/tests/upgradeTests/Changes10_4.java

          Verify correct upgrade behavior.

          Committed at subversion revision 578452.

          Show
          Rick Hillegas added a comment - Attaching derby-716-08-upgrade-aa.diff. This prevents table functions from being created after soft-upgrade. Added a unit test to verify this behavior. Touches the following files: M java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj Reject table function creation if we have only soft-upgraded. M java/testing/org/apache/derbyTesting/functionTests/tests/upgradeTests/Changes10_4.java Verify correct upgrade behavior. Committed at subversion revision 578452.
          Hide
          Knut Anders Hatlen added a comment -

          In this part of the patch

          @@ -10136,6 +10136,8 @@
          ( <COMMA> functionTableReturnColumn( names, types ) ) *
          <RIGHT_PAREN>
          {
          + checkVersion( DataDictionary.DD_VERSION_DERBY_10_4, "table functions");
          +
          columnCount = names.size();
          nameArray = new String[ columnCount ];
          names.toArray( nameArray );

          would it be better to replace "table functions" with "CREATE FUNCTION ... RETURNS TABLE"? I think this would look better in the message since it puts quotes around the string (Use of '

          {0}

          ' requires database to be upgraded from...). It would also look better in the localized messages since the SQL keywords are the same in all locales, whereas the term "table functions" would need to be translated.

          Show
          Knut Anders Hatlen added a comment - In this part of the patch @@ -10136,6 +10136,8 @@ ( <COMMA> functionTableReturnColumn( names, types ) ) * <RIGHT_PAREN> { + checkVersion( DataDictionary.DD_VERSION_DERBY_10_4, "table functions"); + columnCount = names.size(); nameArray = new String[ columnCount ]; names.toArray( nameArray ); would it be better to replace "table functions" with "CREATE FUNCTION ... RETURNS TABLE"? I think this would look better in the message since it puts quotes around the string (Use of ' {0} ' requires database to be upgraded from...). It would also look better in the localized messages since the SQL keywords are the same in all locales, whereas the term "table functions" would need to be translated.
          Hide
          Rick Hillegas added a comment -

          Thanks for raising this localization issue, Knut. I like your proposal for changing the error message. I have committed that change at subversion revision 578822. I've attached the change as derby-716-09-upgradeLocalization-aa.diff. It touches one file:

          sqlgrammar.jj

          Show
          Rick Hillegas added a comment - Thanks for raising this localization issue, Knut. I like your proposal for changing the error message. I have committed that change at subversion revision 578822. I've attached the change as derby-716-09-upgradeLocalization-aa.diff. It touches one file: sqlgrammar.jj
          Hide
          Rick Hillegas added a comment -

          I am recording here my understanding of the collation associated with string columns in the rows returned by Table Functions. The rules which I think apply are in the SQL Standard, part 2. The DDL for Table Functions is described in section 11.50. There the declaration of the types of the returned columns are governed by the same <data type> production which is used for columns in ordinary tables. The <data type> production is described in section 6.1. I therefore believe that the same rules apply for determining the collation of columns in ordinary tables and in the rowsets returned by Table Functions. For Derby this means that if a territory-based collation has been declared for the database, then all string columns returned by Table Functions must have territory-based collation. In databases which DO NOT have a territory-based collation, the collation of string columns returned by Table Functions must be the basic collation.

          Show
          Rick Hillegas added a comment - I am recording here my understanding of the collation associated with string columns in the rows returned by Table Functions. The rules which I think apply are in the SQL Standard, part 2. The DDL for Table Functions is described in section 11.50. There the declaration of the types of the returned columns are governed by the same <data type> production which is used for columns in ordinary tables. The <data type> production is described in section 6.1. I therefore believe that the same rules apply for determining the collation of columns in ordinary tables and in the rowsets returned by Table Functions. For Derby this means that if a territory-based collation has been declared for the database, then all string columns returned by Table Functions must have territory-based collation. In databases which DO NOT have a territory-based collation, the collation of string columns returned by Table Functions must be the basic collation.
          Hide
          Rick Hillegas added a comment -

          Attaching derby-716-10-datatypesCollation-aa.diff. This adds tests verifying the following:

          1) Make sure that Derby calls the expected getXXX() method for each legal datatype in the rowset returned by a table function.

          2) Make sure that string columns in the returned rowset have the expected collation (described in the previous comment).

          The second test uncovered a bug: we were always applying BASIC collation to string columns in the returned rowsets. This patch fixes that bug.

          Touches the following files:

          M java/engine/org/apache/derby/impl/sql/compile/CreateAliasNode.java
          M java/engine/org/apache/derby/catalog/types/TypeDescriptorImpl.java

          Fix for the collation bug.

          M java/testing/org/apache/derbyTesting/functionTests/tests/lang/TableFunctionTest.java
          M java/testing/org/apache/derbyTesting/functionTests/tests/lang/StringArrayVTI.java

          New tests.

          Show
          Rick Hillegas added a comment - Attaching derby-716-10-datatypesCollation-aa.diff. This adds tests verifying the following: 1) Make sure that Derby calls the expected getXXX() method for each legal datatype in the rowset returned by a table function. 2) Make sure that string columns in the returned rowset have the expected collation (described in the previous comment). The second test uncovered a bug: we were always applying BASIC collation to string columns in the returned rowsets. This patch fixes that bug. Touches the following files: M java/engine/org/apache/derby/impl/sql/compile/CreateAliasNode.java M java/engine/org/apache/derby/catalog/types/TypeDescriptorImpl.java Fix for the collation bug. M java/testing/org/apache/derbyTesting/functionTests/tests/lang/TableFunctionTest.java M java/testing/org/apache/derbyTesting/functionTests/tests/lang/StringArrayVTI.java New tests.
          Hide
          Rick Hillegas added a comment -

          Committed derby-716-10-datatypesCollation-aa.diff at subversion revision 585710.

          Show
          Rick Hillegas added a comment - Committed derby-716-10-datatypesCollation-aa.diff at subversion revision 585710.
          Hide
          Rick Hillegas added a comment -

          Attaching derby-716-11-subqueries-aa.diff. This adds a test case to TableFunctionTest, verifying that user-written table functions behave correctly when invoked in subqueries with correlated references to outer query blocks.

          Show
          Rick Hillegas added a comment - Attaching derby-716-11-subqueries-aa.diff. This adds a test case to TableFunctionTest, verifying that user-written table functions behave correctly when invoked in subqueries with correlated references to outer query blocks.
          Hide
          Rick Hillegas added a comment -

          Committed derby-716-11-subqueries-aa.diff at subversion revision 587491.

          Show
          Rick Hillegas added a comment - Committed derby-716-11-subqueries-aa.diff at subversion revision 587491.
          Hide
          Dyre Tjeldvoll added a comment -

          Hi Rick, can we resolve/close this now? As you probably have seen, I have closed the sub-issues.

          Show
          Dyre Tjeldvoll added a comment - Hi Rick, can we resolve/close this now? As you probably have seen, I have closed the sub-issues.
          Hide
          Rick Hillegas added a comment -

          Closing this issue because I've finished the work I planned to do on this and the issue has been dormant for a couple months.

          Show
          Rick Hillegas added a comment - Closing this issue because I've finished the work I planned to do on this and the issue has been dormant for a couple months.
          Hide
          Rick Hillegas added a comment -

          DERBY-3616 was fixed by hand-rolling an encoding of byte arrays to strings. Knut points out that we may be able to use an existing encoding. This may be useful to consider along with Dan's other ideas for improving the implementation.

          Show
          Rick Hillegas added a comment - DERBY-3616 was fixed by hand-rolling an encoding of byte arrays to strings. Knut points out that we may be able to use an existing encoding. This may be useful to consider along with Dan's other ideas for improving the implementation.

            People

            • Assignee:
              Rick Hillegas
              Reporter:
              Rick Hillegas
            • Votes:
              2 Vote for this issue
              Watchers:
              1 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Development