Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-16921

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

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Duplicate
    • Affects Version/s: 10.1, 10.2, 10.3, 10.4
    • Fix Version/s: N/A
    • Component/s: Optimizer
    • Labels:
      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

            Activity

              People

              Assignee:
              varun Varun Gupta
              Reporter:
              varun Varun Gupta
              Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved: