Bug #120687 Incorrect result of MEMBER OF

• Submitted: 15 Jun 8:48
• Modified: 15 Jun 11:06
• Reporter: chi zhang
• Status: Verified
• Category: MySQL Server: JSON
• Severity: S3 (Non-critical)
• Version: 9.7.0
• OS: Any
• CPU Architecture: Any

[15 Jun 8:48] chi zhang

Description:
Hi,

In the following test case, there are two equivalent queries; the second one is optimized by index, but the first one is without index; however, they have different results.

```
CREATE TABLE t(
  j JSON,
  KEY idate((CAST(j->'$[*]' AS DATE ARRAY))),
  KEY idt((CAST(j->'$[*]' AS DATETIME ARRAY)))
);

INSERT INTO t VALUES
  ('["2001-01-01 12:34:56"]'),
  ('["2001-01-02 00:00:00"]');

SELECT COUNT(*) AS cnt
FROM t IGNORE INDEX (idate, idt)
WHERE '2001-01-01 00:00:00' MEMBER OF (j->'$[*]'); -- 0

SELECT COUNT(*) AS cnt
FROM t
WHERE '2001-01-01 00:00:00' MEMBER OF (j->'$[*]'); -- 1
```

How to repeat:
```
CREATE TABLE t(
  j JSON,
  KEY idate((CAST(j->'$[*]' AS DATE ARRAY))),
  KEY idt((CAST(j->'$[*]' AS DATETIME ARRAY)))
);

INSERT INTO t VALUES
  ('["2001-01-01 12:34:56"]'),
  ('["2001-01-02 00:00:00"]');

SELECT COUNT(*) AS cnt
FROM t IGNORE INDEX (idate, idt)
WHERE '2001-01-01 00:00:00' MEMBER OF (j->'$[*]'); -- 0

SELECT COUNT(*) AS cnt
FROM t
WHERE '2001-01-01 00:00:00' MEMBER OF (j->'$[*]'); -- 1
```

[15 Jun 11:06] Roy Lyseng

Thnk you for the bug report.
Verified as described.