[TransactSQL] Missing astify cases for TransactSQL

[albertyanzz]

First, thank you for all your work on this project! Truly incredible stuff.

I've been working with an MSSQL database (so using the TransactSQL database type) and came across some cases that astify couldn't handle (and probably should).

Describe the bug
A clear and concise description of what the bug is.

• This query: "SELECT * FROM (SELECT * FROM dummy_table) AS dummy_table_alias"

  • after passing it through astify and then passing the result of that through sqlify, the parsed result is "SELECT * FROM (SELECT * FROM [dummy_table]) AS [dummy_table_alias]"
  • seems like there are some issues with subqueries that have the [] identifiers

• This query doesn't astify in TransactSQL: "SELECT STRING_AGG(DISTINCT column_name, ',') AS aggregated_values FROM table_name;"

  • It works with PostgreSQL astify

• JSON PATH is not handled: "SELECT column_name FROM table_name FOR JSON PATH"

  • XML PATH works for certain queries, but not in this straightforward case either "SELECT column_name FROM table_name FOR XML PATH"

• The OFFSET, FETCH NEXT pattern
  SELECT * FROM transactions ORDER BY created_at OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY

Database Engine
TransactSQL

To Reproduce
-the SQL that be parsed:

• "SELECT * FROM (SELECT * FROM [dummy_table]) AS [dummy_table_alias]"
• "SELECT STRING_AGG(DISTINCT column_name, ',') AS aggregated_values FROM table_name"
• "SELECT column_name FROM table_name FOR JSON PATH"
  -the node-sql-parser version: 5.3.6
  -the node version: >=14.20

Expected behavior

• The above queries should work after an astify -> sqlify -> astify operation
  • If a query is the output of sqlify, then it should be astify-able
• Additional astify cases to work with valid TransactSQL queries

Thank you!

[albertyanzz]

An additional case:

• The OFFSET, FETCH NEXT pattern
  • SELECT * FROM transactions ORDER BY created_at OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY

[taozhi8833998]

@albertyanzz I am currently working on these issues. By the way, the SQL query SELECT * FROM (SELECT * FROM [dummy_table]) AS [dummy_table_alias] is invalid in MSSQL. However, the query SELECT * FROM (SELECT * FROM dummy_table) AS [dummy_table_alias] is valid. Am I understanding this correctly?

[albertyanzz]

My apologies, my bullet point was not clear. I believe the query itself is valid. Astify cannot astify it though.

Basically, this is the process.

1. Original Query SELECT * FROM (SELECT * FROM dummy_table) AS dummy_table_alias goes through astify and this is the result:

{
    "ast": {
        "with": null,
        "type": "select",
        "options": null,
        "distinct": null,
        "columns": [
            {
                "expr": {
                    "type": "column_ref",
                    "table": null,
                    "column": "*"
                },
                "as": null
            }
        ],
        "into": {
            "position": null
        },
        "from": [
            {
                "expr": {
                    "tableList": [
                        "select::null::dummy_table"
                    ],
                    "columnList": [
                        "select::null::(.*)"
                    ],
                    "ast": {
                        "with": null,
                        "type": "select",
                        "options": null,
                        "distinct": null,
                        "columns": [
                            {
                                "expr": {
                                    "type": "column_ref",
                                    "table": null,
                                    "column": "*"
                                },
                                "as": null
                            }
                        ],
                        "into": {
                            "position": null
                        },
                        "from": [
                            {
                                "db": null,
                                "table": "dummy_table",
                                "as": null,
                                "table_hint": null,
                                "temporal_table": null,
                                "operator": null
                            }
                        ],
                        "for": null,
                        "where": null,
                        "groupby": null,
                        "having": null,
                        "top": null,
                        "orderby": null,
                        "limit": null
                    },
                    "parentheses": true
                },
                "as": "dummy_table_alias",
                "operator": null
            }
        ],
        "for": null,
        "where": null,
        "groupby": null,
        "having": null,
        "top": null,
        "orderby": null,
        "limit": null
    }
}

2. I take this ast and pass it through sqlify and get: SELECT * FROM (SELECT * FROM [dummy_table]) AS [dummy_table_alias]

3. However, when I try to pass this new query through astify again, I get:

{
    "success": false,
    "message": "Error parsing query at line 1 offset 47: Expected \"--\", \"/*\", [ \\t\\n\\r], or [A-Za-z_@#一-龥] but \"[\" found."
}

[albertyanzz]

Essentially, I think this case might be missing in the SQL_LIST in transactsql.spec.ts

{
      title: 'subquery with alias in identifiers',
      sql: [
        'SELECT * FROM (SELECT * FROM [dummy_table]) AS [dummy_table_alias],
        'SELECT * FROM (SELECT * FROM [dummy_table]) AS [dummy_table_alias]'
      ]
    },

notably,
SELECT * FROM (SELECT * FROM [dummy_table]) AS dummy_table_alias
successfully astifies, but
SELECT * FROM (SELECT * FROM [dummy_table]) AS [dummy_table_alias] does not

[taozhi8833998]

@albertyanzz All of the issues have been resolved.

[albertyanzz]

Amazing, thanks for your work and time @taozhi8833998!