[MDEV-10461] Multiple left join, group by query returns wrong result type Created: 2016-07-28  Updated: 2016-08-29  Resolved: 2016-08-24

Status: Closed
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 10.0.26, 5.5, 10.0, 10.1
Fix Version/s: 5.5.51, 10.1.17, 10.0.27, 10.2.2

Type: Bug Priority: Major
Reporter: Crystal Lemire Assignee: Alexander Barkov
Resolution: Fixed Votes: 0
Labels: None
Environment:

ubuntu


Attachments: File data.sql     File min-query.sql    
Issue Links:
Duplicate
duplicates MDEV-10500 CASE/IF Statement returns multiple va... Closed

 Description   

The attached query run on the attached data file produces the following results:

*************************** 1. row ***************************
                     id: NULL
                      a: 0.0000
                      b: NULL
                      c: NULL
                      d: 0.00000
                      e: NULL
this_should_be_a_bigint: 2.3456
      what_it_should_be: 1
                      f: NULL
*************************** 2. row ***************************
                     id: NULL
                      a: 0.0000
                      b: NULL
                      c: NULL
                      d: 0.00000
                      e: NULL
this_should_be_a_bigint: 2.3456
      what_it_should_be: 1
                      f: NULL
*************************** 3. row ***************************
                     id: NULL
                      a: 0.0000
                      b: NULL
                      c: NULL
                      d: 0.00000
                      e: NULL
this_should_be_a_bigint: 2.3456
      what_it_should_be: 1
                      f: NULL
3 rows in set (0.00 sec)

Notice that the result column this_should_be_a_bigint returns a floating point number. This is wrong. The 2.3456 value suspiciously appears in an earlier column calculation in the query.



 Comments   
Comment by Elena Stepanova [ 2016-07-30 ]

Thanks for the report.
The problem started appearing in 5.5 tree after this revision:

commit 8b5da9f73b4af8e5a11fdddc402da822d3df8d93
Merge: 42f5655 c2b3852
Author: Alexander Barkov <bar@mnogosearch.org>
Date:   Tue Sep 10 10:08:11 2013 +0400
 
    Merge from 5.3
    
    pending merges:
      Alexander Barkov 2013-09-09 MDEV-4863 COALESCE(time_or_datetime) returns...

Debug version produces assertion failure

Stack trace from 5.5 commit 1b5da2ca49f69605ccfe4d98e9207e7b8551e21f

mysqld: /data/src/5.5/sql/item_func.h:435: my_decimal* Item_func_hybrid_result_type::decimal_op_with_null_check(my_decimal*): Assertion `(res != __null) ^ null_value' failed.
160730  3:25:44 [ERROR] mysqld got signal 6 ;
 
#7  0x00007fb7e33f8312 in __GI___assert_fail (assertion=0xdb4e80 "(res != __null) ^ null_value", file=0xdb4e39 "/data/src/5.5/sql/item_func.h", line=435, function=0xdb6300 <Item_func_hybrid_result_type::decimal_op_with_null_check(my_decimal*)::__PRETTY_FUNCTION__> "my_decimal* Item_func_hybrid_result_type::decimal_op_with_null_check(my_decimal*)") at assert.c:101
#8  0x0000000000822ae8 in Item_func_hybrid_result_type::decimal_op_with_null_check (this=0x7fb7ddd80860, decimal_buffer=0x7fb7e544ed70) at /data/src/5.5/sql/item_func.h:435
#9  0x000000000080d392 in Item_func_hybrid_result_type::val_str (this=0x7fb7ddd80860, str=0x7fb7e544eec0) at /data/src/5.5/sql/item_func.cc:890
#10 0x00000000007cf7a7 in Item::send (this=0x7fb7ddd80860, protocol=0x7fb7dee74628, buffer=0x7fb7e544eec0) at /data/src/5.5/sql/item.cc:6540
#11 0x0000000000568df0 in Protocol::send_result_set_row (this=0x7fb7dee74628, row_items=0x7fb7ddd45b20) at /data/src/5.5/sql/protocol.cc:903
#12 0x00000000005cb43a in select_send::send_data (this=0x7fb7ddd45730, items=...) at /data/src/5.5/sql/sql_class.cc:2374
#13 0x000000000065e419 in end_send_group (join=0x7fb7ddd45750, join_tab=0x7fb7dde2a278, end_of_records=false) at /data/src/5.5/sql/sql_select.cc:18186
#14 0x000000000065badf in evaluate_join_record (join=0x7fb7ddd45750, join_tab=0x7fb7dde29f58, error=0) at /data/src/5.5/sql/sql_select.cc:17169
#15 0x000000000065b441 in sub_select (join=0x7fb7ddd45750, join_tab=0x7fb7dde29f58, end_of_records=false) at /data/src/5.5/sql/sql_select.cc:16950
#16 0x000000000065badf in evaluate_join_record (join=0x7fb7ddd45750, join_tab=0x7fb7dde29c38, error=0) at /data/src/5.5/sql/sql_select.cc:17169
#17 0x000000000065b441 in sub_select (join=0x7fb7ddd45750, join_tab=0x7fb7dde29c38, end_of_records=false) at /data/src/5.5/sql/sql_select.cc:16950
#18 0x000000000065badf in evaluate_join_record (join=0x7fb7ddd45750, join_tab=0x7fb7dde29918, error=0) at /data/src/5.5/sql/sql_select.cc:17169
#19 0x000000000065b5d3 in sub_select (join=0x7fb7ddd45750, join_tab=0x7fb7dde29918, end_of_records=false) at /data/src/5.5/sql/sql_select.cc:16989
#20 0x000000000065acad in do_select (join=0x7fb7ddd45750, fields=0x7fb7ddd45b20, table=0x0, procedure=0x0) at /data/src/5.5/sql/sql_select.cc:16612
#21 0x000000000063951f in JOIN::exec (this=0x7fb7ddd45750) at /data/src/5.5/sql/sql_select.cc:2873
#22 0x0000000000639cea in mysql_select (thd=0x7fb7dee74060, rref_pointer_array=0x7fb7dee77ce0, tables=0x7fb7dde104c0, wild_num=0, fields=..., conds=0x0, og_num=2, order=0x0, group=0x7fb7ddd45480, having=0x0, proc_param=0x0, select_options=2147748608, result=0x7fb7ddd45730, unit=0x7fb7dee77390, select_lex=0x7fb7dee77a70) at /data/src/5.5/sql/sql_select.cc:3094
#23 0x00000000006305d0 in handle_select (thd=0x7fb7dee74060, lex=0x7fb7dee772e0, result=0x7fb7ddd45730, setup_tables_done_option=0) at /data/src/5.5/sql/sql_select.cc:319
#24 0x0000000000609ac7 in execute_sqlcom_select (thd=0x7fb7dee74060, all_tables=0x7fb7dde104c0) at /data/src/5.5/sql/sql_parse.cc:4689
#25 0x0000000000602e18 in mysql_execute_command (thd=0x7fb7dee74060) at /data/src/5.5/sql/sql_parse.cc:2234
#26 0x000000000060c692 in mysql_parse (thd=0x7fb7dee74060, rawbuf=0x7fb7ddd48078 "SELECT CONCAT(a.id, CONCAT(\".\", e.Id)) AS id,\nCASE\nWHEN a.a = 'foo' AND a.g = 'bar'  THEN SUM(b2.c - b1.c)\nWHEN a.a = 'foo' AND a.g <> 'bar' THEN h1.a - h2.a\nELSE 1.3579\nEND AS a,\nCASE\nWHEN a.a = 'foo"..., length=1204, parser_state=0x7fb7e5450650) at /data/src/5.5/sql/sql_parse.cc:5934
#27 0x00000000006003a7 in dispatch_command (command=COM_QUERY, thd=0x7fb7dee74060, packet=0x7fb7ddd2f061 "", packet_length=1205) at /data/src/5.5/sql/sql_parse.cc:1079
#28 0x00000000005ff561 in do_command (thd=0x7fb7dee74060) at /data/src/5.5/sql/sql_parse.cc:793
#29 0x00000000007016df in do_handle_one_connection (thd_arg=0x7fb7dee74060) at /data/src/5.5/sql/sql_connect.cc:1270
#30 0x000000000070146c in handle_one_connection (arg=0x7fb7dee74060) at /data/src/5.5/sql/sql_connect.cc:1186
#31 0x00000000009435ab in pfs_spawn_thread (arg=0x7fb7dfb7a300) at /data/src/5.5/storage/perfschema/pfs.cc:1015
#32 0x00007fb7e508c0a4 in start_thread (arg=0x7fb7e5451700) at pthread_create.c:309
#33 0x00007fb7e34b287d in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:111

Comment by Elena Stepanova [ 2016-08-24 ]

Fixed in scope of MDEV-10500.

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