Unexpected Results when Using Concat Operator

[suyZhong]

CrateDB version

version: crate-5.6.0-2023-11-21-14-52-f951788

CrateDB setup information

Manual build following the instructions here: https://github.com/crate/crate/blob/master/devs/docs/basics.rst

Also could reproduce in 5.5.0

Problem description

The third SELECT returns an empty result, which is surprising, given that the result of second query is TRUE. The value of the WHERE condition is TRUE, and thus the third query should return the whole table.

I suppose there are some issues concerning the || operator.

Steps to Reproduce

CREATE TABLE t0(c0 INT);
INSERT INTO t0(c0) VALUES (NULL);

SELECT * FROM t0; -- NULL
SELECT NOT (c0||0) < -1 FROM t0; -- TRUE
SELECT * FROM t0 WHERE NOT (c0||0)<-1; 
-- Expected: NULL
-- Actual: Empty

Actual Result

Empty

Expected Result

NULL

[jeeminso]

Thank you for reporting @suyZhong. It seems like using a concat under not predicate is an edge case that has not been covered. To be precise,

crate/server/src/main/java/io/crate/expression/predicate/NotPredicate.java

Lines 202 to 212 in faf4c3b

	// not x = not x & x is not null
	BooleanQuery.Builder builder = new BooleanQuery.Builder();
	builder.add(notX, BooleanClause.Occur.MUST);
	for (Reference reference : ctx.references()) {
	if (reference.isNullable()) {
	var refExistsQuery = IsNullPredicate.refExistsQuery(reference, context, false);
	if (refExistsQuery != null) {
	builder.add(refExistsQuery, BooleanClause.Occur.MUST);
	}
	}
	}

I think it would be good idea to look at other parts of the LuceneQueryBuilder that attaches refExistsQuery for similar behaviours.

[mkleen]

@suyZhong Thank you for finding and submitting this bug. I am curious about how you found it. Do you have some kind of automatic approach to this? Are there any tools you are developing?

[suyZhong]

@mkleen Thanks for your feedback and fix of the issue!

Yes, I am indeed working on an automatic fuzz tester, and I started it recently. I plan to develop the method further and then share the fuzzer, making it open-source.

Do you think these bug reports are helpful? I would be happy to provide the tool once I fully developed it, in case you'd consider integrating it into your CI process or so

[mkleen]

@suyZhong Yes, those bug reports are very helpful. We are interested in an automatic fuzz testing tool and we would integrate it into our development cycle. Please keep us updated!