[ https://issues.apache.org/jira/browse/CALCITE-5248?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17617925#comment-17617925 ]
Julian Hyde commented on CALCITE-5248: -------------------------------------- [~jaynarale], I am open to adding new validation modes to Calcite as long as they can be controlled using flags and don't complicate the existing code too much. I like the approach you have taken, of thinking of corner cases and trying them out in other systems. Before we accept this PR, we will want to check other systems: say Postgres and Calcite. Some important cases that I'm not sure I saw in your tests: * Cycles, e.g. 'select z as x, x + 2 as y, y + 1 as z from t'; * Aggregate functions, e.g. 'select deptno, sum(empno) as s from emp where s < 10'; * Multiple aliases with the same definition, e.g. 'select y from (select x + 1 as y, x + 1 as y from t)'. Apparently Postgres is OK with those but SQL Server is not. * Aliases with the same name as columns, and whether it's possible to qualify columns with table names. Some of those important cases will be negative. I'd like to see what error Teradata (or Postgres) gives. If aliases later in the SELECT clause override earlier ones, as some of your cases seem to indicate, that behavior seems to me not consistent with how SQL generally behaves, very peculiar to Teradata, and definitely would need to be off by default and protected by a Teradata-specific flag. > Support Extended Column Aliasing > -------------------------------- > > Key: CALCITE-5248 > URL: https://issues.apache.org/jira/browse/CALCITE-5248 > Project: Calcite > Issue Type: New Feature > Reporter: Jay Narale > Priority: Major > Labels: pull-request-available > Time Spent: 0.5h > Remaining Estimate: 0h > > Based on the discussion here [1]. > The goal is to support exteneded column aliasing in where , on clause and > select List. An example is Teradata [2] > A few edge cases to be considered for proper semantics > 1. When SubQuery( maybe table) in from and Alias have the same name eg > {code:java} > SELECT c_customerId as c FROM ( SELECT c FROM table) WHERE c = 'test' {code} > {code:java} > SELECT trim(c_customer_name) as c FROM ( SELECT c FROM table) WHERE c = > 'test' {code} > Comments > In a database, the execution order is *FROM > WHERE > SELECT* so the > semantics should be that column in the subquery is given priority and a > filter is applied to that column > > Some edge cases that need consideration > [doc|https://docs.google.com/document/d/1bt_5CmRg97gh_j_lsVrnoEDn5N3TRNGeYcyX4uGD_0g/edit] > > [1] - [https://lists.apache.org/thread/7zk5wqgsk64903w5mbt72pwjmsftx0kz] > [2] - > [https://docs.teradata.com/r/Teradata-Database-SQL-Fundamentals/June-2017/Basic-SQL-Syntax/Referencing-Object-Names-in-a-Request/Using-a-Column-Alias] > -- This message was sent by Atlassian Jira (v8.20.10#820010)