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

Wrong/Unexpected result with the value optimizer_use_condition_selectivity set to 4

Details

    • 10.3.6-1

    Description

      Here is the mtr test, 

      CREATE TABLE t1 (a INT);
      		 
      INSERT INTO t1 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
      		 
      set optimizer_use_condition_selectivity=4;
      		 
      CREATE FUNCTION f1() RETURNS INT DETERMINISTIC
      		 
      BEGIN
      		 
      SET @cnt := @cnt + 1;
      		 
      RETURN 1;
      		 
      END;|
      		 
      SET @cnt := 0;
      		 
      SELECT * FROM t1 WHERE a = f1();
      		 
      a
      		 
      1
      		 
      SELECT @cnt;
      		 
      @cnt
      		 
      2
      		 
      SET @cnt := 0;
      		 
      set optimizer_use_condition_selectivity=1;
      		 
      SELECT * FROM t1 WHERE a = f1();
      		 
      a
      		 
      1
      		 
      drop table t1;
      		 
      drop function f1;

      cc igorpsergey

      Attachments

        Issue Links

          relates to

          Task - A task that needs to be done. MDEV-15253 Default optimizer setting changes for MariaDB 10.4

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            varun Varun Gupta (Inactive) created issue -
            varun Varun Gupta (Inactive) made changes -
            Field Original Value New Value
            Description Here is the mtr test,

            {code:sql}
            CREATE TABLE t1 (a INT);
            INSERT INTO t1 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
            set optimizer_use_condition_selectivity=4;
            CREATE FUNCTION f1() RETURNS INT DETERMINISTIC
            BEGIN
            SET @cnt := @cnt + 1;
            RETURN 1;
            END;|
            SET @cnt := 0;
            SELECT * FROM t1 WHERE a = f1();
            a
            1
            SELECT @cnt;
            @cnt
            2
            SET @cnt := 0;
            set optimizer_use_condition_selectivity=1;
            SELECT * FROM t1 WHERE a = f1();
            a
            1
            drop table t1;
            drop function f1;
            {code}

            [~igor][~psergey]             		Here is the mtr test,

            {code:sql}
            CREATE TABLE t1 (a INT);
            INSERT INTO t1 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
            set optimizer_use_condition_selectivity=4;
            CREATE FUNCTION f1() RETURNS INT DETERMINISTIC
            BEGIN
            SET @cnt := @cnt + 1;
            RETURN 1;
            END;|
            SET @cnt := 0;
            SELECT * FROM t1 WHERE a = f1();
            a
            1
            SELECT @cnt;
            @cnt
            2
            SET @cnt := 0;
            set optimizer_use_condition_selectivity=1;
            SELECT * FROM t1 WHERE a = f1();
            a
            1
            drop table t1;
            drop function f1;
            {code}

            cc [~igor][~psergey]
            varun Varun Gupta (Inactive) made changes -
            varun Varun Gupta (Inactive) made changes -
            Sprint 10.3.6-0 [ 237 ]
            varun Varun Gupta (Inactive) made changes -
            Status Open [ 1 ] In Progress [ 3 ]
            varun Varun Gupta (Inactive) made changes -
            Assignee Varun Gupta [ varun ] Sergei Petrunia [ psergey ]
            Status In Progress [ 3 ] In Review [ 10002 ]
            serg Sergei Golubchik made changes -
            Affects Version/s 10.3 [ 22126 ]
            psergei Sergei Petrunia made changes -
            Assignee Sergei Petrunia [ psergey ] Varun Gupta [ varun ]
            Status In Review [ 10002 ] Stalled [ 10000 ]
            varun Varun Gupta (Inactive) made changes -
            Status Stalled [ 10000 ] In Progress [ 3 ]
            varun Varun Gupta (Inactive) made changes -
            Status In Progress [ 3 ] In Review [ 10002 ]
            varun Varun Gupta (Inactive) made changes -
            Status In Review [ 10002 ] Stalled [ 10000 ]
            varun Varun Gupta (Inactive) made changes -
            Component/s Optimizer [ 10200 ]
            Fix Version/s 10.0.37 [ 22917 ]
            Fix Version/s 10.1.36 [ 23117 ]
            Fix Version/s 10.2.18 [ 23112 ]
            Fix Version/s 10.3.10 [ 23140 ]
            Fix Version/s 10.4.0 [ 23115 ]
            Fix Version/s 10.3 [ 22126 ]
            Resolution Fixed [ 1 ]
            Status Stalled [ 10000 ] Closed [ 6 ]
            serg Sergei Golubchik made changes -
            Workflow MariaDB v3 [ 85568 ] MariaDB v4 [ 153792 ]

            People

              varun Varun Gupta (Inactive)
              varun Varun Gupta (Inactive)
              Votes:
              0 Vote for this issue
              Watchers:
              3 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.