Details
-
Bug
-
Status: Confirmed (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.11, 11.4, 11.8, 12.3, 13.1, 12.3.2
-
Operating system:
- Ubuntu 20.04.6 LTS (Focal Fossa)
- Linux kernel: 5.4.0-204-generic
- Architecture: x86_64
Hardware:
- CPU: 2 × Intel Xeon Gold 5218R @ 2.10 GHz
- 40 physical cores / 80 logical CPUs
Description
Problem Description
With `optimizer_switch='derived_merge=off'`, the single-table path and the multi-table reconstructed path return different results for the same WHERE predicate: the single-table path returns `0`, while the multi-table reconstructed path returns an empty set. The root cause is that with `condition_pushdown_for_derived=on`, the entire `CONCAT(...) IN ('2035-02-08 07:06:22', c0)` predicate is pushed down onto the `lpart`'s `hash_ALL` (BNLH) join node, causing the same predicate to be evaluated twice and the pushed-down path to produce wrong results. After disabling `condition_pushdown_for_derived`, the `lpart` node no longer has this predicate attached, and the result recovers to `0`.
How to Reproduce
Execute the following SQL:
DROP DATABASE IF EXISTS repro_mariadb817_db2_min; |
CREATE DATABASE repro_mariadb817_db2_min; |
USE repro_mariadb817_db2_min; |
|
|
CREATE TABLE s ( |
vp_rowid BIGINT NOT NULL, |
c0 BIT(16) NOT NULL, |
UNIQUE KEY (vp_rowid), |
KEY idx_s (vp_rowid, c0) |
) ENGINE=Aria;
|
|
|
CREATE TABLE lpart ( |
vp_rowid BIGINT NOT NULL PRIMARY KEY, |
c0 BIT(16) NOT NULL |
) ENGINE=Aria;
|
|
|
CREATE TABLE rpart ( |
vp_rowid BIGINT NOT NULL PRIMARY KEY |
) ENGINE=Aria;
|
INSERT INTO s VALUES |
(1, b'0000000000001000'), |
(2, b'0000000000000000'), |
(3, b'0000000000000010'), |
(4, b'0000000000000011'), |
(5, b'0000000000000100'), |
(6, b'0000000000000101'), |
(7, b'0000000000000110'), |
(8, b'0000000000000111'); |
|
|
INSERT INTO lpart SELECT * FROM s; |
INSERT INTO rpart SELECT vp_rowid FROM s; |
|
|
SET SESSION optimizer_switch='derived_merge=off'; |
|
-- Single-table path
|
SELECT DISTINCT c0 + 0 |
FROM s |
WHERE ( |
CONCAT(
|
CAST(c0 AS CHAR CHARACTER SET utf8mb4) COLLATE utf8mb4_bin, |
CAST(59615238 AS CHAR CHARACTER SET utf8mb4) COLLATE utf8mb4_bin, |
CAST('2017-12-25 07:22:08' AS CHAR CHARACTER SET utf8mb4) COLLATE utf8mb4_bin |
) IN ('2035-02-08 07:06:22', c0) |
);
|
|
-- Multi-table reconstructed path
|
SELECT DISTINCT c0 + 0 |
FROM ( |
SELECT l.vp_rowid, l.c0 |
FROM lpart l |
JOIN rpart r USING (vp_rowid) |
) AS reconstructed |
WHERE ( |
CONCAT(
|
CAST(c0 AS CHAR CHARACTER SET utf8mb4) COLLATE utf8mb4_bin, |
CAST(59615238 AS CHAR CHARACTER SET utf8mb4) COLLATE utf8mb4_bin, |
CAST('2017-12-25 07:22:08' AS CHAR CHARACTER SET utf8mb4) COLLATE utf8mb4_bin |
) IN ('2035-02-08 07:06:22', c0) |
);
|
Expected Behavior
The single-table path and the multi-table reconstructed path should return the same result, i.e., both should return `0`.
Actual Behavior
- Single-table path: returns `0`.
- Multi-table reconstructed path: returns `empty set`.
Attachments
Issue Links
- is duplicated by
-
-
- Closed
-
- relates to
-
-
- Confirmed
-