sql: Incorrect result for LIKE query

[mrigger]

Consider the following test case:

CREATE TABLE t0(c0 STRING UNIQUE);
INSERT INTO t0 (c0) VALUES ('\a');
SELECT * FROM t0 WHERE c0 LIKE '\a'; -- unexpected: row is fetched

Unexpectedly, the query fetches a row. When removing the UNIQUE constraint, no row is fetched. That the predicate should evaluate to FALSE is also demonstrated by the following query:

SELECT c0 LIKE '\a' FROM t0; -- FALSE

I built CockroachDB from source (commit 55c0e012b9539a327640e42ceaaf556b7a0e7b10) on Ubuntu 19.04.

[mrigger]

It might also be that

SELECT c0 LIKE '\a' FROM t0;

should yield TRUE, which is, for example, the behavior in SQLite3.

[RaduBerinde]

The bug is on the planning side, but we would need some guidance from someone familiar with the semantics (and the relevant code in sem/tree/eval.go, which seems pretty complex).

[RaduBerinde]

We don't handle escaping at all in the pattern when we create index constraints from a LIKE expression. There are many other bugs here, e.g. '\' is not LIKE '\%' but it would get returned with an index. Fortunately it should be easy to write a targeted fuzz test that cross-checks queries with and without using the index.

[cucaroach]

Seems like there's more going on here that meets the eye. We always get the right answer if we use LIKE '\a' ESCAPE '', ie this forces the escaping to take place and things work. If we use the row engine and comment out the opt.LikeOp handling in index_constraints then things work (w/ and w/o UNIQUE). If I use the vector engine it only breaks when there's UNIQUE constraint.

So I think there's two issues:

• when converting a LIKE right hand side to a filter we need to process escapes, since the execution engines will reduce cases like this to an equality, this needs to happen in index_constraint and in the non-optimized generic filter case. I think the logic we want is contained in tree.unescapePattern this needs to be added to colbuilder.planProjectionExpr and index_constraints

• with the vectorized engine the UNIQUE constraint enables the index_constraints span optimization not sure why

[piotrb5e3]

We're still getting this on v21.1.12 and v21.2.2 (docker image distribution).

Removing the index or adding an explicit ESCAPE '\' helps. Our test example:

CREATE TABLE IF NOT EXISTS "prefix_search_test" ("id" serial NOT NULL PRIMARY KEY, "value" STRING NOT NULL);
-- CREATE UNIQUE INDEX "prefix_search_test_value_uniq" ON "prefix_search_test" ("value");  -- Uncommenting breaks results
INSERT INTO "prefix_search_test" ("value") VALUES ('foo_bar1');
SELECT * FROM "prefix_search_test" WHERE ("value"::text LIKE 'foo\_bar%');