using h2 1.3.161 using spring JdbcTemplate, this query select max(best) from ((select max(celeblevel) as best from actor where name like ?)) with value 'chan, jackie %' doesn't return (i wated 10 minutes).
a full table scan (forced by something like "where a='x' or b='x') takes 18 seconds. table has ~2mio records. when using string concatenation then the query returns instantly select max(best) from ((select max(celeblevel) as best from actor where name like 'chan, jackie %')) it's a reduced version to reproduce the problem. the goal would be to write select max(celeblevel) as best from actor where name like ? or nameascii like ? which must be written as (in order to avoid the table scan for the 2nd) select max(best) from ( (select max(celeblevel) as best from actor where name like ?) union (select max(celeblevel) as best from actor where nameascii like ?) ) note: a yet simpler version of the query select max(celeblevel) as best from actor where name like ? also returns instantly. now i have the choice between not using named params (and thus making my code possibly vulnerable to sql injection), or running 2 separate sql queries (and thus changing even more code). or am i missing something? my table: COLUMN_NAME DATA_TYPE PK NULLABLE DEFAULT AUTOINCREMENT REMARKS JDBC Type SCALE/SIZE PRECISION POSITION ID INTEGER YES NO (NEXT VALUE FOR PUBLIC.SYSTEM_SEQUENCE_7EE1DA42_1DFF_4F1D_84E3_760C8893C4DA) YES 4 10 0 1 JMDBID INTEGER NO NO 0 NO 4 10 0 2 NAME VARCHAR_IGNORECASE(60) NO NO '' NO 12 60 0 3 NAMEASCII VARCHAR_IGNORECASE(60) NO NO '' NO 12 60 0 4 GENDER TINYINT NO YES NO -6 3 0 5 CELEBLEVEL SMALLINT NO YES NO 5 5 0 6 and the indexes: INDEX_NAME UNIQUE PK DEFINITION TYPE ACTOR_JMDBID NO NO JMDBID ASC NORMAL ACTOR_NAME NO NO NAME ASC NORMAL ACTOR_NAMEASCII NO NO NAMEASCII ASC NORMAL PRIMARY_KEY_3 YES YES ID ASC NORMAL -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/h2-database?hl=en.
