[SPARK-29842] PostgreSQL dialect: cast to double

• Type: Sub-task
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Affects Version/s: 3.1.0
• Component/s: SQL
• Assignee: Unassigned
• Reporter: jobit mathew
• Created: 11/Nov/19 12:15
• Updated: 29/Mar/20 02:06

Description

Make SparkSQL's cast to double behavior be consistent with PostgreSQL when

spark.sql.dialect is configured as PostgreSQL.

some examples

spark-sql> select CAST ('10.2' AS DOUBLE PRECISION);
Error in query:
extraneous input 'PRECISION' expecting ')'(line 1, pos 30)

== SQL ==
select CAST ('10.2' AS DOUBLE PRECISION)
------------------------------^^^

spark-sql> select CAST ('10.2' AS DOUBLE PRECISION);
Error in query:
extraneous input 'PRECISION' expecting ')'(line 1, pos 30)

== SQL ==
select CAST ('10.2' AS DOUBLE PRECISION)
------------------------------^^^

spark-sql> select CAST ('10.2' AS DOUBLE);
10.2
Time taken: 0.08 seconds, Fetched 1 row(s)
spark-sql> select CAST ('10.222222222222' AS DOUBLE);
10.222222222222
Time taken: 0.08 seconds, Fetched 1 row(s)
spark-sql> select CAST ('ff' AS DOUBLE);
NULL
Time taken: 0.08 seconds, Fetched 1 row(s)
spark-sql> select CAST ('11111111111111111' AS DOUBLE);
1.1111111111111112E16
Time taken: 0.067 seconds, Fetched 1 row(s)
spark-sql> 

Postgresql

select CAST ('10.22222222222' AS DOUBLE PRECISION);
select CAST ('11111111111111111' AS DOUBLE PRECISION);
select CAST ('ff' AS DOUBLE PRECISION);

 
 

	float8
1	10,22222222222

 

	float8
1	1,11111111111111E+16

Error(s), warning(s):

22P02: invalid input syntax for type double precision: "ff"

 

Issue Links

• links to: GitHub Pull Request #26522