Ok, thanks for that. It confirms my suspicions! BioMart currently relies on the MySQL-specific quirk that when using 'like', MySQL implicitly adds % symbols to both ends of the expression, guessing that is what the user wanted. It only skips doing this if a % is explicitly included in the search term by the user.
MySQL is unique in this approach, and other systems (Postgres, Oracle, etc.) do not make any such assumptions. For these systems, writing 'like' but not explicitly specifying any % symbols is exactly the same as writing = instead of like. So, it's a BioMart bug! Syed/Arek - do you know how to fix this? (I guess some kind of extra step is needed during SQL construction that spots the like qualifier and wraps the search expression in % symbols unless it already contains one). cheers, Richard 2008/7/1 Renato Golin <[EMAIL PROTECTED]>: > Richard Holland wrote: >> >> Can you check the generated SQL to see if it is actually getting through? > > Hi Richard, > > I hacked it and made the names different (to print me the query again) and > the like is wrong: > > (UNIPROT__DESCRIPTION__dm.text_1054 like 'Cytochrome') > > Where it should be "like '%Cytochrome%'"... > > > The full query: > > SELECT renato1.UNIPROT__DESCRIPTION__DM.text_1054 FROM > renato1.UNIPROT__DESCRIPTION__DM, renato1.UNIPROT__DESCRIPTION__dm, > renato1.UNIPROT__ENTRY__MAIN main WHERE (UNIPROT__DESCRIPTION__dm.text_1054 > like 'Cytochrome') AND > main.dbentry_id_1039_key=renato1.UNIPROT__DESCRIPTION__DM.dbentry_id_1039_key > AND main.dbentry_id_1039_key=<*>UNIPROT__DESCRIPTION__dm.dbentry_id_1039_key > AND rowNum < 201 > > cheers, > --renato >
