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

Comparison ROW(stored_func(),1)=ROW(1,1) calls the function twice per row

Details

    Description

      I run this script:

      DELIMITER /
      		 
      CREATE OR REPLACE FUNCTION f1() RETURNS INT
      		 
      BEGIN
      		 
        SET @counter= COALESCE(@counter, 0) + 1;
      		 
        RETURN @counter;
      		 
      END;
      		 
      /
      		 
      DELIMITER ;
      		 
      SET @counter=0;
      		 
      SELECT f1()=1, @counter FROM seq_1_to_5;


      +--------+----------+
      		 
      | f1()=1 | @counter |
      		 
      +--------+----------+
      		 
      |      1 |        1 |
      		 
      |      0 |        2 |
      		 
      |      0 |        3 |
      		 
      |      0 |        4 |
      		 
      |      0 |        5 |
      		 
      +--------+----------+

      So far so good: the counter increments by one every row.

      Now I change the query to use ROWs:

      SELECT ROW(f1(),1) = ROW(1,1), @counter FROM seq_1_to_5;


      +------------------+----------+
      		 
      | (f1(),1) = (1,1) | @counter |
      		 
      +------------------+----------+
      		 
      |                0 |        7 |
      		 
      |                0 |        9 |
      		 
      |                0 |       11 |
      		 
      |                0 |       13 |
      		 
      |                0 |       15 |
      		 
      +------------------+----------+

      Now the counter increments twice per row. Looks wrong.

      Tracing in gdb reveals that Item_func_sp::execute() is indeed called twice per row:

      • From Item_func_sp::bring_value()
      • From Arg_comparator::compare_int_signed() when Arg_comparator::compare_row() evaluates equality of its members.

      Attachments

        Issue Links

          is caused by

          Technical task - A technical task. MDEV-12252 ROW data type for stored function return values

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

          Activity

            Ascending order - Click to sort in descending order
            bar Alexander Barkov created issue -
            bar Alexander Barkov made changes -
            Field Original Value New Value
            Description I run this script:

            {code:sql}
            DELIMITER /
            CREATE OR REPLACE FUNCTION f1() RETURNS INT
            BEGIN
              SET @counter= COALESCE(@counter, 0) + 1;
              RETURN @counter;
            END;
            /
            DELIMITER ;
            SET @counter=0;
            SELECT f1()=1, @counter FROM seq_1_to_5;
            {code}
            {noformat}
            +--------+----------+
            | f1()=1 | @counter |
            +--------+----------+
            | 1 | 1 |
            | 0 | 2 |
            | 0 | 3 |
            | 0 | 4 |
            | 0 | 5 |
            +--------+----------+
            {noformat}

            So far so good: the counter increments by one every row.

            Now I change the quuer to use ROWs:

            {code:sql}
            SELECT ROW(f1(),1) = ROW(1,1), @counter FROM seq_1_to_5;
            {code}
            {noformat}
            +------------------+----------+
            | (f1(),1) = (1,1) | @counter |
            +------------------+----------+
            | 0 | 7 |
            | 0 | 9 |
            | 0 | 11 |
            | 0 | 13 |
            | 0 | 15 |
            +------------------+----------+
            {noformat}

            Now the counter increments twice per row. Looks wrong.

            Tracing in gdb reveals that Item_func_sp::execute() is indeed called twice per row:
            - From Item_func_sp::bring_value()
            - From Arg_comparator::compare_int_signed() when Arg_comparator::compare_row() evaluates equality of its members.             		I run this script:

            {code:sql}
            DELIMITER /
            CREATE OR REPLACE FUNCTION f1() RETURNS INT
            BEGIN
              SET @counter= COALESCE(@counter, 0) + 1;
              RETURN @counter;
            END;
            /
            DELIMITER ;
            SET @counter=0;
            SELECT f1()=1, @counter FROM seq_1_to_5;
            {code}
            {noformat}
            +--------+----------+
            | f1()=1 | @counter |
            +--------+----------+
            | 1 | 1 |
            | 0 | 2 |
            | 0 | 3 |
            | 0 | 4 |
            | 0 | 5 |
            +--------+----------+
            {noformat}

            So far so good: the counter increments by one every row.

            Now I change the query to use ROWs:

            {code:sql}
            SELECT ROW(f1(),1) = ROW(1,1), @counter FROM seq_1_to_5;
            {code}
            {noformat}
            +------------------+----------+
            | (f1(),1) = (1,1) | @counter |
            +------------------+----------+
            | 0 | 7 |
            | 0 | 9 |
            | 0 | 11 |
            | 0 | 13 |
            | 0 | 15 |
            +------------------+----------+
            {noformat}

            Now the counter increments twice per row. Looks wrong.

            Tracing in gdb reveals that Item_func_sp::execute() is indeed called twice per row:
            - From Item_func_sp::bring_value()
            - From Arg_comparator::compare_int_signed() when Arg_comparator::compare_row() evaluates equality of its members.
            bar Alexander Barkov made changes -
            bar Alexander Barkov made changes -
            Summary Comparison of a ROW(stored_func(),1)=ROW(1,1) calls the function twice per row Comparison ROW(stored_func(),1)=ROW(1,1) calls the function twice per row
            bar Alexander Barkov made changes -
            Status Open [ 1 ] Confirmed [ 10101 ]
            bar Alexander Barkov made changes -
            Assignee Alexander Barkov [ bar ]
            bar Alexander Barkov made changes -
            Assignee Alexander Barkov [ bar ] Oleksandr Byelkin [ sanja ]
            Status Confirmed [ 10101 ] In Review [ 10002 ]
            sanja Oleksandr Byelkin made changes -
            Assignee Oleksandr Byelkin [ sanja ] Alexander Barkov [ bar ]
            Status In Review [ 10002 ] Stalled [ 10000 ]
            bar Alexander Barkov made changes -
            Fix Version/s 11.8.2 [ 30001 ]
            Fix Version/s 12.0.0 [ 30115 ]
            Fix Version/s 11.8 [ 29921 ]
            bar Alexander Barkov made changes -
            Resolution Fixed [ 1 ]
            Status Stalled [ 10000 ] Closed [ 6 ]

            People

              bar Alexander Barkov
              bar Alexander Barkov
              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.