{"id":12238,"date":"2023-03-05T18:30:32","date_gmt":"2023-03-05T17:30:32","guid":{"rendered":"https:\/\/www.salvis.com\/blog\/?p=12238"},"modified":"2024-06-17T11:09:14","modified_gmt":"2024-06-17T09:09:14","slug":"islandsql-episode-4-expressions-2","status":"publish","type":"post","link":"https:\/\/www.salvis.com\/blog\/2023\/03\/05\/islandsql-episode-4-expressions-2\/","title":{"rendered":"IslandSQL Episode 4: Expressions"},"content":{"rendered":"\n

Introduction<\/h2>\n\n\n\n

In the last episode<\/a>, we extended the IslandSQL grammar covering the complete lock table<\/code> statement. However, the support for expressions was very limited. It was not possible to use a date literal or to_date<\/code> function to determine a partition to be locked. Time to fix that. In this episode, we will have a closer look at some SQL expressions and how to deal with the complexity of the SQL language.<\/p>\n\n\n\n

The full source code is available on GitHub<\/a> and the binaries are on Maven Central<\/a>.<\/p>\n\n\n\n

Lock Table Partition<\/h2>\n\n\n\n

To lock a table partition we use the partition name like this:<\/p>\n\n\n\n

1) Lock partition by name<\/span><\/path><\/path><\/svg><\/span>
lock <\/span>table<\/span> sales <\/span>partition<\/span> (sales_q2_2000) <\/span>in<\/span> exclusive mode <\/span>nowait<\/span>;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
Or we let the Oracle Database determine the partition by passing the values of a partition key like this:<\/p>\n\n\n\n
2) Lock partition by value<\/span><\/path><\/path><\/svg><\/span>
lock <\/span>table<\/span> sales <\/span>partition<\/span> <\/span>for<\/span> (<\/span>date<\/span> <\/span>'2000-04-01'<\/span>) <\/span>in<\/span> exclusive mode <\/span>nowait<\/span>;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
There are a lot of possibilities to specify the date. Here is a selection of sensible and less sensible alternatives:<\/p>\n\n\n\n
3) Lock partition by value – more variants<\/span><\/path><\/path><\/svg><\/span>
lock <\/span>table<\/span> sales <\/span>partition<\/span> <\/span>for<\/span> (to_date(<\/span>'2000-04'<\/span>, <\/span>'YYYY-MM'<\/span>)) <\/span>in<\/span> exclusive mode <\/span>nowait<\/span>;<\/span><\/span>\nlock <\/span>table<\/span> sales <\/span>partition<\/span> <\/span>for<\/span> (to_date(<\/span>'2000-092'<\/span>, <\/span>'YYYY-DDD'<\/span>)) <\/span>in<\/span> exclusive mode <\/span>nowait<\/span>;<\/span><\/span>\nlock <\/span>table<\/span> sales <\/span>partition<\/span> <\/span>for<\/span> (<\/span>timestamp<\/span> <\/span>'2000-04-01 08:42:42'<\/span>) <\/span>in<\/span> exclusive mode <\/span>nowait<\/span>;<\/span><\/span>\nlock <\/span>table<\/span> sales <\/span>partition<\/span> <\/span>for<\/span> (add_months(trunc(to_date(<\/span>'2000-12-31'<\/span>, <\/span>'YYYY-MM-DD'<\/span>), <\/span>'YYYY'<\/span>), <\/span>3<\/span>)) <\/span>in<\/span> exclusive mode <\/span>nowait<\/span>;<\/span><\/span>\nlock <\/span>table<\/span> sales <\/span>partition<\/span> <\/span>for<\/span> (<\/span>date<\/span> <\/span>'2000-01-01'<\/span> + interval <\/span>'3'<\/span> <\/span>month<\/span>) <\/span>in<\/span> exclusive mode <\/span>nowait<\/span>;<\/span><\/span>\nlock <\/span>table<\/span> sales <\/span>partition<\/span> <\/span>for<\/span> (<\/span>timestamp<\/span> <\/span>'2000-12-31 18:00:00'<\/span> + <\/span>1<\/span>\/<\/span>4<\/span> + -<\/span>9<\/span> * interval <\/span>'1'<\/span> <\/span>month<\/span>) <\/span>in<\/span> exclusive mode <\/span>nowait<\/span>;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
All these alternatives lock the partition in the sales<\/code> table where the data for 1st April 2000 is stored. Using different SQL expressions, of course.<\/p>\n\n\n\n
Expressions<\/h2>\n\n\n\n
I mentioned in my last post that expressions are the most extensive part of the SQL grammar. However, there are ways to optimise the extent of the grammar. ANTLR 4 helps because it allows defining left recursive parts of a grammar naturally. See the expressions labelled with binaryExpression<\/code> in the excerpt of the parser in version 0.4.0<\/a> of the grammar below.<\/p>\n\n\n\n
4) Expressions in v0.4.0 of IslandSQL<\/span><\/path><\/path><\/svg><\/span>
expression<\/span>:<\/span><\/span>\n      <\/span>expr<\/span>=<\/span>STRING<\/span>                                               # <\/span>simpleExpressionStringLiteral<\/span><\/span>\n    | <\/span>expr<\/span>=<\/span>NUMBER<\/span>                                               # <\/span>simpleExpressionNumberLiteral<\/span><\/span>\n    | <\/span>K_DATE<\/span> <\/span>expr<\/span>=<\/span>STRING<\/span>                                        # <\/span>dateLiteral<\/span><\/span>\n    | <\/span>K_TIMESTAMP<\/span> <\/span>expr<\/span>=<\/span>STRING<\/span>                                   # <\/span>timestampLiteral<\/span><\/span>\n    | <\/span>expr<\/span>=<\/span>intervalExpression<\/span>                                   # <\/span>intervalLiteral<\/span><\/span>\n    | <\/span>expr<\/span>=<\/span>sqlName<\/span>                                              # <\/span>simpleExpressionName<\/span><\/span>\n    | <\/span>LPAR<\/span> <\/span>exprs<\/span>+=<\/span>expression<\/span> (<\/span>COMMA<\/span> <\/span>exprs<\/span>+=<\/span>expression<\/span>)* <\/span>RPAR<\/span>    # <\/span>expressionList<\/span><\/span>\n    | <\/span>expr<\/span>=<\/span>caseExpression<\/span>                                       # <\/span>caseExpr<\/span><\/span>\n    | <\/span>operator<\/span>=<\/span>unaryOperator<\/span> <\/span>expr<\/span>=<\/span>expression<\/span>                    # <\/span>unaryExpression<\/span><\/span>\n    | <\/span>expr<\/span>=<\/span>functionExpression<\/span>                                   # <\/span>functionExpr<\/span><\/span>\n    | <\/span>expr<\/span>=<\/span>AST<\/span>                                                  # <\/span>allColumnWildcardExpression<\/span><\/span>\n    | <\/span>left<\/span>=<\/span>expression<\/span> <\/span>operator<\/span>=(<\/span>AST<\/span>|<\/span>SOL<\/span>) <\/span>right<\/span>=<\/span>expression<\/span>       # <\/span>binaryExpression<\/span><\/span>\n    | <\/span>left<\/span>=<\/span>expression<\/span><\/span>\n        (<\/span><\/span>\n              <\/span>operator<\/span>=<\/span>PLUS<\/span><\/span>\n            | <\/span>operator<\/span>=<\/span>MINUS<\/span><\/span>\n            | <\/span>operator<\/span>=<\/span>VERBAR<\/span> <\/span>VERBAR<\/span><\/span>\n        )<\/span><\/span>\n      <\/span>right<\/span>=<\/span>expression<\/span>                                          # <\/span>binaryExpression<\/span><\/span>\n    | <\/span>left<\/span>=<\/span>expression<\/span> <\/span>operator<\/span>=<\/span>K_COLLATE<\/span> <\/span>right<\/span>=<\/span>expression<\/span>       # <\/span>binaryExpression<\/span><\/span>\n    | <\/span>left<\/span>=<\/span>expression<\/span> <\/span>operator<\/span>=<\/span>PERIOD<\/span> <\/span>right<\/span>=<\/span>expression<\/span>          # <\/span>binaryExpression<\/span><\/span>\n;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
ANTLR 4 solves the ambiguity by prioritizing the alternatives in the order of the definition. As a result, multiplication\/division has a higher priority than addition\/subtraction and a lower priority than the function expression on line 11.<\/p>\n\n\n\n
Left-factoring still might be helpful to optimize the runtime performance of the parser. However, it’s not necessary anymore. And I’m happy with a simpler grammar.<\/p>\n\n\n\n
Function Expressions<\/h2>\n\n\n\n
It would be a bad idea to handle each function like to_date<\/code> or to_char<\/code> separately in the grammar. Why? It would be more work and we still would need a solution for custom functions. As a result, we generically implement functions. The most na\u00efve grammar definition would look like this:<\/p>\n\n\n\n
5) Na\u00efve functionExpression<\/span><\/path><\/path><\/svg><\/span>
functionExpression<\/span>:<\/span><\/span>\n    <\/span>name<\/span>=<\/span>sqlName<\/span> <\/span>LPAR<\/span> (<\/span>params<\/span>+=<\/span>expression<\/span> (<\/span>COMMA<\/span> <\/span>params<\/span>+=<\/span>expression<\/span>)*)? <\/span>RPAR<\/span><\/span>\n;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
Parameterless functions that do not allow parentheses like sysdate<\/code> will be treated as simpleExpressionName<\/code>.  This rule handles parameterless functions that require parentheses like sys_guid()<\/code>. And it can handle functions with an unbounded number of parameters.<\/p>\n\n\n\n
So what’s the problem? Why do I call this definition “na\u00efve”? Because the following cases are not covered:<\/p>\n\n\n\n
In line 13 we deal with multiplication and division. Both sides of the operation allow an expression. Unlike grammars based on ANTLR 3, it is no longer necessary to left-factor<\/a> this left-recursion in ANTLR 4<\/a>.<\/p>\n\n\n\n
Both statements will lock the same partition of the table sales<\/code>. The latter is IMO better since it works also with system-generated partition names, which you might use with interval partitioning<\/a>.<\/p>\n\n\n\n