oceanbase
37 строк · 999.0 Байт
1--disable_query_log
2set @@session.explicit_defaults_for_timestamp=off;
3--enable_query_log
4# owner: peihan.dph
5# owner group: SQL1
6# description
7#
8--disable_warnings
9drop table if exists t1,t2,t5,t6;
10--enable_warnings
11#
12##
13## bug http://bugfree.corp.taobao.com/bug/199785
14## join with null values
15##
16CREATE TABLE t1 (
17pk int primary key,
18id int,
19gender varchar(1)
20);
21CREATE TABLE t2 (
22user_id int primary key,
23birthday datetime
24);
25
26insert into t1 values (1, NULL, 'M'), (2, 1, 'M'), (3, 2, 'F'),(4, 3, 'F'),(5, 4, 'F'),(6, 5, 'M');
27insert into t2 values (1, '2002-06-09 00:00:00'),(2, '2002-06-09 00:00:00'),(100, '2002-06-09 00:00:00'),
28(3, '2002-06-09 00:00:00'),(4, '2002-06-09 00:00:00');
29
30select id,gender,user_id from t1,t2 where t2.user_id=t1.id;
31####test for null safe equal with join....
32create table t5(a int);
33create table t6(a int);
34insert into t5 values (null), (null);
35insert into t6 values (null), (null);
36select * from t5, t6 where t5.a <=> t6.a;
37drop table t1,t2,t5,t6;
38