LIKE does not match empty strings when used with a prepared statement

• Key: DERBY-1231
• Type: Bug
• Status: Closed
• Resolution: Fixed
• Priority: Major
• Assignee: Bernt M. Johnsen
• Reporter: Dyre Tjeldvoll
• Component/s: JDBC, SQL
• Affects Version/s: 10.1.2.1, 10.1.3.1, 10.2.1.6, 10.3.1.4
• Fix Version/s: 10.2.2.0, 10.3.1.4

Description

LIKE should give true for two 0-length strings. See SQL 2003 Ch. 8.5
General Rules 3)c)i)

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 returns an empty result set. See attached repro for the full example.

Comments

[Dyre Tjeldvoll]

Attaching vti.java which is a simple repro for the problem (the name vti is not meaningful).

Moving to 10.2.2.0.

[Bernt M. Johnsen]

Simple fix to Like.lessThanString. It should not return null when the pattern has zero length, but proceed to the comparision logic.

Another small issue caught while studying the logic of this. Like.isOptimizable should return true if the pattern has zero length. Slight optimization if the user does a lot of blabla LIKE ''

QUESTION: Is the tweak in the second last line in metadata.properties:
      AND (V."COLUMN_NAME" LIKE ? OR V."COLUMN_NAME" = ?) \
a consequence of this bug and could now be changed to
      AND V."COLUMN_NAME" LIKE ? \
is part of this fix?

[Dyre Tjeldvoll]

Thanks for looking at this Bernt! :) Patch looks good to me. +1 to commit.

Wrt, your question above: Yes, I think the tweak in metadata.properties was to added as a workaround for this bug. It was added in revision 395414 which was Rick Hillegas' checkin of the work I did on DERBY-925. I'll let you figure out if it is safe to change metadata.properties now, or if that could cause problems during upgrade, (will you ever use the new metadata.properties with the old code that has the bug? Does it matter if a metadata query returns the wrong results in that situation?).

[Knut Anders Hatlen]

The patch looks correct to me. Some minor comments:

In lessThanString() the following is changed:

- if (pattern.length() == 0)
+ if (pattern == null)
  {
- // pattern is ""
  return null;
  }

Couldn't the entire if be removed? If pattern could be null, we would have got a NullPointerException here with the old code, so I don't think it ever will be null.

Some comments should be updated:
  - javadoc for lessThanString says: (NOTE: This may be null if the pattern is an empty string.)
  - a comment in lessThanString says:
/* Find the last non-wildcard character in the pattern
* and increment it. In the most common case,
* "asdf%" becomes "asdg". However, we need to
* handle the following:
*
* pattern return
* ------- ------
* "" null
* "%..." SUPER_STRING (match against super string)
* "_..." SUPER_STRING (match against super string)
* "asdf%" "asdg"
*/
       -> null should be replaced with SUPER_STRING
  - this comment in lessThanString should also be changed:
// Pattern starts with wildcard.
if (upperLimit.length() == 0) {
return SUPER_STRING;
}
       -- > Should say "Pattern is empty or starts with wildcard."

[Knut Anders Hatlen]

Dyre wrote:
> I'll let you figure out if it is safe to change metadata.properties now, or if that could cause problems during upgrade

It is safe to make this change to metadata.properties on trunk. However, it is not safe to make this change on the 10.2 branch. If one creates a database with Derby 10.2.X.Y which includes that change, the query will not work correctly if one moves back to 10.2.1.6 which doesn't include the change.

[Bernt M. Johnsen]

Thanks Knut! lessThanString will never be called with pattern==null since null is not allowed as a literal for pattern. When using a parameter, null is allowed, but the check is done in lessThanStringFromParameter and lessThanStringFromParameterWithEsc before lessThanString is called. I'l incorporate the comments in the patch.

Dyre: You're right. I'll create an separate issue for a change to metadata.properties that should NOT be ported to the 10.2 branch.

[Bernt M. Johnsen]

New version. Ready for commit.

[Knut Anders Hatlen]

Looks good, +1 to commit. You could also remove "NOTE: This may be null if the pattern is an empty string." from lessThanString's javadoc before you commit.

[Bernt M. Johnsen]

Thanks Knut. Here's the final patch

[Bernt M. Johnsen]

Committed revision 482983.

[Bernt M. Johnsen]

Merge to 10.2

[Bernt M. Johnsen]

Committed revision 483042.

[Andrew McIntyre]

This issue has been resolved for over a year with no further movement. Closing.