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

ANALYZE doesn't work with pushed derived tables

    XMLWordPrintable

Details

    Description

      (I tried on 10.6 and 10.10 but perhaps earlier versions are affected as well)

      Put this into mysql-test/suite/federated/a1.test :

      --source have_federatedx.inc
      --source include/federated.inc
       
      connection default;
       
      set global federated_pushdown=1;
       
      connection slave;
       
      DROP TABLE IF EXISTS federated.t1;
      CREATE TABLE federated.t1 (
        a  int(20) NOT NULL
      );
      INSERT INTO federated.t1 VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
       
      connection master;
       
      DROP TABLE IF EXISTS federated.t1;
       
      --replace_result $SLAVE_MYPORT SLAVE_PORT
      eval
      CREATE TABLE federated.t1 (
        a int(20) NOT NULL
      )
      ENGINE="FEDERATED" CONNECTION='mysql://root@127.0.0.1:$SLAVE_MYPORT/federated/t1';
       
      analyze format=json
      select * from (select * from federated.t1 where a<3 union 
                     select * from federated.t1 where a>=3) T;
       
      set global federated_pushdown=0;
       
      source include/federated_cleanup.inc;
      

      and run.

      At the end of output see:

      {
        "query_block": {
          "select_id": 1,
          "r_loops": 1,
          "r_total_time_ms": 0.548587957,
          "table": {
            "table_name": "<derived2>",
            "access_type": "ALL",
            "r_loops": 1,
            "rows": 20,
            "r_rows": 0,
            "r_table_time_ms": 1.660416e-4,
            "r_other_time_ms": 0.005957995,
            "filtered": 100,
            "r_filtered": 100,
            "materialized": {
              "query_block": {
                "select_id": 2,
                "table": {
                  "message": "Pushed derived"
                }
              }
            }
          }
        }
      }
      

      Note the {{ "r_rows": 0}}.

      This is because pushdown code doesn't handle ANALYZE correctly.

      Attachments

        Activity

          People

            oleg.smirnov Oleg Smirnov
            psergei Sergei Petrunia
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.