taozhi8833998 / node-sql-parser

Parse simple SQL statements into an abstract syntax tree (AST) with the visited tableList and convert it back to SQL
https://taozhi8833998.github.io/node-sql-parser/
Apache License 2.0
818 stars 181 forks source link

Parsing PostgreSQL recursive query fails #2197

Closed kasvith closed 2 weeks ago

kasvith commented 3 weeks ago

Describe the bug When we try to have a valid recursive query with PostgreSQL it fails with following error

{
        message: 'Expected "(", "--", "/*", "AS", or [ \\t\\n\\r] but "t" found.',
        expected: [
          {
            type: 'class',
            parts: [Array],
            inverted: false,
            ignoreCase: false
          },
          { type: 'literal', text: '/*', ignoreCase: false },
          { type: 'literal', text: '--', ignoreCase: false },
          { type: 'literal', text: '(', ignoreCase: false },
          {
            type: 'class',
            parts: [Array],
            inverted: false,
            ignoreCase: false
          },
          { type: 'literal', text: '/*', ignoreCase: false },
          { type: 'literal', text: '--', ignoreCase: false },
          { type: 'literal', text: 'AS', ignoreCase: true },
          {
            type: 'class',
            parts: [Array],
            inverted: false,
            ignoreCase: false
          },
          { type: 'literal', text: '/*', ignoreCase: false },
          { type: 'literal', text: '--', ignoreCase: false },
          { type: 'literal', text: '(', ignoreCase: false },
          {
            type: 'class',
            parts: [Array],
            inverted: false,
            ignoreCase: false
          },
          { type: 'literal', text: '/*', ignoreCase: false },
          { type: 'literal', text: '--', ignoreCase: false },
          { type: 'literal', text: 'AS', ignoreCase: true }
        ],
        found: 't',
        location: {
          start: { offset: 15, line: 1, column: 16 },
          end: { offset: 16, line: 1, column: 17 }
        },
        name: 'SyntaxError'
      }

Database Engine PostgreSQL v16

To Reproduce -the SQL that be parsed

    with recursive t(year, revenue, operating_income, profit)
    as (
      select f.year, f.revenue, f.operating_income, f.profit
      from financials as f
      where f.year = 2022

      union all

      select
        t.year + 1,
        cast(round(t.revenue * (1 - .2), 0) as int),
        t.operating_income,
        cast(round(t.revenue * (1 - .2), 0) as int) - t.operating_income
      from t
      where t.profit >= 0
    )
    select year, profit from t
    limit 10;

-the node-sql-parser version 5.3.3 -the node version 20

Expected behavior Should process the above query

Screenshots it works fine with PostgreSQL image

Additional context Add any other context about the problem here.