[SPARK-46077] Error in postgresql when pushing down filter by timestamp_ntz field

• Type: Sub-task
• Status: Resolved
• Priority: Minor
• Resolution: Fixed
• Affects Version/s: 3.5.0
• Fix Version/s: 4.0.0
• Component/s: SQL
• Labels: pull-request-available
• Assignee: Pablo Langa Blanco
• Reporter: Marina Krasilnikova
• Created: 23/Nov/23 21:08
• Updated: 12/Mar/24 14:39
• Resolved: 28/Feb/24 08:36

Description

code to reproduce:

SparkSession sparkSession = SparkSession
.builder()
.appName("test-app")
.master("local[*]")
.config("spark.sql.timestampType", "TIMESTAMP_NTZ")
.getOrCreate();

String url = "...";

String catalogPropPrefix = "spark.sql.catalog.myc";
sparkSession.conf().set(catalogPropPrefix, JDBCTableCatalog.class.getName());
sparkSession.conf().set(catalogPropPrefix + ".url", url);

Map<String, String> options = new HashMap<>();
options.put("driver", "org.postgresql.Driver");
// options.put("pushDownPredicate", "false");  it works fine if  this line is uncommented

Dataset<Row> dataset = sparkSession.read()
.options(options)
.table("myc.demo.`My table`");

dataset.createOrReplaceTempView("view1");
String sql = "select * from view1 where `my date` = '2021-04-01 00:00:00'";
Dataset<Row> result = sparkSession.sql(sql);
result.show();
result.printSchema();

Field `my date` is of type timestamp. This code results in org.postgresql.util.PSQLException  syntax error

 

 

String sql = "select * from view1 where `my date` = to_timestamp('2021-04-01 00:00:00', 'yyyy-MM-dd HH:mm:ss')";  // this query also doesn't work

String sql = "select * from view1 where `my date` = date_trunc('DAY', to_timestamp('2021-04-01 00:00:00', 'yyyy-MM-dd HH:mm:ss'))";  // but this is OK

 

Is it a bug or I got something wrong?

Issue Links

• links to: GitHub Pull Request #45261