[CALCITE-6566] JDBC adapter should generate PI function with parentheses in most dialects

• Type: Improvement
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Fix Version/s: 1.39.0
• Labels: pull-request-available
• Assignee: xiong duan
• Reporter: xiong duan
• Created: 09/Sep/24 11:18
• Updated: 20 hours ago

Description

Now In Calcite, support the PI function:

select PI;
return 3.141592653589793

Jdbc will generate:

SELECT PI AS "PI" FROM (VALUES (0)) AS "t" ("ZERO");

However, they always need parentheses when I test in different data sources.

Spark:

select PI();
return 3.141592653589793
select PI;
Error in query: cannot resolve '`PI`' given input columns: []; line 1 pos 7;

Hive:

select PI();
return 3.141592653589793
select PI;
SemanticException [Error 10004]: Line 1:7 Invalid table alias or column reference 'PI': (possible column names are: ) (state=42000,code=10004)

Postgresql:

select PI();
return 3.141592653589793
select PI;
ERROR: column "pi" does not exist

MySQL:

select PI();
return 3.141593
select PI;
ERROR 1054 (42S22) at line 1: Unknown column 'PI' in 'field list'

SQLServer:

select PI();
3.1415926535897931
select PI;

So I will generate PI() in Jdbc Dialect.

Issue Links

• blocks: CALCITE-6582 Release Calcite 1.38.0
• is related to: CALCITE-2750 PI operator incorrectly identified as dynamic function
• relates to: CALCITE-1066 Add an extension operator table with Oracle-specific operators
• relates to: CALCITE-1766 Support system functions having no args with parenthesis too
• links to: GitHub Pull Request #3956