Flemming Frandsen wrote: > "Becker, Holger" wrote: > > So perhaps if you drop these indexes step by step and check if the > > error disappear we can find out which table and index is responsible. > Urgh, I'll try.
Ok here is the result of my mucking about: I get this error: General error;-9211 POS(1) System error: KB Stack type illegal. (SQL-S1000)(DBD: st_execute/SQLExecute err=-1) When executing: SELECT * FROM "ticket" WHERE ? IN (owner_dude_id, seater_dude_id, user_dude_id) ORDER BY ticket_type_id When there is an index on the ticket_type_id column. The index was created using: CREATE INDEX "fk_TICKET_TYPE_ID" ON "ticket" (TICKET_TYPE_ID) The table was created using: CREATE TABLE "ticket" ( OWNER_DUDE_ID INTEGER NOT NULL DEFAULT NULL, ARRIVED INTEGER NOT NULL DEFAULT 0, TICKET_TYPE_ID INTEGER NOT NULL DEFAULT NULL, TICKET_ID VARCHAR(29) NOT NULL DEFAULT NULL, SEATER_DUDE_ID INTEGER DEFAULT NULL, ORDER_ID INTEGER DEFAULT NULL, UPDATED INTEGER NOT NULL DEFAULT NULL, CREATED INTEGER NOT NULL DEFAULT NULL, SEAT_ID INTEGER DEFAULT NULL, USER_DUDE_ID INTEGER DEFAULT NULL, PRIMARY KEY(TICKET_ID) ) I've tried getting an explaination from the database, and it has this moronic idea that it would be cool to sort first when the index is on, that would be stupid to begin with, but blowing up is worse: /opt/sapdb/indep_prog/bin/dbmcli on ZDB>sql_execute explain SELECT * FROM "ticket" WHERE 1 IN (owner_dude_id, seater_dude_id, user_dude_id) ORDER BY ticket_type_id OK --- /opt/sapdb/indep_prog/bin/dbmcli on ZDB>sql_execute select * from show OK END 'TEST';'ticket';'TICKET_TYPE_ID';''ORDER BY' VIA INDEXED COLUMN';' 40';'';'';'';'*' 'TEST';'';'';' RESULT IS NOT COPIED , COSTVALUE IS';' 61';'';'';'';'' --- I've tried running sql_updatestat but the optimizer still makes that mistake. So there may be two bugs here: Bug 1: The optimizer chooses to sort first. Bug 2: It blows up when trying to execute. The optimizer does other things when I split the IN construct up in seperate or clauses, this is somewhat defective as x IN (a,b,c) ought to be the same as x=a or x=b or x=c: /opt/sapdb/indep_prog/bin/dbmcli on ZDB>sql_execute explain SELECT * FROM "ticket" WHERE owner_dude_id=1 or seater_dude_id=1 or user_dude_id=1 ORDER BY ticket_type_id OK --- /opt/sapdb/indep_prog/bin/dbmcli on ZDB>sql_execute select * from show OK END 'TEST';'ticket';'';'DIFFERENT STRATEGIES FOR OR-TERMS';' 40';'';'';'';'' '';'';'OWNER_DUDE_ID';'EQUAL CONDITION FOR INDEXED COLUMN';'';'';'';'';'' '';'';'SEATER_DUDE_ID';'EQUAL CONDITION FOR INDEXED COLUMN';'';'';'';'';'' '';'';'USER_DUDE_ID';'EQUAL CONDITION FOR INDEXED COLUMN';'';'';'';'';'' 'TEST';'';'';' RESULT IS COPIED , COSTVALUE IS';' 1';'';'';'';'' --- Bug 3: '? IN (a,b,c)' is not the same as '?=a or ?=b or ?=c' Solution: I've replaced the line: WHERE ? IN (owner_dude_id, seater_dude_id, user_dude_id) with: WHERE owner_dude_id=? or seater_dude_id=? or user_dude_id=? ...and my application seems to work correctly with this. -- Regards Flemming Frandsen aka. Dion/Swamp http://dion.swamp.dk _______________________________________________ sapdb.general mailing list [EMAIL PROTECTED] http://listserv.sap.com/mailman/listinfo/sapdb.general
