test.in.txt

非常棒的java数据库

drop table address;
> ok

select cast('12345678123456781234567812345678' as uuid);
> '12345678-1234-5678-1234-567812345678'
> --------------------------------------
> 12345678-1234-5678-1234-567812345678
> rows: 1

select cast('000102030405060708090a0b0c0d0e0f' as uuid);
> '00010203-0405-0607-0809-0a0b0c0d0e0f'
> --------------------------------------
> 00010203-0405-0607-0809-0a0b0c0d0e0f
> rows: 1

CREATE ALIAS PARSE_INT2 FOR "java.lang.Integer.parseInt(java.lang.String, int)";
> ok

select min(SUBSTRING(random_uuid(), 15,1)='4') from system_range(1, 10);
> MIN(SUBSTRING(RANDOM_UUID(), 15, 1) = '4')
> ------------------------------------------
> TRUE
> rows: 1

select min(8=bitand(12, PARSE_INT2(SUBSTRING(random_uuid(), 20,1), 16))) from system_range(1, 10);
> MIN(8 = BITAND(12, PARSE_INT2(SUBSTRING(RANDOM_UUID(), 20, 1), 16)))
> --------------------------------------------------------------------
> TRUE
> rows: 1

drop alias PARSE_INT2;
> ok

create memory table test(name varchar check(name = upper(name)));
> ok

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

insert into test values('aa');
> exception

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

script nodata nopasswords nosettings;
> SCRIPT
> -------------------------------------------------------------------------------
> -- 2 = SELECT COUNT(*) FROM PUBLIC.TEST;
> CREATE MEMORY TABLE PUBLIC.TEST(     NAME VARCHAR CHECK (NAME = UPPER(NAME)) );
> CREATE USER IF NOT EXISTS SA PASSWORD '' ADMIN;
> rows: 3

drop table test;
> ok

create domain email as varchar(200) check (position('@' in value) > 1);
> ok

create domain gmail as email default '@gmail.com' check (position('gmail' in value) > 1);
> ok

create memory table address(id int primary key, name email, name2 gmail);
> ok

insert into address(id, name, name2) values(1, 'test@abc', '[e-mail]');
> update count: 1

insert into address(id, name, name2) values(2, 'test@abc', 'test@acme');
> exception

insert into address(id, name, name2) values(3, 'test_abc', 'test@gmail');
> exception

insert into address2(name) values('test@abc');
> exception

CREATE DOMAIN STRING AS VARCHAR(255) DEFAULT '' NOT NULL;
> ok

CREATE DOMAIN IF NOT EXISTS STRING AS VARCHAR(255) DEFAULT '' NOT NULL;
> ok

CREATE DOMAIN STRING1 AS VARCHAR NULL;
> ok

CREATE DOMAIN STRING2 AS VARCHAR NOT NULL;
> ok

CREATE DOMAIN STRING3 AS VARCHAR DEFAULT '<empty>';
> ok

create domain string_x as string3;
> ok

create memory table test(a string, b string1, c string2, d string3);
> ok

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

select * from test;
> A B    C    D
> - ---- ---- -------
>   null null <empty>
> rows: 1

select DOMAIN_NAME, COLUMN_DEFAULT, IS_NULLABLE, DATA_TYPE, PRECISION, SCALE, TYPE_NAME, SELECTIVITY, CHECK_CONSTRAINT, REMARKS, SQL from information_schema.domains;
> DOMAIN_NAME COLUMN_DEFAULT IS_NULLABLE DATA_TYPE PRECISION  SCALE TYPE_NAME SELECTIVITY CHECK_CONSTRAINT                                                REMARKS SQL
> ----------- -------------- ----------- --------- ---------- ----- --------- ----------- --------------------------------------------------------------- ------- ------------------------------------------------------------------------------------------------------------------------------
> EMAIL       null           YES         12        200        0     VARCHAR   50          (POSITION('@', VALUE) > 1)                                              CREATE DOMAIN EMAIL AS VARCHAR(200) CHECK (POSITION('@', VALUE) > 1)
> GMAIL       '@gmail.com'   YES         12        200        0     VARCHAR   50          ((POSITION('@', VALUE) > 1) AND (POSITION('gmail', VALUE) > 1))         CREATE DOMAIN GMAIL AS VARCHAR(200) DEFAULT '@gmail.com' CHECK ((POSITION('@', VALUE) > 1) AND (POSITION('gmail', VALUE) > 1))
> STRING      ''             NO          12        255        0     VARCHAR   50                                                                                  CREATE DOMAIN STRING AS VARCHAR(255) DEFAULT '' NOT NULL
> STRING1     null           YES         12        2147483647 0     VARCHAR   50                                                                                  CREATE DOMAIN STRING1 AS VARCHAR
> STRING2     null           NO          12        2147483647 0     VARCHAR   50                                                                                  CREATE DOMAIN STRING2 AS VARCHAR NOT NULL
> STRING3     '<empty>'      YES         12        2147483647 0     VARCHAR   50                                                                                  CREATE DOMAIN STRING3 AS VARCHAR DEFAULT '<empty>'
> STRING_X    '<empty>'      YES         12        2147483647 0     VARCHAR   50                                                                                  CREATE DOMAIN STRING_X AS VARCHAR DEFAULT '<empty>'
> rows: 7

script nodata nopasswords nosettings;
> SCRIPT
> ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
> -- 1 = SELECT COUNT(*) FROM PUBLIC.ADDRESS;
> -- 1 = SELECT COUNT(*) FROM PUBLIC.TEST;
> ALTER TABLE PUBLIC.ADDRESS ADD CONSTRAINT PUBLIC.CONSTRAINT_E PRIMARY KEY(ID);
> CREATE DOMAIN EMAIL AS VARCHAR(200) CHECK (POSITION('@', VALUE) > 1);
> CREATE DOMAIN GMAIL AS VARCHAR(200) DEFAULT '@gmail.com' CHECK ((POSITION('@', VALUE) > 1) AND (POSITION('gmail', VALUE) > 1));
> CREATE DOMAIN STRING AS VARCHAR(255) DEFAULT '' NOT NULL;
> CREATE DOMAIN STRING1 AS VARCHAR;
> CREATE DOMAIN STRING2 AS VARCHAR NOT NULL;
> CREATE DOMAIN STRING3 AS VARCHAR DEFAULT '<empty>';
> CREATE DOMAIN STRING_X AS VARCHAR DEFAULT '<empty>';
> CREATE MEMORY TABLE PUBLIC.ADDRESS(     ID INT NOT NULL,     NAME VARCHAR(200) CHECK (POSITION('@', NAME) > 1),     NAME2 VARCHAR(200) DEFAULT '@gmail.com' CHECK ((POSITION('@', NAME2) > 1) AND (POSITION('gmail', NAME2) > 1)) );
> CREATE MEMORY TABLE PUBLIC.TEST(     A VARCHAR(255) DEFAULT '',     B VARCHAR,     C VARCHAR,     D VARCHAR DEFAULT '<empty>' );
> CREATE USER IF NOT EXISTS SA PASSWORD '' ADMIN;
> rows: 13

drop table test;
> ok

drop domain string;
> ok

drop domain string1;
> ok

drop domain string2;
> ok

drop domain string3;
> ok

drop domain string_x;
> ok

drop table address;
> ok

drop domain email;
> ok

drop domain gmail;
> ok

create force view address_view as select * from address;
> ok

create table address(id identity, name varchar check instr(value, '@') > 1);
> exception

create table address(id identity, name varchar check instr(name, '@') > 1);
> ok

drop table address;
> ok

drop view if exists address_view;
> ok

create memory table a(k10 blob(10k), m20 blob(20m), g30 clob(30g));
> ok

script NODATA NOPASSWORDS NOSETTINGS drop;
> SCRIPT
> -------------------------------------------------------------------------------------------------------
> -- 0 = SELECT COUNT(*) FROM PUBLIC.A;
> CREATE MEMORY TABLE PUBLIC.A(     K10 BLOB(10240),     M20 BLOB(20971520),     G30 CLOB(32212254720) );
> CREATE USER IF NOT EXISTS SA PASSWORD '' ADMIN;
> DROP TABLE IF EXISTS PUBLIC.A;
> rows: 4

create table b();
> ok

create table c();
> ok

drop table information_schema.columns;
> exception

create table columns as select * from information_schema.columns;
> ok

create table tables as select * from information_schema.tables where false;
> ok

create table dual2 as select 1 from dual;
> ok

select * from dual2;
> 1
> -
> 1
> rows: 1

drop table dual2, columns, tables;
> ok

drop table a, a;
> ok

drop table b, c;
> ok

CREATE SCHEMA CONST;
> ok

CREATE CONSTANT IF NOT EXISTS ONE VALUE 1;
> ok

COMMENT ON CONSTANT ONE IS 'Eins';
> ok

CREATE CONSTANT IF NOT EXISTS ONE VALUE 1;
> ok

CREATE CONSTANT CONST.ONE VALUE 1;
> ok

SELECT CONSTANT_SCHEMA, CONSTANT_NAME, DATA_TYPE, REMARKS, SQL FROM INFORMATION_SCHEMA.CONSTANTS;
> CONSTANT_SCHEMA CONSTANT_NAME DATA_TYPE REMARKS SQL
> --------------- ------------- --------- ------- ---
> CONST           ONE           4                 1
> PUBLIC          ONE           4         Eins    1
> rows: 2

SELECT ONE, CONST.ONE FROM DUAL;
> 1 1
> - -
> 1 1
> rows: 1

COMMENT ON CONSTANT ONE IS NULL;
> ok

DROP SCHEMA CONST;
> ok

SELECT CONSTANT_SCHEMA, CONSTANT_NAME, DATA_TYPE, REMARKS, SQL FROM INFORMATION_SCHEMA.CONSTANTS;
> CONSTANT_SCHEMA CONSTANT_NAME DATA_TYPE REMARKS SQL
> --------------- ------------- --------- ------- ---
> PUBLIC          ONE           4                 1
> rows: 1

DROP CONSTANT ONE;
> ok

DROP CONSTANT IF EXISTS ONE;
> ok

DROP CONSTANT IF EXISTS ONE;
> ok

CREATE TABLE A (ID_A int primary key);
> ok

CREATE TABLE B (ID_B int primary key);
> ok

CREATE TABLE C (ID_C int primary key);
> ok

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

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

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

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

SELECT * FROM C WHERE NOT EXISTS ((SELECT ID_A FROM A) EXCEPT (SELECT ID_B FROM B));
> ID_C
> ----
> rows: 0

(SELECT ID_A FROM A) EXCEPT (SELECT ID_B FROM B);
> ID_A
> ----
> 2
> rows: 1

drop table a;
> ok

drop table b;
> ok

drop table c;
> ok

CREATE TABLE X (ID INTEGER PRIMARY KEY);
> ok

insert into x values(0), (1), (10);
> update count: 3

SELECT t1.ID, (SELECT t1.id || ':' || AVG(t2.ID) FROM X t2) FROM X t1;
> ID SELECT ((T1.ID || ':') || AVG(T2.ID)) FROM PUBLIC.X T2 /* PUBLIC.X_TABLE_SCAN */
> -- --------------------------------------------------------------------------------
> 0  0:3
> 1  1:3
> 10 10:3
> rows: 3

drop table x;
> ok

select (select t1.x from system_range(1,1) t2) from system_range(1,1) t1;
> SELECT T1.X FROM SYSTEM_RANGE(1, 1) T2 /* PUBLIC.RANGE_INDEX */
> ---------------------------------------------------------------
> 1
> rows: 1

create table test(id int primary key, name varchar);
> ok

insert into test values(rownum, '11'), (rownum, '22'), (rownum, '33');
> update count: 3

select * from test order by id;
> ID NAME
> -- ----
> 1  11
> 2  22
> 3  33
> rows (ordered): 3

select rownum, (select count(*) from test), rownum from test;
> ROWNUM() SELECT COUNT(*) FROM PUBLIC.TEST /* PUBLIC.TEST_TABLE_SCAN */ /* direct lookup */ ROWNUM()
> -------- --------------------------------------------------------------------------------- --------
> 1        3                                                                                 1
> 2        3                                                                                 2
> 3        3                                                                                 3
> rows: 3

delete from test t0 where rownum<2;
> update count: 1

select rownum, * from (select * from test where id>1 order by id desc);
> ROWNUM() ID NAME
> -------- -- ----
> 1        3  33
> 2        2  22
> rows (ordered): 2

update test set name='x' where rownum<2;
> update count: 1

select * from test;
> ID NAME
> -- ----
> 2  x
> 3  33
> rows: 2

merge into test values(2, 'r' || rownum), (10, rownum), (11, rownum);
> update count: 3

select * from test;
> ID NAME
> -- ----
> 10 2
> 11 3
> 2  r1
> 3  33
> rows: 4

call rownum;
> ROWNUM()
> --------
> 1
> rows: 1

drop table test;
> ok

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

create index idx_test_name on test(name);
> ok

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

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

set ignorecase true;
> ok

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

create unique index idx_test2_name on test2(name);
> ok

INSERT INTO TEST2 VALUES(1, 'HElLo');
> update count: 1

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

INSERT INTO TEST2 VALUES(3, 'WoRlD');
> exception

drop index idx_test2_name;
> ok

select * from test where name='HELLO';
> ID NAME
> -- ----
> rows: 0

select * from test2 where name='HELLO';
> ID NAME
> -- -----
> 1  HElLo
> rows: 1

select * from test where name like 'HELLO';
> ID NAME
> -- ----
> rows: 0

select * from test2 where name like 'HELLO';
> ID NAME
> -- -----
> 1  HElLo
> rows: 1

explain plan for select * from test2, test where test2.name = test.name;
> PLAN
> -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
> SELECT TEST2.ID, TEST2.NAME, TEST.ID, TEST.NAME FROM PUBLIC.TEST2 /* PUBLIC.TEST2_TABLE_SCAN */ INNER JOIN PUBLIC.TEST /* PUBLIC.TEST_TABLE_SCAN */ WHERE TEST2.NAME = CAST(TEST.NAME AS VARCHAR_IGNORECASE(255))
> rows: 1

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

explain plan for select * from test, test2 where test2.name = test.name;
> PLAN
> -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
> SELECT TEST.ID, TEST.NAME, TEST2.ID, TEST2.NAME FROM PUBLIC.TEST /* PUBLIC.TEST_TABLE_SCAN */ INNER JOIN PUBLIC.TEST2 /* PUBLIC.TEST2_TABLE_SCAN */ WHERE TEST2.NAME = CAST(TEST.NAME AS VARCHAR_IGNORECASE(255))
> 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

create index idx_test2_name on test2(name);
> ok

explain plan for select * from test2, test where test2.name = test.name;
> PLAN
> ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
> SELECT TEST2.ID, TEST2.NAME, TEST.ID, TEST.NAME FROM PUBLIC.TEST /* PUBLIC.TEST_TABLE_SCAN */ INNER JOIN PUBLIC.TEST2 /* PUBLIC.IDX_TEST2_NAME: NAME = CAST(TEST.NAME AS VARCHAR_IGNORECASE(255)) */ WHERE TEST2.NAME = CAST(TEST.NAME AS VARCHAR_IGNORECASE(255))
> rows: 1

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

explain plan for select * from test, test2 where test2.name = test.name;
> PLAN
> ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
> SELECT TEST.ID, TEST.NAME, TEST2.ID, TEST2.NAME FROM PUBLIC.TEST /* PUBLIC.TEST_TABLE_SCAN */ INNER JOIN PUBLIC.TEST2 /* PUBLIC.IDX_TEST2_NAME: NAME = CAST(TEST.NAME AS VARCHAR_IGNORECASE(255)) */ WHERE TEST2.NAME = CAST(TEST.NAME AS VARCHAR_IGNORECASE(255))