Expressions

语法

expr:
    expr OR expr
  | expr || expr
  | expr AND expr
  | NOT expr
  | ! expr
  | boolean_primary IS [NOT] {TRUE | FALSE | UNKNOWN}
  | boolean_primary

boolean_primary:
    boolean_primary IS [NOT] NULL
  | boolean_primary <=> predicate
  | boolean_primary comparison_operator predicate
  | predicate

comparison_operator: = | >= | > | <= | < | <> | !=

predicate:
    bit_expr [NOT] IN (subquery)
  | bit_expr [NOT] IN (expr [, expr] ...)
  | bit_expr [NOT] BETWEEN bit_expr AND predicate
  | bit_expr [NOT] LIKE simple_expr [ESCAPE simple_expr]
  | bit_expr [NOT] REGEXP bit_expr
  | bit_expr

bit_expr:
   bit_expr + bit_expr
  | bit_expr - bit_expr
  | bit_expr * bit_expr
  | bit_expr / bit_expr
  | bit_expr % bit_expr
  | bit_expr | bit_expr
  | simple_expr

  | bit_expr & bit_expr # 暂未实现
  | bit_expr << bit_expr # 暂未实现
  | bit_expr >> bit_expr # 暂未实现
  | bit_expr ^ bit_expr # 暂未实现

simple_expr:
    literal
  | identifier
  | function_call
  | simple_expr COLLATE collation_name
  | param_marker
  | variable
  | simple_expr || simple_expr
  | + simple_expr
  | - simple_expr
  | ~ simple_expr
  | ! simple_expr
  | BINARY simple_expr
  | (expr [, expr] ...)
  | ROW (expr, expr [, expr] ...)
  | (subquery)
  | EXISTS (subquery)
  | {identifier expr}
  | match_expr
  | case_expr
  | interval_expr

有关运算符的优先级，请参见运算符优先级。

示例

SELECT 1 = 1, 10 >= 1, 9 > 1, 1 <= 3, 2 < 10, 1 <> 2, 1 != 1, 
SELECT 1 IS NULL, 1 IS NOT NULL, 1 IS NULL, 1 IS NOT NULL;
SELECT (15 - 6)*2
SELECT (18 * 6)
SELECT (12 / 6)
SELECT (19 % 6)
SELECT 1 / 1
SELECT 1 % 1
SELECT -1
SELECT !1
SELECT 1 || 1
SELECT * FROM CUSTOMERS WHERE SALARY = 10000;
SELECT * FROM CUSTOMERS WHERE NAME LIKE 'a%';
SELECT * FROM CUSTOMERS WHERE NAME IN ('a', 'b');
SELECT * FROM CUSTOMERS WHERE NAME NOT IN ('a', 'b');
SELECT * FROM CUSTOMERS WHERE NAME BETWEEN 'a' AND 'b';
SELECT * FROM CUSTOMERS WHERE NAME NOT BETWEEN 'a' AND 'b';
SELECT CASE int32_column WHEN 1 THEN '1 值' ELSE '非 1 值' END FROM demo_table
SELECT string_column FROM demo_table WHERE datetime_column LIKE '2019%.160%'
SELECT string_column FROM demo_table WHERE spd < 100 AND id > 10

NULL 表达式

NULL 表示未知或不适用

LDB 采用与 MySQL 相同的计算策略，在计算中存在如下真值表：

x	NOT x
TRUE	FALSE
NULL	NULL
FALSE	TRUE

AND	TRUE	NULL	FALSE
TRUE	TRUE	NULL	FALSE
NULL	NULL	NULL	FALSE
FALSE	FALSE	FALSE	FALSE

说明：

TRUE AND TRUE                   : TRUE
TRUE AND NULL / NULL AND TRUE   : NULL   
TRUE AND FALSE / FALSE AND TRUE : FALSE   
NULL AND NULL                   : NULL
NULL AND FALSE / FALSE AND NULL : FALSE
FALSE AND FALSE                 : FALSE

OR	TRUE	NULL	FALSE
TRUE	TRUE	TRUE	TRUE
NULL	TRUE	NULL	NULL
FALSE	TRUE	NULL	FALSE

说明：

TRUE OR TRUE                  : TRUE
TRUE OR NULL / NULL OR TRUE   : TRUE   
TRUE OR FALSE / FALSE OR TRUE : TRUE   
NULL OR NULL                  : NULL
NULL OR FALSE / FALSE OR NULL : NULL   
FALSE OR FALSE                : FALSE

IS	NULL	NOT NULL	TRUE	FALSE	NOT TRUE	NOT FALSE
TRUE	FALSE	TRUE	TRUE	FALSE	FALSE	TRUE
NULL	TRUE	FALSE	FALSE	FALSE	TRUE	TRUE
FALSE	FALSE	TRUE	FALSE	TRUE	TRUE	FALSE

说明：

TRUE IS NULL: FALSE
TRUE IS NOT NULL: TRUE
TRUE IS TRUE: TRUE
TRUE IS FALSE: FALSE
TRUE IS NOT TRUE: FALSE
TRUE IS NOT FALSE: TRUE

NULL IS NULL: TRUE
NULL IS NOT NULL: FALSE
NULL IS TRUE: FALSE
NULL IS FALSE: FALSE
NULL IS NOT TRUE: TRUE
NULL IS NOT FALSE: TRUE

FALSE IS NULL: FALSE
FALSE IS NOT NULL: TRUE
FALSE IS TRUE: FALSE
FALSE IS FALSE: TRUE
FALSE IS NOT TRUE: TRUE
FALSE IS NOT FALSE: FALSE

NULL 计算除以上真值表所列的其他操作均为 NULL:

1 > NULL: NULL

...