Error in like operator - works with HSQL, Oracle, MySQL

[shrestha]

I have:

statement = "SELECT * FROM JOB_STATUS WHERE (ASSIGNED_TO = ? OR
ASSIGNED_TO LIKE ? OR ASSIGNED_TO LIKE ?)
....
pstmt.setString(2, technician.toLowerCase() + "%");
...
if value of technician = "abc\mike"

I get this error:

org.h2.jdbc.JdbcSQLException: Error in LIKE ESCAPE: abc\[*]mike%; SQL
statement:
SELECT * FROM JOB_STATUS WHERE (ASSIGNED_TO = ? OR ASSIGNED_TO LIKE ?
OR ASSIGNED_TO LIKE ?)

The same works in HSQL, Oracle, MySQL, and MSSQL.

Thanks
Roshan

[Kerry Sainsbury]

Hey Thomas,

My experimenting with H2 continues, so I've attached a patch that fixes this problem.

In summary:

- Changed "CompareLike" to escape any escape characters that aren't followed by wildcard characters
- Added test SQL to testSimple.in.txt

A patch is attached.

Cheers
Kerry

PS: Let me know if I should stop sending these -- you are strangely quiet about them... (perhaps you are just very polite, and can't find a nice way to tell me how horrible they are?)

--

You received this message because you are subscribed to the Google Groups "H2 Database" group.
To post to this group, send email to h2-da...@googlegroups.com.
To unsubscribe from this group, send email to h2-database...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/h2-database?hl=en.

[Kerry Sainsbury]

I swear gmail sent the email below without my permission -- patch is now attached.

Kerry

[Thomas Mueller]

Hi,

> PS: Let me know if I should stop sending these -- you are strangely quiet
> about them... (perhaps you are just very polite, and can't find a nice way
> to tell me how horrible they are?)

The problem is only that I didn't have time to look at it. Please don't stop!

Regards,
Thomas

[Thomas Mueller]

Hi,

> The same works in HSQL, Oracle, MySQL, and MSSQL.

Yes, but those databases don't return the same rows (see also my test
case below).

H2 uses \ as the default escape character (like PostgreSQL and MySQL).
However H2 throws an exception if the escape character is not followed
by \, % or _ (like Apache Derby, if it would use \ as the default
escape character). PostgreSQL and MySQL don't throw an exception in
this case.

The LIKE ESCAPE processing is quite tricky. And unfortunately, all
databases work a bit different. I will change the behavior of H2 to
match PostgreSQL, and, I believe, Oracle and MS SQL Server.

Results from my test case:

jdbc:mysql://localhost:3306/test
result1: 0
result2: 1
result3: 1

jdbc:postgresql:test
result1: 0
result2: 0
result3: 0

jdbc:derby:test;create=true
result1: 1
result2: java.sql.SQLDataException: Escape character must be followed
by escape character, '_', or '%'. It cannot be followed by any other
character or be at the end of the pattern.
result3: 0

jdbc:hsqldb:test;hsqldb.default_table_type=cached
result1: 1
result2: java.sql.SQLException: data exception: invalid escape sequence
result3: java.sql.SQLException: data type cast needed for parameter or
null literal

jdbc:h2:data/test;page_store=true (next release)
result1: 0
result2: 0
result3: 0

My test case:

package db;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import org.h2.tools.DeleteDbFiles;

public class TestMultiDb {

public static void main(String[] a) throws Exception {
DeleteDbFiles.execute("data/test", "test", true);
test("com.mysql.jdbc.Driver",
"jdbc:mysql://localhost:3306/test",
"sa", "sa");
test("org.postgresql.Driver",
"jdbc:postgresql:test",
"sa", "sa");
test("org.apache.derby.jdbc.EmbeddedDriver",
"jdbc:derby:test;create=true", "sa", "sa");
test("org.hsqldb.jdbcDriver",
"jdbc:hsqldb:test;hsqldb.default_table_type=cached",
"sa", "");
test("org.h2.Driver",
"jdbc:h2:data/test;page_store=true", "sa", "sa");
}

static void test(String driver,
String url, String user,
String password) throws Exception {
Class.forName(driver);
Connection conn = DriverManager.getConnection(
url, user, password);
ResultSet rs;
Statement stat = conn.createStatement();
try {
stat.execute("drop table test");
} catch (SQLException e) {
// ignore
}
System.out.println(url);
stat.execute(
"create table test(name varchar(255))");
PreparedStatement prep;
prep = conn.prepareStatement(
"insert into test values(?)");
prep.setString(1, "abc+mike\\+");
prep.execute();
try {
prep = conn.prepareStatement(
"select count(*) from test where name like ?");
prep.setString(1, "abc+mike\\+");
rs = prep.executeQuery();
rs.next();
System.out.println("result1: " + rs.getString(1));
} catch (SQLException e) {
System.out.println("result1: " + e);
}
try {
prep = conn.prepareStatement(
"select count(*) from test where name like ? escape '+'");
prep.setString(1, "abc++mike\\+");
rs = prep.executeQuery();
rs.next();
System.out.println("result2: " + rs.getString(1));
} catch (SQLException e) {
System.out.println("result2: " + e);
}
try {
prep = conn.prepareStatement(
"select count(*) from test where name like '%%' escape ?");
prep.setString(1, "%");
rs = prep.executeQuery();
rs.next();
System.out.println("result3: " + rs.getString(1));
} catch (SQLException e) {
System.out.println("result3: " + e);
}
conn.close();
}
}

Regards,
Thomas