Uploaded image for project: 'Spark'
  1. Spark
  2. SPARK-47361 Improve JDBC data sources
  3. SPARK-46077

Error in postgresql when pushing down filter by timestamp_ntz field

    XMLWordPrintableJSON

Details

    • Sub-task
    • Status: Resolved
    • Minor
    • Resolution: Fixed
    • 3.5.0
    • 4.0.0
    • SQL

    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?

      Attachments

        Issue Links

          Activity

            People

              planga82 Pablo Langa Blanco
              mkrasilnikova Marina Krasilnikova
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved: