[jira] [Commented] (CALCITE-493) Support syntax for defining columns and their types at query/DML time

["Julian Hyde (JIRA)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/CALCITE-493?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14233601#comment-14233601 ] 

Julian Hyde commented on CALCITE-493:
-------------------------------------

It plays nicely with Calcite internals, because Calcite can just augment the definition of the table it read from the catalog before it starts validating and planning the query.

And it’s nice for end-users, because they get to use strongly-typed columns in their query.

It’s pretty close to standard SQL

{code}
SELECT ... FROM t [ AS ] tableAlias (columnAlias1, columnAlias2, ...)
{code}

Standard SQL allows you to omit {{AS}} but not {{tableAlias}}, so I think your proposed syntax is not ambiguous. However an extra keyword would be helpful. What would you (and Phoenix users) think of this syntax:

{code}
SELECT ... FROM t EXTEND (c1 type1, c2 type2)
{code}

It would occur before the table-alias and column-aliases, if present. The following are all valid:

{code}
SELECT e.id, e.my_horoscope
FROM emp EXTEND (horoscope VARCHAR(100))  AS e (id, name, deptno, my_horoscope);

SELECT e.id, e.my_horoscope
FROM emp EXTEND (horoscope VARCHAR(100)) e (id, name, deptno, my_horoscope);

SELECT e.empno, e.horoscope
FROM emp EXTEND (horoscope VARCHAR(100)) e;

SELECT e.empno, e.horoscope
FROM emp EXTEND (horoscope VARCHAR(100)) AS e;

SELECT e.empno, e.horoscope
FROM emp EXTEND (horoscope VARCHAR(100));
{code}

Clearly, extended columns are only valid on base tables. (Not sub-queries, views, table functions, or relations defined in the WITH clause.)

We should have a means for a table to say it is OK to ask for augmenting columns. I propose an extra method:

{code}
interface Table {
  /** Returns a new Table containing all of the fields of this Table, plus the given fields. */
  Table augment(List<RelDataTypeField> fields);
}
{code}

Note if the table implements the expansion interfaces such as {{ScannableTable}}, {{FilterableTable}}, {{ProjectableFilterableTable}}, the object returned from augment may or may not implement those interfaces too.

> Support syntax for defining columns and their types at query/DML time
> ---------------------------------------------------------------------
>
>                 Key: CALCITE-493
>                 URL: https://issues.apache.org/jira/browse/CALCITE-493
>             Project: Calcite
>          Issue Type: Improvement
>            Reporter: James Taylor
>            Assignee: Julian Hyde
>              Labels: phoenix
>
> Often times only being able to define a static schema over which to query is not flexible enough. It would be useful to be able to define columns and their type at query/DML time. For an example syntax, see http://phoenix.apache.org/dynamic_columns.html



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)