search

sql-formatter-org / sql-formatter

A whitespace formatter for different query languages
https://sql-formatter-org.github.io/sql-formatter/
MIT License
2.36k stars 405 forks source link

Feature Request: support for BETWEEN AND clause #696

Closed ChenZhouUC closed 10 months ago

ChenZhouUC commented 10 months ago

Describe the Feature when use this formatter to format sql like this:

SELECT * FROM member WHERE ds BETWEEN 20230912 AND 20230918 AND member_id = "abcs";

It would report error like this:

Unable to format SQL: Error: Parse error: Ambiguous grammar [ [ { "type": "statement", "children": [ { "type": "clause", "nameKw": { "type": "keyword", "tokenType": "RESERVED_SELECT", "text": "SELECT", "raw": "SELECT" }, "children": [ { "type": "all_columns_asterisk" } ] }, { "type": "clause", "nameKw": { "type": "keyword", "tokenType": "RESERVED_CLAUSE", "text": "FROM", "raw": "FROM" }, "children": [ { "type": "keyword", "tokenType": "RESERVED_KEYWORD", "text": "MEMBER", "raw": "member" } ] }, { "type": "clause", "nameKw": { "type": "keyword", "tokenType": "RESERVED_CLAUSE", "text": "WHERE", "raw": "WHERE" }, "children": [ { "type": "identifier", "text": "ds" }, { "type": "between_predicate", "betweenKw": { "type": "keyword", "tokenType": "BETWEEN", "text": "BETWEEN", "raw": "BETWEEN" }, "expr1": [ { "type": "literal", "text": "20230912" } ], "andKw": { "type": "keyword", "tokenType": "AND", "text": "AND", "raw": "AND" }, "expr2": [ { "type": "literal", "text": "20230918" } ] }, { "type": "keyword", "tokenType": "AND", "text": "AND", "raw": "AND" }, { "type": "identifier", "text": "member_id" }, { "type": "operator", "text": "=" }, { "type": "identifier", "text": "\"abcs\"" } ] } ], "hasSemicolon": true } ], [ { "type": "statement", "children": [ { "type": "clause", "nameKw": { "type": "keyword", "tokenType": "RESERVED_SELECT", "text": "SELECT", "raw": "SELECT" }, "children": [ { "type": "all_columns_asterisk" } ] }, { "type": "clause", "nameKw": { "type": "keyword", "tokenType": "RESERVED_CLAUSE", "text": "FROM", "raw": "FROM" }, "children": [ { "type": "keyword", "tokenType": "RESERVED_KEYWORD", "text": "MEMBER", "raw": "member" } ] }, { "type": "clause", "nameKw": { "type": "keyword", "tokenType": "RESERVED_CLAUSE", "text": "WHERE", "raw": "WHERE" }, "children": [ { "type": "identifier", "text": "ds" }, { "type": "between_predicate", "betweenKw": { "type": "keyword", "tokenType": "BETWEEN", "text": "BETWEEN", "raw": "BETWEEN" }, "expr1": [ { "type": "literal", "text": "20230912" }, { "type": "keyword", "tokenType": "AND", "text": "AND", "raw": "AND" }, { "type": "literal", "text": "20230918" } ], "andKw": { "type": "keyword", "tokenType": "AND", "text": "AND", "raw": "AND" }, "expr2": [ { "type": "identifier", "text": "member_id" } ] }, { "type": "operator", "text": "=" }, { "type": "identifier", "text": "\"abcs\"" } ] } ], "hasSemicolon": true } ] ]

Why do you want this feature? BETWEEN AND clause is useful in many SQL Derivatives like Hive SQL.

ChenZhouUC commented 10 months ago

already solved when updated to latest version

mxr commented 9 months ago

Hello, thanks for the extension. I can still repro the error with the same example using version v1.6.0 of the extension, which is the latest version in the VSCode marketplace (extension ID inferrinizzard.prettier-sql-vscode).

Error 
Unable to format SQL:
Error: Parse error: Ambiguous grammar
[
  [
    {
      "type": "statement",
      "children": [
        {
          "type": "clause",
          "nameKw": {
            "type": "keyword",
            "tokenType": "RESERVED_SELECT",
            "text": "SELECT",
            "raw": "SELECT"
          },
          "children": [
            {
              "type": "all_columns_asterisk"
            }
          ]
        },
        {
          "type": "clause",
          "nameKw": {
            "type": "keyword",
            "tokenType": "RESERVED_CLAUSE",
            "text": "FROM",
            "raw": "FROM"
          },
          "children": [
            {
              "type": "keyword",
              "tokenType": "RESERVED_KEYWORD",
              "text": "MEMBER",
              "raw": "member"
            }
          ]
        },
        {
          "type": "clause",
          "nameKw": {
            "type": "keyword",
            "tokenType": "RESERVED_CLAUSE",
            "text": "WHERE",
            "raw": "WHERE"
          },
          "children": [
            {
              "type": "identifier",
              "text": "ds"
            },
            {
              "type": "between_predicate",
              "betweenKw": {
                "type": "keyword",
                "tokenType": "BETWEEN",
                "text": "BETWEEN",
                "raw": "BETWEEN"
              },
              "expr1": [
                {
                  "type": "literal",
                  "text": "20230912"
                }
              ],
              "andKw": {
                "type": "keyword",
                "tokenType": "AND",
                "text": "AND",
                "raw": "AND"
              },
              "expr2": [
                {
                  "type": "literal",
                  "text": "20230918"
                }
              ]
            },
            {
              "type": "keyword",
              "tokenType": "AND",
              "text": "AND",
              "raw": "AND"
            },
            {
              "type": "identifier",
              "text": "member_id"
            },
            {
              "type": "operator",
              "text": "="
            },
            {
              "type": "identifier",
              "text": "\"abcs\""
            }
          ]
        }
      ],
      "hasSemicolon": true
    }
  ],
  [
    {
      "type": "statement",
      "children": [
        {
          "type": "clause",
          "nameKw": {
            "type": "keyword",
            "tokenType": "RESERVED_SELECT",
            "text": "SELECT",
            "raw": "SELECT"
          },
          "children": [
            {
              "type": "all_columns_asterisk"
            }
          ]
        },
        {
          "type": "clause",
          "nameKw": {
            "type": "keyword",
            "tokenType": "RESERVED_CLAUSE",
            "text": "FROM",
            "raw": "FROM"
          },
          "children": [
            {
              "type": "keyword",
              "tokenType": "RESERVED_KEYWORD",
              "text": "MEMBER",
              "raw": "member"
            }
          ]
        },
        {
          "type": "clause",
          "nameKw": {
            "type": "keyword",
            "tokenType": "RESERVED_CLAUSE",
            "text": "WHERE",
            "raw": "WHERE"
          },
          "children": [
            {
              "type": "identifier",
              "text": "ds"
            },
            {
              "type": "between_predicate",
              "betweenKw": {
                "type": "keyword",
                "tokenType": "BETWEEN",
                "text": "BETWEEN",
                "raw": "BETWEEN"
              },
              "expr1": [
                {
                  "type": "literal",
                  "text": "20230912"
                },
                {
                  "type": "keyword",
                  "tokenType": "AND",
                  "text": "AND",
                  "raw": "AND"
                },
                {
                  "type": "literal",
                  "text": "20230918"
                }
              ],
              "andKw": {
                "type": "keyword",
                "tokenType": "AND",
                "text": "AND",
                "raw": "AND"
              },
              "expr2": [
                {
                  "type": "identifier",
                  "text": "member_id"
                }
              ]
            },
            {
              "type": "operator",
              "text": "="
            },
            {
              "type": "identifier",
              "text": "\"abcs\""
            }
          ]
        }
      ],
      "hasSemicolon": true
    }
  ]
]
mxr commented 9 months ago

Apologies I should be using renesaarsoo.sql-formatter-vsc instead.