[MDEV-12993] LEFT JOIN computes forever - possibly when no or few results in right side of join Created: 2017-06-05  Updated: 2017-07-03  Resolved: 2017-07-03

Status: Closed
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 5.5, 10.0, 10.1, 10.0.30, 10.1.21, 10.2
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Mario DE WEERD Assignee: Unassigned
Resolution: Won't Fix Votes: 0
Labels: None
Environment:

Debian8, Windows 10 (XAMPP)

Attachments: File y_metrics.7z    

 Description   

A query with joins on the same table works for "most" queries, but there are random cases where the query is in "sending data" state forever.

Luckilly these random cases are deterministic enough to provide a test case.
A table dump is provided in attachment. It was generated on debian, and the issue was verified on windows.
The examples below work for the table provided in the attachment (the table must be imported in a database).

In practice, the number of rows expected is determined first with a "COUNT" statement, and then the results are retrieved using the full select statement.
The issue also occurred with the "COUNT" statements. To work around it, the left joins are removed for counting which makes the SQL command work.

Just below is the SQL statement that counts that there are 637 results to be expected from the failing query which is shown just below the COUNT query.

SELECT `t`.`timems` 
FROM `y_metrics` `t` USE INDEX(`entity_time`) 
 WHERE (`t`.`timems`>=1496527200000 AND `t`.`timems`<=1496613599000) AND (`t`.`entity_id`=60) 
GROUP BY `t`.`timems`

The query just further below fails, "mysqladmin proc" or "SHOW PROCESSLIST;" shows query in "sending data" state forever:
---------- PROCESSLIST --------

19054 root localhost avy_iot Query 5 Sending data SELECT `t`.`timems`, 566 as Cl_id, 568 as rawCl_id, 570 as pH_id, 571 as Temperature_id, 572 as f_id 0.000

---------- QUERY --------------

 SELECT `t`.`timems`, 566 as Cl_id, 568 as rawCl_id, 570 as pH_id, 571 as Temperature_id, 572 as f_id, `s566Cl`.`metric_val` as Cl, `s568rawCl`.`metric_val` as rawCl, `s570pH`.`metric_val` as pH, `s571Temperature`.`metric_val` as Temperature, 
`s572f`.`metric_val` as f 
FROM `y_metrics` `t` USE INDEX(`entity_time`) 
LEFT OUTER JOIN `y_metrics` s566Cl USE INDEX(time_series) ON `t`.`timems`=`s566Cl`.`timems`AND (`s566Cl`.`series_id`=566) 
LEFT OUTER JOIN `y_metrics` s568rawCl USE INDEX(time_series) ON `t`.`timems`=`s568rawCl`.`timems`AND (`s568rawCl`.`series_id`=568) 
LEFT OUTER JOIN `y_metrics` s570pH USE INDEX(time_series) ON `t`.`timems`=`s570pH`.`timems`AND (`s570pH`.`series_id`=570)
 
LEFT OUTER JOIN `y_metrics` s571Temperature USE INDEX(time_series) ON `t`.`timems`=`s571Temperature`.`timems`AND (`s571Temperature`.`series_id`=571) 
LEFT OUTER JOIN `y_metrics` s572f USE INDEX(time_series) ON `t`.`timems`=`s572f`.`timems`AND (`s572f`.`series_id`=572)
 
 WHERE (`t`.`timems`>=1496527200000 AND `t`.`timems`<=1496613599000) AND (`t`.`entity_id`=60) 
GROUP BY `t`.`timems` LIMIT 637

Killing the query and requesting just a few results works for the subset:
-------- LIMIT 5 -------------

SELECT `t`.`timems`, 566 as Cl_id, 568 as rawCl_id, 570 as pH_id, 571 as Temperature_id, 572 as f_id, `s566Cl`.`metric_val` as Cl, `s568rawCl`.`metric_val` as rawCl, `s570pH`.`metric_val` as pH, `s571Temperature`.`metric_val` as Temperature, 
`s572f`.`metric_val` as f 
FROM `y_metrics` `t` USE INDEX(`entity_time`) 
LEFT OUTER JOIN `y_metrics` s566Cl USE INDEX(time_series) ON `t`.`timems`=`s566Cl`.`timems`AND (`s566Cl`.`series_id`=566) 
LEFT OUTER JOIN `y_metrics` s568rawCl USE INDEX(time_series) ON `t`.`timems`=`s568rawCl`.`timems`AND (`s568rawCl`.`series_id`=568) 
LEFT OUTER JOIN `y_metrics` s570pH USE INDEX(time_series) ON `t`.`timems`=`s570pH`.`timems`AND (`s570pH`.`series_id`=570)
 
LEFT OUTER JOIN `y_metrics` s571Temperature USE INDEX(time_series) ON `t`.`timems`=`s571Temperature`.`timems`AND (`s571Temperature`.`series_id`=571) 
LEFT OUTER JOIN `y_metrics` s572f USE INDEX(time_series) ON `t`.`timems`=`s572f`.`timems`AND (`s572f`.`series_id`=572) 
WHERE (`t`.`timems`>=1496527200000 AND `t`.`timems`<=1496613599000) AND (`t`.`entity_id`=60)
 
 GROUP BY `t`.`timems` LIMIT 5

Example to show that query logic is ok (this example gives correct results):

-------- determine count — returns 985 -----

select count(*) from (SELECT `t`.`timems` from y_metrics `t`
 
 WHERE (`t`.`timems`>=1496527200000 AND `t`.`timems`<=1496613599000) AND (`t`.`entity_id`=47) 
GROUP BY `t`.`timems`) s

-------- get data — with limit 985 – (when using limit 1000 also returns 985 rows) -------------------------

SELECT `t`.`timems`, 505 as Temperature_id, 506 as pH_id, 507 as Cl_id, 508 as rawCl_id, 509 as f_id, `s505Temperature`.`metric_val` as Temperature, `s506pH`.`metric_val` as pH, `s507Cl`.`metric_val` as Cl, `s508rawCl`.`metric_val` as rawCl, `s509f`.`metric_val` as f FROM `y_metrics` `t` USE INDEX(`entity_time`) LEFT OUTER JOIN `y_metrics` s505Temperature USE INDEX(time_series) ON `t`.`timems`=`s505Temperature`.`timems`AND (`s505Temperature`.`series_id`=505) LEFT OUTER JOIN `y_metrics` s506pH USE INDEX(time_series) ON `t`.`timems`=`s506pH`.`timems`AND (`s506pH`.`series_id`=506) LEFT OUTER JOIN `y_metrics` s507Cl USE INDEX(time_series) ON `t`.`timems`=`s507Cl`.`timems`AND (`s507Cl`.`series_id`=507) LEFT OUTER JOIN `y_metrics` s508rawCl USE INDEX(time_series) ON `t`.`timems`=`s508rawCl`.`timems`AND (`s508rawCl`.`series_id`=508) LEFT OUTER JOIN `y_metrics` s509f USE INDEX(time_series) ON `t`.`timems`=`s509f`.`timems`AND (`s509f`.`series_id`=509) 
WHERE (`t`.`timems`>=1496527200000 AND `t`.`timems`<=1496613599000) AND (`t`.`entity_id`=47) 
GROUP BY `t`.`timems` LIMIT 985



 Comments   
Comment by Elena Stepanova [ 2017-06-06 ]

Thanks for the report and test case, reproducible as described on all of 5.5-10.2 and MySQL 5.7 (for the latter, ONLY_FULL_GROUP_BY needs to be removed from sql_mode).

More precisely, on the provided data, on my machine, with the default server configuration, on any server, the difference comes between LIMIT 623 and LIMIT 624.

SELECT `t`.`timems`, 566 as Cl_id, 568 as rawCl_id, 570 as pH_id, 571 as Temperature_id, 572 as f_id, `s566Cl`.`metric_val` as Cl, `s568rawCl`.`metric_val` as rawCl, `s570pH`.`metric_val` as pH, `s571Temperature`.`metric_val` as Temperature,  `s572f`.`metric_val` as f  FROM `y_metrics` `t` USE INDEX(`entity_time`)  LEFT OUTER JOIN `y_metrics` s566Cl USE INDEX(time_series) ON `t`.`timems`=`s566Cl`.`timems`AND (`s566Cl`.`series_id`=566)  LEFT OUTER JOIN `y_metrics` s568rawCl USE INDEX(time_series) ON `t`.`timems`=`s568rawCl`.`timems`AND (`s568rawCl`.`series_id`=568)  LEFT OUTER JOIN `y_metrics` s570pH USE INDEX(time_series) ON `t`.`timems`=`s570pH`.`timems`AND (`s570pH`.`series_id`=570) LEFT OUTER JOIN `y_metrics` s571Temperature USE INDEX(time_series) ON `t`.`timems`=`s571Temperature`.`timems`AND (`s571Temperature`.`series_id`=571)  LEFT OUTER JOIN `y_metrics` s572f USE INDEX(time_series) ON `t`.`timems`=`s572f`.`timems`AND (`s572f`.`series_id`=572)  WHERE (`t`.`timems`>=1496527200000 AND `t`.`timems`<=1496613599000) AND (`t`.`entity_id`=60)  GROUP BY `t`.`timems` LIMIT 623;
 
...
 
+---------------+-------+----------+-------+----------------+------+-------------+---------------+-------------+--------------+--------------+
 
623 rows in set (0.22 sec)


SELECT `t`.`timems`, 566 as Cl_id, 568 as rawCl_id, 570 as pH_id, 571 as Temperature_id, 572 as f_id, `s566Cl`.`metric_val` as Cl, `s568rawCl`.`metric_val` as rawCl, `s570pH`.`metric_val` as pH, `s571Temperature`.`metric_val` as Temperature,  `s572f`.`metric_val` as f  FROM `y_metrics` `t` USE INDEX(`entity_time`)  LEFT OUTER JOIN `y_metrics` s566Cl USE INDEX(time_series) ON `t`.`timems`=`s566Cl`.`timems`AND (`s566Cl`.`series_id`=566)  LEFT OUTER JOIN `y_metrics` s568rawCl USE INDEX(time_series) ON `t`.`timems`=`s568rawCl`.`timems`AND (`s568rawCl`.`series_id`=568)  LEFT OUTER JOIN `y_metrics` s570pH USE INDEX(time_series) ON `t`.`timems`=`s570pH`.`timems`AND (`s570pH`.`series_id`=570) LEFT OUTER JOIN `y_metrics` s571Temperature USE INDEX(time_series) ON `t`.`timems`=`s571Temperature`.`timems`AND (`s571Temperature`.`series_id`=571)  LEFT OUTER JOIN `y_metrics` s572f USE INDEX(time_series) ON `t`.`timems`=`s572f`.`timems`AND (`s572f`.`series_id`=572)  WHERE (`t`.`timems`>=1496527200000 AND `t`.`timems`<=1496613599000) AND (`t`.`entity_id`=60)  GROUP BY `t`.`timems` LIMIT 624;
 
...
 
# does not end in any reasonable time

The plan is seemingly the same, both in standard and JSON format. 

| {
 
  "query_block": {
 
    "select_id": 1,
 
    "table": {
 
      "table_name": "t",
 
      "access_type": "range",
 
      "possible_keys": ["entity_time"],
 
      "key": "entity_time",
 
      "key_length": "13",
 
      "used_key_parts": ["entity_id", "timems"],
 
      "rows": 2182,
 
      "filtered": 75.023,
 
      "attached_condition": "((t.entity_id = 60) and (t.timems >= 1496527200000) and (t.timems <= 1496613599000))",
 
      "using_index": true
 
    },
 
    "table": {
 
      "table_name": "s566Cl",
 
      "access_type": "ref",
 
      "possible_keys": ["time_series"],
 
      "key": "time_series",
 
      "key_length": "13",
 
      "used_key_parts": ["timems", "series_id"],
 
      "ref": ["test.t.timems", "const"],
 
      "rows": 1,
 
      "filtered": 100
 
    },
 
    "table": {
 
      "table_name": "s568rawCl",
 
      "access_type": "ref",
 
      "possible_keys": ["time_series"],
 
      "key": "time_series",
 
      "key_length": "13",
 
      "used_key_parts": ["timems", "series_id"],
 
      "ref": ["test.t.timems", "const"],
 
      "rows": 1,
 
      "filtered": 100
 
    },
 
    "table": {
 
      "table_name": "s570pH",
 
      "access_type": "ref",
 
      "possible_keys": ["time_series"],
 
      "key": "time_series",
 
      "key_length": "13",
 
      "used_key_parts": ["timems", "series_id"],
 
      "ref": ["test.t.timems", "const"],
 
      "rows": 1,
 
      "filtered": 100
 
    },
 
    "table": {
 
      "table_name": "s571Temperature",
 
      "access_type": "ref",
 
      "possible_keys": ["time_series"],
 
      "key": "time_series",
 
      "key_length": "13",
 
      "used_key_parts": ["timems", "series_id"],
 
      "ref": ["test.t.timems", "const"],
 
      "rows": 1,
 
      "filtered": 100
 
    },
 
    "table": {
 
      "table_name": "s572f",
 
      "access_type": "ref",
 
      "possible_keys": ["time_series"],
 
      "key": "time_series",
 
      "key_length": "13",
 
      "used_key_parts": ["timems", "series_id"],
 
      "ref": ["test.t.timems", "const"],
 
      "rows": 1,
 
      "filtered": 100
 
    }
 
  }
 
} |

The server uses 100% CPU.
Two stack traces taken consequently:

10.0 449a88e1c629

 
#0  0x0000000000b94ede in mach_read_from_4 (b=0x4000 <error: Cannot access memory at address 0x4000>) at /data/src/10.0/storage/xtradb/include/mach0data.ic:183
 
#1  0x0000000000b94f4b in mach_read_from_8 (b=0x7fd1afbe4038 "") at /data/src/10.0/storage/xtradb/include/mach0data.ic:306
 
#2  0x0000000000b98d8a in page_get_max_trx_id (page=0x7fd1afbe4000 "\202\233", <incomplete sequence \320>) at /data/src/10.0/storage/xtradb/include/page0page.ic:73
 
#3  0x0000000000b9a1e7 in lock_sec_rec_cons_read_sees (rec=0x7fd1afbe5182 "\200", view=0x7fd19f8140e0) at /data/src/10.0/storage/xtradb/lock/lock0lock.cc:596
 
#4  0x0000000000c71ffe in row_search_for_mysql (buf=0x7fd19f899a88 "", mode=2, prebuilt=0x7fd19f9be078, match_mode=1, direction=1) at /data/src/10.0/storage/xtradb/row/row0sel.cc:4647
 
#5  0x0000000000b4d09b in ha_innobase::general_fetch (this=0x7fd19f87f888, buf=0x7fd19f899a88 "", direction=1, match_mode=1) at /data/src/10.0/storage/xtradb/handler/ha_innodb.cc:9223
 
#6  0x0000000000b4d36a in ha_innobase::index_next_same (this=0x7fd19f87f888, buf=0x7fd19f899a88 "", key=0x7fd19f938be8 "\350\201\vs\\\001", keylen=13) at /data/src/10.0/storage/xtradb/handler/ha_innodb.cc:9302
 
#7  0x000000000083d357 in handler::ha_index_next_same (this=0x7fd19f87f888, buf=0x7fd19f899a88 "", key=0x7fd19f938be8 "\350\201\vs\\\001", keylen=13) at /data/src/10.0/sql/handler.cc:2751
 
#8  0x00000000006ad0e5 in join_read_next_same (info=0x7fd19f937f28) at /data/src/10.0/sql/sql_select.cc:18698
 
#9  0x00000000006ab501 in sub_select (join=0x7fd19f9ba548, join_tab=0x7fd19f937e80, end_of_records=false) at /data/src/10.0/sql/sql_select.cc:17896
 
#10 0x00000000006abb51 in evaluate_join_record (join=0x7fd19f9ba548, join_tab=0x7fd19f937b58, error=0) at /data/src/10.0/sql/sql_select.cc:18097
 
#11 0x00000000006ab5e2 in sub_select (join=0x7fd19f9ba548, join_tab=0x7fd19f937b58, end_of_records=false) at /data/src/10.0/sql/sql_select.cc:17916
 
#12 0x00000000006abb51 in evaluate_join_record (join=0x7fd19f9ba548, join_tab=0x7fd19f937830, error=0) at /data/src/10.0/sql/sql_select.cc:18097
 
#13 0x00000000006ab44e in sub_select (join=0x7fd19f9ba548, join_tab=0x7fd19f937830, end_of_records=false) at /data/src/10.0/sql/sql_select.cc:17877
 
#14 0x00000000006abb51 in evaluate_join_record (join=0x7fd19f9ba548, join_tab=0x7fd19f937508, error=0) at /data/src/10.0/sql/sql_select.cc:18097
 
#15 0x00000000006ab5e2 in sub_select (join=0x7fd19f9ba548, join_tab=0x7fd19f937508, end_of_records=false) at /data/src/10.0/sql/sql_select.cc:17916
 
#16 0x00000000006abb51 in evaluate_join_record (join=0x7fd19f9ba548, join_tab=0x7fd19f9371e0, error=0) at /data/src/10.0/sql/sql_select.cc:18097
 
#17 0x00000000006ab44e in sub_select (join=0x7fd19f9ba548, join_tab=0x7fd19f9371e0, end_of_records=false) at /data/src/10.0/sql/sql_select.cc:17877
 
#18 0x00000000006abb51 in evaluate_join_record (join=0x7fd19f9ba548, join_tab=0x7fd19f936eb8, error=0) at /data/src/10.0/sql/sql_select.cc:18097
 
#19 0x00000000006ab5e2 in sub_select (join=0x7fd19f9ba548, join_tab=0x7fd19f936eb8, end_of_records=false) at /data/src/10.0/sql/sql_select.cc:17916
 
#20 0x00000000006aacc1 in do_select (join=0x7fd19f9ba548, fields=0x7fd19f9ba928, table=0x0, procedure=0x0) at /data/src/10.0/sql/sql_select.cc:17539
 
#21 0x0000000000687d2c in JOIN::exec_inner (this=0x7fd19f9ba548) at /data/src/10.0/sql/sql_select.cc:3089
 
#22 0x0000000000685202 in JOIN::exec (this=0x7fd19f9ba548) at /data/src/10.0/sql/sql_select.cc:2378
 
#23 0x000000000068858a in mysql_select (thd=0x7fd1a73c1070, rref_pointer_array=0x7fd1a73c5388, tables=0x7fd19f81d588, wild_num=0, fields=..., conds=0x7fd19f9b9e78, og_num=1, order=0x0, group=0x7fd19f9ba408, having=0x0, proc_param=0x0, select_options=2147748608, result=0x7fd19f9ba528, unit=0x7fd1a73c4a08, select_lex=0x7fd1a73c50f8) at /data/src/10.0/sql/sql_select.cc:3314
 
#24 0x000000000067e8aa in handle_select (thd=0x7fd1a73c1070, lex=0x7fd1a73c4940, result=0x7fd19f9ba528, setup_tables_done_option=0) at /data/src/10.0/sql/sql_select.cc:373
 
#25 0x000000000065300d in execute_sqlcom_select (thd=0x7fd1a73c1070, all_tables=0x7fd19f81d588) at /data/src/10.0/sql/sql_parse.cc:5303
 
#26 0x000000000064b598 in mysql_execute_command (thd=0x7fd1a73c1070) at /data/src/10.0/sql/sql_parse.cc:2563
 
#27 0x0000000000655c8e in mysql_parse (thd=0x7fd1a73c1070, rawbuf=0x7fd19f81c088 "SELECT `t`.`timems`, 566 as Cl_id, 568 as rawCl_id, 570 as pH_id, 571 as Temperature_id, 572 as f_id, `s566Cl`.`metric_val` as Cl, `s568rawCl`.`metric_val` as rawCl, `s570pH`.`metric_val` as pH, `s571"..., length=1089, parser_state=0x7fd1cba806a0) at /data/src/10.0/sql/sql_parse.cc:6579
 
#28 0x00000000006487ee in dispatch_command (command=COM_QUERY, thd=0x7fd1a73c1070, packet=0x7fd1ae31f071 "2\v3.800000000\r226.000000000\373\373\f18.000000000K", packet_length=1089) at /data/src/10.0/sql/sql_parse.cc:1309
 
#29 0x0000000000647ab1 in do_command (thd=0x7fd1a73c1070) at /data/src/10.0/sql/sql_parse.cc:999
 
#30 0x0000000000766d78 in do_handle_one_connection (thd_arg=0x7fd1a73c1070) at /data/src/10.0/sql/sql_connect.cc:1377
 
#31 0x0000000000766aea in handle_one_connection (arg=0x7fd1a73c1070) at /data/src/10.0/sql/sql_connect.cc:1292
 
#32 0x00007fd1cb6d2494 in start_thread (arg=0x7fd1cba81700) at pthread_create.c:333
 
#33 0x00007fd1c981d93f in clone () from /lib/x86_64-linux-gnu/libc.so.6


#0  0x0000000000d03715 in ut_align_offset (ptr=0x7fd1b356268b, align_no=16384) at /data/src/10.0/storage/xtradb/include/ut0byte.ic:126
 
#1  0x0000000000d07fb8 in page_offset (ptr=0x7fd1b356268b) at /data/src/10.0/storage/xtradb/include/page0page.ic:61
 
#2  0x0000000000d08656 in page_rec_check (rec=0x7fd1b356268b "\200", <incomplete sequence \355\274>) at /data/src/10.0/storage/xtradb/include/page0page.ic:665
 
#3  0x0000000000d082ee in page_rec_is_supremum (rec=0x7fd1b356268b "\200", <incomplete sequence \355\274>) at /data/src/10.0/storage/xtradb/include/page0page.ic:433
 
#4  0x0000000000d0a969 in btr_search_check_guess (cursor=0x7fd19f9be1d0, can_only_compare_to_cursor_rec=0, tuple=0x7fd19f9be5f8, mode=4, mtr=0x7fd1cba7e6b0) at /data/src/10.0/storage/xtradb/btr/btr0sea.cc:853
 
#5  0x0000000000d0ae70 in btr_search_guess_on_hash (index=0x7fd19f8e7878, info=0x7fd19f878190, tuple=0x7fd19f9be5f8, mode=4, latch_mode=1, cursor=0x7fd19f9be1d0, has_search_latch=0, mtr=0x7fd1cba7e6b0) at /data/src/10.0/storage/xtradb/btr/btr0sea.cc:1013
 
#6  0x0000000000cf4d65 in btr_cur_search_to_nth_level (index=0x7fd19f8e7878, level=0, tuple=0x7fd19f9be5f8, mode=4, latch_mode=1, cursor=0x7fd19f9be1d0, has_search_latch=0, file=0xff7100 "/data/src/10.0/storage/xtradb/row/row0sel.cc", line=3066, mtr=0x7fd1cba7e6b0) at /data/src/10.0/storage/xtradb/btr/btr0cur.cc:550
 
#7  0x0000000000c695bf in btr_pcur_open_with_no_init_func (index=0x7fd19f8e7878, tuple=0x7fd19f9be5f8, mode=4, latch_mode=1, cursor=0x7fd19f9be1d0, has_search_latch=0, file=0xff7100 "/data/src/10.0/storage/xtradb/row/row0sel.cc", line=3066, mtr=0x7fd1cba7e6b0) at /data/src/10.0/storage/xtradb/include/btr0pcur.ic:485
 
#8  0x0000000000c6ee15 in row_sel_get_clust_rec_for_mysql (prebuilt=0x7fd19f9be078, sec_index=0x7fd19f8e8478, rec=0x7fd1afbe4f9e "\200", thr=0x7fd19f9be7f0, out_rec=0x7fd1cba7ebc0, offsets=0x7fd1cba7eba0, offset_heap=0x7fd1cba7eba8, mtr=0x7fd1cba7e6b0) at /data/src/10.0/storage/xtradb/row/row0sel.cc:3066
 
#9  0x0000000000c7253f in row_search_for_mysql (buf=0x7fd19f899a88 "", mode=2, prebuilt=0x7fd19f9be078, match_mode=1, direction=1) at /data/src/10.0/storage/xtradb/row/row0sel.cc:4839
 
#10 0x0000000000b4d09b in ha_innobase::general_fetch (this=0x7fd19f87f888, buf=0x7fd19f899a88 "", direction=1, match_mode=1) at /data/src/10.0/storage/xtradb/handler/ha_innodb.cc:9223
 
#11 0x0000000000b4d36a in ha_innobase::index_next_same (this=0x7fd19f87f888, buf=0x7fd19f899a88 "", key=0x7fd19f938be8 "\350\201\vs\\\001", keylen=13) at /data/src/10.0/storage/xtradb/handler/ha_innodb.cc:9302
 
#12 0x000000000083d357 in handler::ha_index_next_same (this=0x7fd19f87f888, buf=0x7fd19f899a88 "", key=0x7fd19f938be8 "\350\201\vs\\\001", keylen=13) at /data/src/10.0/sql/handler.cc:2751
 
#13 0x00000000006ad0e5 in join_read_next_same (info=0x7fd19f937f28) at /data/src/10.0/sql/sql_select.cc:18698
 
#14 0x00000000006ab501 in sub_select (join=0x7fd19f9ba548, join_tab=0x7fd19f937e80, end_of_records=false) at /data/src/10.0/sql/sql_select.cc:17896
 
#15 0x00000000006abb51 in evaluate_join_record (join=0x7fd19f9ba548, join_tab=0x7fd19f937b58, error=0) at /data/src/10.0/sql/sql_select.cc:18097
 
#16 0x00000000006ab5e2 in sub_select (join=0x7fd19f9ba548, join_tab=0x7fd19f937b58, end_of_records=false) at /data/src/10.0/sql/sql_select.cc:17916
 
#17 0x00000000006abb51 in evaluate_join_record (join=0x7fd19f9ba548, join_tab=0x7fd19f937830, error=0) at /data/src/10.0/sql/sql_select.cc:18097
 
#18 0x00000000006ab5e2 in sub_select (join=0x7fd19f9ba548, join_tab=0x7fd19f937830, end_of_records=false) at /data/src/10.0/sql/sql_select.cc:17916
 
#19 0x00000000006abb51 in evaluate_join_record (join=0x7fd19f9ba548, join_tab=0x7fd19f937508, error=0) at /data/src/10.0/sql/sql_select.cc:18097
 
#20 0x00000000006ab5e2 in sub_select (join=0x7fd19f9ba548, join_tab=0x7fd19f937508, end_of_records=false) at /data/src/10.0/sql/sql_select.cc:17916
 
#21 0x00000000006abb51 in evaluate_join_record (join=0x7fd19f9ba548, join_tab=0x7fd19f9371e0, error=0) at /data/src/10.0/sql/sql_select.cc:18097
 
#22 0x00000000006ab44e in sub_select (join=0x7fd19f9ba548, join_tab=0x7fd19f9371e0, end_of_records=false) at /data/src/10.0/sql/sql_select.cc:17877
 
#23 0x00000000006abb51 in evaluate_join_record (join=0x7fd19f9ba548, join_tab=0x7fd19f936eb8, error=0) at /data/src/10.0/sql/sql_select.cc:18097
 
#24 0x00000000006ab5e2 in sub_select (join=0x7fd19f9ba548, join_tab=0x7fd19f936eb8, end_of_records=false) at /data/src/10.0/sql/sql_select.cc:17916
 
#25 0x00000000006aacc1 in do_select (join=0x7fd19f9ba548, fields=0x7fd19f9ba928, table=0x0, procedure=0x0) at /data/src/10.0/sql/sql_select.cc:17539
 
#26 0x0000000000687d2c in JOIN::exec_inner (this=0x7fd19f9ba548) at /data/src/10.0/sql/sql_select.cc:3089
 
#27 0x0000000000685202 in JOIN::exec (this=0x7fd19f9ba548) at /data/src/10.0/sql/sql_select.cc:2378
 
#28 0x000000000068858a in mysql_select (thd=0x7fd1a73c1070, rref_pointer_array=0x7fd1a73c5388, tables=0x7fd19f81d588, wild_num=0, fields=..., conds=0x7fd19f9b9e78, og_num=1, order=0x0, group=0x7fd19f9ba408, having=0x0, proc_param=0x0, select_options=2147748608, result=0x7fd19f9ba528, unit=0x7fd1a73c4a08, select_lex=0x7fd1a73c50f8) at /data/src/10.0/sql/sql_select.cc:3314
 
#29 0x000000000067e8aa in handle_select (thd=0x7fd1a73c1070, lex=0x7fd1a73c4940, result=0x7fd19f9ba528, setup_tables_done_option=0) at /data/src/10.0/sql/sql_select.cc:373
 
#30 0x000000000065300d in execute_sqlcom_select (thd=0x7fd1a73c1070, all_tables=0x7fd19f81d588) at /data/src/10.0/sql/sql_parse.cc:5303
 
#31 0x000000000064b598 in mysql_execute_command (thd=0x7fd1a73c1070) at /data/src/10.0/sql/sql_parse.cc:2563
 
#32 0x0000000000655c8e in mysql_parse (thd=0x7fd1a73c1070, rawbuf=0x7fd19f81c088 "SELECT `t`.`timems`, 566 as Cl_id, 568 as rawCl_id, 570 as pH_id, 571 as Temperature_id, 572 as f_id, `s566Cl`.`metric_val` as Cl, `s568rawCl`.`metric_val` as rawCl, `s570pH`.`metric_val` as pH, `s571"..., length=1089, parser_state=0x7fd1cba806a0) at /data/src/10.0/sql/sql_parse.cc:6579
 
#33 0x00000000006487ee in dispatch_command (command=COM_QUERY, thd=0x7fd1a73c1070, packet=0x7fd1ae31f071 "2\v3.800000000\r226.000000000\373\373\f18.000000000K", packet_length=1089) at /data/src/10.0/sql/sql_parse.cc:1309
 
#34 0x0000000000647ab1 in do_command (thd=0x7fd1a73c1070) at /data/src/10.0/sql/sql_parse.cc:999
 
#35 0x0000000000766d78 in do_handle_one_connection (thd_arg=0x7fd1a73c1070) at /data/src/10.0/sql/sql_connect.cc:1377
 
#36 0x0000000000766aea in handle_one_connection (arg=0x7fd1a73c1070) at /data/src/10.0/sql/sql_connect.cc:1292
 
#37 0x00007fd1cb6d2494 in start_thread (arg=0x7fd1cba81700) at pthread_create.c:333
 
#38 0x00007fd1c981d93f in clone () from /lib/x86_64-linux-gnu/libc.so.6

Comment by Igor Babaev [ 2017-06-30 ]

Mario,
Elena,
Here's the explanation why it works so:

MariaDB [test]> SELECT timems FROM y_metrics USE INDEX (entity_time) WHERE (timems >=1496527200000 AND timems<=1496613599000) AND (entity_id=60) GROUP BY timems LIMIT 1 OFFSET 623;  
+---------------+
 
| timems        |
 
+---------------+
 
| 1496578753000 |
 
+---------------+
 
1 row in set (0.01 sec)
 
 
 
MariaDB [test]> select count(*) from y_metrics where timems=1496578753000 and series_id=566;  
+----------+
 
| count(*) |
 
+----------+
 
|      104 |
 
+----------+
 
1 row in set (0.00 sec)
 
 
 
MariaDB [test]> select count(*) from y_metrics where timems=1496578753000 and series_id=568;
 
+----------+
 
| count(*) |
 
+----------+
 
|      104 |
 
+----------+
 
1 row in set (0.00 sec)
 
MariaDB [test]> select count(*) from y_metrics where timems=1496578753000 and series_id=570;
 
+----------+
 
| count(*) |
 
+----------+
 
|        3 |
 
+----------+
 
1 row in set (0.01 sec)
 
 
 
MariaDB [test]> select count(*) from y_metrics where timems=1496578753000 and series_id=571;
 
+----------+
 
| count(*) |
 
+----------+
 
|      105 |
 
+----------+
 
1 row in set (0.01 sec)
 
 
 
MariaDB [test]> select count(*) from y_metrics where timems=1496578753000 and series_id=572;
 
+----------+
 
| count(*) |
 
+----------+
 
|      104 |
 
+----------+
 
1 row in set (0.01 sec)
 
 
 
MariaDB [test]> select 104*104*3*105*104;
 
+-------------------+
 
| 104*104*3*105*104 |
 
+-------------------+
 
|         354332160 |
 
+-------------------+
 
1 row in set (0.00 sec)

The problem for the optimizer here is quite an uneven distribution of the index time_series.
Statistics cannot help here because LIMIT for a different index is used.
I think that the case should be closed.

Comment by Elena Stepanova [ 2017-07-02 ]

le_top, are you okay with the explanation above?

Comment by Mario DE WEERD [ 2017-07-02 ]

Thank you for looking into this, and identifying why the query runs "indefinitively".

There are two parts in this answer:
1) Why I expected this query to work;
2) Confirmation that the explication for how it works now seems exact.

1)Why I expected this query to work
************************************
The rational that made me expect the query to be possible was that I expected the server to:

a) build list of 'timems' values corresponding to the WHERE conditions;
b) for each outer join "individually", select just one of the values (due to "group by") where 'timems' and 'series_id' is valid;

Therefore, there would not be "104*104*3*105*104" possibilities for timems=1496578753000 .:

So this could be something that IMHO could be improved, but I do not expect this to be simple.

2) Confirmation that the explication is correct
***********************************************
In order to validate the analysis, I first removed all entries where 'time_series' is not unique.
Then I tested the query again - it finishes in limited time.
Then I changed the indexes.

 DELETE a FROM y_metrics as a inner join (SELECT MAX(metric_id) as m, series_id as s, timems as i FROM `y_metrics`  GROUP BY series_id,timems HAVING COUNT(*)>1
 
 ORDER BY `metric_id` DESC) t on a.series_id=t.s AND a.timems=t.i AND a.metric_id!=t.m;
 
 
   SELECT `t`.`timems`, 566 as Cl_id, 568 as rawCl_id, 570 as pH_id, 571 as Temperature_id, 572 as f_id, `s566Cl`.`metric_val` as Cl, `s568rawCl`.`metric_val` as rawCl, `s570pH`.`metric_val` as pH, `s571Temperature`.`metric_val` as Temperature, 
`s572f`.`metric_val` as f 
FROM `y_metrics` `t` USE INDEX(`entity_time`) 
LEFT OUTER JOIN `y_metrics` s566Cl USE INDEX(time_series) ON `t`.`timems`=`s566Cl`.`timems`AND (`s566Cl`.`series_id`=566) 
LEFT OUTER JOIN `y_metrics` s568rawCl USE INDEX(time_series) ON `t`.`timems`=`s568rawCl`.`timems`AND (`s568rawCl`.`series_id`=568) 
LEFT OUTER JOIN `y_metrics` s570pH USE INDEX(time_series) ON `t`.`timems`=`s570pH`.`timems`AND (`s570pH`.`series_id`=570)
 
LEFT OUTER JOIN `y_metrics` s571Temperature USE INDEX(time_series) ON `t`.`timems`=`s571Temperature`.`timems`AND (`s571Temperature`.`series_id`=571) 
LEFT OUTER JOIN `y_metrics` s572f USE INDEX(time_series) ON `t`.`timems`=`s572f`.`timems`AND (`s572f`.`series_id`=572)
 
 WHERE (`t`.`timems`>=1496527200000 AND `t`.`timems`<=1496613599000) AND (`t`.`entity_id`=60) 
GROUP BY `t`.`timems` LIMIT 637
 
 
 
 ALTER TABLE `y_metrics` DROP INDEX `time_series`, ADD UNIQUE `time_series` (`timems`, `series_id`) USING BTREE;
 
 ALTER TABLE `y_metrics` DROP INDEX `series_time`, ADD UNIQUE `series_time` (`series_id`,`timems`) USING BTREE;

Conclusion:
***********
In my application I can live with this: I enforce that there is only one entry for any 'timems'x'series_id' which should work for the optimizer.
There seems to be a possibility to improve the SQL server, but that is not my call.

Comment by Elena Stepanova [ 2017-07-02 ]

igor, do you want to address the comment above, or should I close the report as you previously suggested?

Comment by Igor Babaev [ 2017-07-03 ]

Elena.
Please close it. We can't afford ourselves to support non-standard queries with unclear semantics.
Mario, wants us to scan only one record for each joined table for each group. It's possible,
but I don't think it makes sense to introduce any optimization for non-standard queries

Generated at Thu Feb 08 08:02:05 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.