test.in.txt

非常棒的java数据库

--- special grammar and test cases ---------------------------------------------------------------------------------------------
(SELECT X FROM DUAL ORDER BY X+2) UNION SELECT X FROM DUAL;
> X
> -
> 1
> 1
> rows (ordered): 2

create table test(a int, b int default 1);
> ok

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

select * from test;
> A B
> - ----
> 1 1
> 2 2
> 3 null
> rows: 3

update test set b = default where a = 2;
> update count: 1

explain update test set b = default where a = 2;
> PLAN
> ---------------------------------------------------------------------------
> UPDATE PUBLIC.TEST /* PUBLIC.TEST_TABLE_SCAN */ SET B = DEFAULT WHERE A = 2
> rows: 1

select * from test;
> A B
> - ----
> 1 1
> 2 1
> 3 null
> rows: 3

update test set a=default;
> update count: 3

drop table test;
> ok

CREATE ROLE X;
> ok

GRANT X TO X;
> exception

CREATE ROLE Y;
> ok

GRANT Y TO X;
> ok

DROP ROLE Y;
> ok

DROP ROLE X;
> ok

create table test as select * from table(id int=(1, 2, 3));
> ok

SELECT * FROM (SELECT * FROM TEST) ORDER BY id;
> ID
> --
> 1
> 2
> 3
> rows (ordered): 3

SELECT * FROM (SELECT * FROM TEST) x ORDER BY id;
> ID
> --
> 1
> 2
> 3
> rows (ordered): 3

drop table test;
> ok

select top sum(1) 0 from dual;
> exception

create table test(id int primary key, name varchar) as select 1, 'Hello World';
> ok

select * from test;
> ID NAME
> -- -----------
> 1  Hello World
> rows: 1

drop table test;
> ok

select rtrim() from dual;
> exception

CREATE TABLE COUNT(X INT);
> ok

CREATE FORCE TRIGGER T_COUNT BEFORE INSERT ON COUNT CALL "com.Unknown";
> ok

INSERT INTO COUNT VALUES(NULL);
> exception

DROP TRIGGER T_COUNT;
> ok

CREATE TABLE ITEMS(ID INT CHECK ID < SELECT MAX(ID) FROM COUNT);
> ok

insert into items values(DEFAULT);
> update count: 1

DROP TABLE COUNT;
> exception

insert into items values(DEFAULT);
> update count: 1

drop table items, count;
> ok

CREATE TABLE TEST(ID INT PRIMARY KEY, LABEL CHAR(20), LOOKUP CHAR(30));
> ok

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

SELECT * FROM TEST;
> ID LABEL LOOKUP
> -- ----- ------
> 1  Mouse MOUSE
> 2  MOUSE Mouse
> rows: 2

DROP TABLE TEST;
> ok

call 'a' regexp '\Ho.*';
> exception

set @t = 0;
> ok

call set(1, 2);
> exception

select x, set(@t, ifnull(@t, 0) + x) from system_range(1, 3);
> X SET(@T, (IFNULL(@T, 0) + X))
> - ----------------------------
> 1 1
> 2 3
> 3 6
> rows: 3

select * from system_range(1, 2) a,
(select * from system_range(1, 2) union select * from system_range(1, 2)
union select * from system_range(1, 1)) v where a.x = v.x;
> X X
> - -
> 1 1
> 2 2
> rows: 2

create table test(id int);
> ok

select * from ((select * from test) union (select * from test)) where id = 0;
> ID
> --
> rows: 0

select * from ((test d1 inner join test d2 on d1.id = d2.id) inner join test d3 on d1.id = d3.id) inner join test d4 on d4.id = d1.id;
> ID ID ID ID
> -- -- -- --
> rows: 0

select * from dual a left join dual b on b.x=(select max(x) from dual);
> exception

drop table test;
> ok

select count(*) from system_range(1, 2) where x in(1, 1, 1);
> COUNT(*)
> --------
> 1
> rows: 1

create table person(id bigint auto_increment, name varchar(100));
> ok

insert into person(name) values ('a'), ('b'), ('c');
> update count: 3

select * from person order by id;
> ID NAME
> -- ----
> 1  a
> 2  b
> 3  c
> rows (ordered): 3

select * from person order by id limit 2;
> ID NAME
> -- ----
> 1  a
> 2  b
> rows (ordered): 2

select * from person order by id limit 2 offset 1;
> ID NAME
> -- ----
> 2  b
> 3  c
> rows (ordered): 2

select * from person order by id limit 2147483647 offset 1;
> ID NAME
> -- ----
> 2  b
> 3  c
> rows (ordered): 2

select * from person order by id limit 2147483647-1 offset 1;
> ID NAME
> -- ----
> 2  b
> 3  c
> rows (ordered): 2

select * from person order by id limit 2147483647-1 offset 2;
> ID NAME
> -- ----
> 3  c
> rows (ordered): 1

select * from person order by id limit 2147483647-2 offset 2;
> ID NAME
> -- ----
> 3  c
> rows (ordered): 1

drop table person;
> ok

CREATE TABLE TEST(ID INTEGER NOT NULL, ID2 INTEGER DEFAULT 0);
> ok

ALTER TABLE test ALTER COLUMN ID2 RENAME TO ID;
> exception

drop table test;
> ok

create table test(id int primary key, data array);
> ok

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

select * from test order by data;
> ID DATA
> -- ---------
> 1  (1, 1)
> 3  (1, 1, 1)
> 2  (1, 2)
> rows (ordered): 3

drop table test;
> ok

CREATE TABLE FOO (A CHAR(10));
> ok

CREATE TABLE BAR AS SELECT * FROM FOO;
> ok

select table_name, numeric_precision from information_schema.columns where column_name = 'A';
> TABLE_NAME NUMERIC_PRECISION
> ---------- -----------------
> BAR        10
> FOO        10
> rows: 2

DROP TABLE FOO, BAR;
> ok

create table multi_pages(dir_num int, bh_id int);
> ok

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

create table b_holding(id int primary key, site varchar(255));
> ok

insert into b_holding values(1, 'Hello'), (2, 'Hello'), (3, 'Hello');
> update count: 3

select * from (select dir_num, count(*) as cnt from multi_pages  t, b_holding bh
where t.bh_id=bh.id and bh.site='Hello' group by dir_num) as x
where cnt < 1000 order by dir_num asc;
> DIR_NUM CNT
> ------- ---
> 1       1
> 2       1
> 3       1
> rows (ordered): 3

explain select * from (select dir_num, count(*) as cnt from multi_pages  t, b_holding bh
where t.bh_id=bh.id and bh.site='Hello' group by dir_num) as x
where cnt < 1000 order by dir_num asc;
> PLAN
> ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
> SELECT X.DIR_NUM, X.CNT FROM (SELECT DIR_NUM, COUNT(*) AS CNT FROM PUBLIC.MULTI_PAGES T /* PUBLIC.MULTI_PAGES_TABLE_SCAN */ INNER JOIN PUBLIC.B_HOLDING BH /* PUBLIC.PRIMARY_KEY_3: ID = T.BH_ID */ WHERE (BH.SITE = 'Hello') AND (T.BH_ID = BH.ID) GROUP BY DIR_NUM) X /* SELECT DIR_NUM, COUNT(*) AS CNT FROM PUBLIC.MULTI_PAGES T /++ PUBLIC.MULTI_PAGES_TABLE_SCAN ++/ INNER JOIN PUBLIC.B_HOLDING BH /++ PUBLIC.PRIMARY_KEY_3: ID = T.BH_ID ++/ WHERE (BH.SITE = 'Hello') AND (T.BH_ID = BH.ID) GROUP BY DIR_NUM HAVING COUNT(*) <= CAST(?1 AS BIGINT): CNT < 1000 */ WHERE CNT < 1000 ORDER BY 1
> rows (ordered): 1

select dir_num, count(*) as cnt from multi_pages  t, b_holding bh
where t.bh_id=bh.id and bh.site='Hello' group by dir_num
having count(*) < 1000 order by dir_num asc;
> DIR_NUM CNT
> ------- ---
> 1       1
> 2       1
> 3       1
> rows (ordered): 3

drop table multi_pages, b_holding;
> ok

select * from dual where x = 1000000000000000000000;
> X
> -
> rows: 0

select * from dual where x = 'Hello';
> exception

CREATE TABLE PARENT(ID INT PRIMARY KEY);
> ok

CREATE TABLE CHILD(ID INT PRIMARY KEY);
> ok

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

SELECT * FROM PARENT P LEFT OUTER JOIN CHILD C ON C.PARENTID=P.ID;
> exception

DROP TABLE PARENT, CHILD;
> ok

create table test(id smallint primary key);
> ok

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

explain select * from test where id = 1;
> PLAN
> -------------------------------------------------------------------------------
> SELECT TEST.ID FROM PUBLIC.TEST /* PUBLIC.PRIMARY_KEY_2: ID = 1 */ WHERE ID = 1
> rows: 1

EXPLAIN SELECT * FROM TEST WHERE ID = (SELECT MAX(ID) FROM TEST);
> PLAN
> -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
> SELECT TEST.ID FROM PUBLIC.TEST /* PUBLIC.PRIMARY_KEY_2: ID = (SELECT MAX(ID) FROM PUBLIC.TEST /++ PUBLIC.TEST_TABLE_SCAN ++/ /++ direct lookup ++/) */ WHERE ID = (SELECT MAX(ID) FROM PUBLIC.TEST /* PUBLIC.TEST_TABLE_SCAN */ /* direct lookup */)
> rows: 1

drop table test;
> ok

create table test(id tinyint primary key);
> ok

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

explain select * from test where id = 3;
> PLAN
> -------------------------------------------------------------------------------
> SELECT TEST.ID FROM PUBLIC.TEST /* PUBLIC.PRIMARY_KEY_2: ID = 3 */ WHERE ID = 3
> rows: 1

explain select * from test where id = 255;
> PLAN
> -------------------------------------------------------------------------------
> SELECT TEST.ID FROM PUBLIC.TEST /* PUBLIC.TEST_TABLE_SCAN: FALSE */ WHERE FALSE
> rows: 1

drop table test;
> ok

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

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

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

drop table test;
> ok

create alias "SYSDATE" for "java.lang.Integer.parseInt(java.lang.String)";
> exception

create alias "MIN" for "java.lang.Integer.parseInt(java.lang.String)";
> exception

create alias "CAST" for "java.lang.Integer.parseInt(java.lang.String)";
> exception

CREATE TABLE PARENT(A INT, B INT, PRIMARY KEY(A, B));
> ok

CREATE TABLE CHILD(A INT, B INT, CONSTRAINT CP FOREIGN KEY(A, B) REFERENCES PARENT(A, B));
> ok

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

INSERT INTO CHILD VALUES(2, NULL), (NULL, 3), (NULL, NULL), (1, 2);
> update count: 4

set autocommit false;
> ok

ALTER TABLE CHILD SET REFERENTIAL_INTEGRITY FALSE;
> ok

ALTER TABLE CHILD SET REFERENTIAL_INTEGRITY TRUE CHECK;
> ok

set autocommit true;
> ok

DROP TABLE CHILD, PARENT;
> ok

CREATE TABLE TEST(BIRTH TIMESTAMP);
> ok

INSERT INTO TEST VALUES('2006-04-03 10:20:30'), ('2006-04-03 10:20:31'), ('2006-05-05 00:00:00'), ('2006-07-03 22:30:00'), ('2006-07-03 22:31:00');
> update count: 5

SELECT * FROM (SELECT CAST(BIRTH AS DATE) B
FROM TEST GROUP BY CAST(BIRTH AS DATE)) A
WHERE A.B >= '2006-05-05';
> B
> ----------
> 2006-05-05
> 2006-07-03
> rows: 2

DROP TABLE TEST;
> ok

CREATE TABLE Parent(ID INT PRIMARY KEY, Name VARCHAR);
> ok

CREATE TABLE Child(ID INT);
> ok

ALTER TABLE Child ADD FOREIGN KEY(ID) REFERENCES Parent(ID);
> ok

INSERT INTO Parent VALUES(1,  '0'), (2,  '0'), (3,  '0');
> update count: 3

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

ALTER TABLE Parent ALTER COLUMN Name BOOLEAN NULL;
> ok

DELETE FROM Parent WHERE ID=3;
> update count: 1

DROP TABLE Parent, Child;
> ok

set autocommit false;
> ok

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