Skip to content

Unexpected results when using AND #15458

Closed

Description

@suyZhong

CrateDB version

CrateDB 5.7.0-SNAPSHOT built 93ee46e

CrateDB setup information

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

Problem description

Thanks for your effort for all the fixes!

Consider the test case below. It is unexpected that the third query returns empty result, since the second query returns TRUE, which means that the WHERE clause in the third query should be evaluated to TRUE and thus return the row in the table.

Steps to Reproduce

DROP TABLE IF EXISTS t0;
CREATE TABLE t0(c0 VARCHAR, c1 INT, PRIMARY KEY(c0));
INSERT INTO t0 (c0, c1) VALUES ('a', 1);
REFRESH TABLE t0;

SELECT * FROM t0; -- a, 1
SELECT NOT ((t0.c0 NOT IN (''))AND(t0.c1!=t0.c1)) FROM t0; -- TRUE
SELECT * FROM t0 WHERE (NOT ((t0.c0 NOT IN (''))AND(t0.c1!=t0.c1)));
-- Expected: a, 1
-- Actual:   empty result

It seems like pretty similar to #15395? I guess the previous issue has fixed as the test case is working as expected now (on 93ee46e).

In case to better fix the issue, here's another test case which I think is due to the same root cause (get and collect?), but kindly lemme know if it's caused by another reason.

DROP TABLE IF EXISTS t0;

CREATE TABLE t0(c0 VARCHAR, PRIMARY KEY(c0));
INSERT INTO t0 (c0) VALUES ('a');
REFRESH TABLE t0;


SELECT t0.c0 FROM t0;  -- a
SELECT NOT ((t0.c0 != 'b')AND(t0.c0 = 'c')) FROM t0; -- TRUE
SELECT t0.c0 FROM t0 WHERE NOT ((t0.c0 != 'b')AND(t0.c0 = 'c'));
-- expected: a
-- actual: empty table

Actual Result

As mentioned above

Expected Result

As mentioned above

Activity

  1. added
    triageAn issue that needs to be triaged by a maintainer
    on Jan 26, 2024
  2. added
    bugClear identification of incorrect behaviour
    and removed
    triageAn issue that needs to be triaged by a maintainer
    on Jan 26, 2024
  3. self-assigned this
    on Jan 28, 2024
  4. jeeminso commented on Jan 31, 2024

    @jeeminso
    Contributor

    Thanks for reporting @suyZhong .

    It seems like pretty similar to #15395?

    Yes they are supposed to due to the same cause. Initially I attempted to solve it elsewhere (but was reverted). This time the two reports should be handled by the same fix.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

Labels

bugClear identification of incorrect behaviour

Type

No type

Projects

No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions