-- -- TestSelfQueries.txt -- -- Tests for Query results, especially the neutrality of constraints and indexes -- drop table TESTTABLE if exists; create cached table TESTTABLE ( aString varchar(256) not null, firstNum integer not null, aDate date not null, secondNum integer not null, thirdNum integer not null, aName varchar(32) not null ); create index IDX_TESTTABLE_aString on TESTTABLE (aString); create index IDX_TESTTABLE_aDate_secondNum on TESTTABLE (aDate,secondNum); insert into TESTTABLE(aString, firstNum, aDate, secondNum, thirdNum, aName) values ('Current', 22, '2003-11-10', 18, 3, 'my name goes here'); insert into TESTTABLE(aString, firstNum, aDate, secondNum, thirdNum, aName) values ('Popular', 23, '2003-11-10', 18, 3, 'my name goes here'); insert into TESTTABLE(aString, firstNum, aDate, secondNum, thirdNum, aName) values ('New', 5, '2003-11-10', 18, 3, 'my name goes here'); insert into TESTTABLE(aString, firstNum, aDate, secondNum, thirdNum, aName) values ('Old', 5, '2003-11-10', 18, 3, 'my name goes here'); insert into TESTTABLE(aString, firstNum, aDate, secondNum, thirdNum, aName) values ('CCurrent', 5, '2003-11-10', 18, 3, 'my name goes here'); insert into TESTTABLE(aString, firstNum, aDate, secondNum, thirdNum, aName) values ('ELV', 5, '2003-11-10', 18, 3, 'my name goes here'); insert into TESTTABLE(aString, firstNum, aDate, secondNum, thirdNum, aName) values ('ELNA', 5, '2003-11-10', 18, 3, 'my name goes here'); insert into TESTTABLE(aString, firstNum, aDate, secondNum, thirdNum, aName) values ('Older', 5, '2003-11-10', 18, 3, 'my name goes here'); insert into TESTTABLE(aString, firstNum, aDate, secondNum, thirdNum, aName) values ('RA', 20, '2003-11-10', 18, 3, 'my name goes here'); insert into TESTTABLE(aString, firstNum, aDate, secondNum, thirdNum, aName) values ('RP', 2, '2003-11-10', 18, 3, 'my name goes here'); insert into TESTTABLE(aString, firstNum, aDate, secondNum, thirdNum, aName) values ('VS', 3, '2003-11-10', 18, 3, 'my name goes here'); -- /*c11*/select * from testtable where adate = '2003-11-10' and secondNum = 18; /*c11*/select * from testtable where adate = '2003-11-10'; /*c1*/select * from testtable where adate = '2003-11-10' and firstNum = 20; /*c11*/select * from testtable where adate = '2003-11-10' and thirdNum = 3; drop index IDX_TESTTABLE_aString; drop index IDX_TESTTABLE_aDate_secondNum; alter table TESTTABLE add constraint tt_pk primary key (astring); /*c11*/select * from testtable where adate = '2003-11-10' and secondNum = 18; /*c11*/select * from testtable where adate = '2003-11-10'; /*c1*/select * from testtable where adate = '2003-11-10' and firstNum = 20; /*c11*/select * from testtable where adate = '2003-11-10' and thirdNum = 3; alter table testtable drop column aname; /*c1*/select * from testtable where adate = '2003-11-10' and firstNum = 20; /*c11*/select * from testtable where adate = '2003-11-10' and thirdNum = 3; alter table testtable add column aname char(10) default 'a string' not null; /*c1*/select * from testtable where adate = '2003-11-10' and firstNum = 20; /*c11*/select * from testtable where adate = '2003-11-10' and thirdNum = 3; /*e*/update testtable set name=null; -- bug #722443 DROP TABLE CONFIGUSER IF EXISTS; CREATE CACHED TABLE CONFIGUSER(USR_USERID NUMERIC NOT NULL PRIMARY KEY,USR_USERNAME VARCHAR(10) NOT NULL,USR_PASSWORD VARCHAR(10)); INSERT INTO CONFIGUSER VALUES(-5,'guest','guest'); INSERT INTO CONFIGUSER VALUES(-4,'user','user'); INSERT INTO CONFIGUSER VALUES(-3,'owner','owner'); INSERT INTO CONFIGUSER VALUES(-2,'admin','xxx'); INSERT INTO CONFIGUSER VALUES(-1,'sadmin','xxx'); INSERT INTO CONFIGUSER VALUES(0,'nobody',null); -- select all users with their username as password /*c3*/select * from configuser where usr_username = usr_password; -- create a unique index on one column CREATE UNIQUE INDEX IDX_USERNAME ON CONFIGUSER(USR_USERNAME); -- select all users with their username as password /*c3*/select * from configuser where usr_username = usr_password; /*c3*/select * from configuser where usr_username in (select usr_password from configuser) /*c3*/select * from configuser where usr_password in (select usr_username from configuser) /*c3*/select * from configuser where usr_password in (usr_username); /*c2*/select * from configuser where usr_password in ('guest', 'user', 'wrong') DROP INDEX IDX_USERNAME -- select all users with their username as password /*c3*/select * from configuser where usr_username = usr_password; /*c3*/select * from configuser where usr_username in (select usr_password from configuser) /*c3*/select * from configuser where usr_password in (select usr_username from configuser) /*c3*/select * from configuser where usr_password in (usr_username); /*c2*/select * from configuser where usr_password in ('guest', 'user', 'wrong') CREATE INDEX IDX_USERNAME ON CONFIGUSER(USR_USERNAME); -- select all users with their username as password /*c3*/select * from configuser where usr_username = usr_password; /*c3*/select * from configuser where usr_username in (select usr_password from configuser) /*c3*/select * from configuser where usr_password in (select usr_username from configuser) /*c3*/select * from configuser where usr_password in (usr_username); /*c2*/select * from configuser where usr_password in ('guest', 'user', 'wrong') -- -- COUNT(DISTINCT ) when there are no records -- bug #718866 CREATE TABLE IBANX_PERMIT(ID INT, A1 VARCHAR(10)); /*r0*/SELECT count(distinct A0.ID) FROM IBANX_PERMIT A0; -- -- use of column aliases in the where clause --bug #696595 CREATE TABLE "liste"("kosten" INT, "preis" INT); insert into "liste" values(100, 10); /*r100*/SELECT "kosten" AS "total" FROM "liste" WHERE "total" > 0 ORDER BY "total" DESC; /*r110*/SELECT ("kosten"+"preis") AS "total" FROM "liste" WHERE "total" > 0 ORDER BY "total" DESC;