>>>>>>>>>>>> Bernt M. Johnsen wrote (2006-04-18 16:58:05): > >>>>>>>>>>>> [EMAIL PROTECTED] wrote (2006-04-18 16:45:04): > > I puzzled by the following: > > > > The query SELECT * FROM t WHERE v like '' > > > > appears to give me all rows where v is an empty > > string, as expected. > > > > But a prepared statement > > > > SELECT * FROM t WHERE v like ?, with setString(1, "") > > > > always gives zero rows. Is this expected? > > I would say no. The two statements should have given the same > result. I guess the first one is correct, but since LIKE may have some > weird semantics I would not bet on it without consulting the spec.
LIKE should give true for two 0-length strings. See SQL 2003 Ch. 8.5 General Rules 3)c)i) And, there is a bug here wrt. the use of setString and 0-length strings. Dyre, could you ile an issue? For those who are interested in the more weird bits of the spec: ================================================================ comparision with "=" is specified to pad the shortest string (unless NO PAD is specified), while LIKE is specified to do the match *without* padding. This is actually implemented correctly in Derby, so the following ij-output is according to the spec: ij> create table t(k int primary key, fc char(8), vc varchar(8)); 0 rows inserted/updated/deleted ij> insert into t values(1,'en', 'en'); 1 row inserted/updated/deleted ij> select * from t where fc = 'en'; K |FC |VC ----------------------------- 1 |en |en 1 row selected ij> select * from t where fc like 'en'; K |FC |VC ----------------------------- 0 rows selected ij> select * from t where vc = 'en'; K |FC |VC ----------------------------- 1 |en |en 1 row selected ij> select * from t where vc like 'en'; K |FC |VC ----------------------------- 1 |en |en 1 row selected ij> select * from t where fc = 'en '; K |FC |VC ----------------------------- 1 |en |en 1 row selected ij> select * from t where fc like 'en '; K |FC |VC ----------------------------- 0 rows selected ij> select * from t where vc = 'en '; K |FC |VC ----------------------------- 1 |en |en 1 row selected ij> select * from t where vc like 'en '; K |FC |VC ----------------------------- 0 rows selected ij> -- Bernt Marius Johnsen, Database Technology Group, Staff Engineer, Technical Lead Derby/Java DB Sun Microsystems, Trondheim, Norway
pgp80L2jcb9QB.pgp
Description: PGP signature
