Character to numeric conversion error

HP-UX 10.20 IDS 7.3 Here's a problem that I've noticed going back several versions. Consider the following select; select * from systables where tabname in (select tabid from systables) Now obviously unless you have a table named with a number this will return no rows, but the query is for simplicity sake. This will give a character to number conversion error because it will try to convert tabname to an integer and then do the comparison. My question is, shouldn't it convert tabid to a character and then do the comparison. Shouldn't this be the same as; select * from systables where tabname in ("1","2","3","4","5",...) I have several queries on tables with fields that may contain a number or a character. If it contains a number, I want to join it to a field in another table containing only numbers. The way Informix is doing the conversion, my queries never work. Now there are things that you can do to try and make the query work, like; select * from systables where tabname in (select tabid from systables) and tabname matches "[0-9]*" but unless the optimizer decides to do the and clause first the query will still fail. You can't rely on this to work, in fact on all of my real world queries it never does. The only workaround I can see is to load all of the number records into a temporary table and do the join on the subset. Am I alone in thinking that Informix is doing the conversion wrong? Now that I'm using 7.3 is there a way to tell the optimizer to do the subquery first? TIA