[MySQL (Oracle)] Cannot parse user variable in EXPLAIN INTO

[OnMYLai]

My SQL, copying from MySQL doc with changes of the select sql to sth in my db:
EXPLAIN FORMAT=JSON INTO @myselect SELECT a FROM ab1;
Parsing return error msg line 1:25 mismatched input '@myselect' expecting '@', and unable to parse the 'myselect' as an ID.

the rule for the INTO part:
explainInto
: INTO_SYMBOL AT_SIGN_SYMBOL textOrIdentifier
;
maybe need to recognize AT_TEXT_SUFFIX?

[mike-lischke]

Good find @OnMYLai!

[OnMYLai]

Found another similar issue on NTILE call:

SELECT a, NTILE(2) OVER w AS 'ntile2' FROM ab1 WINDOW w as (ORDER BY a);

This runs fine and parses fine;

SET @var = 2;
SELECT a, NTILE(@var) OVER w AS 'ntile2' FROM ab1 WINDOW w as (ORDER BY a);

This can run, but parsing returns line 2:15 no viable alternative at input '('.

MySQL doc saids it is ok to use user-defined variables in NTILE call.

Related rules:

windowFunctionCall
    : ...
    | NTILE_SYMBOL (
        OPEN_PAR_SYMBOL stableInteger CLOSE_PAR_SYMBOL
        | {this.isServerVersionLt80024()}? simpleExprWithParentheses
    ) windowingClause
    ...
    ;

stableInteger
    : int64Literal
    | paramOrVar
    ;

paramOrVar
    : PARAM_MARKER
    | identifier
    | AT_SIGN_SYMBOL textOrIdentifier
    ;

P.S. I kept the server version used in parser as default 80200, if it was less than 80024 then the simpleExprWithParenthese will kick in and capture the @var without issue.

[OnMYLai]

Same issue MAYBE on TABLESAMPLE clause as well:

tablesampleClause
    : {this.isServerVersionGe80200()}? TABLESAMPLE_SYMBOL samplingMethod OPEN_PAR_SYMBOL samplingPercentage CLOSE_PAR_SYMBOL
    ;

samplingPercentage
    : ulonglongNumber
    | AT_SIGN_SYMBOL textOrIdentifier
    | PARAM_MARKER
    ;

I use MAYBE becoz

1. it is Heatwave SQL and I don't have the env to test and
2. the doc has so little info on that it doesnt even state if an user variable can be used there.

Anyway my testing SQL:

SELECT COUNT(*) FROM LINEITEM TABLESAMPLE SYSTEM (@var);

got me error line 1:50 no viable alternative at input 'SELECT COUNT(*) FROM LINEITEM TABLESAMPLE SYSTEM (@var'

[mike-lischke]

The best option we have for this kind of problem is to compare the ANTLR grammar with the yacc grammar of the MySQL server. That's the ultimate source of truth. If the ANTLR grammar misses something it needs to be adjusted, otherwise that syntax is not allowed.

According to that it is not allowed to use a user variable in the table sample clause. I see, that AT_SIGN_SYMBOL textOrIdentifier stands for a user variable, but we are missing that AT_TEXT_SUFFIX.

[mike-lischke]

We already have a rule userVariable. Can you replace all the occurrences of AT_SIGN_SYMBOL textOrIdentifier by that? There's also one place (roleOrPrivilege) which uses the correct alts, which can also be changed to use this userVariable rule.

If it works just file a PR.