quote java.lang.Long in SQL statement

[zipper78]

Hi,

I've written an EJB 2.0 CMP applikation based on JBoss 3.0.7 and PostgreSQL 7.2.3. It runs fine but is quite slow when getting beans out of the database.
I've found out that the follwoing SQL statement generated by JBoss is wrong:
SELECT id,zip,city,country FROM zipcode where (id=1) or (id=2) or (id=3) or (id=4)
It should be:
SELECT id,zip,city,country FROM zipcode where (id='1') or (id='2') or (id='3') or (id='4')
or:
SELECT id,zip,city,country FROM zipcode where (id=1::bigint) or (id=2::bigint) or (id=3::bigint) or (id=4::bigint)

although the column id is numeric (int8).
The problem with the first statement is that PostgreSQL does not know the type of 1,2,3 or 4 and therefore has to cast it while comparing it with every single id value row by row (seq scan). As I have more than 40.000 entries in the database, this tooks about 600ms.
With the second and third statement PostgreSQL knows the data type (don't ask me, why in the second, too) and can use an index. This takes about 20ms.

So how can I tell JBoss to quote numeric values, too as it does for strings?

PostgreSQL query plan as additional infiormation:
db=# explain analyze select id,zip,city from zipcode where id=1 or id=2 or id=3 or id=4;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------
Seq Scan on zipcode (cost=100000000.00..100001265.02 rows=4 width=30) (actual time=0.39..623.53 rows=4 loops=1)
Filter: ((id = 1) OR (id = 2) OR (id = 3) OR (id = 4))
Total runtime: 625.07 msec
(3 rows)


db=# explain analyze select id,zip,city from zipcode where id='1' or id='2' or id='3' or id='4';
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Index Scan using pk_zipcode, pk_zipcode, pk_zipcode, pk_zipcode on zipcode (cost=0.00..22.59 rows=4 width=30) (actual time=19.77..20.04 rows=4 loops=1)
Index Cond: ((id = 1::bigint) OR (id = 2::bigint) OR (id = 3::bigint) OR (id = 4::bigint))
Total runtime: 20.48 msec
(3 rows)

[marcin]

Hello,

I had the same problem... I think that it is not possible to make jboss generate type casting or quoting bigint values. So i made a little patch for jboss...

Changed org.jboss.ejb.plugins.cmp.jdbc.SQLUtil.getWhereClause(...) to this:

public static String getWhereClause(JDBCType type, String identifier) {
if(identifier.length() > 0) {
identifier += ".";
}

String[] columnNames = type.getColumnNames();

StringBuffer buf = new StringBuffer();
for(int i=0; i<columnNames.length; i++) {
if(i!=0) {
buf.append(" AND ");
}
buf.append(identifier).append(columnNames).append("=?");
//
String [] sqlTypes = type.getSQLTypes();
if ("BIGINT".equals(sqlTypes[0])) {
buf.append("::INT8");
}
}
return buf.toString();
}

I added last 'if' instruction which adds appropriate type casting to the query.
This kind of query (WHERE (id = ...) OR ... ) is created when you have 'read-ahead' turned on and type is 'on-load' mode. If you change 'read-ahead' to 'on-find' and set eager-load-group then jboss will not generate this set of OR conditions.

I think this code change should also work for custom queries, when you need to select entities by the attribute which is not a primary key. But i have not tried it.

Marcin

[zipper78]

Thanks, that brought me to a solution.
As sqlTypes[0] always contains the SQL type and not the JDBC type I had to compare it with "int8" and not "BIGINT".
But then I saw that there shouldn't be an issue when always use explicit type casting so I added these lines just at line 357 in SQLUtil.java (JBoss 3.0.7):

// fix for PostgreSQL 7.2.x
String[] sqltype = type.getSQLTypes();
buf.append("::").append(sqlTypes[0]);