Lightdb数据库——where in条件查询功能增强

where in查询的语法示意图

如上图所示，在原始的在where in查询中，in后续的差选必须带有括号，否则无法查询。

本次对In条件查询功能进行增强：

①子查询、多元素链表必须待括号；

②单个元素查询可以不带括号，直接查询，例子如下：

支持类型和用例

目前In后查询不加括号的有：数值类型单个元素、字符串类型单个元素、函数、数值加减运算、函数加减运算等。

以下案例参考的表结构如下：

create table test (

a int,

b varchar(100),

c varchar2(10),

d date,

m timestamp(6),

n ROWID

);

数值

select * from test where a in 2;

字符串

select * from test where b in 'abc';

select * from test where c in 'abc';

日期

select * from test where d in to_date('2022-01-01','yyyy-mm-dd');

select * from test where m in to_timestamp('2022-01-01','yyyy-mm-dd');

Rowid类型

select * from test where n in '(0,1)'::tid;

加减运算

1.数值类型的加减运算：

select * from test where a in 2+1;

select * from test where a in 2-1;

select * from test where a in 2*1;

select * from test where a in 2/1;

2.表的列名的加减运算

select * from test t where a in t.a/1;

select * from test t where a in t.a+1;

select * from test t where a in t.a-1;

select * from test t where a in t.a*1;

select * from test t where a in t.a/1;

select * from test t where a in t.a+t.a;

select * from test t where a in t.a-t.a;

select * from test t where a in t.a*t.a;

3.函数加减运算

select a from test where a in TO_NUMBER(2)+2;

select a from test where a in TO_NUMBER(2)+ TO_NUMBER(2)

函数

SELECT * FROM test WHERE a IN CAST ('1' AS INTEGER);

select * from test where a in TO_NUMBER(2);

select * from test where  m in TIMESTAMP '2022-01-01 00:00:00' AT TIME ZONE 'Asia/Shanghai';

 支持右括号和无括号混合使用

select * from test where a in

  (select a from test where a in TO_NUMBER(2)+2 and 

   b in (select b from test where b in 'test' and b is not null) and

   c in  (select  c from test where c in 'cf' and c like 'c%')and

   d in  to_date('2022-01-01','yyyy-mm-dd') and

   m in TIMESTAMP '2022-01-01' AT TIME ZONE 'Asia/Shanghai' ) ;