test.in.txt

非常棒的java数据库

> rows: 1

select * from test, test2 where test2.name = test.name;
> ID NAME  ID NAME
> -- ----- -- -----
> 1  Hello 1  HElLo
> 2  World 2  World
> rows: 2

DROP TABLE IF EXISTS TEST;
> ok

DROP TABLE IF EXISTS TEST2;
> ok

set ignorecase false;
> ok

create table test(f1 varchar, f2 varchar);
> ok

insert into test values('abc','222');
> update count: 1

insert into test values('abc','111');
> update count: 1

insert into test values('abc','333');
> update count: 1

SELECT t.f1, t.f2 FROM test t ORDER BY t.f2;
> F1  F2
> --- ---
> abc 111
> abc 222
> abc 333
> rows (ordered): 3

SELECT t1.f1, t1.f2, t2.f1, t2.f2 FROM test t1, test t2 ORDER BY t2.f2;
> F1  F2  F1  F2
> --- --- --- ---
> abc 222 abc 111
> abc 111 abc 111
> abc 333 abc 111
> abc 222 abc 222
> abc 111 abc 222
> abc 333 abc 222
> abc 222 abc 333
> abc 111 abc 333
> abc 333 abc 333
> rows (ordered): 9

drop table if exists test;
> ok

CREATE TABLE TEST(ID INT PRIMARY KEY, NAME VARCHAR(255));
> ok

INSERT INTO TEST VALUES(1, 'Hello');
> update count: 1

explain select t0.id, t1.id from test t0, test t1 order by t0.id, t1.id;
> PLAN
> -----------------------------------------------------------------------------------------------------------------------------------------
> SELECT T0.ID, T1.ID FROM PUBLIC.TEST T0 /* PUBLIC.TEST_TABLE_SCAN */ INNER JOIN PUBLIC.TEST T1 /* PUBLIC.TEST_TABLE_SCAN */ ORDER BY 1, 2
> rows (ordered): 1

INSERT INTO TEST VALUES(2, 'World');
> update count: 1

SELECT id, sum(id) FROM test GROUP BY id ORDER BY id*sum(id);
> ID SUM(ID)
> -- -------
> 1  1
> 2  2
> rows (ordered): 2

select *
from test t1
inner join test t2 on t2.id=t1.id
inner join test t3 on t3.id=t2.id
where exists (select 1 from test t4 where t2.id=t4.id);
> ID NAME  ID NAME  ID NAME
> -- ----- -- ----- -- -----
> 1  Hello 1  Hello 1  Hello
> 2  World 2  World 2  World
> rows: 2

explain select * from test t1 where id in(select id from test t2 where t1.id=t2.id);
> PLAN
> ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
> SELECT T1.ID, T1.NAME FROM PUBLIC.TEST T1 /* PUBLIC.TEST_TABLE_SCAN */ WHERE ID IN(SELECT ID FROM PUBLIC.TEST T2 /* PUBLIC.PRIMARY_KEY_2: ID = T1.ID */ WHERE T1.ID = T2.ID)
> rows: 1

select * from test t1 where id in(select id from test t2 where t1.id=t2.id);
> ID NAME
> -- -----
> 1  Hello
> 2  World
> rows: 2

explain select * from test t1 where id in(id, id+1);
> PLAN
> ------------------------------------------------------------------------------------------------
> SELECT T1.ID, T1.NAME FROM PUBLIC.TEST T1 /* PUBLIC.TEST_TABLE_SCAN */ WHERE ID IN(ID, (ID + 1))
> rows: 1

select * from test t1 where id in(id, id+1);
> ID NAME
> -- -----
> 1  Hello
> 2  World
> rows: 2

explain select * from test t1 where id in(id);
> PLAN
> ------------------------------------------------------------------------------------
> SELECT T1.ID, T1.NAME FROM PUBLIC.TEST T1 /* PUBLIC.TEST_TABLE_SCAN */ WHERE ID = ID
> rows: 1

select * from test t1 where id in(id);
> ID NAME
> -- -----
> 1  Hello
> 2  World
> rows: 2

explain select * from test t1 where id in(select id from test);
> PLAN
> -------------------------------------------------------------------------------------------------------------------------------------------
> SELECT T1.ID, T1.NAME FROM PUBLIC.TEST T1 /* PUBLIC.TEST_TABLE_SCAN */ WHERE ID IN(SELECT ID FROM PUBLIC.TEST /* PUBLIC.TEST_TABLE_SCAN */)
> rows: 1

select * from test t1 where id in(select id from test);
> ID NAME
> -- -----
> 1  Hello
> 2  World
> rows: 2

explain select * from test t1 where id in(1, select max(id) from test);
> PLAN
> -----------------------------------------------------------------------------------------------------------
> SELECT T1.ID, T1.NAME FROM PUBLIC.TEST T1 /* PUBLIC.PRIMARY_KEY_2: ID >= 1 AND ID <= 2 */ WHERE ID IN(1, 2)
> rows: 1

select * from test t1 where id in(1, select max(id) from test);
> ID NAME
> -- -----
> 1  Hello
> 2  World
> rows: 2

explain select * from test t1 where id in(1, select max(id) from test t2 where t1.id=t2.id);
> PLAN
> --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
> SELECT T1.ID, T1.NAME FROM PUBLIC.TEST T1 /* PUBLIC.TEST_TABLE_SCAN */ WHERE ID IN(1, (SELECT MAX(ID) FROM PUBLIC.TEST T2 /* PUBLIC.PRIMARY_KEY_2: ID = T1.ID */ WHERE T1.ID = T2.ID))
> rows: 1

select * from test t1 where id in(1, select max(id) from test t2 where t1.id=t2.id);
> ID NAME
> -- -----
> 1  Hello
> 2  World
> rows: 2

DROP TABLE TEST;
> ok

create force view t1 as select * from t1;
> ok

select * from t1;
> exception

drop table t1;
> ok

create table one (id int primary key);
> ok

create table two (id int primary key, val date);
> ok

insert into one values(0);
> update count: 1

insert into one values(1);
> update count: 1

insert into one values(2);
> update count: 1

insert into two values(0, null);
> update count: 1

insert into two values(1, DATE'2006-01-01');
> update count: 1

insert into two values(2, DATE'2006-07-01');
> update count: 1

insert into two values(3, null);
> update count: 1

select * from one;
> ID
> --
> 0
> 1
> 2
> rows: 3

select * from two;
> ID VAL
> -- ----------
> 0  null
> 1  2006-01-01
> 2  2006-07-01
> 3  null
> rows: 4

-- Query #1: should return one row
-- okay
select * from one natural join two left join two three on
one.id=three.id left join one four on two.id=four.id where three.val
is null;
> ID ID VAL  ID VAL  ID
> -- -- ---- -- ---- --
> 0  0  null 0  null 0
> rows: 1

-- Query #2: should return one row
-- okay
select * from one natural join two left join two three on
one.id=three.id left join one four on two.id=four.id where
three.val>=DATE'2006-07-01';
> ID ID VAL        ID VAL        ID
> -- -- ---------- -- ---------- --
> 2  2  2006-07-01 2  2006-07-01 2
> rows: 1

-- Query #3: should return the union of #1 and #2
select * from one natural join two left join two three on
one.id=three.id left join one four on two.id=four.id where three.val
is null or three.val>=DATE'2006-07-01';
> ID ID VAL        ID VAL        ID
> -- -- ---------- -- ---------- --
> 0  0  null       0  null       0
> 2  2  2006-07-01 2  2006-07-01 2
> rows: 2

explain select * from one natural join two left join two three on
one.id=three.id left join one four on two.id=four.id where three.val
is null or three.val>=DATE'2006-07-01';
> PLAN
> ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
> SELECT ONE.ID, TWO.ID, TWO.VAL, THREE.ID, THREE.VAL, FOUR.ID FROM PUBLIC.ONE /* PUBLIC.ONE_TABLE_SCAN */ INNER JOIN PUBLIC.TWO /* PUBLIC.PRIMARY_KEY_14: ID = PUBLIC.ONE.ID AND ID = PUBLIC.ONE.ID */ /* WHERE PUBLIC.ONE.ID = PUBLIC.TWO.ID */ LEFT OUTER JOIN PUBLIC.TWO THREE /* PUBLIC.PRIMARY_KEY_14: ID = ONE.ID */ ON ONE.ID = THREE.ID LEFT OUTER JOIN PUBLIC.ONE FOUR /* PUBLIC.PRIMARY_KEY_1: ID = TWO.ID */ ON TWO.ID = FOUR.ID WHERE (PUBLIC.ONE.ID = PUBLIC.TWO.ID) AND ((THREE.VAL IS NULL) OR (THREE.VAL >= DATE '2006-07-01'))
> rows: 1

-- Query #4: same as #3, but the joins have been manually re-ordered
-- Correct result set, same as expected for #3.
select * from one natural join two left join one four on
two.id=four.id left join two three on one.id=three.id where three.val
is null or three.val>=DATE'2006-07-01';
> ID ID VAL        ID ID VAL
> -- -- ---------- -- -- ----------
> 0  0  null       0  0  null
> 2  2  2006-07-01 2  2  2006-07-01
> rows: 2

drop table one;
> ok

drop table two;
> ok

CREATE TABLE TEST(id INT PRIMARY KEY, foo BIGINT);
> ok

INSERT INTO TEST VALUES(1, 100);
> update count: 1

INSERT INTO TEST VALUES(2, 123456789012345678);
> update count: 1

SELECT * FROM TEST WHERE foo = 123456789014567;
> ID FOO
> -- ---
> rows: 0

DROP TABLE IF EXISTS TEST;
> ok

create table test1 (id int primary key);
> ok

create table test2 (id int primary key);
> ok

create table test3 (id int primary key);
> ok

insert into test1 values(1);
> update count: 1

insert into test2 values(1);
> update count: 1

insert into test3 values(1);
> update count: 1

select * from test1
inner join test2 on test1.id=test2.id left
outer join test3 on test2.id=test3.id
where test3.id is null;
> ID ID ID
> -- -- --
> rows: 0

explain select * from test1
inner join test2 on test1.id=test2.id left
outer join test3 on test2.id=test3.id
where test3.id is null;
> PLAN
> -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
> SELECT TEST1.ID, TEST2.ID, TEST3.ID FROM PUBLIC.TEST1 /* PUBLIC.TEST1_TABLE_SCAN */ INNER JOIN PUBLIC.TEST2 /* PUBLIC.PRIMARY_KEY_4C: ID = TEST1.ID AND ID = TEST1.ID */ /* WHERE TEST1.ID = TEST2.ID */ LEFT OUTER JOIN PUBLIC.TEST3 /* PUBLIC.PRIMARY_KEY_4C0: ID = TEST2.ID */ ON TEST2.ID = TEST3.ID WHERE (TEST3.ID IS NULL) AND (TEST1.ID = TEST2.ID)
> rows: 1

insert into test1 select x from system_range(2, 1000);
> update count: 999

select * from test1
inner join test2 on test1.id=test2.id
left outer join test3 on test2.id=test3.id
where test3.id is null;
> ID ID ID
> -- -- --
> rows: 0

explain select * from test1
inner join test2 on test1.id=test2.id
left outer join test3 on test2.id=test3.id
where test3.id is null;
> PLAN
> --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
> SELECT TEST1.ID, TEST2.ID, TEST3.ID FROM PUBLIC.TEST2 /* PUBLIC.TEST2_TABLE_SCAN */ LEFT OUTER JOIN PUBLIC.TEST3 /* PUBLIC.PRIMARY_KEY_4C0: ID = TEST2.ID */ ON TEST2.ID = TEST3.ID INNER JOIN PUBLIC.TEST1 /* PUBLIC.PRIMARY_KEY_4: ID = TEST2.ID */ WHERE (TEST3.ID IS NULL) AND (TEST1.ID = TEST2.ID)
> rows: 1

SELECT TEST1.ID, TEST2.ID, TEST3.ID
FROM TEST2
LEFT OUTER JOIN TEST3 ON TEST2.ID = TEST3.ID
INNER JOIN TEST1
WHERE TEST3.ID IS NULL AND TEST1.ID = TEST2.ID;
> ID ID ID
> -- -- --
> rows: 0

drop table test1;
> ok

drop table test2;
> ok

drop table test3;
> ok

create table test(v boolean);
> ok

insert into test values(null), (true), (false);
> update count: 3

SELECT CASE WHEN NOT (false IN (null)) THEN false END;
> NULL
> ----
> null
> rows: 1

select a.v as av, b.v as bv, a.v IN (b.v), not a.v IN (b.v) from test a, test b;
> AV    BV    A.V = B.V NOT (A.V = B.V)
> ----- ----- --------- ---------------
> FALSE FALSE TRUE      FALSE
> FALSE TRUE  FALSE     TRUE
> FALSE null  null      null
> TRUE  FALSE FALSE     TRUE
> TRUE  TRUE  TRUE      FALSE
> TRUE  null  null      null
> null  FALSE null      null
> null  TRUE  null      null
> null  null  null      null
> rows: 9

select a.v as av, b.v as bv, a.v IN (b.v, null), not a.v IN (b.v, null) from test a, test b;
> AV    BV    A.V IN(B.V, NULL) NOT (A.V IN(B.V, NULL))
> ----- ----- ----------------- -----------------------
> FALSE FALSE TRUE              FALSE
> FALSE TRUE  null              null
> FALSE null  null              null
> TRUE  FALSE null              null
> TRUE  TRUE  TRUE              FALSE
> TRUE  null  null              null
> null  FALSE null              null
> null  TRUE  null              null
> null  null  null              null
> rows: 9

drop table test;
> ok

SELECT CASE WHEN NOT (false IN (null)) THEN false END;
> NULL
> ----
> null
> rows: 1

SELECT DATEDIFF('SECOND', '1900-01-01 00:00:00.001', '1900-01-01 00:00:00.002'), DATEDIFF('SECOND', '2000-01-01 00:00:00.001', '2000-01-01 00:00:00.002');
> 0 0
> - -
> 0 0
> rows: 1

SELECT DATEDIFF('SECOND', '1900-01-01 00:00:00.000', '1900-01-01 00:00:00.001'), DATEDIFF('SECOND', '2000-01-01 00:00:00.000', '2000-01-01 00:00:00.001');
> 0 0
> - -
> 0 0
> rows: 1

SELECT DATEDIFF('MINUTE', '1900-01-01 00:00:00.000', '1900-01-01 00:00:01.000'), DATEDIFF('MINUTE', '2000-01-01 00:00:00.000', '2000-01-01 00:00:01.000');
> 0 0
> - -
> 0 0
> rows: 1

SELECT DATEDIFF('MINUTE', '1900-01-01 00:00:01.000', '1900-01-01 00:00:02.000'), DATEDIFF('MINUTE', '2000-01-01 00:00:01.000', '2000-01-01 00:00:02.000');
> 0 0
> - -
> 0 0
> rows: 1

SELECT DATEDIFF('HOUR', '1900-01-01 00:00:00.000', '1900-01-01 00:00:01.000'), DATEDIFF('HOUR', '2000-01-01 00:00:00.000', '2000-01-01 00:00:01.000');
> 0 0
> - -
> 0 0
> rows: 1

SELECT DATEDIFF('HOUR', '1900-01-01 00:00:00.001', '1900-01-01 00:00:01.000'), DATEDIFF('HOUR', '2000-01-01 00:00:00.001', '2000-01-01 00:00:01.000');
> 0 0
> - -
> 0 0
> rows: 1

SELECT DATEDIFF('HOUR', '1900-01-01 01:00:00.000', '1900-01-01 01:00:01.000'), DATEDIFF('HOUR', '2000-01-01 01:00:00.000', '2000-01-01 01:00:01.000');
> 0 0
> - -
> 0 0
> rows: 1

SELECT DATEDIFF('HOUR', '1900-01-01 01:00:00.001', '1900-01-01 01:00:01.000'), DATEDIFF('HOUR', '2000-01-01 01:00:00.001', '2000-01-01 01:00:01.000');
> 0 0
> - -
> 0 0
> rows: 1

create table test(id int);
> ok

insert into test values(1), (2), (3), (4);
> update count: 4

(select * from test a, test b) minus (select * from test a, test b);
> ID ID
> -- --
> rows: 0

drop table test;
> ok

call datediff('MS', TIMESTAMP '2001-02-03 04:05:06.789001', TIMESTAMP '2001-02-03 04:05:06.789002');
> 0
> -
> 0
> rows: 1

call datediff('MS', TIMESTAMP '1900-01-01 00:00:01.000', TIMESTAMP '2008-01-01 00:00:00.000');
> 3408134399000
> -------------
> 3408134399000
> rows: 1

call select 1.0/3.0*3.0, 100.0/2.0, -25.0/100.0, 0.0/3.0, 6.9/2.0;
> C1                            C2 C3    C4 C5
> ----------------------------- -- ----- -- ----
> 0.999999999999999999999999990 50 -0.25 0  3.45
> rows: 1

call dateadd('MS', 1, TIMESTAMP '2001-02-03 04:05:06.789001');
> TIMESTAMP '2001-02-03 04:05:06.790001'
> --------------------------------------
> 2001-02-03