[
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)
