[MDEV-8663] IF Statement returns multiple values erroneously (or Assertion `!null_value' failed in Item::send(Protocol*, String*)) Created: 2015-08-21  Updated: 2015-09-04  Resolved: 2015-09-04

Status: Closed
Project: MariaDB Server
Component/s: OTHER
Affects Version/s: 5.5.33a, 5.3.13, 5.5, 10.0, 10.1
Fix Version/s: 5.5.46, 10.0.22, 10.1.7

Type: Bug Priority: Critical
Reporter: Kyle Misner Assignee: Alexander Barkov
Resolution: Fixed Votes: 0
Labels: None
Environment:

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.

 Comments   
Comment by Elena Stepanova [ 2015-08-22 ]

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

Comment by Oleksandr Byelkin [ 2015-09-03 ]

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.

—

Generated at Thu Feb 08 07:28:51 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.