Flemming Frandsen wrote: > 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.
I think it's not a bug to use an index to support order citeria given by the user. Without the index the database have to sort the result additionaly. > Bug 2: It blows up when trying to execute. Ok, as I wrote in my previous mail I will fix it in the next release. The Problem was that the optimizer tried to use this index for some qualification and generated wrong internal stack code. So in this case I would agree with you an we could call him moronic ;-) > 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' The SAP DB doesn't supports query rewrite. So an IN predicate is not the same as the OR predicate. So I would call it a missing feature and not a bug. And perhaps some days... Regards, Holger SAP Labs Berlin _______________________________________________ sapdb.general mailing list [EMAIL PROTECTED] http://listserv.sap.com/mailman/listinfo/sapdb.general
