[BUG] JSQLParser Version : RDBMS : failing feature description

[tiboun]

Hi,

Here are a test case written by Claude showing some parser issues regarding duckdb syntax on MAP and PIVOT:

import net.sf.jsqlparser.parser.CCJSqlParserUtil;
import net.sf.jsqlparser.statement.Statement;
import net.sf.jsqlparser.statement.create.view.CreateView;
import net.sf.jsqlparser.statement.select.Select;
import org.junit.jupiter.api.Test;

import static org.junit.jupiter.api.Assertions.*;

/**

• Demonstrates DuckDB-specific SQL syntax that JSqlParser cannot parse.

• DuckDB is a popular analytical database (https://duckdb.org) whose SQL dialect

• extends standard SQL with several constructs. These tests document syntax patterns

• returned by DuckDB's information_schema.views (view_definition column) that

• JSqlParser fails to parse.

• Context: We use JSqlParser to extract table references from view definitions

• for SQL-level access control. When JSqlParser fails to parse a DuckDB view

• definition, we cannot determine which base tables the view references, which

• breaks transparent view authorization checks.

• All SQL statements below have been verified to execute successfully in DuckDB 1.2.x.

• All 4 tests FAIL on JSqlParser 5.3.186 — throws net.sf.jsqlparser.JSQLParserException.
  */
  public class DuckDBCompatibilityTest {

  // =========================================================================
  // DuckDB MAP literal syntax
  // =========================================================================

  /**

  • DuckDB supports MAP literals using MAP {'key': 'value', ...} syntax.

  • This is commonly used in DuckDB extensions (DuckLake, Delta Lake, Iceberg)

  • for metadata configuration.

  • Verified in DuckDB 1.2.x:

  • D SELECT MAP {'key': 'value', 'key2': 'other'} AS m FROM (SELECT 1) t;

  • ┌─────────────────────────┐

  • │ m │

  • │ map(varchar, varchar) │

  • ├─────────────────────────┤

  • │ {key=value, key2=other} │

  • └─────────────────────────┘

  • @see https://duckdb.org/docs/sql/data_types/map.html
    */
    @test
    void testMapLiteral() {
    String sql = "SELECT MAP {'key': 'value', 'key2': 'other'} AS m FROM t";

    assertDoesNotThrow(() -> {
    Statement stmt = CCJSqlParserUtil.parse(sql);
    assertInstanceOf(Select.class, stmt);
    }, "JSqlParser should parse DuckDB MAP literal syntax: MAP {'key': 'value'}");
    }

  /**

  • MAP literal inside a CREATE VIEW definition.
  • This is the pattern seen in DuckLake view definitions stored in
  • information_schema.views.
  • Verified in DuckDB 1.2.x:
  • D CREATE TABLE products(name VARCHAR, cnt INTEGER);
  • D CREATE VIEW v AS SELECT MAP {'category': name, 'count': name} AS summary FROM products;
  • -- Success
    */
    @test
    void testCreateViewWithMapLiteral() {
    String sql = "CREATE VIEW v AS SELECT MAP {'category': name, 'count': name} AS summary FROM products";

   assertDoesNotThrow(() -> {
       Statement stmt = CCJSqlParserUtil.parse(sql);
       assertInstanceOf(CreateView.class, stmt);
   }, "JSqlParser should parse CREATE VIEW containing MAP literal");
  

  }

  // =========================================================================
  // DuckDB PIVOT syntax
  // =========================================================================

  /**

  • DuckDB supports PIVOT as a first-class statement/clause.

  • Verified in DuckDB 1.2.x:

  • D CREATE TABLE sales(region VARCHAR, amount INTEGER);

  • D INSERT INTO sales VALUES ('US', 100), ('EU', 200), ('US', 150);

  • D PIVOT sales ON region USING SUM(amount);

  • ┌────────┬────────┐

  • │ EU │ US │

  • │ int128 │ int128 │

  • ├────────┼────────┤

  • │ 200 │ 250 │

  • └────────┴────────┘

  • @see https://duckdb.org/docs/sql/statements/pivot.html
    */
    @test
    void testPivotStatement() {
    String sql = "PIVOT sales ON region USING SUM(amount)";

    assertDoesNotThrow(() -> {
    CCJSqlParserUtil.parse(sql);
    }, "JSqlParser should parse DuckDB PIVOT statement");
    }

  /**

  • PIVOT used as a subquery inside a SELECT.
  • Verified in DuckDB 1.2.x:
  • D SELECT * FROM (PIVOT sales ON region USING SUM(amount));
  • ┌────────┬────────┐
  • │ EU │ US │
  • │ int128 │ int128 │
  • ├────────┼────────┤
  • │ 200 │ 250 │
  • └────────┴────────┘
    */
    @test
    void testPivotInSubquery() {
    String sql = "SELECT * FROM (PIVOT sales ON region USING SUM(amount))";

   assertDoesNotThrow(() -> {
       Statement stmt = CCJSqlParserUtil.parse(sql);
       assertInstanceOf(Select.class, stmt);
   }, "JSqlParser should parse PIVOT as subquery in SELECT");
  

  }
  }

Can you handle those syntax please ?
Thank you :)

[manticore-projects]

Certainly!