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

IF Statement returns multiple values erroneously (or Assertion `!null_value' failed in Item::send(Protocol*, String*))

Details

    • Bug
    • Status: Closed (View Workflow)
    • Critical
    • Resolution: Fixed
    • 5.5.33a, 5.3.13, 5.5(EOL), 10.0(EOL), 10.1(EOL)
    • 5.5.46, 10.0.22, 10.1.7
    • OTHER
    • None
    • Centos 7

    Description

      Test Case:

      CREATE TABLE `testing` (
      		 
      `datas` VARCHAR(25) NOT NULL
      		 
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
      		 
       
      		 
      INSERT INTO `testing` VALUES ('1,2'), ('2,3'), ('3,4');

      Shows issue -- See last row

      		 
      SELECT IF(FIND_IN_SET('1', `datas`), 1.5, IF(FIND_IN_SET('2', `datas`), 2, NULL)) AS `First`, '1' AS `Second`, '2' AS `Third` FROM `testing`;
      		 
      +-------+--------+-------+
      		 
      | First | Second | Third |
      		 
      +-------+--------+-------+
      		 
      |   1.5 | 1      | 2     |
      		 
      |   2.0 | 1      | 2     |
      		 
      |   0.0 | NULL   | 1     |
      		 
      +-------+--------+-------+
      		 
       
      		 
      SELECT IF(FIND_IN_SET('1', `datas`), 1, IF(FIND_IN_SET('2', `datas`), 2.5, IF(FIND_IN_SET('5', `datas`), 5, NULL))) AS `First`, '1' AS `Second`, '2' AS `Third` FROM `testing`;
      		 
      +-------+--------+-------+
      		 
      | First | Second | Third |
      		 
      +-------+--------+-------+
      		 
      |   1.0 | 1      | 2     |
      		 
      |   2.5 | 1      | 2     |
      		 
      |   0.0 | NULL   | 1     |
      		 
      +-------+--------+-------+

      Works as expected

      		 
      SELECT IF(FIND_IN_SET('1', `datas`), '1.5', IF(FIND_IN_SET('2', `datas`), 2, NULL)) AS `First`, '1' AS `Second`, '2' AS `Third` FROM `testing`;
      		 
      +-------+--------+-------+
      		 
      | First | Second | Third |
      		 
      +-------+--------+-------+
      		 
      | 1.5   | 1      | 2     |
      		 
      | 2     | 1      | 2     |
      		 
      | NULL  | 1      | 2     |
      		 
      +-------+--------+-------+
      		 
       
      		 
      SELECT IF(FIND_IN_SET('1', `datas`), 1, IF(FIND_IN_SET('2', `datas`), 2, NULL)) AS `First`, '1' AS `Second`, '2' AS `Third` FROM `testing`;
      		 
      +-------+--------+-------+
      		 
      | First | Second | Third |
      		 
      +-------+--------+-------+
      		 
      |     1 | 1      | 2     |
      		 
      |     2 | 1      | 2     |
      		 
      |  NULL | 1      | 2     |
      		 
      +-------+--------+-------+

      The issue is that when an IF statement returns a NULL value and there's a decimal value being returned for one of the upstream IF options then the IF is returning a 0.0 value as well as the expected NULL and therefore causing all further values to be shifted to the next column.

      If I change the decimal to a string or an integer, then the expected data is returned as shown in the last two SELECT statements.

      I've tested using MyISAM and InnoDB and it happens to both. I tested and confirmed this happens on MariaDB version "10.0.21-MariaDB-log". This issue does not happen on version "5.5.32-MariaDB-log". It does not happen on 5.6.26 MySQL Community Server (GPL) either.

      Attachments

        Activity

          Ascending order - Click to sort in descending order
          elenst Elena Stepanova added a comment -

          Thanks for the report and the test case.
          On a debug build, it causes assertion failure:

          Stack trace from 5.5 commit fa51f70dc68fe2f3afe943e2c81fcbdb34f16cad

          		 
          5.5/sql/item.cc:6518: virtual bool Item::send(Protocol*, String*): Assertion `!null_value' failed.
          		 
          150822  0:27:07 [ERROR] mysqld got signal 6 ;
          		 
           
          		 
          #6  0x00007fbefd785311 in *__GI___assert_fail (assertion=0xe114ed "!null_value", file=<optimized out>, line=6518, function=0xe13560 "virtual bool Item::send(Protocol*, String*)") at assert.c:81
          		 
          #7  0x000000000080838e in Item::send (this=0x7fbef8287b90, protocol=0x7fbef9150628, buffer=0x7fbef8bb3fc0) at 5.5/sql/item.cc:6518
          		 
          #8  0x000000000059b2ea in Protocol::send_result_set_row (this=0x7fbef9150628, row_items=0x7fbef9153b88) at 5.5/sql/protocol.cc:903
          		 
          #9  0x00000000005fe0aa in select_send::send_data (this=0x7fbef8288538, items=...) at 5.5/sql/sql_class.cc:2374
          		 
          #10 0x0000000000690bca in end_send (join=0x7fbef8288558, join_tab=0x7fbef8169cb8, end_of_records=false) at 5.5/sql/sql_select.cc:18035
          		 
          #11 0x000000000068e9ca in evaluate_join_record (join=0x7fbef8288558, join_tab=0x7fbef8169998, error=0) at 5.5/sql/sql_select.cc:17146
          		 
          #12 0x000000000068e493 in sub_select (join=0x7fbef8288558, join_tab=0x7fbef8169998, end_of_records=false) at 5.5/sql/sql_select.cc:16966
          		 
          #13 0x000000000068db61 in do_select (join=0x7fbef8288558, fields=0x7fbef9153b88, table=0x0, procedure=0x0) at 5.5/sql/sql_select.cc:16589
          		 
          #14 0x000000000066c4fc in JOIN::exec (this=0x7fbef8288558) at 5.5/sql/sql_select.cc:2871
          		 
          #15 0x000000000066cd07 in mysql_select (thd=0x7fbef9150060, rref_pointer_array=0x7fbef9153ce0, tables=0x7fbef8287eb0, wild_num=0, fields=..., conds=0x0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748608, result=0x7fbef8288538, unit=0x7fbef9153390, select_lex=0x7fbef9153a70) at 5.5/sql/sql_select.cc:3092
          		 
          #16 0x0000000000663715 in handle_select (thd=0x7fbef9150060, lex=0x7fbef91532e0, result=0x7fbef8288538, setup_tables_done_option=0) at 5.5/sql/sql_select.cc:319
          		 
          #17 0x000000000063c994 in execute_sqlcom_select (thd=0x7fbef9150060, all_tables=0x7fbef8287eb0) at 5.5/sql/sql_parse.cc:4689
          		 
          #18 0x0000000000635b76 in mysql_execute_command (thd=0x7fbef9150060) at 5.5/sql/sql_parse.cc:2234
          		 
          #19 0x000000000063f485 in mysql_parse (thd=0x7fbef9150060, rawbuf=0x7fbef8287078 "SELECT IF(FIND_IN_SET('1', `datas`), 1.5, IF(FIND_IN_SET('2', `datas`), 2, NULL)) AS `First`, '1' AS `Second`, '2' AS `Third` FROM `testing`", length=140, parser_state=0x7fbef8bb5620) at 5.5/sql/sql_parse.cc:5911
          		 
          #20 0x00000000006330bd in dispatch_command (command=COM_QUERY, thd=0x7fbef9150060, packet=0x7fbef9207061 "", packet_length=140) at 5.5/sql/sql_parse.cc:1079
          		 
          #21 0x0000000000632249 in do_command (thd=0x7fbef9150060) at 5.5/sql/sql_parse.cc:793
          		 
          #22 0x000000000073536d in do_handle_one_connection (thd_arg=0x7fbef9150060) at 5.5/sql/sql_connect.cc:1269
          		 
          #23 0x00000000007350e7 in handle_one_connection (arg=0x7fbef9150060) at 5.5/sql/sql_connect.cc:1185
          		 
          #24 0x0000000000b6fcd1 in pfs_spawn_thread (arg=0x7fbef9171c00) at 5.5/storage/perfschema/pfs.cc:1015
          		 
          #25 0x00007fbeff57fb50 in start_thread (arg=<optimized out>) at pthread_create.c:304
          		 
          #26 0x00007fbefd83595d in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:112

          It was introduced by the following revision in 5.3 tree:

          revno: 3689
          		 
          revision-id: bar@mnogosearch.org-20130909113225-k56ivrazr3wq1up4
          		 
          parent: igor@askmonty.org-20130906165532-5v2ybq2t8f0o9pke
          		 
          committer: Alexander Barkov <bar@mnogosearch.org>
          		 
          branch nick: maria-5.3.b4863
          		 
          timestamp: Mon 2013-09-09 15:32:25 +0400
          		 
          message:
          		 
            MDEV-4863 COALESCE(time_or_datetime) returns wrong results in numeric context

          elenst Elena Stepanova added a comment - Thanks for the report and the test case. On a debug build, it causes assertion failure: Stack trace from 5.5 commit fa51f70dc68fe2f3afe943e2c81fcbdb34f16cad 5.5/sql/item.cc:6518: virtual bool Item::send(Protocol*, String*): Assertion `!null_value' failed. 150822 0:27:07 [ERROR] mysqld got signal 6 ;   #6 0x00007fbefd785311 in *__GI___assert_fail (assertion=0xe114ed "!null_value", file=<optimized out>, line=6518, function=0xe13560 "virtual bool Item::send(Protocol*, String*)") at assert.c:81 #7 0x000000000080838e in Item::send (this=0x7fbef8287b90, protocol=0x7fbef9150628, buffer=0x7fbef8bb3fc0) at 5.5/sql/item.cc:6518 #8 0x000000000059b2ea in Protocol::send_result_set_row (this=0x7fbef9150628, row_items=0x7fbef9153b88) at 5.5/sql/protocol.cc:903 #9 0x00000000005fe0aa in select_send::send_data (this=0x7fbef8288538, items=...) at 5.5/sql/sql_class.cc:2374 #10 0x0000000000690bca in end_send (join=0x7fbef8288558, join_tab=0x7fbef8169cb8, end_of_records=false) at 5.5/sql/sql_select.cc:18035 #11 0x000000000068e9ca in evaluate_join_record (join=0x7fbef8288558, join_tab=0x7fbef8169998, error=0) at 5.5/sql/sql_select.cc:17146 #12 0x000000000068e493 in sub_select (join=0x7fbef8288558, join_tab=0x7fbef8169998, end_of_records=false) at 5.5/sql/sql_select.cc:16966 #13 0x000000000068db61 in do_select (join=0x7fbef8288558, fields=0x7fbef9153b88, table=0x0, procedure=0x0) at 5.5/sql/sql_select.cc:16589 #14 0x000000000066c4fc in JOIN::exec (this=0x7fbef8288558) at 5.5/sql/sql_select.cc:2871 #15 0x000000000066cd07 in mysql_select (thd=0x7fbef9150060, rref_pointer_array=0x7fbef9153ce0, tables=0x7fbef8287eb0, wild_num=0, fields=..., conds=0x0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748608, result=0x7fbef8288538, unit=0x7fbef9153390, select_lex=0x7fbef9153a70) at 5.5/sql/sql_select.cc:3092 #16 0x0000000000663715 in handle_select (thd=0x7fbef9150060, lex=0x7fbef91532e0, result=0x7fbef8288538, setup_tables_done_option=0) at 5.5/sql/sql_select.cc:319 #17 0x000000000063c994 in execute_sqlcom_select (thd=0x7fbef9150060, all_tables=0x7fbef8287eb0) at 5.5/sql/sql_parse.cc:4689 #18 0x0000000000635b76 in mysql_execute_command (thd=0x7fbef9150060) at 5.5/sql/sql_parse.cc:2234 #19 0x000000000063f485 in mysql_parse (thd=0x7fbef9150060, rawbuf=0x7fbef8287078 "SELECT IF(FIND_IN_SET('1', `datas`), 1.5, IF(FIND_IN_SET('2', `datas`), 2, NULL)) AS `First`, '1' AS `Second`, '2' AS `Third` FROM `testing`", length=140, parser_state=0x7fbef8bb5620) at 5.5/sql/sql_parse.cc:5911 #20 0x00000000006330bd in dispatch_command (command=COM_QUERY, thd=0x7fbef9150060, packet=0x7fbef9207061 "", packet_length=140) at 5.5/sql/sql_parse.cc:1079 #21 0x0000000000632249 in do_command (thd=0x7fbef9150060) at 5.5/sql/sql_parse.cc:793 #22 0x000000000073536d in do_handle_one_connection (thd_arg=0x7fbef9150060) at 5.5/sql/sql_connect.cc:1269 #23 0x00000000007350e7 in handle_one_connection (arg=0x7fbef9150060) at 5.5/sql/sql_connect.cc:1185 #24 0x0000000000b6fcd1 in pfs_spawn_thread (arg=0x7fbef9171c00) at 5.5/storage/perfschema/pfs.cc:1015 #25 0x00007fbeff57fb50 in start_thread (arg=<optimized out>) at pthread_create.c:304 #26 0x00007fbefd83595d in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:112 It was introduced by the following revision in 5.3 tree: revno: 3689 revision-id: bar@mnogosearch.org-20130909113225-k56ivrazr3wq1up4 parent: igor@askmonty.org-20130906165532-5v2ybq2t8f0o9pke committer: Alexander Barkov <bar@mnogosearch.org> branch nick: maria-5.3.b4863 timestamp: Mon 2013-09-09 15:32:25 +0400 message: MDEV-4863 COALESCE(time_or_datetime) returns wrong results in numeric context
          sanja Oleksandr Byelkin added a comment -

          revision-id: 62fe87c5a239c7304b245ce5954c87aecb6382ad (mariadb-5.5.45-1-g62fe87c)
          parent(s): fa51f70dc68fe2f3afe943e2c81fcbdb34f16cad
          committer: Oleksandr Byelkin
          timestamp: 2015-09-03 18:00:43 +0200
          message:

          MDEV-8663: IF Statement returns multiple values erroneously (or Assertion `!null_value' failed in Item::send(Protocol*, String*))

          keeping contract: NULL value mean NULL pointer in val_str and val_deciman.

          —

          sanja Oleksandr Byelkin added a comment - revision-id: 62fe87c5a239c7304b245ce5954c87aecb6382ad (mariadb-5.5.45-1-g62fe87c) parent(s): fa51f70dc68fe2f3afe943e2c81fcbdb34f16cad committer: Oleksandr Byelkin timestamp: 2015-09-03 18:00:43 +0200 message: MDEV-8663 : IF Statement returns multiple values erroneously (or Assertion `!null_value' failed in Item::send(Protocol*, String*)) keeping contract: NULL value mean NULL pointer in val_str and val_deciman. —

          People

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