You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by Dy...@Sun.COM on 2006/04/18 16:45:04 UTC
LIKE with prepared statements and empty strings
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?
Detailed example:
conn.setAutoCommit(true);
Statement s = conn.createStatement();
try { s.execute("drop table t"); } catch (SQLException e) {}
s.execute("create table t (i int, v varchar(128))");
PreparedStatement ins =
conn.prepareStatement("insert into t values (?, ?)");
ins.setInt(1,0);
ins.setString(2, "ABC");
ins.execute();
ins.setInt(1,1);
ins.setString(2, "XYZ");
ins.execute();
ins.setInt(1,2);
ins.setString(2, "");
ins.execute();
PreparedStatement ok =
conn.prepareStatement("select * from t where v like ''");
ResultSet ok_rs = ok.executeQuery();
while (ok_rs.next()) {
System.out.println("ok: "+ok_rs.getObject(1) + " <" +
ok_rs.getObject(2) + ">");
}
PreparedStatement bad =
conn.prepareStatement("select * from t where v like ?");
bad.setString(1, "");
ResultSet bad_rs = bad.executeQuery();
while (bad_rs.next()) {
System.out.println("bad: "+bad_rs.getObject(1) + " <" +
bad_rs.getObject(2) + ">");
}
This prints:
ok: 2 <>
--
dt
Re: LIKE with prepared statements and empty strings
Posted by "Bernt M. Johnsen" <Be...@Sun.COM>.
>>>>>>>>>>>> Bernt M. Johnsen wrote (2006-04-18 16:58:05):
> >>>>>>>>>>>> Dyre.Tjeldvoll@Sun.COM 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
Re: LIKE with prepared statements and empty strings
Posted by "Bernt M. Johnsen" <Be...@Sun.COM>.
>>>>>>>>>>>> Dyre.Tjeldvoll@Sun.COM 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.
> Detailed example:
> conn.setAutoCommit(true);
>
> Statement s = conn.createStatement();
> try { s.execute("drop table t"); } catch (SQLException e) {}
> s.execute("create table t (i int, v varchar(128))");
>
> PreparedStatement ins =
> conn.prepareStatement("insert into t values (?, ?)");
>
> ins.setInt(1,0);
> ins.setString(2, "ABC");
> ins.execute();
>
> ins.setInt(1,1);
> ins.setString(2, "XYZ");
> ins.execute();
>
> ins.setInt(1,2);
> ins.setString(2, "");
> ins.execute();
>
> PreparedStatement ok =
> conn.prepareStatement("select * from t where v like ''");
> ResultSet ok_rs = ok.executeQuery();
> while (ok_rs.next()) {
> System.out.println("ok: "+ok_rs.getObject(1) + " <" +
> ok_rs.getObject(2) + ">");
> }
>
> PreparedStatement bad =
> conn.prepareStatement("select * from t where v like ?");
> bad.setString(1, "");
> ResultSet bad_rs = bad.executeQuery();
> while (bad_rs.next()) {
> System.out.println("bad: "+bad_rs.getObject(1) + " <" +
> bad_rs.getObject(2) + ">");
> }
>
> This prints:
>
> ok: 2 <>
>
> --
> dt
>
--
Bernt Marius Johnsen, Database Technology Group,
Staff Engineer, Technical Lead Derby/Java DB
Sun Microsystems, Trondheim, Norway