[MDEV-16921] Filtered shows 0 with derived tables/views when optimizer_use_condition_selectivity is set to 3 - Jira

Varun Gupta (Inactive) created issue - 2018-08-08 17:29

Varun Gupta (Inactive) made changes - 2018-08-08 17:31

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}

Varun Gupta (Inactive) made changes - 2018-08-08 17:32

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

Varun Gupta (Inactive) made changes - 2018-08-08 17:46

Link

This issue is part of ~~MDEV-15253~~ [ ~~MDEV-15253~~ ]

Varun Gupta (Inactive) added a comment - 2018-08-10 12:34 - edited

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

Varun Gupta (Inactive) added a comment - 2018-08-10 12:34 - edited 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 } } }

Varun Gupta (Inactive) added a comment - 2018-08-10 12:38

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.

Varun Gupta (Inactive) added a comment - 2018-08-10 12:38 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.

Varun Gupta (Inactive) made changes - 2018-08-14 09:17

Status

Open [ 1 ]

In Progress [ 3 ]

Varun Gupta (Inactive) made changes - 2018-08-14 10:51

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

Varun Gupta (Inactive) added a comment - 2018-08-14 13:54

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

Varun Gupta (Inactive) added a comment - 2018-08-14 13:54 Patch http://lists.askmonty.org/pipermail/commits/2018-August/012795.html

Varun Gupta (Inactive) made changes - 2018-08-14 13:54

Assignee	Varun Gupta [ varun ]	Sergei Petrunia [ psergey ]
Status	In Progress [ 3 ]	In Review [ 10002 ]

Varun Gupta (Inactive) added a comment - 2018-08-23 14:55

Duplicate of ~~MDEV-15306~~

Varun Gupta (Inactive) added a comment - 2018-08-23 14:55 Duplicate of MDEV-15306

Varun Gupta (Inactive) made changes - 2018-09-19 10:58

Assignee

Sergei Petrunia [ psergey ]

Varun Gupta [ varun ]

Varun Gupta (Inactive) made changes - 2018-09-19 10:58

Status

In Review [ 10002 ]

Stalled [ 10000 ]

Varun Gupta (Inactive) made changes - 2018-09-19 10:58

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 ]

Sergei Golubchik made changes - 2021-12-06 21:47

Workflow

MariaDB v3 [ 88798 ]

MariaDB v4 [ 154764 ]

MariaDB Server

Filtered shows 0 with derived tables/views when optimizer_use_condition_selectivity is set to 3

Details

Description

Attachments

Issue Links

Activity

People

Dates

Git Integration