[
https://issues.apache.org/jira/browse/CALCITE-6425?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Julian Hyde updated CALCITE-6425:
---------------------------------
Description:
Add operators and metadata so that columns can have attributes.
For example, we wish to associate a FORMATTED_VALUE attribute to the SALARY
column. The following example defines an attribute in a CTE and then uses it in
a query.
{code:java}
CREATE VIEW EnhancedEmp AS
SELECT deptno, ename,
DEFINE_ATTRIBUTE(
DEFINE_ATTRIBUTE(sal, 'FORMATTED_VALUE',
CAST(sal AS TO_CHAR(revenue, 'L99D99')),
'SORT_KEY', sal)
FROM Emp);
SELECT ename,
sal,
GET_ATTRIBUTE(sal, 'FORMATTED_VALUE') AS formatted_sal,
DESCRIBE_ATTRIBUTES(sal) AS attributes
FROM EnhancedEmp
WHERE ename = 'SCOTT';
ENAME SAL FORMATTED_SAL ATTRIBUTES
===== ==== ============= ==========================================
SCOTT 1200 $1200.00. FORMATTED_VALUE: VARCHAR, SORT_KEY: NUMBER
SELECT deptno,
SUM(sal) AS sum_sal,
GET_ATTRIBUTE(SUM(sal), 'FORMATTED_VALUE') AS formatted_sum_sal
FROM EnhancedEmp
WHERE ename = 'SCOTT';
DEPTNO SUM_SAL FORMATTED_SUM_SAL
====== ======= =================
10 5000 $5,000.00
20 3750 $3,750.00{code}
Here are the functions:
* {{DEFINE_ATTRIBUTE(targetExpression, attributeName, expression)}} defines an
attribute on targetExpression; the return value is the same but has an extra
attribute
* {{GET_ATTRIBUTE(targetExpression, attributeName)}} evaluates an attribute of
an expression
* {{DESCRIBE_ATTRIBUTES(targetExpression)}} returns a string describing the
names and types of available attributes
What is the goal of this facility? To be able to attach "semantic" metadata,
such as format string, formatted value, sort key, to values in a way that can
easily be transmitted over JDBC.
An alternative approach would have been to convert values into records. But the
consuming expression and JDBC would have to deal with those records. In the
proposed approach, you can find what attributes are available as part of an
'extended type' using {{DESCRIBE_ATTRIBUTES}}.
Also required is a set of rules for how particular attributes propagate through
queries and expressions. For example, the {{FORMATTED_VALUE}} attribute
propagates through subquery ({{SELECT}}), filter ({{WHERE}}), aggregate
({{GROUP BY}}), and through the {{SUM}} aggregate function.
The default alias of a column defined by DEFINE_ATTRIBUTE is that of the target
expression.
was:
Add operators and metadata so that columns can have attributes.
For example, we wish to associate a FORMATTED_VALUE attribute to the SALARY
column. The following example defines an attribute in a CTE and then uses it in
a query.
{code:java}
CREATE VIEW EnhancedEmp AS
SELECT deptno, ename,
DEFINE_ATTRIBUTE(
DEFINE_ATTRIBUTE(revenue, 'FORMATTED_VALUE',
CAST(revenue AS TO_CHAR(revenue, 'L99D99')),
'SORT_KEY', revenue)
FROM Emp);
SELECT ename,
sal,
GET_ATTRIBUTE(sal, 'FORMATTED_VALUE') AS formatted_sal,
DESCRIBE_ATTRIBUTES(sal) AS attributes
FROM EnhancedEmp
WHERE ename = 'SCOTT';
ENAME SAL FORMATTED_SAL ATTRIBUTES
===== ==== ============= ==========================================
SCOTT 1200 $1200.00. FORMATTED_VALUE: VARCHAR, SORT_KEY: NUMBER
SELECT deptno,
SUM(sal) AS sum_sal,
GET_ATTRIBUTE(SUM(sal), 'FORMATTED_VALUE') AS formatted_sum_sal
FROM EnhancedEmp
WHERE ename = 'SCOTT';
DEPTNO SUM_SAL FORMATTED_SUM_SAL
====== ======= =================
10 5000 $5,000.00
20 3750 $3,750.00{code}
Here are the functions:
* {{DEFINE_ATTRIBUTE(targetExpression, attributeName, expression)}} defines an
attribute on targetExpression; the return value is the same but has an extra
attribute
* {{GET_ATTRIBUTE(targetExpression, attributeName)}} evaluates an attribute of
an expression
* {{DESCRIBE_ATTRIBUTES(targetExpression)}} returns a string describing the
names and types of available attributes
What is the goal of this facility? To be able to attach "semantic" metadata,
such as format string, formatted value, sort key, to values in a way that can
easily be transmitted over JDBC.
An alternative approach would have been to convert values into records. But the
consuming expression and JDBC would have to deal with those records. In the
proposed approach, you can find what attributes are available as part of an
'extended type' using {{DESCRIBE_ATTRIBUTES}}.
Also required is a set of rules for how particular attributes propagate through
queries and expressions. For example, the {{FORMATTED_VALUE}} attribute
propagates through subquery ({{SELECT}}), filter ({{WHERE}}), aggregate
({{GROUP BY}}), and through the {{SUM}} aggregate function.
> Attributes
> ----------
>
> Key: CALCITE-6425
> URL: https://issues.apache.org/jira/browse/CALCITE-6425
> Project: Calcite
> Issue Type: Improvement
> Reporter: Julian Hyde
> Priority: Major
>
> Add operators and metadata so that columns can have attributes.
> For example, we wish to associate a FORMATTED_VALUE attribute to the SALARY
> column. The following example defines an attribute in a CTE and then uses it
> in a query.
> {code:java}
> CREATE VIEW EnhancedEmp AS
> SELECT deptno, ename,
> DEFINE_ATTRIBUTE(
> DEFINE_ATTRIBUTE(sal, 'FORMATTED_VALUE',
> CAST(sal AS TO_CHAR(revenue, 'L99D99')),
> 'SORT_KEY', sal)
> FROM Emp);
> SELECT ename,
> sal,
> GET_ATTRIBUTE(sal, 'FORMATTED_VALUE') AS formatted_sal,
> DESCRIBE_ATTRIBUTES(sal) AS attributes
> FROM EnhancedEmp
> WHERE ename = 'SCOTT';
> ENAME SAL FORMATTED_SAL ATTRIBUTES
> ===== ==== ============= ==========================================
> SCOTT 1200 $1200.00. FORMATTED_VALUE: VARCHAR, SORT_KEY: NUMBER
> SELECT deptno,
> SUM(sal) AS sum_sal,
> GET_ATTRIBUTE(SUM(sal), 'FORMATTED_VALUE') AS formatted_sum_sal
> FROM EnhancedEmp
> WHERE ename = 'SCOTT';
> DEPTNO SUM_SAL FORMATTED_SUM_SAL
> ====== ======= =================
> 10 5000 $5,000.00
> 20 3750 $3,750.00{code}
>
> Here are the functions:
> * {{DEFINE_ATTRIBUTE(targetExpression, attributeName, expression)}} defines
> an attribute on targetExpression; the return value is the same but has an
> extra attribute
> * {{GET_ATTRIBUTE(targetExpression, attributeName)}} evaluates an attribute
> of an expression
> * {{DESCRIBE_ATTRIBUTES(targetExpression)}} returns a string describing the
> names and types of available attributes
> What is the goal of this facility? To be able to attach "semantic" metadata,
> such as format string, formatted value, sort key, to values in a way that can
> easily be transmitted over JDBC.
> An alternative approach would have been to convert values into records. But
> the consuming expression and JDBC would have to deal with those records. In
> the proposed approach, you can find what attributes are available as part of
> an 'extended type' using {{DESCRIBE_ATTRIBUTES}}.
> Also required is a set of rules for how particular attributes propagate
> through queries and expressions. For example, the {{FORMATTED_VALUE}}
> attribute propagates through subquery ({{SELECT}}), filter ({{WHERE}}),
> aggregate ({{GROUP BY}}), and through the {{SUM}} aggregate function.
> The default alias of a column defined by DEFINE_ATTRIBUTE is that of the
> target expression.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
