min and max treated as keywords instead of functions

I've come across tables that have columns named 'min' and 'max',
I've also never seen max used as a keyword, only as a function.

consider the following example:

SELECT min, max FROM foo
;

Token.Keyword.DML                       :SELECT
Token.Keyword                           :  min
Token.Keyword                           :  max
Token.Keyword                           :FROM
Token.Name                              :  foo

In this case, min and max are not keywords, or atleast this query works in mysql to mean literal column names.

When min and max are used as functions they behave as expected, although the ttype 'Keyword' still seems inaccurate.

SELECT min(bar), max(baz) FROM foo
;

Token.Keyword.DML                       :SELECT
Token.Keyword                           :min
Token.Name                              :    bar
Token.Keyword                           :max
Token.Name                              :    baz
Token.Keyword                           :FROM
Token.Name                              :  foo

Of course this can be avoided by explicitly backticking columns names:

SELECT `min`, `max` FROM foo
;

Token.Keyword.DML                       :SELECT
Token.Name                              :    `min`
Token.Name                              :    `max`
Token.Keyword                           :FROM
Token.Name                              :  foo

But I can't expect the quires I deal with to be well-escaped so long as they run as expected on mysql.

[vmuriart]

is this pre or post grouping? post grouping you should have min and max defined as functions

"Grouping" is what gives a node a tokens property right?

sqlparse version sqlparse==0.2.0.dev0

When I walk tokens like so, I get a min and max ttype of Token.Name

import sqlparse

def handle_tokens(query):
    for token in query.tokens:
        if hasattr(token, 'tokens'):
            handle_tokens(token)
        else:
            print('{:40} {}'.format(
                token.ttype,
                token,
            ))

def main(query):
    tokenized_queries = sqlparse.parse(query)
    handle_tokens(tokenized_queries[0])

if __name__ == '__main__':
    main('select min(foo) max(bar) from baz')

out

Token.Keyword.DML                        select
Token.Text.Whitespace                     
Token.Name                               min
Token.Punctuation                        (
Token.Name                               foo
Token.Punctuation                        )
Token.Text.Whitespace                     
Token.Name                               max
Token.Punctuation                        (
Token.Name                               bar
Token.Punctuation                        )
Token.Text.Whitespace                     
Token.Keyword                            from
Token.Text.Whitespace                     
Token.Name                               baz

[vmuriart]

Ah, I think I see the problem. You are going all the way to the most basic level of the token which can only be

# Special token types
Text = Token.Text
Whitespace = Text.Whitespace
Newline = Whitespace.Newline
Error = Token.Error

# Text that doesn't belong to this lexer (e.g. HTML in PHP)
Other = Token.Other

# Common token types for source code
Keyword = Token.Keyword
Name = Token.Name
Literal = Token.Literal
String = Literal.String
Number = Literal.Number
Punctuation = Token.Punctuation
Operator = Token.Operator
Comparison = Operator.Comparison
Wildcard = Token.Wildcard
Comment = Token.Comment
Assignment = Token.Assignment

# Generic types for non-source code
Generic = Token.Generic

# String and some others are not direct child of Token.
# alias them:
Token.Token = Token
Token.String = String
Token.Number = Number

a function is defined as a combination of an identifier and a parenthesis classes which in turn defined to be a combination of tokens.

This might explain it better:

import sqlparse

def handle_tokens(query):
    for token in query.tokens:
        print(repr(token))
    print('\n')

    for token in query.tokens[2]:
        print(repr(token))
    print('\n')

    for token in query.tokens[2][0]:
        print(repr(token))
    print('\n')

    for token in query.tokens[2][0][0]:
        print(repr(token))

def main(query):

    tokenized_queries = sqlparse.parse(query)
    handle_tokens(tokenized_queries[0])

if __name__ == '__main__':
    # Note, there should be a comma between (foo) and max
    main('select min(foo) max(bar) from baz')    

This is showing the composition of each token/class. Also note my comment about the missing comma since it confused the parser.

<DML              select                   at 0x983E410>
<Whitespace                                at 0x975DAF0>
<Function         min(foo) max(bar)        at 0x9834348>
<Whitespace                                at 0x981B200>
<Keyword          from                     at 0x981B150>
<Whitespace                                at 0x981B0F8>
<Identifier       baz                      at 0x9834148>


<Function         min(foo)                 at 0x98341C8>
<Whitespace                                at 0x9816F10>
<Function         max(bar)                 at 0x9834CC8>


<Identifier       min                      at 0x9834248>
<Parenthesis      (foo)                    at 0x97A3C48>


<Name             min                      at 0x97F3EB8>

Hope that makes sense.

[vmuriart]

i'm running out of my forked version of sqlparse your output formatting might look different, but should still give you the same results.

@vmuriart thank you very much for pointing this out and explaining! Surely this is not an issue then, Closing.

[vmuriart]

Glad that made sense!