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

Crash in prepared statement with NO_ZERO_IN_DATE and ROLLUP

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 10.0.9
    • 10.0.10
    • None
    • None

    Description

      This SQL script makes 10.0 crash:

      DROP TABLE IF EXISTS t1;
      		 
      CREATE TABLE t1
      		 
      (
      		 
        year    INT NOT NULL,
      		 
        product VARCHAR(32) NOT NULL,
      		 
        profit  INT
      		 
      );
      		 
      INSERT INTO t1 VALUES ('2001','car',101);
      		 
      INSERT INTO t1 VALUES ('2001','gas',102);
      		 
      INSERT INTO t1 VALUES ('2001','toy',103);
      		 
      INSERT INTO t1 VALUES ('2002','car',201);
      		 
      INSERT INTO t1 VALUES ('2002','gas',202);
      		 
      INSERT INTO t1 VALUES ('2002','toy',203);
      		 
      SET sql_mode=DEFAULT;
      		 
      PREPARE stmt FROM "SELECT DATE'2001-00-00' AS c,year, SUM(profit) FROM t1 GROUP BY c,year WITH ROLLUP";
      		 
      EXECUTE stmt;
      		 
      SET sql_mode='no_zero_in_date';
      		 
      EXECUTE stmt;

      GDB trace:

      (gdb) where
      		 
      #0  0x00000000005a6642 in String::length (this=0x0)
      		 
          at /home/bar/maria-bzr/maria-10.0/sql/sql_string.h:130
      		 
      #1  0x00000000006ed824 in sortcmp (s=0x7fff70c23e60, t=0x0, 
          cs=0x173de60 <my_charset_latin1>)
      		 
          at /home/bar/maria-bzr/maria-10.0/sql/sql_string.cc:726
      		 
      #2  0x000000000086acac in Cached_item_str::cmp (this=0x7fff70c23e40)
      		 
          at /home/bar/maria-bzr/maria-10.0/sql/item_buff.cc:94
      		 
      #3  0x00000000006b7c62 in test_if_group_changed (list=...)
      		 
          at /home/bar/maria-bzr/maria-10.0/sql/sql_select.cc:21647
      		 
      #4  0x00000000006b0c20 in end_send_group (join=0x7fff70c221b8, 
          join_tab=0x7fff70c235c8, end_of_records=false)
      		 
          at /home/bar/maria-bzr/maria-10.0/sql/sql_select.cc:18757
      		 
      #5  0x00000000006ae11e in evaluate_join_record (join=0x7fff70c221b8, 
          join_tab=0x7fff70c232a0, error=0)
      		 
          at /home/bar/maria-bzr/maria-10.0/sql/sql_select.cc:17689
      		 
      ...
      		 
      (gdb) p this
      		 
      $1 = (const String * const) 0x0

      Note, 5.5 does not crash, but produces a wrong result in the second EXECUTE:

      +------------+------+-------------+
      		 
      | c          | year | SUM(profit) |
      		 
      +------------+------+-------------+
      		 
      | 2001-00-00 | 2001 |         306 |
      		 
      | 2001-00-00 | 2002 |         606 |
      		 
      | 2001-00-00 | NULL |         912 |
      		 
      | NULL       | NULL |         912 |
      		 
      +------------+------+-------------+

      '2001-00-00' should not be possible in a result set when running with NO_ZERO_IN_DATE.

      The problem most likely happens because DATE literal is uncertain about its NULL-ability:

      SET sql_mode=DEFAULT;
      		 
      PREPARE stmt FROM "select isnull(DATE'2001-00-00'), DATE'2001-00-00'";
      		 
      SET sql_mode='no_zero_in_date';
      		 
      EXECUTE stmt;
      		 
       
      		 
      +--------------------------+------------------+
      		 
      | isnull(DATE'2001-00-00') | DATE'2001-00-00' |
      		 
      +--------------------------+------------------+
      		 
      |                        0 | NULL             |
      		 
      +--------------------------+------------------+
      		 
      1 row in set, 1 warning (0.00 sec)

      Attachments

        Activity

          Ascending order - Click to sort in descending order
          bar Alexander Barkov created issue -
          bar Alexander Barkov made changes -
          Field Original Value New Value
          Summary Crash prepared statement with NO_ZERO_IN_DATE and ROLLUP Crash in prepared statement with NO_ZERO_IN_DATE and ROLLUP
          bar Alexander Barkov made changes -
          Description This SQL script makes 10.0 crash:
          {code}
          DROP TABLE IF EXISTS t1;
          CREATE TABLE t1
          (
            year INT NOT NULL,
            product VARCHAR(32) NOT NULL,
            profit INT
          );
          INSERT INTO t1 VALUES ('2001','car',101);
          INSERT INTO t1 VALUES ('2001','gas',102);
          INSERT INTO t1 VALUES ('2001','toy',103);
          INSERT INTO t1 VALUES ('2002','car',201);
          INSERT INTO t1 VALUES ('2002','gas',202);
          INSERT INTO t1 VALUES ('2002','toy',203);
          SET sql_mode=DEFAULT;
          PREPARE stmt FROM "SELECT DATE'2001-00-00' AS c,year, SUM(profit) FROM t1 GROUP BY c,year WITH ROLLUP";
          EXECUTE stmt;
          SET sql_mode='no_zero_in_date';
          EXECUTE stmt;
          {code}

          GDB trace:

          {code}
          (gdb) where
          #0 0x00000000005a6642 in String::length (this=0x0)
              at /home/bar/maria-bzr/maria-10.0/sql/sql_string.h:130
          #1 0x00000000006ed824 in sortcmp (s=0x7fff70c23e60, t=0x0,
              cs=0x173de60 <my_charset_latin1>)
              at /home/bar/maria-bzr/maria-10.0/sql/sql_string.cc:726
          #2 0x000000000086acac in Cached_item_str::cmp (this=0x7fff70c23e40)
              at /home/bar/maria-bzr/maria-10.0/sql/item_buff.cc:94
          #3 0x00000000006b7c62 in test_if_group_changed (list=...)
              at /home/bar/maria-bzr/maria-10.0/sql/sql_select.cc:21647
          #4 0x00000000006b0c20 in end_send_group (join=0x7fff70c221b8,
              join_tab=0x7fff70c235c8, end_of_records=false)
              at /home/bar/maria-bzr/maria-10.0/sql/sql_select.cc:18757
          #5 0x00000000006ae11e in evaluate_join_record (join=0x7fff70c221b8,
              join_tab=0x7fff70c232a0, error=0)
              at /home/bar/maria-bzr/maria-10.0/sql/sql_select.cc:17689
          ...
          (gdb) p this
          $1 = (const String * const) 0x0
          {code}

          Note, 5.5 does not crash, but produces a wrong result in the second EXECUTE:
          {code}
          +------------+------+-------------+
          | c | year | SUM(profit) |
          +------------+------+-------------+
          | 2001-00-00 | 2001 | 306 |
          | 2001-00-00 | 2002 | 606 |
          | 2001-00-00 | NULL | 912 |
          | NULL | NULL | 912 |
          +------------+------+-------------+
          {code}
          '2001-00-00' should not be possible with NO_ZERO_IN_DATE.
          		 This SQL script makes 10.0 crash:
          {code}
          DROP TABLE IF EXISTS t1;
          CREATE TABLE t1
          (
            year INT NOT NULL,
            product VARCHAR(32) NOT NULL,
            profit INT
          );
          INSERT INTO t1 VALUES ('2001','car',101);
          INSERT INTO t1 VALUES ('2001','gas',102);
          INSERT INTO t1 VALUES ('2001','toy',103);
          INSERT INTO t1 VALUES ('2002','car',201);
          INSERT INTO t1 VALUES ('2002','gas',202);
          INSERT INTO t1 VALUES ('2002','toy',203);
          SET sql_mode=DEFAULT;
          PREPARE stmt FROM "SELECT DATE'2001-00-00' AS c,year, SUM(profit) FROM t1 GROUP BY c,year WITH ROLLUP";
          EXECUTE stmt;
          SET sql_mode='no_zero_in_date';
          EXECUTE stmt;
          {code}

          GDB trace:

          {code}
          (gdb) where
          #0 0x00000000005a6642 in String::length (this=0x0)
              at /home/bar/maria-bzr/maria-10.0/sql/sql_string.h:130
          #1 0x00000000006ed824 in sortcmp (s=0x7fff70c23e60, t=0x0,
              cs=0x173de60 <my_charset_latin1>)
              at /home/bar/maria-bzr/maria-10.0/sql/sql_string.cc:726
          #2 0x000000000086acac in Cached_item_str::cmp (this=0x7fff70c23e40)
              at /home/bar/maria-bzr/maria-10.0/sql/item_buff.cc:94
          #3 0x00000000006b7c62 in test_if_group_changed (list=...)
              at /home/bar/maria-bzr/maria-10.0/sql/sql_select.cc:21647
          #4 0x00000000006b0c20 in end_send_group (join=0x7fff70c221b8,
              join_tab=0x7fff70c235c8, end_of_records=false)
              at /home/bar/maria-bzr/maria-10.0/sql/sql_select.cc:18757
          #5 0x00000000006ae11e in evaluate_join_record (join=0x7fff70c221b8,
              join_tab=0x7fff70c232a0, error=0)
              at /home/bar/maria-bzr/maria-10.0/sql/sql_select.cc:17689
          ...
          (gdb) p this
          $1 = (const String * const) 0x0
          {code}

          Note, 5.5 does not crash, but produces a wrong result in the second EXECUTE:
          {code}
          +------------+------+-------------+
          | c | year | SUM(profit) |
          +------------+------+-------------+
          | 2001-00-00 | 2001 | 306 |
          | 2001-00-00 | 2002 | 606 |
          | 2001-00-00 | NULL | 912 |
          | NULL | NULL | 912 |
          +------------+------+-------------+
          {code}
          '2001-00-00' should not be possible in a result set when running with NO_ZERO_IN_DATE.
          serg Sergei Golubchik made changes -
          Fix Version/s 10.0.10 [ 14500 ]
          bar Alexander Barkov made changes -
          Description This SQL script makes 10.0 crash:
          {code}
          DROP TABLE IF EXISTS t1;
          CREATE TABLE t1
          (
            year INT NOT NULL,
            product VARCHAR(32) NOT NULL,
            profit INT
          );
          INSERT INTO t1 VALUES ('2001','car',101);
          INSERT INTO t1 VALUES ('2001','gas',102);
          INSERT INTO t1 VALUES ('2001','toy',103);
          INSERT INTO t1 VALUES ('2002','car',201);
          INSERT INTO t1 VALUES ('2002','gas',202);
          INSERT INTO t1 VALUES ('2002','toy',203);
          SET sql_mode=DEFAULT;
          PREPARE stmt FROM "SELECT DATE'2001-00-00' AS c,year, SUM(profit) FROM t1 GROUP BY c,year WITH ROLLUP";
          EXECUTE stmt;
          SET sql_mode='no_zero_in_date';
          EXECUTE stmt;
          {code}

          GDB trace:

          {code}
          (gdb) where
          #0 0x00000000005a6642 in String::length (this=0x0)
              at /home/bar/maria-bzr/maria-10.0/sql/sql_string.h:130
          #1 0x00000000006ed824 in sortcmp (s=0x7fff70c23e60, t=0x0,
              cs=0x173de60 <my_charset_latin1>)
              at /home/bar/maria-bzr/maria-10.0/sql/sql_string.cc:726
          #2 0x000000000086acac in Cached_item_str::cmp (this=0x7fff70c23e40)
              at /home/bar/maria-bzr/maria-10.0/sql/item_buff.cc:94
          #3 0x00000000006b7c62 in test_if_group_changed (list=...)
              at /home/bar/maria-bzr/maria-10.0/sql/sql_select.cc:21647
          #4 0x00000000006b0c20 in end_send_group (join=0x7fff70c221b8,
              join_tab=0x7fff70c235c8, end_of_records=false)
              at /home/bar/maria-bzr/maria-10.0/sql/sql_select.cc:18757
          #5 0x00000000006ae11e in evaluate_join_record (join=0x7fff70c221b8,
              join_tab=0x7fff70c232a0, error=0)
              at /home/bar/maria-bzr/maria-10.0/sql/sql_select.cc:17689
          ...
          (gdb) p this
          $1 = (const String * const) 0x0
          {code}

          Note, 5.5 does not crash, but produces a wrong result in the second EXECUTE:
          {code}
          +------------+------+-------------+
          | c | year | SUM(profit) |
          +------------+------+-------------+
          | 2001-00-00 | 2001 | 306 |
          | 2001-00-00 | 2002 | 606 |
          | 2001-00-00 | NULL | 912 |
          | NULL | NULL | 912 |
          +------------+------+-------------+
          {code}
          '2001-00-00' should not be possible in a result set when running with NO_ZERO_IN_DATE.
          		 This SQL script makes 10.0 crash:
          {code}
          DROP TABLE IF EXISTS t1;
          CREATE TABLE t1
          (
            year INT NOT NULL,
            product VARCHAR(32) NOT NULL,
            profit INT
          );
          INSERT INTO t1 VALUES ('2001','car',101);
          INSERT INTO t1 VALUES ('2001','gas',102);
          INSERT INTO t1 VALUES ('2001','toy',103);
          INSERT INTO t1 VALUES ('2002','car',201);
          INSERT INTO t1 VALUES ('2002','gas',202);
          INSERT INTO t1 VALUES ('2002','toy',203);
          SET sql_mode=DEFAULT;
          PREPARE stmt FROM "SELECT DATE'2001-00-00' AS c,year, SUM(profit) FROM t1 GROUP BY c,year WITH ROLLUP";
          EXECUTE stmt;
          SET sql_mode='no_zero_in_date';
          EXECUTE stmt;
          {code}

          GDB trace:

          {code}
          (gdb) where
          #0 0x00000000005a6642 in String::length (this=0x0)
              at /home/bar/maria-bzr/maria-10.0/sql/sql_string.h:130
          #1 0x00000000006ed824 in sortcmp (s=0x7fff70c23e60, t=0x0,
              cs=0x173de60 <my_charset_latin1>)
              at /home/bar/maria-bzr/maria-10.0/sql/sql_string.cc:726
          #2 0x000000000086acac in Cached_item_str::cmp (this=0x7fff70c23e40)
              at /home/bar/maria-bzr/maria-10.0/sql/item_buff.cc:94
          #3 0x00000000006b7c62 in test_if_group_changed (list=...)
              at /home/bar/maria-bzr/maria-10.0/sql/sql_select.cc:21647
          #4 0x00000000006b0c20 in end_send_group (join=0x7fff70c221b8,
              join_tab=0x7fff70c235c8, end_of_records=false)
              at /home/bar/maria-bzr/maria-10.0/sql/sql_select.cc:18757
          #5 0x00000000006ae11e in evaluate_join_record (join=0x7fff70c221b8,
              join_tab=0x7fff70c232a0, error=0)
              at /home/bar/maria-bzr/maria-10.0/sql/sql_select.cc:17689
          ...
          (gdb) p this
          $1 = (const String * const) 0x0
          {code}

          Note, 5.5 does not crash, but produces a wrong result in the second EXECUTE:
          {code}
          +------------+------+-------------+
          | c | year | SUM(profit) |
          +------------+------+-------------+
          | 2001-00-00 | 2001 | 306 |
          | 2001-00-00 | 2002 | 606 |
          | 2001-00-00 | NULL | 912 |
          | NULL | NULL | 912 |
          +------------+------+-------------+
          {code}
          '2001-00-00' should not be possible in a result set when running with NO_ZERO_IN_DATE.


          The problem most likely happens because DATE literal is uncertain about its NULL-ability:
          {code}
          SET sql_mode=DEFAULT;
          PREPARE stmt FROM "select isnull(DATE'2001-00-00'), DATE'2001-00-00'";
          SET sql_mode='no_zero_in_date';
          EXECUTE stmt;

          +--------------------------+------------------+
          | isnull(DATE'2001-00-00') | DATE'2001-00-00' |
          +--------------------------+------------------+
          | 0 | NULL |
          +--------------------------+------------------+
          1 row in set, 1 warning (0.00 sec)
          {code}
          serg Sergei Golubchik made changes -
          Resolution Fixed [ 1 ]
          Status Open [ 1 ] Closed [ 6 ]
          serg Sergei Golubchik made changes -
          Workflow defaullt [ 37707 ] MariaDB v2 [ 43415 ]
          ratzpo Rasmus Johansson (Inactive) made changes -
          Workflow MariaDB v2 [ 43415 ] MariaDB v3 [ 63204 ]
          serg Sergei Golubchik made changes -
          Workflow MariaDB v3 [ 63204 ] MariaDB v4 [ 147709 ]

          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.