Structure of expression
Expression is widely used in the sql, such as in the where clause,having clause. The expression in General SQL Parser also known as condition or predicate. If you like to retrieve more info from the expression or like to modify the expression before rebuild your sql, then it's very important to know how expression is organized.
Expression is represented by the class TLzCustomExpression, it is organized in a binary tree mode. So the expression : f1>1 and f2=2 will be constructed something like this.
and
|
|
|-------|
| |
> =
| |
|-----| |-----|
| | | |
f1 1 f2 2
So, from this diagram, you may know the basic elements of expression are operator and operand, here "and" is operator, "f1>1" and "f2=4" are operand. In the TLzCustomExpression, lexpr : TLz_node, rexpr : TLz_node are used to represent the left and right side operand separately if any. opname:TSourceToken is the source token which represents the operator. Another key property of TLzCustomExpression is oper : TLzOpType which give you a clearly type definition of this expression that supported by General SQL Parser.
Here is a list of all expressions type specified by oper:TLzOpType, along with their binary tree diagram.
Expr_Arithmetic
+ - / %
String Concatenation Operator
+ ||
Expr_Comparison
= > < >= <= <> != !< !>
Expr_Between Expr_NotBetween
Expr_In Expr_NotIn
Expr_Like Expr_NotLike
Expr_Exists
Expr_IsNull, Expr_IsNotNull
Logical operators
AND OR NOT
Expr_Unary
+ - ~
Expr_Assign
=
Bitwise Operators
& | ^
Expr_Parenthesis
( )
Expr_Comma
,
Expr_subquery
Expr_FuncCall
Expr_Attr
Expr_Const
More expression types
+ Addition
- Subtraction
* Multiplication
/ Division
% Returns the integer remainder of a division. For example, 12 % 5 = 2
Sample: f1+2
Diagram:
+
|
----|----
| |
| |
f1 2
Visit binary parse tree of this expression:
Pre-order: + f1 2
In-order: f1 + 2
Post-order: f1 2 +
+(sql server), represented by + of Expr_Arithmetic
|| (Oracle) Expr_ConcatenationOP
Sample: f1||f2
Diagram:
||
|
----|----
| |
| |
f1 f2
Visit binary parse tree of this expression:
Pre-order: || f1 f2
In-order: f1 || f2
Post-order: f1 f2 ||
= Equal to
> Greater than
< Less than
>= Greater than or equal to
<= Less than or equal to
<> Not equal to
!= Not equal to (not SQL-92 standard)
!< Not less than (not SQL-92 standard)
!> Not greater than (not SQL-92 standard)
Sample: f1>2
Diagram:
>
|
----|----
| |
| |
f1 2
Visit binary parse tree of this expression:
Pre-order: > f1 2
In-order: f1 > 2
Post-order: f1 2 >
ALL/ANY/SOME Compares a scalar value with a single-column set of values. So, if oper : TLzOpType is type of Expr_Comparison, then groupcompareType : TLzOPGroupCompareType maybe one of the followings TLzOPGroupCompareType = (gctNone,gctany,gctSome,gctAll);
Sample: edate [NOT] BETWEEN '01-JAN-2004' AND '01-APR-2004'
Diagram:
[NOT] BETWEEN
|
----|----
| |
| |
edate AND
|
|
----|---------
| |
| |
'01-JAN-2004' '01-APR-2004'
Visit binary parse tree of this expression:
Pre-order: between edate and(expr_betweento) '01-JAN-2004' '01-APR-2004'
In-order: edate between '01-JAN-2004' and(expr_betweento) '01-APR-2004'
Post-order: edate '01-JAN-2004' '01-APR-2004' and(expr_betweento) between
Sample: f1 in (1,2,3)
Diagram:
in
|
----|----
| |
| |
f1 ()
|
---------
| |
nil ,
-----------
| |
1 ,
-----------
| |
2 3
Visit binary parse tree of this expression:
Pre-order: in f1 () , 1 , 2 3
In-order: f1 in 1 , 2 , 3 ()
Post-order: f1 1 2 3 , , () in
Sample: lname LIKE 'Fud%'
Diagram:
LIKE
|
----|----
| |
| |
lname 'Fud%'
Visit binary parse tree of this expression:
Pre-order: like lname 'Fud%'
In-order: lname like 'Fud%'
Post-order: lname 'Fud%' like
Expr: lname LIKE '\%\_\\%' ESCAPE '\'
Diagram:
LIKE
|
----|----
| |
| |
lname ESCAPE
|
|
----|----
| |
| |
'\%\_\\%' '\'
Visit binary parse tree of this expression:
Pre-order: like lname escape '\%\_\\%' '\'
In-order: lname like '\%\_\\%' escape '\'
Post-order: lname '\%\_\\%' '\' escape like
Sample: exists (select f1 from t1)
Diagram:
exists
|
----|----
| |
| |
nil (select f1 from t1)
Visit binary parse tree of this expression:
This is a leaf node of expression, so no more iterate.
you can check right node of this tree to find more info about subquery.
right node of this expression cab be casted to TSelectSqlStatement
Sample: f1 is null
Diagram:
is null
|
----|----
| |
| |
f1 nil
Visit binary parse tree of this expression:
Pre-order: Expr_IsNull f1
In-order: f1 Expr_IsNull
Post-order: f1 Expr_IsNull
Expr_AND
Expr_OR
Expr_NOT
Sample: f1>1 and f2=2
Diagram:
and
|
----|----
| |
| |
f1>1 f2=2
Visit binary parse tree of this expression:
Pre-order: and > f1 1 = f2 2
In-order: f1 > 1 and f2 = 2
Post-order: f1 1 > f2 2 = and
Sample: not f1>1
Diagram:
not
|
----|----
| |
| |
nil f1>1
Note: In this expression, left operand is null.
Visit binary parse tree of this expression:
Pre-order: not > f1 1
In-order: not f1 > 1
Post-order: f1 1 > not
+ (Positive) Numeric value is positive.
- (Negative) Numeric value is negative.
~ (Bitwise NOT) Returns the ones complement of the number.
Sample: -1
Diagram:
-
|
----|----
| |
| |
nil 1
Note: In this expression, left operand is null.
Visit binary parse tree of this expression:
Pre-order: - 1
In-order: - 1
Post-order: 1 -
Sample: f1 = 2
Diagram:
=
|
----|----
| |
| |
f1 2
Visit binary parse tree of this expression:
Pre-order: = f1 2
In-order: f1 = 2
Post-order: f1 2 =
Assignment operator = maybe mishandled by Expr_Comparison equal operator =, or vice versa.
= in following clauses must be Assignment operator
SQL Server:
select list in select statement
set clause in update statement
opt_output_clause
index_params in create index statement
& (Bitwise AND) Bitwise AND (two operands).
| (Bitwise OR) Bitwise OR (two operands).
^ (Bitwise Exclusive OR) Bitwise exclusive OR (two operands).
Sample: f1 & 2
Diagram:
&
|
----|----
| |
| |
f1 2
Visit binary parse tree of this expression:
Pre-order: & f1 2
In-order: f1 & 2
Post-order: f1 2 &
() The parentheses are grouping operators that make sure that all the operators in the expression within the parentheses are evaluated before the resulting expression is combined with another.
Sample: (1,2,3)
Diagram:
()
|
---------
| |
nil ,
-----------
| |
1 ,
-----------
| |
2 3
Visit binary parse tree of this expression:
Pre-order: () , 1 , 2 3
In-order: 1 , 2 , 3 ()
Post-order: 1 2 3 , , ()
This expression use comma(,) to connect left and right node.
Sample: (1,2,3)
Diagram:
()
|
---------
| |
nil ,
-----------
| |
1 ,
-----------
| |
2 3
Visit binary parse tree of this expression:
Pre-order: () , 1 , 2 3
In-order: 1 , 2 , 3 ()
Post-order: 1 2 3 , , ()
Left node of this expression is a parse tree node with type of TSelectSqlStatement. and right node is null.
This is a leaf node of a binary tree that represents a expression.
Left node of this expression is a parse tree node with type of TLz_FuncCall. and right node is null.
This is a leaf node of a binary tree that represents a expression.
Left node of this expression is a parse tree node with type of TLz_Attr. and right node is null.
This is a leaf node of a binary tree that represents a expression.
Expression of type Expr_Attr is often used to represent a database object. such as empno in empno > 1, scott.empno in scott.empno > 1.
You can check left node of this expression which is type of TLz_Attr for more detail info.
Left node of this expression is a parse tree node with type of TLz_Const. and right node is null.
This is a leaf node of a binary tree that represents a expression.
Expression of type Expr_Const is used to represent the const in an expression such as integer, float and single quote string.
1000 in expr: sal > 1000, 'John' in expr: empname like 'John'.
Case function.
Rollup function.
Cube function.
grouping sets.
left node of this expression is a parse tree node with type of Tlz_Ident. and right node is null.
Old left join syntax of SQL Server, t1.f1 *= t2.f2 in where clause
Old right join syntax of SQL Server, t1.f1 =* t2.f2 in where clause
Old join sytle of oracle, expr1(+)
cursor (select f1 from t1)
expr1 at time zone expr2
Oracle: expr at local
expr1 day to second
expr1 year to month
function expression in pl/sql.
condition expression in pl/sql.
expression in pl/sql.
timestamp literal of SQL Server.
DB2 specific
DB2 specific.
Unknown expression, check opname:TSourceToken for operator, left and right node for child node.
Unknown expression, check opname:TSourceToken for operator, left and right node for child node.
Unknown expression, check opname:TSourceToken for operator, left and right node for child node.
Unknown expression, check opname:TSourceToken for operator, left and right node for child node.
Leaf and non-leaf node of expression binary tree
Leaf node has no child node which type is TLzCustomExpression. In other words, lexpr, rexpr of leaf node is not the type of TLzCustomExpression.
non-leaf node has at least one child node that is type of TLzCustomExpression.
Expression with expr type of Expr_subquery, Expr_FuncCall, Expr_Case, Expr_Attr, Expr_Const, Expr_Cursor is leaf node.
You can visit the expression binary tree in pre-order,in-order and post-order by using
PreOrderTraverse(pVisit : TLzExprVisitFunc)
procedure InOrderTraverse(pVisit : TLzExprVisitFunc);
procedure PostOrderTraverse(pVisit : TLzExprVisitFunc);
pVisit : TLzExprVisitFunc is a user defined function to process the tree node.
Take f1+f2>3 for example:
pre-order: > + f1 f2 3
in-order: f1 + f2 > 3
post-order: f1 f2 + 3 >
Usually, expression is created by parser after process the sql query. However, you can create expression directly by using another constructor of TLzCustomExpression by specifing the AOwner: TComponent; pDBVendor : TDBVendor; pStr : LzString.
Here is an example shows how to use a manual created expression together with a parser created one, and form a new expression.
Expression created by parser: f1+f2>3
Diagram:
>
|
----|-----
| |
| |
+ 3
|
----|-----
| |
| |
f1 f2
Create a expression f3-f4, and replace left node of f1+f2>3, then
The expression will be like this : f1+f2> f3-f4
Diagram:
>
|
----|--------
| |
| |
+ -
| |
----|----- --|---
| | | |
| | | |
f1 f2 f3 f4
Modify expression, especially modify predicate in where clause is a feature requested by lots of users. Although it's possbile, before ver1.5, It's a tough task to modify the expression . Now, we add two features to expression which make it very easy to manipulate expression.
1.Create your own visitor to visit expression in pre-order/in-order/post-order.
2. Rebuild expression automatically after you set different parts of the expression, and still make expression valid in the predicate. so, if you remove f2 from this expression: f1>1 and f2<10, it will become to f1>1 which is more reasonable than f1>1 and <10
The way to modify an expression is as easy as this: expr.AsText = NewValue
Note: If you want to remove expression, just assign a space (not an empty string) to it like this: expr.AsText = ' '
Check http://www.sqlparser.com/dl/samples/visitexpression.zip to find out more detail info about how to use expression in your sql.