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

UCASE(varchar_col)=... not handled for partition tables

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Duplicate
    • 10.6
    • 11.3.0
    • Optimizer
    • None

    Description

      Expected that in partition table SELECT will be used "attached_condition": "t2.s1 = 'AA'" when " optimizer_switch='sargable_casefold=on'", but they still use "attached_condition": "ucase(t1.s1) = 'AA'":
      Testcase:

      -- source include/have_partition.inc
       
      set
        @tmp_switch_sarg_casefold=@@optimizer_switch,
        optimizer_switch='sargable_casefold=on';
       
      create table t1 (s1 varchar(15)) collate utf8mb3_general_ci partition by key (s1) ;
      insert into t1 values ('aa'),('bb'),('0');
      explain format=json select * from t1 where upper(s1)='AA';
       
      drop table t1;
       
      set optimizer_switch=@tmp_switch_sarg_casefold;
      

      Actual result:

      EXPLAIN
      {
        "query_block": {
          "select_id": 1,
          "table": {
            "table_name": "t1",
            "partitions": ["p0"],
            "access_type": "ALL",
            "rows": 3,
            "filtered": 100,
            "attached_condition": "ucase(t1.s1) = 'AA'"
          }
        }
      }
      

      Attachments

        Issue Links

          Activity

            People

              psergei Sergei Petrunia
              lstartseva Lena Startseva
              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.