Details
Critical
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');
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 | +-------+--------+-------+
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.
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
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.
—
Thanks for the report and the test case.
On a debug build, it causes assertion failure:
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:112It was introduced by the following revision in 5.3 tree: