oceanbase

1
--disable_query_log
2
set @@session.explicit_defaults_for_timestamp=off;
3
--enable_query_log
4
# owner: peihan.dph
5
# owner group: SQL3
6
# description:
7
# tags: dml
8

9
--disable_warnings
10
drop table if exists persons,orders, t1, t2, t3;
11
--enable_warnings
12

13
create table persons (id_p int primary key, lastname varchar(20), firstname varchar(20), address varchar(20), city varchar(20));
14

15
insert into persons values(1,'a','a1','a2', 'a3');
16
insert into persons values(2,'b','b1','b2', 'b3');
17
insert into persons values(3,'c','c1','c2', 'c3');
18

19

20
create table orders(id_o int primary key, orderno int, id_p int);
21
insert into orders values(1,1111,3);
22
insert into orders values(2,2222,3);
23
insert into orders values(3,3333,1);
24
insert into orders values(4,4444,1);
25
insert into orders values(5,5555,65);
26

27
select persons.lastname, persons.firstname, orders.orderno from persons left join orders on persons.id_p=orders.id_p order by persons.lastname, persons.firstname, orders.orderno;
28
--sorted_result
29
select persons.lastname, persons.firstname, orders.orderno from persons right join orders on persons.id_p=orders.id_p order by persons.lastname, persons.firstname, orders.orderno;
30

31
select persons.lastname, persons.firstname, orders.orderno
32
from persons
33
inner join orders
34
on persons.id_p=orders.id_p
35
order by persons.lastname, persons.firstname, orders.orderno;
36

37
select persons.lastname, persons.firstname, orders.orderno from persons full join orders on persons.id_p=orders.id_p order by persons.lastname, persons.firstname, orders.orderno;
38

39
create table t1(c1 date);
40
create table t2(c1 int primary key);
41
insert into t1 values('2078-10-10'), ('1970-11-01');
42
insert into t2 values(320);
43
select * from t1, t2 where t1.c1<=t2.c1;
44

45
drop table if exists t1;
46
create table t1(a bigint);
47
insert into t1 values (32);
48

49
drop table if exists t2;
50
create table t2(b year(4), key key_b (b));
51
insert into t2 values (1901);
52

53
create table t3(c year(4));
54
insert into t3 values (1901);
55

56
select a, b from t1, t2 where a > b;
57
select a, c from t1, t3 where a > c;
58

59
drop table if exists t1, t2;
60
create table t1(c1 bigint(92));
61
create table t2(c1 year(4) primary key);
62
insert into t1 values(32);
63
insert into t2 values(1901);
64
select * from t1;
65
select * from t2;
66
select * from t1, t2 where t1.c1>=t2.c1;
67

68
drop table orders,persons, t1, t2, t3;
69

70