test.in.txt

非常棒的java数据库

> ok

ALTER TABLE A ADD CONSTRAINT AC FOREIGN KEY(SK) REFERENCES A(ID);
> ok

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

INSERT INTO A VALUES(-2, NULL);
> update count: 1

ALTER TABLE A SET REFERENTIAL_INTEGRITY FALSE;
> ok

ALTER TABLE A SET REFERENTIAL_INTEGRITY TRUE CHECK;
> ok

ALTER TABLE A SET REFERENTIAL_INTEGRITY FALSE;
> ok

INSERT INTO A VALUES(2, 3);
> update count: 1

ALTER TABLE A SET REFERENTIAL_INTEGRITY TRUE;
> ok

ALTER TABLE A SET REFERENTIAL_INTEGRITY FALSE;
> ok

ALTER TABLE A SET REFERENTIAL_INTEGRITY TRUE CHECK;
> exception

DROP TABLE A;
> ok

set autocommit true;
> ok

CREATE TABLE PARENT(ID INT);
> ok

CREATE TABLE CHILD(PID INT);
> ok

INSERT INTO PARENT VALUES(1);
> update count: 1

INSERT INTO CHILD VALUES(2);
> update count: 1

ALTER TABLE CHILD ADD CONSTRAINT CP FOREIGN KEY(PID) REFERENCES PARENT(ID);
> exception

UPDATE CHILD SET PID=1;
> update count: 1

ALTER TABLE CHILD ADD CONSTRAINT CP FOREIGN KEY(PID) REFERENCES PARENT(ID);
> ok

DROP TABLE CHILD, PARENT;
> ok

CREATE TABLE A(ID INT PRIMARY KEY, SK INT);
> ok

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

ALTER TABLE A ADD CONSTRAINT AC FOREIGN KEY(SK) REFERENCES A(ID);
> exception

DROP TABLE A;
> ok

CREATE TABLE TEST(ID INT);
> ok

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

ALTER TABLE TEST ADD CONSTRAINT T CHECK ID<100;
> exception

UPDATE TEST SET ID=20 WHERE ID=100;
> update count: 1

ALTER TABLE TEST ADD CONSTRAINT T CHECK ID<100;
> ok

DROP TABLE TEST;
> ok

create table test(id int);
> ok

set autocommit false;
> ok

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

prepare commit tx1;
> ok

commit transaction tx1;
> ok

rollback;
> ok

select * from test;
> ID
> --
> 1
> rows: 1

drop table test;
> ok

set autocommit true;
> ok

CALL REGEXP_REPLACE('abckaboooom', 'o+', 'o');
> 'abckabom'
> ----------
> abckabom
> rows: 1

SELECT 'Hello' ~ 'He.*' T1, 'HELLO' ~ 'He.*' F2, CAST('HELLO' AS VARCHAR_IGNORECASE) ~ 'He.*' T3;
> T1   F2    T3
> ---- ----- ----
> TRUE FALSE TRUE
> rows: 1

SELECT 'Hello' ~* 'He.*' T1, 'HELLO' ~* 'He.*' T2, 'hallo' ~* 'He.*' F3;
> T1   T2   F3
> ---- ---- -----
> TRUE TRUE FALSE
> rows: 1

SELECT 'Hello' !~* 'Ho.*' T1, 'HELLO' !~* 'He.*' F2, 'hallo' !~* 'Ha.*' F3;
> T1   F2    F3
> ---- ----- -----
> TRUE FALSE FALSE
> rows: 1

create table test(parent int primary key, child int, foreign key(child) references (parent));
> ok

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

insert into test values(2, 3);
> exception

set autocommit false;
> ok

set referential_integrity false;
> ok

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

insert into test values(5, 6);
> update count: 1

set referential_integrity true;
> ok

insert into test values(7, 7), (8, 9);
> exception

set autocommit true;
> ok

drop table test;
> ok

create table test as select 1, space(10) from dual where 1=0 union all select x, cast(space(100) as varchar(101)) d from system_range(1, 100);
> ok

drop table test;
> ok

explain select * from system_range(1, 2) where x=x+1 and x=1;
> PLAN
> ---------------------------------------------------------------------------------------------------------------------------------
> SELECT SYSTEM_RANGE.X FROM SYSTEM_RANGE(1, 2) /* PUBLIC.RANGE_INDEX: X = 1 */ WHERE ((X = 1) AND (X = (X + 1))) AND (1 = (X + 1))
> rows: 1

explain select * from system_range(1, 2) where not (x = 1 and x*2 = 2);
> PLAN
> -------------------------------------------------------------------------------------------------------
> SELECT SYSTEM_RANGE.X FROM SYSTEM_RANGE(1, 2) /* PUBLIC.RANGE_INDEX */ WHERE (X <> 1) OR ((X * 2) <> 2)
> rows: 1

explain select * from system_range(1, 10) where (NOT x >= 5);
> PLAN
> ------------------------------------------------------------------------------------------
> SELECT SYSTEM_RANGE.X FROM SYSTEM_RANGE(1, 10) /* PUBLIC.RANGE_INDEX: X < 5 */ WHERE X < 5
> rows: 1

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

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

select * from test where name = -1 and name = id;
> ID NAME
> -- ----
> -1 -1
> rows: 1

explain select * from test where name = -1 and name = id;
> PLAN
> ----------------------------------------------------------------------------------------------------------------------------
> SELECT TEST.ID, TEST.NAME FROM PUBLIC.TEST /* PUBLIC.TEST_TABLE_SCAN */ WHERE (NAME = '-1') AND (CAST(NAME AS INTEGER) = ID)
> rows: 1

DROP TABLE TEST;
> ok

select * from system_range(1, 2) where x=x+1 and x=1;
> X
> -
> rows: 0

CREATE TABLE A as select 6 a;
> ok

CREATE TABLE B(B INT PRIMARY KEY);
> ok

CREATE VIEW V(V) AS (SELECT A FROM A UNION SELECT B FROM B);
> ok

create table C as select * from table(c int = (0,6));
> ok

select * from V, C where V.V  = C.C;
> V C
> - -
> 6 6
> rows: 1

drop table A, B, C, V;
> ok

explain select * from table(id int = (1, 2), name varchar=('Hello', 'World'));
> PLAN
> ------------------------------------------------------------------------------------------------------
> SELECT TABLE.ID, TABLE.NAME FROM TABLE(ID INT=(1, 2), NAME VARCHAR=('Hello', 'World')) /* PUBLIC."" */
> rows: 1

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

CREATE INDEX IDX_FLAG ON TEST(FLAG, NAME);
> ok

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

EXPLAIN SELECT * FROM TEST WHERE FLAG;
> PLAN
> ---------------------------------------------------------------------------------------------------
> SELECT TEST.ID, TEST.FLAG, TEST.NAME FROM PUBLIC.TEST /* PUBLIC.IDX_FLAG: FLAG = TRUE */ WHERE FLAG
> rows: 1

EXPLAIN SELECT * FROM TEST WHERE FLAG AND NAME>'I';
> PLAN
> -----------------------------------------------------------------------------------------------------------------------------------
> SELECT TEST.ID, TEST.FLAG, TEST.NAME FROM PUBLIC.TEST /* PUBLIC.IDX_FLAG: FLAG = TRUE AND NAME > 'I' */ WHERE FLAG AND (NAME > 'I')
> rows: 1

DROP TABLE TEST;
> ok

CREATE TABLE test_table (first_col varchar(20), second_col integer);
> ok

insert into test_table values('a', 10), ('a', 4), ('b', 30), ('b', 3);
> update count: 4

CREATE VIEW test_view AS SELECT first_col AS renamed_col, MIN(second_col) AS also_renamed FROM test_table GROUP BY first_col;
> ok

SELECT * FROM test_view WHERE renamed_col = 'a';
> RENAMED_COL ALSO_RENAMED
> ----------- ------------
> a           4
> rows: 1

drop view test_view;
> ok

drop table test_table;
> ok

create table test(id int);
> ok

explain select id+1 a from test group by id+1;
> PLAN
> ----------------------------------------------------------------------------------
> SELECT (ID + 1) AS A FROM PUBLIC.TEST /* PUBLIC.TEST_TABLE_SCAN */ GROUP BY ID + 1
> rows: 1

drop table test;
> ok

set autocommit off;
> ok

set search_path = public, information_schema;
> ok

select table_name from tables where 1=0;
> TABLE_NAME
> ----------
> rows: 0

set search_path = public;
> ok

set autocommit on;
> ok

create table script.public.x(a int);
> ok

select * from script.PUBLIC.x;
> A
> -
> rows: 0

create index script.public.idx on script.public.x(a);
> ok

drop table script.public.x;
> ok

create table t1 (i int);
> ok

create table t2 (i int);
> ok

create table t3 (i int);
> ok

select a.i from t1 a inner join (select a.i from t2 a inner join (select i from t3) b on a.i=b.i) b on a.i=b.i;
> I
> -
> rows: 0

drop table t1, t2, t3;
> ok

create table d(d double, r real);
> ok

insert into d(d, d, r) values(1.1234567890123456789, 1.1234567890123456789, 3);
> exception

insert into d values(1.1234567890123456789, 1.1234567890123456789);
> update count: 1

select r+d, r+r, d+d from d;
> R + D             R + R     D + D
> ----------------- --------- ------------------
> 2.246913624759111 2.2469137 2.2469135780246914
> rows: 1

drop table d;
> ok

create table test(id int, c char(5), v varchar(5));
> ok

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

insert into test values(2, 'a ', 'a ');
> update count: 1

insert into test values(3, 'abcde      ', 'abcde');
> update count: 1

select distinct length(c) from test order by length(c);
> LENGTH(C)
> ---------
> 1
> 5
> rows (ordered): 2

select id, c, v, length(c), length(v) from test order by id;
> ID C     V     LENGTH(C) LENGTH(V)
> -- ----- ----- --------- ---------
> 1  a     a     1         1
> 2  a     a     1         2
> 3  abcde abcde 5         5
> rows (ordered): 3

select id from test where c='a' order by id;
> ID
> --
> 1
> 2
> rows (ordered): 2

select id from test where c='a ' order by id;
> ID
> --
> 1
> 2
> rows (ordered): 2

select id from test where c=v order by id;
> ID
> --
> 1
> 2
> 3
> rows (ordered): 3

drop table test;
> ok

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

INSERT INTO TEST VALUES(1, '10', NULL), (2, '0', NULL);
> update count: 2

SELECT LEAST(ID, C, NAME), GREATEST(ID, C, NAME), LEAST(NULL, C), GREATEST(NULL, NULL), ID FROM TEST ORDER BY ID;
> LEAST(ID, C, NAME) GREATEST(ID, C, NAME) LEAST(NULL, C) NULL ID
> ------------------ --------------------- -------------- ---- --
> 1                  10                    null           null 1
> 0                  2                     null           null 2
> rows (ordered): 2

DROP TABLE IF EXISTS TEST;
> ok

create table people (family varchar(1) not null, person varchar(1) not null);
> ok

create table cars (family varchar(1) not null, car varchar(1) not null);
> ok

insert into people values(1, 1), (2, 1), (2, 2), (3, 1), (5, 1);
> update count: 5

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

select family, (select count(car) from cars where cars.family = people.family) as x
from people group by family order by family;
> FAMILY X
> ------ -
> 1      0
> 2      2
> 3      3
> 5      0
> rows (ordered): 4

drop table people, cars;
> ok

select (1, 2);
> 1, 2
> ------
> (1, 2)
> rows: 1

select * from table(id int=(1, 2), name varchar=('Hello', 'World')) x order by id;
> ID NAME
> -- -----
> 1  Hello
> 2  World
> rows (ordered): 2

create table array_test(x array);
> ok

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

select * from array_test where x = (1, 2, 3);
> X
> ---------
> (1, 2, 3)
> rows: 1

drop table array_test;
> ok

select * from (select 1), (select 2);