Uploaded image for project: 'Derby'
  1. Derby
  2. DERBY-6109

Allow CREATE SYNONYM for table function invocations.

    Details

    • Type: Improvement
    • Status: Open
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: 10.10.1.1
    • Fix Version/s: None
    • Component/s: SQL
    • Urgency:
      Normal

      Description

      SYNONYMs are a non-standard extension supported by many databases. See DERBY-335. Right now SYNONYMs are only allowed for tables and views. It would be useful (and should be easy) to extend this feature to support SYNONYMs for table function invocations. E.g.:

      CREATE SYNONYM LT0 for TABLE( LT0() )
      CREATE SYNONYM LT0_P for TABLE( LT0('with_provenance') )

      See the discussion of this topic on this email thread: http://apache-database.10148.n7.nabble.com/Limitations-of-Table-Functions-vs-old-VTIs-td127988.html

        Issue Links

          Activity

          Hide
          rhillegas Rick Hillegas added a comment -

          Linking to DERBY-6036. SYNONYMs for table function invocations would eliminate the need to declare wrapper views to hide messy argument lists. This, in turn, would probably make it possible to push projections into SYNONYMs for table functions.

          Show
          rhillegas Rick Hillegas added a comment - Linking to DERBY-6036 . SYNONYMs for table function invocations would eliminate the need to declare wrapper views to hide messy argument lists. This, in turn, would probably make it possible to push projections into SYNONYMs for table functions.
          Hide
          rhillegas Rick Hillegas added a comment -

          Note that the proposed syntax would be a significant departure from how other databases implement synonyms. For other databases, the synonym is just a replacement name for the name of another database object (table, view, function, procedure, sequence, another synonym). The syntax for other databases is pretty much as follows:

          create [ or replace ] [ public | private ] synonym [ <schemaName> . ] <identifier> for [ <schemaName> . ] <identifier>

          Here are syntax examples for various other databases:

          Oracle: http://www.techonthenet.com/oracle/synonyms.php

          IBM Informix: http://publib.boulder.ibm.com/infocenter/idshelp/v10/index.jsp?topic=/com.ibm.sqls.doc/sqls272.htm

          Microsoft SQL Server: http://msdn.microsoft.com/en-us/library/ms177544.aspx

          EnterpriseDB: http://www.enterprisedb.com/docs/en/8.4/oracompat/Postgres_Plus_Advanced_Server_Oracle_Compatibility_Guide-33.htm

          The broad agreement among other databases suggests that the proposed Derby syntax would not conform if the SQL committee decided to standardize CREATE SYNONYM.

          Show
          rhillegas Rick Hillegas added a comment - Note that the proposed syntax would be a significant departure from how other databases implement synonyms. For other databases, the synonym is just a replacement name for the name of another database object (table, view, function, procedure, sequence, another synonym). The syntax for other databases is pretty much as follows: create [ or replace ] [ public | private ] synonym [ <schemaName> . ] <identifier> for [ <schemaName> . ] <identifier> Here are syntax examples for various other databases: Oracle: http://www.techonthenet.com/oracle/synonyms.php IBM Informix: http://publib.boulder.ibm.com/infocenter/idshelp/v10/index.jsp?topic=/com.ibm.sqls.doc/sqls272.htm Microsoft SQL Server: http://msdn.microsoft.com/en-us/library/ms177544.aspx EnterpriseDB: http://www.enterprisedb.com/docs/en/8.4/oracompat/Postgres_Plus_Advanced_Server_Oracle_Compatibility_Guide-33.htm The broad agreement among other databases suggests that the proposed Derby syntax would not conform if the SQL committee decided to standardize CREATE SYNONYM.
          Show
          mamtas Mamta A. Satoor added a comment - DB2 syntax for CREATE SYNONYM can be found at http://pic.dhe.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=%2Fcom.ibm.db2z10.doc.sqlref%2Fsrc%2Ftpc%2Fdb2z_sql_createsynonym.htm
          Hide
          dagw Dag H. Wanvik added a comment -

          Trying to grok that rationale for this, what would be the difference between a "wrapper view" and this new mechanism? And how would it facilitate projection pushing?

          Show
          dagw Dag H. Wanvik added a comment - Trying to grok that rationale for this, what would be the difference between a "wrapper view" and this new mechanism? And how would it facilitate projection pushing?
          Hide
          rhillegas Rick Hillegas added a comment -

          A typical wrapper view looks like this:

          create view v as select * from ...

          When you use the view in the following query...

          select a from v

          ...the projection which the optimizer pushes down to the table element (base table or table function) is...

          select *

          ...but you want the optimizer to push down this projection instead...

          select a

          If you used a synonym instead, I'm supposing that the synonym would be replaced with the reference to the table element at bind() time, before the optimizer sees the query. The optimizer would only see "select a" and not "select *", so "select a" would be pushed down.

          Note that I haven't verified that that's what happens with table synonyms today. In the long run, what we really want to do is fix DERBY-6036.

          Show
          rhillegas Rick Hillegas added a comment - A typical wrapper view looks like this: create view v as select * from ... When you use the view in the following query... select a from v ...the projection which the optimizer pushes down to the table element (base table or table function) is... select * ...but you want the optimizer to push down this projection instead... select a If you used a synonym instead, I'm supposing that the synonym would be replaced with the reference to the table element at bind() time, before the optimizer sees the query. The optimizer would only see "select a" and not "select *", so "select a" would be pushed down. Note that I haven't verified that that's what happens with table synonyms today. In the long run, what we really want to do is fix DERBY-6036 .

            People

            • Assignee:
              Unassigned
              Reporter:
              rhillegas Rick Hillegas
            • Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

              • Created:
                Updated:

                Development