Thanks, Justin. For now, it’s enough to know that they have types. If we hit the requirement that Calcite’s validator needs to know that (say) there is a variable called ‘FOO’ and its type is ’TIMESTAMP’ then we can use a plug-in map or even the SqlOperatorTable (modeling variables as functions with no arguments, kind of like CURRENT_TIMESTAMP today).
> On Mar 28, 2022, at 1:52 PM, Justin Swanhart <greenl...@gmail.com> wrote: > > Hi, > > They are strongly typed. Many are strings that can only be set to certain > legal values (like optmizer_switch). These variables can also be used in > MySQL SET statements. Note that there is no way to easily type the > variables except by examining their values, in the performance_schema (at > least in 8.0). MySQL has some special values like ON > (set @@session.unique_checks=ON). 1 and 0 are supported in place of ON or > OFF. > > There are 652 global variables in MySQL 8.0.27 enumerated in the attached > fiddle. Each MySQL version adds (and sometimes removes) global variables > so this is probably hard to maintain on the Calcite side except to maybe > have some generic support for a MySQL global variable. > > [1] > https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=639e08374d2218ab85e16d8210538d88 > <https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=639e08374d2218ab85e16d8210538d88> > > On Mon, Mar 28, 2022 at 3:08 PM Julian Hyde <jhyde.apa...@gmail.com > <mailto:jhyde.apa...@gmail.com>> wrote: > >> A couple more questions. Do these variables have well-defined types? Do >> they have to be valid expressions? If the answer to either of these >> questions is ’no’ then maybe they are what Oracle calls ’substitution >> variables’. Substitution variables have a behavior more like C macros than >> real variables and we would not be able to handle them easily. >> >> Julian >> >> [1] >> https://blogs.oracle.com/opal/post/sql*plus-substitution-variables-define-variables-and-parameters-in-sql-queries#2 >> < >> https://blogs.oracle.com/opal/post/sql*plus-substitution-variables-define-variables-and-parameters-in-sql-queries#2 >> >> <https://blogs.oracle.com/opal/post/sql*plus-substitution-variables-define-variables-and-parameters-in-sql-queries#2>> >> >> >>> On Mar 28, 2022, at 12:02 PM, Julian Hyde <jhyde.apa...@gmail.com> >> wrote: >>> >>> We’d be open to adding support, but it must not be the default behavior, >> so there will be need to be some kind of flag. >>> >>> Can you log a JIRA case with the subject ’Support variables with “@" and >> “@@" prefixes (like MySQL)’. Then we can write a specification and you can >> submit a pull request. >>> >>> At the RexNode level I think there are already constructs for >> referencing variables to this change would be confined to the parser. >>> >>> Julian >>> >>> >>>> On Mar 28, 2022, at 4:04 AM, Adolfo Ochagavía <ado...@ochagavia.nl >> <mailto:ado...@ochagavia.nl <mailto:ado...@ochagavia.nl>>> wrote: >>>> >>>> Would you be open to a patch to add proper support for this kind of >> MySQL variables, or do you consider it to be outside the scope of Calcite? >>>> >>>> On 2022/03/22 21:52:09 Justin Swanhart wrote: >>>>> MySQL support two categories of variables, user variables which are >>>>> prefixed with the @character and session/global SERVER variables which >> are >>>>> prefixed with @@. >>>>> >>>>> You can also access them via: >>>>> >>>>> Select @@session.session_var; >>>>> Select @@global.global_var; >>>>> select @@session_or_global_var; >>>>> >>>>> for example: >>>>> mysql> select @@warp_adjust_table_stats_for_joins; >>>>> +-------------------------------------+ >>>>> | @@warp_adjust_table_stats_for_joins | >>>>> +-------------------------------------+ >>>>> | 1 | >>>>> +-------------------------------------+ >>>>> 1 row in set (0.00 sec) >>>>> >>>>> mysql> set warp_adjust_table_stats_for_joins= false; >>>>> Query OK, 0 rows affected (0.00 sec) >>>>> >>>>> mysql> select @@warp_adjust_table_stats_for_joins; >>>>> +-------------------------------------+ >>>>> | @@warp_adjust_table_stats_for_joins | >>>>> +-------------------------------------+ >>>>> | 0 | >>>>> +-------------------------------------+ >>>>> 1 row in set (0.00 sec) >>>>> >>>>> mysql> select @@session.warp_adjust_table_stats_for_joins; >>>>> +---------------------------------------------+ >>>>> | @@session.warp_adjust_table_stats_for_joins | >>>>> +---------------------------------------------+ >>>>> | 0 | >>>>> +---------------------------------------------+ >>>>> 1 row in set (0.00 sec) >>>>> >>>>> mysql> select @@global.warp_adjust_table_stats_for_joins; >>>>> +--------------------------------------------+ >>>>> | @@global.warp_adjust_table_stats_for_joins | >>>>> +--------------------------------------------+ >>>>> | 1 | >>>>> +--------------------------------------------+ >>>>> 1 row in set (0.00 sec) >>>>> >>>>> >>>>> On Tue, Mar 22, 2022 at 5:02 PM Julian Hyde <jh...@gmail.com >>>>> <http://gmail.com/> < >> http://gmail.com/ <http://gmail.com/>>> wrote: >>>>> >>>>>> The ‘@@‘ prefix is not standard SQL, and Calcite does not support it. >>>>>> >>>>>> Can you do some research to find out how MySQL handles it. Is it >>>>>> considered to be part of the variable name? Or is it a prefix (like $ >> in >>>>>> bash) that means ‘what comes next is a variable’? In other words, >> does the >>>>>> parser say there is a reference to a variable called >>>>>> '@@character_set_server’ or a variable called ‘character_set_server’? >> And >>>>>> is ‘@‘ a legal part of a variable name? >>>>>> >>>>>> Also, is it handled by the core SQL parser or by a preprocessor? >>>>>> >>>>>> Julian >>>>>> >>>>>> >>>>>>> On Mar 22, 2022, at 2:17 AM, Adolfo Ochagavía <ad...@ochagavia.nl >>>>>>> <http://ochagavia.nl/> < >> http://ochagavia.nl/ <http://ochagavia.nl/>>> >>>>>> wrote: >>>>>>> >>>>>>> Hi there, >>>>>>> >>>>>>> I am writing a MySQL-compatible server that talks the MySQL protocol. >>>>>> Some clients are sending special queries to autoconfigure themselves, >> like >>>>>> "SELECT @@character_set_server". I would like to use calcite to parse >> such >>>>>> queries, but parsing fails with an exception, seemingly related to the >>>>>> usage of "@@" in variable names. Is this unsupported or am I doing >>>>>> something wrong? >>>>>>> >>>>>>> The code: >>>>>>>> var config = SqlParser.Config.DEFAULT.withLex(Lex.MYSQL); >>>>>>>> var parser = SqlParser.create("SELECT @@character_set_server", >> config); >>>>>>>> var parsed = parser.parseQuery(); >>>>>>> >>>>>>> The exception: org.apache.calcite.sql.parser.SqlParseException: >> Lexical >>>>>> error at line 1, column 9. Encountered: "@" (64), after : "" >>>>>>> >>>>>>> Any help is appreciated! >>>>>>> Adolfo