SELECT returns incorrect results with underscore in WHERE clause

SQL++
1

I have 20 documents with meta ids like:
document|xxxx

and 20 documents with meta ids like:
document_xxxx

The following statement incorrectly returns 40 documents:

SELECT COUNT(*) FROM bucket WHERE meta().id LIKE 'document_%'

What’s more, if I treat underscore as an escape char, and try doubling, it also fails (returning 40 documents instead of 20):

SELECT COUNT(*) FROM bucket WHERE meta().id LIKE 'document__%'

Insanely, the only way to get the query to correctly return 20 documents is to place EIGHT underscores:

SELECT COUNT(*) FROM bucket WHERE meta().id LIKE 'document________%'

This appears to be a significant bug.

2

This is upcoming 6.6.3, Post exact version you are using. cc @dh

insert into default values ("k|01",1), values ("k|02",1), values ("k_01",1), values ("k_02",1);
-- _ is escape character in like
select count(1) FROM default WHERE meta().id LIKE 'k_%' ;
{
        "$1": 4
    }
-- treat under score as  non escape character
select count(1) FROM default WHERE meta().id LIKE 'k\\_%' ;
 {
        "$1": 2
    }

https://docs.couchbase.com/server/current/n1ql/n1ql-language-reference/comparisonops.html

Match string with a wildcard expression. Use % for zero or more wildcards and _ to match any character at this place in a string.

The wildcard characters can be escaped by preceding them with a backslash (). Backslash itself can also be escaped by preceding it with another backslash.

3

I’m using 7.0 beta.

Thanks for the info.

I’m confused by the concept of the 2 placeholders, which appear to be identical. How are they in any way different?

For example, how is LIKE 'x%yz' any different than LIKE 'x_yz'?

4

It should work in 7.0.0 beta. 7.0.0 has more fixes in this area MB-45428

_ matches one character only, % matches more than one

example:

"x_y"  true of " xay", false of  "xaby"
"x%y" will true for both
1 Like