[jira] [Comment Edited] (CALCITE-3361) Add a test that parses and validates a SQL statement with every built-in Redshift function

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

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

Julian Hyde edited comment on CALCITE-3361 at 9/27/19 9:58 PM:
---------------------------------------------------------------

Here are the issues uncovered so far.

1. The following functions are in Redshift but not in Calcite (with standard + postgresql operator tables). We can get past them if we set {{lenientOperatorLookup}}:
* bool_and
* bool_or
* bpcharcmp
* btrim
* bttext_pattern_cmp
* cbrt
* charindex
* checksum
* crc32
* cume_dist
* date_cmp
* date_cmp_timestamp
* date_cmp_timestamptz
* date_part_year
* dlog1
* dlog10
* func_sha1
* get_bit
* get_byte
* getdate()
* interval_cmp
* isfinite
* log
* now()
* percentile_cont
* percentile_disc
* percent_rank
* pow
* random
* ratio_to_report
* set_bit
* set_byte
* timezone
* to_ascii
* to_hex
* to_timestamp
* trunc

2. The following aggregate functions are in Redshsft but not Calcite. (Aggregate functions are a more serious problem, because the validator needs to know a function is an aggregate function.)
* median
* percentile_cont
* percentile_disc

3. Allow {{APPROXIMATE}} keyword before aggregate functions, e.g. {{approximate avg\(x)}}

4. Allow {{RESPECT NULLS}} in {{FIRST_VALUE}} and {{LAST_VALUE}} functions
* e.g. {{select empno, first_value(sal respect nulls) over (order by empno rows unbounded preceding) from emp where deptno = 10 order by 1}}

5. Allow {{IGNORE NULLS}} in {{LEAD}}, {{LAG}}, {{NTH_VALUE}} functions
* e.g. {{select empno, lead(sal, 2) ignore nulls over (order by empno) from emp order by 1}}

6. Allow NVL with more than 2 arguments.

7. Implicit type coercion (might be solved by {{typeCoercion}} flag, haven't checked):
* apply CONVERT_TIMEZONE to CHAR rather than TIMESTAMP last arg;
* allow CONVERT_TIMEZONE with only 2 args,
*  apply DATEADD to CHAR rather than DATE;
*  apply CONVERT_TIMEZONE to CHAR rather than DATE;

8. Add the following nilary operators that do not take parentheses. (More serious than regular missing operators, because such operators look like column references.)
* SYSDATE

8. AT TIME ZONE operator

9. DATEADD, DATEDIFF, DATE_PART
* Gives validation error if argument is MONTH or another standard interval type
* Gives different kind of error for "M", "W" or "QTR"

10. The following nilary functions are defined in Calcite, and take no parenthese in Calcite but require parentheses in Redshift:
* PI()




was (Author: julianhyde):
Here are the issues uncovered so far.

1. The following functions are in Redshift but not in Calcite (standard + postgres tables)
* bool_and
* bool_or
* bpcharcmp
* btrim
* bttext_pattern_cmp
* cbrt
* charindex
* checksum
* crc32
* cume_dist
* date_cmp
* date_cmp_timestamp
* date_cmp_timestamptz
* date_part_year
* dlog1
* dlog10
* func_sha1
* get_bit
* get_byte
* getdate()
* interval_cmp
* isfinite
* log
* now()
* percentile_cont
* percentile_disc
* percent_rank
* pow
* random
* ratio_to_report
* set_bit
* set_byte
* timezone
* to_ascii
* to_hex
* to_timestamp
* trunc

2. The following aggregate functions are in Redshsft but not Calcite. (Aggregate functions are a more serious problem, because the validator needs to know a function is an aggregate function.)
* median
* percentile_cont
* percentile_disc

3. Allow {{APPROXIMATE}} keyword before aggregate functions, e.g. {{approximate avg\(x)}}

4. Allow {{RESPECT NULLS}} in {{FIRST_VALUE}} and {{LAST_VALUE}} functions
* e.g. {{select empno, first_value(sal respect nulls) over (order by empno rows unbounded preceding) from emp where deptno = 10 order by 1}}

5. Allow {{IGNORE NULLS}} in {{LEAD}}, {{LAG}}, {{NTH_VALUE}} functions
* e.g. {{select empno, lead(sal, 2) ignore nulls over (order by empno) from emp order by 1}}

6. Allow NVL with more than 2 arguments.

7. Implicit type coercion (might be solved by {{typeCoercion}} flag, haven't checked):
* apply CONVERT_TIMEZONE to CHAR rather than TIMESTAMP last arg;
* allow CONVERT_TIMEZONE with only 2 args,
*  apply DATEADD to CHAR rather than DATE;
*  apply CONVERT_TIMEZONE to CHAR rather than DATE;

8. Add the following nilary operators that do not take parentheses. (More serious than regular missing operators, because such operators look like column references.)
* SYSDATE

8. AT TIME ZONE operator

9. DATEADD, DATEDIFF, DATE_PART
* Gives validation error if argument is MONTH or another standard interval type
* Gives different kind of error for "M", "W" or "QTR"

10. The following nilary functions are defined in Calcite, and take no parenthese in Calcite but require parentheses in Redshift:
* PI()



> Add a test that parses and validates a SQL statement with every built-in Redshift function
> ------------------------------------------------------------------------------------------
>
>                 Key: CALCITE-3361
>                 URL: https://issues.apache.org/jira/browse/CALCITE-3361
>             Project: Calcite
>          Issue Type: Bug
>            Reporter: Julian Hyde
>            Priority: Major
>
> Add a test that parses and validates a SQL statement with every built-in Redshift function.
> It would be part of the babel component, but would go beyond parsing, and also validate.



--
This message was sent by Atlassian Jira
(v8.3.4#803005)