|
HSQLDB example source code file (TestSelfSchemaPersistA2.txt)
The HSQLDB TestSelfSchemaPersistA2.txt source code-- TEST OBJECTS -- This TESTS the schema-specific objects created in a previous script. -- (See the *C[12].txt scripts for similar tests of ALTER comands (which -- this script purposefully omits). CREATE USER otheruser PASSWORD otheruser; -- ****************************** MEM Tables /*c2*/SELECT * FROM tblt1; /*c1*/SELECT * FROM tblt1 WHERE i = 0; /*c2*/SELECT * FROM tblt1 WHERE i in (0, 1, 11, 12); /*c1*/SELECT * FROM tblt1 WHERE i < 1; /*c1*/SELECT * FROM tblt1 WHERE tblt1.i = (SELECT i FROM tblj1); /*c1*/SELECT * FROM tblt1 WHERE tblt1.i = (SELECT i FROM tblj1 WHERE tblj1.i = 1); /*c1*/SELECT * FROM tblt1 WHERE tblt1.i in (SELECT i FROM tblj1); /*c0*/SELECT * FROM tblt1 WHERE tblt1.i = (SELECT i FROM tblj1 WHERE i = 0); /*c1*/SELECT * FROM public.tblt1 WHERE tblt1.i = (SELECT i FROM tblj1); /*c1*/SELECT * FROM public.tblt1 WHERE tblt1.i in (SELECT i FROM tblj1); /*c1*/SELECT * FROM public.tblt1 WHERE tblt1.i = (SELECT i FROM tblj1 WHERE i = 1); /*c1*/SELECT * FROM tblt1 WHERE tblt1.i = (SELECT i FROM public.tblj1); /*c1*/SELECT * FROM tblt1 WHERE tblt1.i in (SELECT i FROM public.tblj1); /*c1*/SELECT * FROM tblt1 WHERE tblt1.i = (SELECT i FROM public.tblj1 WHERE i = 1); /*c1*/SELECT * FROM tblt1 WHERE tblt1.i = (SELECT tblj1.i FROM public.tblj1); /*c1*/SELECT * FROM tblt1 WHERE tblt1.i in (SELECT tblj1.i FROM tblj1); /*c1*/SELECT * FROM tblt1 WHERE tblt1.i = (SELECT tblj1.i FROM tblj1 WHERE i = 1); /*c1*/SELECT * FROM tblt1 WHERE tblt1.i = (SELECT i FROM public.tblj1 WHERE tblj1.i = 1); /*c1*/SELECT * FROM tblt1 WHERE tblt1.i = (SELECT tblj1.i FROM tblj1 WHERE tblj1.i = 1); /*c1*/SELECT * FROM tblt1 WHERE i = (SELECT i FROM tblj1); /*c1*/SELECT * FROM tblt1 WHERE i in (SELECT i FROM tblj1); /*c1*/SELECT * FROM tblt1 WHERE i = (SELECT i FROM tblj1 WHERE i = 1); /*c1*/SELECT * FROM public.tblt1 WHERE i = (SELECT i FROM tblj1); /*c1*/SELECT * FROM public.tblt1 WHERE i in (SELECT i FROM tblj1); /*c0*/SELECT * FROM public.tblt1 WHERE i = (SELECT i FROM tblj1 WHERE i = 0); /*c1*/SELECT * FROM tblt1 WHERE i = (SELECT i FROM public.tblj1); /*c1*/SELECT * FROM tblt1 WHERE i in (SELECT i FROM public.tblj1); /*c0*/SELECT * FROM tblt1 WHERE i = (SELECT i FROM public.tblj1 WHERE i = 0); /*c1*/SELECT * FROM tblt1 WHERE i = (SELECT tblj1.i FROM public.tblj1); /*c1*/SELECT * FROM tblt1 WHERE i in (SELECT tblj1.i FROM tblj1); /*c0*/SELECT * FROM tblt1 WHERE i = (SELECT tblj1.i FROM tblj1 WHERE i = 0); /*c1*/SELECT * FROM tblt1 WHERE i = (SELECT i FROM public.tblj1 WHERE tblj1.i = 1); /*c1*/SELECT * FROM tblt1 WHERE i = (SELECT tblj1.i FROM tblj1 WHERE tblj1.i = 1); /*e*/SELECT * FROM system_users WHERE user = 'SA'; /*e*/SELECT * FROM other.tblt1; /*e*/SELECT * FROM other.system_users; /*e*/SELECT * FROM information_schema.tblt1; /*e*/SELECT * FROM public.system_users; /*c2*/SELECT * FROM public.tblt1; /*c1*/SELECT * FROM information_schema.system_users WHERE user = 'SA'; /*c1*/SELECT * FROM public.tblt1 WHERE i = 0; /*e*/SELECT * FROM other.tblt1 WHERE i = 0; /*e*/SELECT * FROM information_schema.tblt1 WHERE i = 0; /*c1*/SELECT * FROM public.tblt1, public.tblj1 WHERE tblt1.i = tblj1.i; /*c1*/SELECT * FROM tblt1, public.tblj1 WHERE tblt1.i = tblj1.i; /*c1*/SELECT * FROM public.tblt1, tblj1 WHERE tblt1.i = tblj1.i; /*c1*/SELECT * FROM tblt1, tblj1 WHERE tblt1.i = tblj1.i; /*c1*/SELECT vc FROM public.tblt1, public.tblj1 WHERE tblt1.i = tblj1.i; /*c1*/SELECT vc FROM tblt1, public.tblj1 WHERE tblt1.i = tblj1.i; /*c1*/SELECT vc FROM public.tblt1, tblj1 WHERE tblt1.i = tblj1.i; /*c1*/SELECT vc FROM tblt1, tblj1 WHERE tblt1.i = tblj1.i; /*c1*/SELECT tblj1.vc FROM public.tblt1, public.tblj1 WHERE tblt1.i = tblj1.i; /*c1*/SELECT tblj1.vc FROM tblt1, public.tblj1 WHERE tblt1.i = tblj1.i; /*c1*/SELECT tblj1.vc FROM public.tblt1, tblj1 WHERE tblt1.i = tblj1.i; /*c1*/SELECT tblj1.vc FROM tblt1, tblj1 WHERE tblt1.i = tblj1.i; /*c1*/SELECT lbla.vc FROM tblt1, tblj1 lbla WHERE tblt1.i = lbla.i; /*c1*/SELECT tblj1.vc FROM tblt1 lblb, tblj1 WHERE lblb.i = tblj1.i; /*c1*/SELECT lbla.vc FROM tblt1 lblb, tblj1 lbla WHERE lblb.i = lbla.i; /*c1*/SELECT lbla.vc FROM public.tblt1, tblj1 lbla WHERE tblt1.i = lbla.i; /*c1*/SELECT tblj1.vc FROM public.tblt1 lblb, tblj1 WHERE lblb.i = tblj1.i; /*c1*/SELECT lbla.vc FROM public.tblt1 lblb, tblj1 lbla WHERE lblb.i = lbla.i; /*c1*/SELECT lbla.vc FROM tblt1, public.tblj1 lbla WHERE tblt1.i = lbla.i; /*c1*/SELECT tblj1.vc FROM tblt1 lblb, public.tblj1 WHERE lblb.i = tblj1.i; /*c1*/SELECT lbla.vc FROM tblt1 lblb, public.tblj1 lbla WHERE lblb.i = lbla.i; /*u0*/SET TABLE tblt1 READONLY true; /*e*/UPDATE tblt1 set i = 11 WHERE i = 1; /*u0*/SET TABLE public.tblt1 READONLY true; /*e*/SET TABLE information_schema.tblt1 READONLY true; /*e*/SET TABLE other.tblt1 READONLY true; /*u0*/SET TABLE tblt1 READONLY false; /*u0*/SET TABLE public.tblt1 READONLY false; /*c2*/SELECT i FROM tblt1; -- N.b.: Do not commit DML changes so that this whole block may be repeated. COMMIT; SET AUTOCOMMIT false; /*u1*/UPDATE tblt1 set i = 11 WHERE i = 1; /*u1*/UPDATE tblt1 set tblt1.i = 12 WHERE i = 11; /*u1*/UPDATE tblt1 set tblt1.i = 13 WHERE tblt1.i = 12; /*u1*/UPDATE public.tblt1 set i = 14 WHERE i = 13; /*u1*/UPDATE public.tblt1 set tblt1.i = 15 WHERE i = 14; /*u1*/UPDATE public.tblt1 set tblt1.i = 16 WHERE tblt1.i = 15; /*e*/UPDATE other.tblt1 set tblt1.i = 17 WHERE tblt1.i = 16; /*e*/UPDATE information_schema.tblt1 set tblt1.i = 17 WHERE tblt1.i = 16; /*u1*/UPDATE tblt1 ali set i = 17 WHERE i = 16; /*u1*/UPDATE tblt1 ali set ali.i = 18 WHERE i = 17; /*u1*/UPDATE tblt1 ali set ali.i = 19 WHERE ali.i = 18; /*u1*/UPDATE tblt1 ali set i = 20 WHERE ali.i = 19; /*u1*/UPDATE public.tblt1 ali set i = 21 WHERE i = 20; /*u1*/UPDATE public.tblt1 ali set ali.i = 22 WHERE i = 21; /*u1*/UPDATE public.tblt1 ali set ali.i = 23 WHERE ali.i = 22; /*u1*/UPDATE public.tblt1 ali set i = 24 WHERE ali.i = 23; /*e*/UPDATE other.tblt1 ali set i = 25 WHERE ali.i = 24; /*e*/UPDATE other.tblt1 ali set i = 25 WHERE .i = 24; /*e*/UPDATE other.tblt1 set i = 25 WHERE i = 24; /*e*/DELETE FROM other.tblt1 ali WHERE ali.i = 24; /*e*/DELETE FROM other.tblt1 ali; /*e*/DELETE FROM other.tblt1 set WHERE i = 24; /*e*/DELETE FROM other.tblt1 WHERE i = 24; /*e*/DELETE FROM system_information.tblj1 WHERE tblj1.i = 1; /*u1*/DELETE FROM tblt1 WHERE i = 0; /*u1*/DELETE FROM public.tblt1 WHERE i = 24; /*u1*/DELETE FROM public.tblj1 WHERE tblj1.i = 1; ROLLBACK; /*c2*/SELECT i FROM tblt1; /*u0*/GRANT ALL ON tblt1 TO otheruser; /*u0*/GRANT ALL ON public.tblt2 TO otheruser; /*e*/GRANT ALL ON other.tblt3 TO otheruser; /*e*/GRANT ALL ON information_schema.tblt3 TO otheruser; /*u0*/REVOKE ALL ON tblt1 FROM otheruser; /*u0*/REVOKE ALL ON public.tblt2 FROM otheruser; /*e*/REVOKE ALL ON other.tblt3 FROM otheruser; /*e*/REVOKE ALL ON information_schema.tblt3 FROM otheruser; /*e*/DROP TABLE other.tblt3; /*e*/DROP TABLE information_schema.tblt4; /*e*/DROP TABLE tblt101; /*e*/DROP TABLE public.tblt101; /*u0*/DROP TABLE tblt101 IF EXISTS; /*u0*/DROP TABLE public.tblt101 IF EXISTS; -- Should "DROP TABLE nonexistentschema.notable IF EXISTS" work? /*u0*/DROP TABLE other.system_users IF exists; /*u0*/DROP TABLE system_users IF exists; -- ****************************** CACH Tables /*c2*/SELECT * FROM ctblt1; /*c1*/SELECT * FROM ctblt1 WHERE i = 0; /*c2*/SELECT * FROM ctblt1 WHERE i in (0, 1, 11, 12); /*c1*/SELECT * FROM ctblt1 WHERE i < 1; /*c1*/SELECT * FROM ctblt1 WHERE ctblt1.i = (SELECT i FROM tblj1); /*c1*/SELECT * FROM ctblt1 WHERE ctblt1.i = (SELECT i FROM tblj1 WHERE tblj1.i = 1); /*c1*/SELECT * FROM ctblt1 WHERE ctblt1.i in (SELECT i FROM tblj1); /*c0*/SELECT * FROM ctblt1 WHERE ctblt1.i = (SELECT i FROM tblj1 WHERE i = 0); /*c1*/SELECT * FROM public.ctblt1 WHERE ctblt1.i = (SELECT i FROM tblj1); /*c1*/SELECT * FROM public.ctblt1 WHERE ctblt1.i in (SELECT i FROM tblj1); /*c1*/SELECT * FROM public.ctblt1 WHERE ctblt1.i = (SELECT i FROM tblj1 WHERE i = 1); /*c1*/SELECT * FROM ctblt1 WHERE ctblt1.i = (SELECT i FROM public.tblj1); /*c1*/SELECT * FROM ctblt1 WHERE ctblt1.i in (SELECT i FROM public.tblj1); /*c1*/SELECT * FROM ctblt1 WHERE ctblt1.i = (SELECT i FROM public.tblj1 WHERE i = 1); /*c1*/SELECT * FROM ctblt1 WHERE ctblt1.i = (SELECT tblj1.i FROM public.tblj1); /*c1*/SELECT * FROM ctblt1 WHERE ctblt1.i in (SELECT tblj1.i FROM tblj1); /*c1*/SELECT * FROM ctblt1 WHERE ctblt1.i = (SELECT tblj1.i FROM tblj1 WHERE i = 1); /*c1*/SELECT * FROM ctblt1 WHERE ctblt1.i = (SELECT i FROM public.tblj1 WHERE tblj1.i = 1); /*c1*/SELECT * FROM ctblt1 WHERE ctblt1.i = (SELECT tblj1.i FROM tblj1 WHERE tblj1.i = 1); /*c1*/SELECT * FROM ctblt1 WHERE i = (SELECT i FROM tblj1); /*c1*/SELECT * FROM ctblt1 WHERE i in (SELECT i FROM tblj1); /*c1*/SELECT * FROM ctblt1 WHERE i = (SELECT i FROM tblj1 WHERE i = 1); /*c1*/SELECT * FROM public.ctblt1 WHERE i = (SELECT i FROM tblj1); /*c1*/SELECT * FROM public.ctblt1 WHERE i in (SELECT i FROM tblj1); /*c0*/SELECT * FROM public.ctblt1 WHERE i = (SELECT i FROM tblj1 WHERE i = 0); /*c1*/SELECT * FROM ctblt1 WHERE i = (SELECT i FROM public.tblj1); /*c1*/SELECT * FROM ctblt1 WHERE i in (SELECT i FROM public.tblj1); /*c0*/SELECT * FROM ctblt1 WHERE i = (SELECT i FROM public.tblj1 WHERE i = 0); /*c1*/SELECT * FROM ctblt1 WHERE i = (SELECT tblj1.i FROM public.tblj1); /*c1*/SELECT * FROM ctblt1 WHERE i in (SELECT tblj1.i FROM tblj1); /*c0*/SELECT * FROM ctblt1 WHERE i = (SELECT tblj1.i FROM tblj1 WHERE i = 0); /*c1*/SELECT * FROM ctblt1 WHERE i = (SELECT i FROM public.tblj1 WHERE tblj1.i = 1); /*c1*/SELECT * FROM ctblt1 WHERE i = (SELECT tblj1.i FROM tblj1 WHERE tblj1.i = 1); /*e*/SELECT * FROM system_users WHERE user = 'SA'; /*e*/SELECT * FROM other.ctblt1; /*e*/SELECT * FROM other.system_users; /*e*/SELECT * FROM information_schema.ctblt1; /*e*/SELECT * FROM public.system_users; /*c2*/SELECT * FROM public.ctblt1; /*c1*/SELECT * FROM information_schema.system_users WHERE user = 'SA'; /*c1*/SELECT * FROM public.ctblt1 WHERE i = 0; /*e*/SELECT * FROM other.ctblt1 WHERE i = 0; /*e*/SELECT * FROM information_schema.ctblt1 WHERE i = 0; /*c1*/SELECT * FROM public.ctblt1, public.tblj1 WHERE ctblt1.i = tblj1.i; /*c1*/SELECT * FROM ctblt1, public.tblj1 WHERE ctblt1.i = tblj1.i; /*c1*/SELECT * FROM public.ctblt1, tblj1 WHERE ctblt1.i = tblj1.i; /*c1*/SELECT * FROM ctblt1, tblj1 WHERE ctblt1.i = tblj1.i; /*c1*/SELECT vc FROM public.ctblt1, public.tblj1 WHERE ctblt1.i = tblj1.i; /*c1*/SELECT vc FROM ctblt1, public.tblj1 WHERE ctblt1.i = tblj1.i; /*c1*/SELECT vc FROM public.ctblt1, tblj1 WHERE ctblt1.i = tblj1.i; /*c1*/SELECT vc FROM ctblt1, tblj1 WHERE ctblt1.i = tblj1.i; /*c1*/SELECT tblj1.vc FROM public.ctblt1, public.tblj1 WHERE ctblt1.i = tblj1.i; /*c1*/SELECT tblj1.vc FROM ctblt1, public.tblj1 WHERE ctblt1.i = tblj1.i; /*c1*/SELECT tblj1.vc FROM public.ctblt1, tblj1 WHERE ctblt1.i = tblj1.i; /*c1*/SELECT tblj1.vc FROM ctblt1, tblj1 WHERE ctblt1.i = tblj1.i; /*c1*/SELECT lbla.vc FROM ctblt1, tblj1 lbla WHERE ctblt1.i = lbla.i; /*c1*/SELECT tblj1.vc FROM ctblt1 lblb, tblj1 WHERE lblb.i = tblj1.i; /*c1*/SELECT lbla.vc FROM ctblt1 lblb, tblj1 lbla WHERE lblb.i = lbla.i; /*c1*/SELECT lbla.vc FROM public.ctblt1, tblj1 lbla WHERE ctblt1.i = lbla.i; /*c1*/SELECT tblj1.vc FROM public.ctblt1 lblb, tblj1 WHERE lblb.i = tblj1.i; /*c1*/SELECT lbla.vc FROM public.ctblt1 lblb, tblj1 lbla WHERE lblb.i = lbla.i; /*c1*/SELECT lbla.vc FROM ctblt1, public.tblj1 lbla WHERE ctblt1.i = lbla.i; /*c1*/SELECT tblj1.vc FROM ctblt1 lblb, public.tblj1 WHERE lblb.i = tblj1.i; /*c1*/SELECT lbla.vc FROM ctblt1 lblb, public.tblj1 lbla WHERE lblb.i = lbla.i; /*u0*/SET TABLE ctblt1 READONLY true; /*e*/UPDATE ctblt1 set i = 11 WHERE i = 1; /*u0*/SET TABLE public.ctblt1 READONLY true; /*e*/SET TABLE information_schema.ctblt1 READONLY true; /*e*/SET TABLE other.ctblt1 READONLY true; /*u0*/SET TABLE ctblt1 READONLY false; /*u0*/SET TABLE public.ctblt1 READONLY false; /*c2*/SELECT i FROM ctblt1; -- N.b.: Do not commit DML changes so that this whole block may be repeated. COMMIT; SET AUTOCOMMIT false; /*u1*/UPDATE ctblt1 set i = 11 WHERE i = 1; /*u1*/UPDATE ctblt1 set ctblt1.i = 12 WHERE i = 11; /*u1*/UPDATE ctblt1 set ctblt1.i = 13 WHERE ctblt1.i = 12; /*u1*/UPDATE public.ctblt1 set i = 14 WHERE i = 13; /*u1*/UPDATE public.ctblt1 set ctblt1.i = 15 WHERE i = 14; /*u1*/UPDATE public.ctblt1 set ctblt1.i = 16 WHERE ctblt1.i = 15; /*e*/UPDATE other.ctblt1 set ctblt1.i = 17 WHERE ctblt1.i = 16; /*e*/UPDATE information_schema.ctblt1 set ctblt1.i = 17 WHERE ctblt1.i = 16; /*u1*/UPDATE ctblt1 ali set i = 17 WHERE i = 16; /*u1*/UPDATE ctblt1 ali set ali.i = 18 WHERE i = 17; /*u1*/UPDATE ctblt1 ali set ali.i = 19 WHERE ali.i = 18; /*u1*/UPDATE ctblt1 ali set i = 20 WHERE ali.i = 19; /*u1*/UPDATE public.ctblt1 ali set i = 21 WHERE i = 20; /*u1*/UPDATE public.ctblt1 ali set ali.i = 22 WHERE i = 21; /*u1*/UPDATE public.ctblt1 ali set ali.i = 23 WHERE ali.i = 22; /*u1*/UPDATE public.ctblt1 ali set i = 24 WHERE ali.i = 23; /*e*/UPDATE other.ctblt1 ali set i = 25 WHERE ali.i = 24; /*e*/UPDATE other.ctblt1 ali set i = 25 WHERE .i = 24; /*e*/UPDATE other.ctblt1 set i = 25 WHERE i = 24; /*e*/DELETE FROM other.ctblt1 ali WHERE ali.i = 24; /*e*/DELETE FROM other.ctblt1 ali; /*e*/DELETE FROM other.ctblt1 set WHERE i = 24; /*e*/DELETE FROM other.ctblt1 WHERE i = 24; /*e*/DELETE FROM system_information.tblj1 WHERE tblj1.i = 1; /*u1*/DELETE FROM ctblt1 WHERE i = 0; /*u1*/DELETE FROM public.ctblt1 WHERE i = 24; /*u1*/DELETE FROM public.tblj1 WHERE tblj1.i = 1; ROLLBACK; /*c2*/SELECT i FROM ctblt1; /*u0*/GRANT ALL ON ctblt1 TO otheruser; /*u0*/GRANT ALL ON public.ctblt2 TO otheruser; /*e*/GRANT ALL ON other.ctblt3 TO otheruser; /*e*/GRANT ALL ON information_schema.ctblt3 TO otheruser; /*u0*/REVOKE ALL ON ctblt1 FROM otheruser; /*u0*/REVOKE ALL ON public.ctblt2 FROM otheruser; /*e*/REVOKE ALL ON other.ctblt3 FROM otheruser; /*e*/REVOKE ALL ON information_schema.ctblt3 FROM otheruser; /*e*/DROP TABLE other.ctblt3; /*e*/DROP TABLE information_schema.ctblt4; /*e*/DROP TABLE ctblt101; /*e*/DROP TABLE public.ctblt101; /*u0*/DROP TABLE ctblt101 IF EXISTS; /*u0*/DROP TABLE public.ctblt101 IF EXISTS; -- Should "DROP TABLE nonexistentschema.notable IF EXISTS" work? /*u0*/DROP TABLE other.system_users IF exists; /*u0*/DROP TABLE system_users IF exists; -- ****************************** Views /*c2*/SELECT * FROM vwv1; /*c2*/SELECT * FROM public.vwv1; /*c2*/SELECT * FROM vwv2; /*c2*/SELECT * FROM vwv3; /*c1*/SELECT * FROM vwv4; /*c1*/SELECT * FROM vwv5; /*c1*/SELECT * FROM vwv8; /*c0*/SELECT * FROM vwv9; /*c0*/SELECT * FROM vwv10; /*c0*/SELECT * FROM vwv11; /*c0*/SELECT * FROM vwv12; /*c0*/SELECT * FROM vwv13; /*c0*/SELECT * FROM vwv14; /*c0*/SELECT * FROM vwv15; /*c0*/SELECT * FROM vwv16; /*c0*/SELECT * FROM vwv17; /*c0*/SELECT * FROM vwv18; /*c1*/SELECT * FROM vwv19; /*c1*/SELECT * FROM vwv20; /*c1*/SELECT * FROM vwv21; /*c1*/SELECT * FROM vwv22; /*c1*/SELECT * FROM vwv23; /*c1*/SELECT * FROM vwv24; /*c1*/SELECT * FROM vwv25; /*c1*/SELECT * FROM vwv26; /*c2*/SELECT * FROM vwv28; /*c1*/SELECT * FROM vwv29; /*c1*/SELECT * FROM vwv30; /*c1*/SELECT * FROM vwv31; /*c0*/SELECT * FROM vwv32; /*c1*/SELECT * FROM vwv33; /*c1*/SELECT * FROM vwv34; /*c0*/SELECT * FROM vwv35; /*c1*/SELECT * FROM vwv36; /*c1*/SELECT * FROM vwv37; /*c1*/SELECT * FROM vwv38; /*c1*/SELECT * FROM vwv39; /*c1*/SELECT * FROM vwv40; /*c1*/SELECT * FROM vwv41; /*c1*/SELECT * FROM vwv42; /*c1*/SELECT * FROM vwv43; /*c1*/SELECT * FROM vwv44; /*c1*/SELECT * FROM vwv45; /*c1*/SELECT * FROM vwv46; /*c1*/SELECT * FROM vwv47; /*c1*/SELECT * FROM vwv48; /*c1*/SELECT * FROM vwv49; /*c1*/SELECT * FROM vwv50; /*c1*/SELECT * FROM vwv51; /*c1*/SELECT * FROM vwv52; /*c1*/SELECT * FROM vwv53; /*c1*/SELECT * FROM vwv54; /*c1*/SELECT * FROM vwv55; /*c1*/SELECT * FROM vwv56; /*c1*/SELECT * FROM vwv57; /*c1*/SELECT * FROM vwv1, vwj1 WHERE vwv1.i = vwj1.i; /*c1*/SELECT * FROM public.vwv1, public.vwj1 WHERE vwv1.i = vwj1.i; /*c2*/SELECT * FROM vwv1 WHERE i in (0, 1, 11, 12); /*c1*/SELECT * FROM vwv1 WHERE i < 1; /*c1*/SELECT * FROM vwv1 WHERE vwv1.i = (SELECT i FROM vwj1); /*c1*/SELECT * FROM vwv1 WHERE vwv1.i in (SELECT i FROM vwj1); /*c1*/SELECT * FROM vwv1 WHERE vwv1.i = (SELECT i FROM vwj1 WHERE i = 1); /*c1*/SELECT * FROM public.vwv1 WHERE vwv1.i in (SELECT i FROM vwj1); /*c1*/SELECT * FROM public.vwv1 WHERE vwv1.i = (SELECT i FROM vwj1 WHERE i = 1); /*c1*/SELECT * FROM vwv1 WHERE vwv1.i = (SELECT i FROM public.vwj1); /*c1*/SELECT * FROM vwv1 WHERE vwv1.i in (SELECT i FROM public.vwj1); /*c0*/SELECT * FROM vwv1 WHERE vwv1.i = (SELECT i FROM public.vwj1 WHERE i = 0); /*c1*/SELECT * FROM vwv1 WHERE vwv1.i = (SELECT vwj1.i FROM public.vwj1); /*c1*/SELECT * FROM vwv1 WHERE vwv1.i in (SELECT vwj1.i FROM vwj1); /*c1*/SELECT * FROM vwv1 WHERE vwv1.i = (SELECT vwj1.i FROM vwj1 WHERE i = 1); /*c0*/SELECT * FROM vwv1 WHERE vwv1.i = (SELECT i FROM public.vwj1 WHERE vwj1.i = 0); /*c0*/SELECT * FROM vwv1 WHERE vwv1.i = (SELECT vwj1.i FROM vwj1 WHERE vwj1.i = 0); /*c1*/SELECT * FROM vwv1 WHERE i = (SELECT i FROM vwj1); /*c1*/SELECT * FROM vwv1 WHERE i in (SELECT i FROM vwj1); /*c1*/SELECT * FROM vwv1 WHERE i = (SELECT i FROM vwj1 WHERE i = 1); /*c1*/SELECT * FROM public.vwv1 WHERE i = (SELECT i FROM vwj1); /*c1*/SELECT * FROM public.vwv1 WHERE i in (SELECT i FROM vwj1); /*c1*/SELECT * FROM public.vwv1 WHERE i = (SELECT i FROM vwj1 WHERE i = 1); /*c1*/SELECT * FROM vwv1 WHERE i = (SELECT i FROM public.vwj1); /*c1*/SELECT * FROM vwv1 WHERE i in (SELECT i FROM public.vwj1); /*c1*/SELECT * FROM vwv1 WHERE i = (SELECT i FROM public.vwj1 WHERE i = 1); /*c1*/SELECT * FROM vwv1 WHERE i = (SELECT vwj1.i FROM public.vwj1); /*c1*/SELECT * FROM vwv1 WHERE i in (SELECT vwj1.i FROM vwj1); /*c1*/SELECT * FROM vwv1 WHERE i = (SELECT vwj1.i FROM vwj1 WHERE i = 1); /*c1*/SELECT * FROM vwv1 WHERE i = (SELECT i FROM public.vwj1 WHERE vwj1.i = 1); /*c1*/SELECT * FROM vwv1 WHERE i = (SELECT vwj1.i FROM vwj1 WHERE vwj1.i = 1); /*c2*/SELECT * FROM vwv1; /*c1*/SELECT * FROM vwv1 WHERE i = 0; /*c2*/SELECT * FROM vwv1 WHERE i in (0, 1, 11, 12); /*c1*/SELECT * FROM vwv1 WHERE i < 1; /*c1*/SELECT * FROM vwv1 WHERE vwv1.i = (SELECT i FROM vwj1); /*c1*/SELECT * FROM vwv1 WHERE vwv1.i in (SELECT i FROM vwj1); /*c1*/SELECT * FROM vwv1 WHERE vwv1.i = (SELECT i FROM vwj1 WHERE i = 1); /*c1*/SELECT * FROM public.vwv1 WHERE vwv1.i = (SELECT i FROM vwj1); /*c1*/SELECT * FROM public.vwv1 WHERE vwv1.i in (SELECT i FROM vwj1); /*c1*/SELECT * FROM public.vwv1 WHERE vwv1.i = (SELECT i FROM vwj1 WHERE i = 1); /*c1*/SELECT * FROM vwv1 WHERE vwv1.i = (SELECT i FROM public.vwj1); /*c1*/SELECT * FROM vwv1 WHERE vwv1.i in (SELECT i FROM public.vwj1); /*c1*/SELECT * FROM vwv1 WHERE vwv1.i = (SELECT i FROM public.vwj1 WHERE i = 1); /*c1*/SELECT * FROM vwv1 WHERE vwv1.i = (SELECT vwj1.i FROM public.vwj1); /*c1*/SELECT * FROM vwv1 WHERE vwv1.i in (SELECT vwj1.i FROM vwj1); /*c1*/SELECT * FROM vwv1 WHERE vwv1.i = (SELECT vwj1.i FROM vwj1 WHERE i = 1); /*c1*/SELECT * FROM vwv1 WHERE vwv1.i = (SELECT i FROM public.vwj1 WHERE vwj1.i = 1); /*c1*/SELECT * FROM vwv1 WHERE vwv1.i = (SELECT vwj1.i FROM vwj1 WHERE vwj1.i = 1); /*c1*/SELECT * FROM vwv1 WHERE i = (SELECT i FROM vwj1); /*c1*/SELECT * FROM vwv1 WHERE i in (SELECT i FROM vwj1); /*c1*/SELECT * FROM vwv1 WHERE i = (SELECT i FROM vwj1 WHERE i = 1); /*c1*/SELECT * FROM public.vwv1 WHERE i = (SELECT i FROM vwj1); /*c1*/SELECT * FROM public.vwv1 WHERE i in (SELECT i FROM vwj1); /*c1*/SELECT * FROM public.vwv1 WHERE i = (SELECT i FROM vwj1 WHERE i = 1); /*c1*/SELECT * FROM vwv1 WHERE i = (SELECT i FROM public.vwj1); /*c1*/SELECT * FROM vwv1 WHERE i in (SELECT i FROM public.vwj1); /*c1*/SELECT * FROM vwv1 WHERE i = (SELECT i FROM public.vwj1 WHERE i = 1); /*c1*/SELECT * FROM vwv1 WHERE i = (SELECT vwj1.i FROM public.vwj1); /*c1*/SELECT * FROM vwv1 WHERE i in (SELECT vwj1.i FROM vwj1); /*c1*/SELECT * FROM vwv1 WHERE i = (SELECT vwj1.i FROM vwj1 WHERE i = 1); /*c1*/SELECT * FROM vwv1 WHERE i = (SELECT i FROM public.vwj1 WHERE vwj1.i = 1); /*c1*/SELECT * FROM vwv1 WHERE i = (SELECT vwj1.i FROM vwj1 WHERE vwj1.i = 1); /*e*/SELECT * FROM other.vwv1; /*e*/SELECT * FROM information_schema.vwv1; /*c2*/SELECT * FROM public.vwv1; /*c1*/SELECT * FROM public.vwv1 WHERE i = 0; /*e*/SELECT * FROM other.vwv1 WHERE i = 0; /*e*/SELECT * FROM information_schema.vwv1 WHERE i = 0; /*u1*/SELECT * INTO public.vwt2 FROM public.vwv1 WHERE i = 0; /*c1*/SELECT * FROM public.vwt2; DROP TABLE vwt2; /*c1*/SELECT * FROM public.vwv1, public.vwj1 WHERE vwv1.i = vwj1.i; /*c1*/SELECT * FROM vwv1, public.vwj1 WHERE vwv1.i = vwj1.i; /*c1*/SELECT * FROM public.vwv1, vwj1 WHERE vwv1.i = vwj1.i; /*c1*/SELECT * FROM vwv1, vwj1 WHERE vwv1.i = vwj1.i; /*c1*/SELECT vc FROM public.vwv1, public.vwj1 WHERE vwv1.i = vwj1.i; /*c1*/SELECT vc FROM vwv1, public.vwj1 WHERE vwv1.i = vwj1.i; /*c1*/SELECT vc FROM public.vwv1, vwj1 WHERE vwv1.i = vwj1.i; /*c1*/SELECT vc FROM vwv1, vwj1 WHERE vwv1.i = vwj1.i; /*c1*/SELECT vwj1.vc FROM public.vwv1, public.vwj1 WHERE vwv1.i = vwj1.i; /*c1*/SELECT vwj1.vc FROM vwv1, public.vwj1 WHERE vwv1.i = vwj1.i; /*c1*/SELECT vwj1.vc FROM public.vwv1, vwj1 WHERE vwv1.i = vwj1.i; /*c1*/SELECT vwj1.vc FROM vwv1, vwj1 WHERE vwv1.i = vwj1.i; /*c1*/SELECT lbla.vc FROM vwv1, vwj1 lbla WHERE vwv1.i = lbla.i; /*c1*/SELECT vwj1.vc FROM vwv1 lblb, vwj1 WHERE lblb.i = vwj1.i; /*c1*/SELECT lbla.vc FROM vwv1 lblb, vwj1 lbla WHERE lblb.i = lbla.i; /*c1*/SELECT lbla.vc FROM public.vwv1, vwj1 lbla WHERE vwv1.i = lbla.i; /*c1*/SELECT vwj1.vc FROM public.vwv1 lblb, vwj1 WHERE lblb.i = vwj1.i; /*c1*/SELECT lbla.vc FROM public.vwv1 lblb, vwj1 lbla WHERE lblb.i = lbla.i; /*c1*/SELECT lbla.vc FROM vwv1, public.vwj1 lbla WHERE vwv1.i = lbla.i; /*c1*/SELECT vwj1.vc FROM vwv1 lblb, public.vwj1 WHERE lblb.i = vwj1.i; /*c1*/SELECT lbla.vc FROM vwv1 lblb, public.vwj1 lbla WHERE lblb.i = lbla.i; /*u0*/GRANT ALL ON vwv1 TO otheruser; /*u0*/GRANT ALL ON public.vwv2 TO otheruser; /*e*/GRANT ALL ON other.vwv3 TO otheruser; /*e*/GRANT ALL ON information_schema.vwv3 TO otheruser; /*u0*/REVOKE ALL ON vwv1 FROM otheruser; /*u0*/REVOKE ALL ON public.vwv2 FROM otheruser; /*e*/REVOKE ALL ON other.vwv3 FROM otheruser; /*e*/REVOKE ALL ON information_schema.vwv3 FROM otheruser; -- ****************************** Indexes /*c2*/SELECT * from indt1; /*c2*/SELECT * from public.indt2; /*c2*/SELECT * from public.indt3; /*c2*/SELECT * from indt4; -- ****************************** CACH Indexes /*c2*/SELECT * from cindt1; /*c2*/SELECT * from public.cindt2; /*c2*/SELECT * from public.cindt3; /*c2*/SELECT * from cindt4; -- ****************************** Sequences -- N.b.: Do not commit DML changes so that this whole block may be repeated. /*r0*/SELECT next value FOR s1 FROM seqt1; /*r2*/SELECT next value FOR public.s2 FROM seqt1; /*r1*/SELECT next value FOR s1 FROM public.seqt1; /*r3*/SELECT next value FOR public.s2 FROM public.seqt1; /*r2*/SELECT next value FOR public.s1 FROM seqt1; /*r4*/SELECT next value FOR s2 FROM seqt1; /*r3*/SELECT next value FOR public.s1 FROM seqt1; /*r5*/SELECT next value FOR s2 FROM seqt1; -- Just to reset orig. value before persisting (can't roll back seq.s). ALTER SEQUENCE s1 RESTART WITH 0; ALTER SEQUENCE s2 RESTART WITH 2; /*e*/DROP SEQUENCE s101; /*e*/DROP SEQUENCE public.s101; /*e*/DROP SEQUENCE information_schema.s1; /*e*/DROP SEQUENCE other.s1; -- ****************************** Triggers /*e*/CREATE TRIGGER trgtrig1 AFTER INSERT ON trgt1 CALL 'org.hsqldb.test.BlaineTrig'; -- Create existing /*e*/CREATE TRIGGER public.trgtrig2 AFTER INSERT ON trgt2 CALL 'org.hsqldb.test.BlaineTrig'; -- Create existing -- ****************************** Constraints -- N.b.: Do not commit DML changes so that this whole block may be repeated. COMMIT; SET AUTOCOMMIT false; /*u1*/INSERT INTO cont1 VALUES (0); /*u1*/INSERT INTO public.cont2 VALUES (0); /*u1*/INSERT INTO cont3 VALUES (0); /*u1*/INSERT INTO cont4 VALUES (0); /*u1*/INSERT INTO cont5 VALUES (0); /*u1*/INSERT INTO cont6 VALUES (0); /*u1*/INSERT INTO cont7 VALUES (0); /*u1*/INSERT INTO cont8 VALUES (0); -- Note that error message implies that problem is due to table conj1, not data: /*u1*/INSERT INTO cont9 VALUES (1); /*u1*/INSERT INTO cont10 VALUES (1); /*u1*/INSERT INTO cont11 VALUES (1); /*u1*/INSERT INTO cont12 VALUES (1); /*u1*/INSERT INTO cont13 VALUES (1); /*u1*/INSERT INTO cont14 VALUES (1); /*u1*/INSERT INTO cont15 VALUES (1); /*u1*/INSERT INTO cont16 VALUES (1); /*u1*/INSERT INTO cont17 VALUES (0); /*u1*/INSERT INTO cont18 VALUES (0); /*u1*/INSERT INTO cont19 VALUES (0); /*u1*/INSERT INTO cont20 VALUES (0); /*e*/INSERT INTO cont1 VALUES (0); /*e*/INSERT INTO cont2 VALUES (0); /*e*/INSERT INTO cont3 VALUES (0); /*e*/INSERT INTO cont4 VALUES (0); /*e*/INSERT INTO cont5 VALUES (0); /*e*/INSERT INTO cont6 VALUES (0); /*e*/INSERT INTO cont7 VALUES (0); /*e*/INSERT INTO cont8 VALUES (0); /*e*/INSERT INTO cont9 VALUES (0); /*e*/INSERT INTO cont10 VALUES (0); /*e*/INSERT INTO cont11 VALUES (0); /*e*/INSERT INTO cont12 VALUES (0); /*e*/INSERT INTO cont13 VALUES (0); /*e*/INSERT INTO cont14 VALUES (0); /*e*/INSERT INTO cont15 VALUES (0); /*e*/INSERT INTO cont16 VALUES (0); /*e*/INSERT INTO cont17 VALUES (1); /*e*/INSERT INTO cont18 VALUES (1); /*e*/INSERT INTO cont19 VALUES (1); /*e*/INSERT INTO cont20 VALUES (1); /*c1*/SELECT * FROM cont1; /*c1*/SELECT * FROM cont2; /*c1*/SELECT * FROM cont3; /*c1*/SELECT * FROM cont4; /*c1*/SELECT * FROM cont5; /*c1*/SELECT * FROM cont6; /*c1*/SELECT * FROM cont7; /*c1*/SELECT * FROM cont8; /*c1*/SELECT * FROM cont9; /*c1*/SELECT * FROM cont10; /*c1*/SELECT * FROM cont11; /*c1*/SELECT * FROM cont12; /*c1*/SELECT * FROM cont13; /*c1*/SELECT * FROM cont14; /*c1*/SELECT * FROM cont15; /*c1*/SELECT * FROM cont16; /*c1*/SELECT * FROM cont17; /*c1*/SELECT * FROM cont18; /*c1*/SELECT * FROM cont19; /*c1*/SELECT * FROM cont20; ROLLBACK; -- ****************************** ALTERs -- Add tests when time permits. DROP USER otheruser; -- This to test .script persistence. SHUTDOWN; Other HSQLDB examples (source code examples)Here is a short list of links related to this HSQLDB TestSelfSchemaPersistA2.txt source code file: |
... this post is sponsored by my books ... | |
#1 New Release! |
FP Best Seller |
Copyright 1998-2021 Alvin Alexander, alvinalexander.com
All Rights Reserved.
A percentage of advertising revenue from
pages under the /java/jwarehouse
URI on this website is
paid back to open source projects.