Skip to content

Unexpected result when casting integer as boolean #15537

Closed

Description

@suyZhong

CrateDB version

CrateDB 5.7.0-SNAPSHOT (built 6e7588d/NA, Linux 5.15.0-67-generic amd64, OpenJDK 64-Bit Server VM 21.0.2+13-LTS)

CrateDB setup information

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

Problem description

Consider the test case below. It is unexpected that the third query returns an empty result, because the second query returns TRUE, and the value of the WHERE predicate should be TRUE. If removing the PK constraint, the third query returns 2, which is expected.

Steps to Reproduce

DROP TABLE IF EXISTS t0;

CREATE TABLE t0(c0 INTEGER, PRIMARY KEY(c0)); -- PK is needed
INSERT INTO t0 (c0) VALUES (2);
REFRESH TABLE t0;

SELECT * FROM t0; -- 2
SELECT (CAST(t0.c0 AS BOOLEAN)= true) FROM t0; -- TRUE
SELECT * FROM t0 WHERE (CAST(t0.c0 AS BOOLEAN)= true);
-- Expected: 2
-- 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 Feb 9, 2024
  2. added
    bugClear identification of incorrect behaviour
    and removed
    triageAn issue that needs to be triaged by a maintainer
    on Feb 9, 2024
  3. matriv commented on Feb 9, 2024

    @matriv
    Contributor

    This is in the same context as: #15326
    but a bit different, since now we have an explicit cast which the symbol optimizer cannot optimize, so we end up with executing a Get plan with docKeys={true} (a boolean value), but the c0 column is an integer so we cannot match a boolean true for it.

    The issue seems to me more complex, as we cannot simply do a symbol optimization and move the cast to the literal, t0.c0 = (CAST true AS INTEGER) , as the cast cannot give back 2 or any other integer that casts to boolean true.

  4. self-assigned this
    on Feb 9, 2024
  5. added 2 commits that reference this issue on Feb 14, 2024
    26f096d
    a891b4e
  6. added a commit that references this issue on Feb 14, 2024
    33709e3
  7. matriv commented on Feb 14, 2024

    @matriv
    Contributor

    Thx a lot @suyZhong for reporting this issue, it has been fixed and will be available with the next hotfix release.

  8. added a commit that references this issue on Feb 14, 2024
    dbed5a1
  9. added a commit that references this issue on Feb 14, 2024
    975ecf1
  10. added a commit that references this issue on Feb 14, 2024
    1741d67
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