Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Duplicate
-
10.1(EOL), 10.2(EOL), 10.3(EOL), 10.4(EOL)
-
None
Description
DATASET
CREATE TABLE t1 (a int) ; |
INSERT INTO t1 VALUES (1), (1); |
CREATE TABLE t2 (b int) ; |
INSERT INTO t2 VALUES (9), (NULL), (7); |
set @@use_stat_tables= PREFERABLY |
set @@optimizer_use_condition_selectivity=3; |
analyze format=json
|
SELECT * FROM (SELECT * FROM t1,t2) t WHERE b IS NULL; |
ANALYZE
|
{
|
"query_block": {
|
"select_id": 1,
|
"r_loops": 1,
|
"r_total_time_ms": 0.4197,
|
"table": {
|
"table_name": "t2",
|
"access_type": "ALL",
|
"r_loops": 1,
|
"rows": 3,
|
"r_rows": 3,
|
"r_total_time_ms": 0.0274,
|
"filtered": 0,
|
"r_filtered": 33.333,
|
"attached_condition": "t2.b is null"
|
},
|
"block-nl-join": {
|
"table": {
|
"table_name": "t1",
|
"access_type": "ALL",
|
"r_loops": 1,
|
"rows": 2,
|
"r_rows": 2,
|
"r_total_time_ms": 0.0181,
|
"filtered": 100,
|
"r_filtered": 100
|
},
|
"buffer_type": "flat",
|
"buffer_size": "256Kb",
|
"join_type": "BNL",
|
"r_filtered": 100
|
}
|
}
|
}
|
Filtered for table t2 shows 0 which is incorrect as we have 1 row that will satisfy the WHERE clause
Attachments
Issue Links
- is part of
-
MDEV-15253 Default optimizer setting changes for MariaDB 10.4
-
- Closed
-
Activity
| Field | Original Value | New Value |
|---|---|---|
| Description |
*DATASET*
{code:sql} CREATE TABLE t1 (a int) ; INSERT INTO t1 VALUES (1), (1); CREATE TABLE t2 (b int) ; INSERT INTO t2 VALUES (9), (NULL), (7); {code} {code:sql} set @@optimizer_use_condition_selectivity=3; analyze format=json SELECT * FROM (SELECT * FROM t1,t2) t WHERE b IS NULL; {code} {noformat} ANALYZE { "query_block": { "select_id": 1, "r_loops": 1, "r_total_time_ms": 0.4197, "table": { "table_name": "t2", "access_type": "ALL", "r_loops": 1, "rows": 3, "r_rows": 3, "r_total_time_ms": 0.0274, "filtered": 0, "r_filtered": 33.333, "attached_condition": "t2.b is null" }, "block-nl-join": { "table": { "table_name": "t1", "access_type": "ALL", "r_loops": 1, "rows": 2, "r_rows": 2, "r_total_time_ms": 0.0181, "filtered": 100, "r_filtered": 100 }, "buffer_type": "flat", "buffer_size": "256Kb", "join_type": "BNL", "r_filtered": 100 } } } {noformat} |
| Description |
*DATASET*
{code:sql} CREATE TABLE t1 (a int) ; INSERT INTO t1 VALUES (1), (1); CREATE TABLE t2 (b int) ; INSERT INTO t2 VALUES (9), (NULL), (7); {code} {code:sql} set @@optimizer_use_condition_selectivity=3; analyze format=json SELECT * FROM (SELECT * FROM t1,t2) t WHERE b IS NULL; {code} {noformat} ANALYZE { "query_block": { "select_id": 1, "r_loops": 1, "r_total_time_ms": 0.4197, "table": { "table_name": "t2", "access_type": "ALL", "r_loops": 1, "rows": 3, "r_rows": 3, "r_total_time_ms": 0.0274, "filtered": 0, "r_filtered": 33.333, "attached_condition": "t2.b is null" }, "block-nl-join": { "table": { "table_name": "t1", "access_type": "ALL", "r_loops": 1, "rows": 2, "r_rows": 2, "r_total_time_ms": 0.0181, "filtered": 100, "r_filtered": 100 }, "buffer_type": "flat", "buffer_size": "256Kb", "join_type": "BNL", "r_filtered": 100 } } } {noformat} |
*DATASET*
{code:sql} CREATE TABLE t1 (a int) ; INSERT INTO t1 VALUES (1), (1); CREATE TABLE t2 (b int) ; INSERT INTO t2 VALUES (9), (NULL), (7); {code} {code:sql} set @@optimizer_use_condition_selectivity=3; analyze format=json SELECT * FROM (SELECT * FROM t1,t2) t WHERE b IS NULL; {code} {noformat} ANALYZE { "query_block": { "select_id": 1, "r_loops": 1, "r_total_time_ms": 0.4197, "table": { "table_name": "t2", "access_type": "ALL", "r_loops": 1, "rows": 3, "r_rows": 3, "r_total_time_ms": 0.0274, "filtered": 0, "r_filtered": 33.333, "attached_condition": "t2.b is null" }, "block-nl-join": { "table": { "table_name": "t1", "access_type": "ALL", "r_loops": 1, "rows": 2, "r_rows": 2, "r_total_time_ms": 0.0181, "filtered": 100, "r_filtered": 100 }, "buffer_type": "flat", "buffer_size": "256Kb", "join_type": "BNL", "r_filtered": 100 } } } {noformat} Filtered for table t2 shows 0 which is incorrect as we have 1 row that will satisfy the WHERE clause |
| Link |
This issue is part of |
| Status | Open [ 1 ] | In Progress [ 3 ] |
| Description |
*DATASET*
{code:sql} CREATE TABLE t1 (a int) ; INSERT INTO t1 VALUES (1), (1); CREATE TABLE t2 (b int) ; INSERT INTO t2 VALUES (9), (NULL), (7); {code} {code:sql} set @@optimizer_use_condition_selectivity=3; analyze format=json SELECT * FROM (SELECT * FROM t1,t2) t WHERE b IS NULL; {code} {noformat} ANALYZE { "query_block": { "select_id": 1, "r_loops": 1, "r_total_time_ms": 0.4197, "table": { "table_name": "t2", "access_type": "ALL", "r_loops": 1, "rows": 3, "r_rows": 3, "r_total_time_ms": 0.0274, "filtered": 0, "r_filtered": 33.333, "attached_condition": "t2.b is null" }, "block-nl-join": { "table": { "table_name": "t1", "access_type": "ALL", "r_loops": 1, "rows": 2, "r_rows": 2, "r_total_time_ms": 0.0181, "filtered": 100, "r_filtered": 100 }, "buffer_type": "flat", "buffer_size": "256Kb", "join_type": "BNL", "r_filtered": 100 } } } {noformat} Filtered for table t2 shows 0 which is incorrect as we have 1 row that will satisfy the WHERE clause |
*DATASET*
{code:sql} CREATE TABLE t1 (a int) ; INSERT INTO t1 VALUES (1), (1); CREATE TABLE t2 (b int) ; INSERT INTO t2 VALUES (9), (NULL), (7); {code} {code:sql} set @@use_stat_tables= PREFERABLY set @@optimizer_use_condition_selectivity=3; analyze format=json SELECT * FROM (SELECT * FROM t1,t2) t WHERE b IS NULL; {code} {noformat} ANALYZE { "query_block": { "select_id": 1, "r_loops": 1, "r_total_time_ms": 0.4197, "table": { "table_name": "t2", "access_type": "ALL", "r_loops": 1, "rows": 3, "r_rows": 3, "r_total_time_ms": 0.0274, "filtered": 0, "r_filtered": 33.333, "attached_condition": "t2.b is null" }, "block-nl-join": { "table": { "table_name": "t1", "access_type": "ALL", "r_loops": 1, "rows": 2, "r_rows": 2, "r_total_time_ms": 0.0181, "filtered": 100, "r_filtered": 100 }, "buffer_type": "flat", "buffer_size": "256Kb", "join_type": "BNL", "r_filtered": 100 } } } {noformat} Filtered for table t2 shows 0 which is incorrect as we have 1 row that will satisfy the WHERE clause |
| Assignee | Varun Gupta [ varun ] | Sergei Petrunia [ psergey ] |
| Status | In Progress [ 3 ] | In Review [ 10002 ] |
| Assignee | Sergei Petrunia [ psergey ] | Varun Gupta [ varun ] |
| Status | In Review [ 10002 ] | Stalled [ 10000 ] |
| Fix Version/s | N/A [ 14700 ] | |
| Fix Version/s | 10.2 [ 14601 ] | |
| Fix Version/s | 10.1 [ 16100 ] | |
| Fix Version/s | 10.3 [ 22126 ] | |
| Fix Version/s | 10.4 [ 22408 ] | |
| Resolution | Duplicate [ 3 ] | |
| Status | Stalled [ 10000 ] | Closed [ 6 ] |
| Workflow | MariaDB v3 [ 88798 ] | MariaDB v4 [ 154764 ] |