andialbrecht / sqlparse

A non-validating SQL parser module for Python
BSD 3-Clause "New" or "Revised" License
3.73k stars 695 forks source link

column named `type` in SET column list leads to broken IdentifierList #342

Open simkoc opened 7 years ago

simkoc commented 7 years ago

Parsing Query: "UPDATE si_tax SET tax_description = 'GST' , tax_percentage = '0' , type = '4' , tax_enabled = '1' WHERE tax_id = '1' AND domain_id = '1'" leads to output:

[<DML 'UPDATE' at 0x7F9965B94258>,
 <Whitespace ' ' at 0x7F9965B942C0>,
 <Identifier 'si_tax' at 0x7F9965B0AA50>,
 <Whitespace ' ' at 0x7F9965B94328>,
 <Keyword 'SET' at 0x7F9965B94600>,
 <Whitespace ' ' at 0x7F9965B943F8>,
 <IdentifierList 'tax_de...' at 0x7F9965B0AED0>,
 <Whitespace ' ' at 0x7F9965B23120>,
 <Comparison '=' at 0x7F9965B23188>,
 <Whitespace ' ' at 0x7F9965B231F0>,
 <IdentifierList ''4' , ...' at 0x7F9965B0AF50>,
 <Whitespace ' ' at 0x7F9965B23600>,
 <Where 'WHERE ...' at 0x7F9965B0A8D0>]

Whereas Query: "UPDATE si_tax SET tax_description = 'GST' , tax_percentage = '0' , tope = '4', tax_enabled = '1' WHERE tax_id = '1' AND domain_id = 1" leads to output:

[<DML 'UPDATE' at 0x7F9965C73A78>,
 <Whitespace ' ' at 0x7F9965C73C80>,
 <Identifier 'si_tax' at 0x7F9965B0A0D0>,
 <Whitespace ' ' at 0x7F9965B8CAE0>,
 <Keyword 'SET' at 0x7F9965B8CB48>,
 <Whitespace ' ' at 0x7F9965B8CBB0>,
 <IdentifierList 'tax_de...' at 0x7F9965B0A6D0>,
 <Whitespace ' ' at 0x7F9965BA48D8>,
 <Where 'WHERE ...' at 0x7F9965B17ED0>]

The obvious issue is that the query having type as a column that is set now has a broken, therefore two inconsistent IdentifierList

pip show sqlparse
---
Name: sqlparse
Version: 0.2.3
simkoc commented 7 years ago

I traced the problem to the defined keywords as TYPE is defined as a keyword even though for postgres it only known to the parser but not a reserved keyword and can consequently be used as column or table names. This leads to confusion for the lexer? - my current hotfix is to simply comment out the TYPE line in the keyword file. However, this does not seem like an appropriate fix as the there might be other SQL languages that have TYPE as keyword.

simkoc commented 7 years ago

same holds for position

joecabezas commented 6 years ago

same for events when used as alias for a table name, this is broken:

in

SELECT
    *
FROM
    table_name events