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

DOUBLE_PREC_HB histogram produces wrong estimates for COMPRESSED columns

    XMLWordPrintable

Details

    • Bug
    • Status: Stalled (View Workflow)
    • Minor
    • Resolution: Unresolved
    • 10.2, 10.3, 10.4, 10.5, 10.6, 10.7
    • 10.4, 10.5, 10.6
    • Optimizer
    • None

    Description

      --source include/have_sequence.inc
       
      CREATE TABLE t (a VARCHAR(16) COMPRESSED COLLATE ucs2_sinhala_ci);
      INSERT INTO t SELECT REPEAT(CHR(seq%26+97),16) FROM seq_1_to_100;
       
      set histogram_type= DOUBLE_PREC_HB;
      analyze table t persistent for all;
      ANALYZE FORMAT=JSON SELECT a FROM t WHERE 'c' BETWEEN 'l' AND a ;
       
      set histogram_type= JSON_HB;
      analyze table t persistent for all;
      ANALYZE FORMAT=JSON SELECT a FROM t WHERE 'c' BETWEEN 'l' AND a ;
       
      # Cleanup
      DROP TABLE t;
      

      DOUBLE_PREC_HB preview-10.8-MDEV-26519-json-histograms 21a81025

              "table": {
                "table_name": "t",
                "access_type": "ALL",
                "r_loops": 1,
                "rows": 100,
                "r_rows": 100,
                "r_table_time_ms": 0.006661524,
                "r_other_time_ms": 0.006350519,
                "filtered": 3.846199989,
                "r_filtered": 0,
                "attached_condition": "'c' between 'l' and t.a"
              }
      

      JSON_HB

              "table": {
                "table_name": "t",
                "access_type": "ALL",
                "r_loops": 1,
                "rows": 100,
                "r_rows": 100,
                "r_table_time_ms": 0.005778671,
                "r_other_time_ms": 0.005598088,
                "filtered": 93,
                "r_filtered": 0,
                "attached_condition": "'c' between 'l' and t.a"
              }
      

      The actual result set is naturally empty. So, DOUBLE_PREC is close, JSON is far away.

      Reproducible with at least MyISAM, InnoDB, Aria.

      Attachments

        Issue Links

          Activity

            People

              psergei Sergei Petrunia
              elenst Elena Stepanova
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

                Created:
                Updated:

                Git Integration

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