[MDEV-16921] Filtered shows 0 with derived tables/views when optimizer_use_condition_selectivity is set to 3 Created: 2018-08-08  Updated: 2018-09-19  Resolved: 2018-09-19

Status: Closed
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 10.1, 10.2, 10.3, 10.4
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Varun Gupta (Inactive) Assignee: Varun Gupta (Inactive)
Resolution: Duplicate Votes: 0
Labels: None

Issue Links:
PartOf
is part of MDEV-15253 Default optimizer setting changes for... Closed

 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

 Comments   
Comment by Varun Gupta (Inactive) [ 2018-08-10 ]

When I run ANALYZE on table t2 on the above query and run the same query, filtered is set correctly

ANALYZE TABLE t2;
 
Table	Op	Msg_type	Msg_text
 
test.t2	analyze	status	Engine-independent statistics collected
 
test.t2	analyze	status	OK
 
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.2351,
 
    "table": {
 
      "table_name": "t2",
 
      "access_type": "ALL",
 
      "r_loops": 1,
 
      "rows": 3,
 
      "r_rows": 3,
 
      "r_total_time_ms": 0.022,
 
      "filtered": 33.33,
 
      "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.0139,
 
        "filtered": 100,
 
        "r_filtered": 100
 
      },
 
      "buffer_type": "flat",
 
      "buffer_size": "256Kb",
 
      "join_type": "BNL",
 
      "r_filtered": 100
 
    }
 
  }
 
}

Comment by Varun Gupta (Inactive) [ 2018-08-10 ]

So in our current approach of EITS ,we try to use the statistics of columns of a table even if we have not calculated them.
This does not look correct , because this can impact query plans.

So we have 2 case which are a bit problematic when we change the defaults to use EITS

  • When we have no statistics for a table.
  • When we have outdated statistics for a table.
Comment by Varun Gupta (Inactive) [ 2018-08-14 ]

Patch
http://lists.askmonty.org/pipermail/commits/2018-August/012795.html

Comment by Varun Gupta (Inactive) [ 2018-08-23 ]

Duplicate of MDEV-15306

Generated at Thu Feb 08 08:32:33 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.