The problem appeared on 10.0 tree with the following revision:
revno: 3969 [merge]
|
revision-id: psergey@askmonty.org-20140204093411-9vbu0o4aw9r15vpb
|
parent: bar@mnogosearch.org-20140204080500-hjlub3377pfzaxv8
|
parent: psergey@askmonty.org-20140204092710-2yt5ysa5ej3l2c03
|
committer: Sergey Petrunya <psergey@askmonty.org>
|
branch nick: 10.0-push
|
timestamp: Tue 2014-02-04 13:34:11 +0400
|
message:
|
Merge
|
------------------------------------------------------------
|
revno: 3966.1.1
|
revision-id: psergey@askmonty.org-20140204092710-2yt5ysa5ej3l2c03
|
parent: bar@mnogosearch.org-20140203051303-r0bmwq1x52wc0f65
|
committer: Sergey Petrunya <psergey@askmonty.org>
|
branch nick: 10.0
|
timestamp: Tue 2014-02-04 13:27:10 +0400
|
message:
|
MDEV-5606: range optimizer: "x < y" is sargable, while "y > x" is not
|
Port to mariadb-1.0 the following fix from mysql-5.6:
|
|
Revision ID: jorgen.loland@oracle.com-20120314131055-ml54x9deueqfsff4
|
BUG#13701206: WHERE A>=B DOES NOT GIVE SAME EXECUTION PLAN
|
AS WHERE B<=A (RANGE OPTIMIZER)
|
|
that fix didn't have a public testcase, so I created one.
|
Test case:
CREATE TABLE t1 (a VARCHAR(3) NOT NULL) ENGINE=MyISAM;
|
INSERT INTO t1 VALUES ('foo');
|
|
CREATE TABLE t2 (b VARCHAR(3), c VARCHAR(3), INDEX(b)) ENGINE=MyISAM;
|
INSERT INTO t2 VALUES ('bar', 'bar'),( 'qux', 'qux');
|
|
SELECT STRAIGHT_JOIN * FROM t1, t2 AS t2_1, t2 AS t2_2
|
WHERE t2_2.c = t2_1.c AND t2_2.b = t2_1.b AND ( a IS NULL OR t2_1.c = a );
|
#2 0x0000000000850ff9 in handle_fatal_signal (sig=11) at 10.0/sql/signal_handler.cc:262
|
#3 <signal handler called>
|
#4 0x00000000006e35fa in Item_equal_iterator<List_iterator_fast, Item>::get_curr_field (this=0x7fdc1ac77a20) at 10.0/sql/item_cmpfunc.h:1939
|
#5 0x000000000096b5e9 in get_full_func_mm_tree (param=0x7fdc1ac77dd0, cond_func=0x7fdc1074deb0, field_item=0x7fdc1074c188, value=0x7fdc104606b0, inv=false) at 10.0/sql/opt_range.cc:7786
|
#6 0x000000000096c39c in get_mm_tree (param=0x7fdc1ac77dd0, cond=0x7fdc1074deb0) at 10.0/sql/opt_range.cc:7990
|
#7 0x0000000000961195 in SQL_SELECT::test_quick_select (this=0x7fdc1053ed90, thd=0x7fdc13335070, keys_to_use=..., prev_tables=13835058055282163713, limit=18446744073709551615, force_quick_range=false, ordered_output=false) at 10.0/sql/opt_range.cc:3071
|
#8 0x00000000006b8537 in make_join_select (join=0x7fdc1074c518, select=0x7fdc10754b78, cond=0x7fdc10460cb0) at 10.0/sql/sql_select.cc:9481
|
#9 0x00000000006a203b in JOIN::optimize_inner (this=0x7fdc1074c518) at 10.0/sql/sql_select.cc:1522
|
#10 0x00000000006a03a8 in JOIN::optimize (this=0x7fdc1074c518) at 10.0/sql/sql_select.cc:1013
|
#11 0x00000000006a7f13 in mysql_select (thd=0x7fdc13335070, rref_pointer_array=0x7fdc133396a0, tables=0x7fdc1045f348, wild_num=1, fields=..., conds=0x7fdc10460cb0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748610, result=0x7fdc1074c4f8, unit=0x7fdc13338d48, select_lex=0x7fdc13339428) at 10.0/sql/sql_select.cc:3278
|
#12 0x000000000069e6ff in handle_select (thd=0x7fdc13335070, lex=0x7fdc13338c88, result=0x7fdc1074c4f8, setup_tables_done_option=0) at 10.0/sql/sql_select.cc:372
|
#13 0x00000000006737b1 in execute_sqlcom_select (thd=0x7fdc13335070, all_tables=0x7fdc1045f348) at 10.0/sql/sql_parse.cc:5301
|
#14 0x000000000066bb5c in mysql_execute_command (thd=0x7fdc13335070) at 10.0/sql/sql_parse.cc:2587
|
#15 0x0000000000675f3b in mysql_parse (thd=0x7fdc13335070, rawbuf=0x7fdc1045f088 "SELECT STRAIGHT_JOIN * FROM t1, t2 AS t2_1, t2 AS t2_2\nWHERE t2_2.c = t2_1.c AND t2_2.b = t2_1.b AND ( a IS NULL OR t2_1.c = a )", length=128, parser_state=0x7fdc1ac7b630) at 10.0/sql/sql_parse.cc:6447
|
#16 0x0000000000668d09 in dispatch_command (command=COM_QUERY, thd=0x7fdc13335070, packet=0x7fdc1332b071 "SELECT STRAIGHT_JOIN * FROM t1, t2 AS t2_1, t2 AS t2_2\nWHERE t2_2.c = t2_1.c AND t2_2.b = t2_1.b AND ( a IS NULL OR t2_1.c = a )", packet_length=128) at 10.0/sql/sql_parse.cc:1308
|
#17 0x00000000006680ab in do_command (thd=0x7fdc13335070) at 10.0/sql/sql_parse.cc:1005
|
#18 0x0000000000782305 in do_handle_one_connection (thd_arg=0x7fdc13335070) at 10.0/sql/sql_connect.cc:1379
|
#19 0x0000000000782058 in handle_one_connection (arg=0x7fdc13335070) at 10.0/sql/sql_connect.cc:1293
|
#20 0x0000000000aa86f1 in pfs_spawn_thread (arg=0x7fdc13b75b70) at 10.0/storage/perfschema/pfs.cc:1853
|
#21 0x00007fdc1a9c9b50 in start_thread (arg=<optimized out>) at pthread_create.c:304
|
#22 0x00007fdc19518a7d in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:112
|
EXPLAIN also crashes. optimizer_switch does not matter, the crash happens with all OFF values as well as with the default set.
- relates to
-
MDEV-7865
Server crashes in Item_equal_iterator<List_iterator_fast, Item>::get_curr_field on query with impossible condition and OR/AND expressions
-
-
Closed
{"report":{"fcp":1288.2000000476837,"ttfb":330.7000000476837,"pageVisibility":"visible","entityId":30424,"key":"jira.project.issue.view-issue","isInitial":true,"threshold":1000,"elementTimings":{},"userDeviceMemory":8,"userDeviceProcessors":64,"apdex":0.5,"journeyId":"942f0094-8ec0-4aba-9bc2-e9f5d4d7017d","navigationType":0,"readyForUser":1353.3000000715256,"redirectCount":0,"resourceLoadedEnd":1454.2000000476837,"resourceLoadedStart":336.7000000476837,"resourceTiming":[{"duration":479.7000000476837,"initiatorType":"link","name":"https://jira.mariadb.org/s/2c21342762a6a02add1c328bed317ffd-CDN/lu2bu7/820016/12ta74/0a8bac35585be7fc6c9cc5a0464cd4cf/_/download/contextbatch/css/_super/batch.css","startTime":336.7000000476837,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":336.7000000476837,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":816.4000000953674,"responseStart":0,"secureConnectionStart":0},{"duration":479.7999999523163,"initiatorType":"link","name":"https://jira.mariadb.org/s/7ebd35e77e471bc30ff0eba799ebc151-CDN/lu2bu7/820016/12ta74/8679b4946efa1a0bb029a3a22206fb5d/_/download/contextbatch/css/jira.browse.project,project.issue.navigator,jira.view.issue,jira.general,jira.global,atl.general,-_super/batch.css?agile_global_admin_condition=true&jag=true&jira.create.linked.issue=true&slack-enabled=true","startTime":336.90000009536743,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":336.90000009536743,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":816.7000000476837,"responseStart":0,"secureConnectionStart":0},{"duration":531.4000000953674,"initiatorType":"script","name":"https://jira.mariadb.org/s/fbf975c0cce4b1abf04784eeae9ba1f4-CDN/lu2bu7/820016/12ta74/0a8bac35585be7fc6c9cc5a0464cd4cf/_/download/contextbatch/js/_super/batch.js?locale=en","startTime":337,"connectEnd":337,"connectStart":337,"domainLookupEnd":337,"domainLookupStart":337,"fetchStart":337,"redirectEnd":0,"redirectStart":0,"requestStart":337,"responseEnd":868.4000000953674,"responseStart":868.4000000953674,"secureConnectionStart":337},{"duration":599.8000000715256,"initiatorType":"script","name":"https://jira.mariadb.org/s/099b33461394b8015fc36c0a4b96e19f-CDN/lu2bu7/820016/12ta74/8679b4946efa1a0bb029a3a22206fb5d/_/download/contextbatch/js/jira.browse.project,project.issue.navigator,jira.view.issue,jira.general,jira.global,atl.general,-_super/batch.js?agile_global_admin_condition=true&jag=true&jira.create.linked.issue=true&locale=en&slack-enabled=true","startTime":337.10000002384186,"connectEnd":337.10000002384186,"connectStart":337.10000002384186,"domainLookupEnd":337.10000002384186,"domainLookupStart":337.10000002384186,"fetchStart":337.10000002384186,"redirectEnd":0,"redirectStart":0,"requestStart":337.10000002384186,"responseEnd":936.9000000953674,"responseStart":936.9000000953674,"secureConnectionStart":337.10000002384186},{"duration":603.2999999523163,"initiatorType":"script","name":"https://jira.mariadb.org/s/94c15bff32baef80f4096a08aceae8bc-CDN/lu2bu7/820016/12ta74/c92c0caa9a024ae85b0ebdbed7fb4bd7/_/download/contextbatch/js/atl.global,-_super/batch.js?locale=en","startTime":337.2000000476837,"connectEnd":337.2000000476837,"connectStart":337.2000000476837,"domainLookupEnd":337.2000000476837,"domainLookupStart":337.2000000476837,"fetchStart":337.2000000476837,"redirectEnd":0,"redirectStart":0,"requestStart":337.2000000476837,"responseEnd":940.5,"responseStart":940.5,"secureConnectionStart":337.2000000476837},{"duration":603.7000000476837,"initiatorType":"script","name":"https://jira.mariadb.org/s/d41d8cd98f00b204e9800998ecf8427e-CDN/lu2bu7/820016/12ta74/1.0/_/download/batch/jira.webresources:calendar-en/jira.webresources:calendar-en.js","startTime":337.2000000476837,"connectEnd":337.2000000476837,"connectStart":337.2000000476837,"domainLookupEnd":337.2000000476837,"domainLookupStart":337.2000000476837,"fetchStart":337.2000000476837,"redirectEnd":0,"redirectStart":0,"requestStart":337.2000000476837,"responseEnd":940.9000000953674,"responseStart":940.9000000953674,"secureConnectionStart":337.2000000476837},{"duration":603.8999999761581,"initiatorType":"script","name":"https://jira.mariadb.org/s/d41d8cd98f00b204e9800998ecf8427e-CDN/lu2bu7/820016/12ta74/1.0/_/download/batch/jira.webresources:calendar-localisation-moment/jira.webresources:calendar-localisation-moment.js","startTime":337.3000000715256,"connectEnd":337.3000000715256,"connectStart":337.3000000715256,"domainLookupEnd":337.3000000715256,"domainLookupStart":337.3000000715256,"fetchStart":337.3000000715256,"redirectEnd":0,"redirectStart":0,"requestStart":337.3000000715256,"responseEnd":941.2000000476837,"responseStart":941.2000000476837,"secureConnectionStart":337.3000000715256},{"duration":604.2999999523163,"initiatorType":"link","name":"https://jira.mariadb.org/s/b04b06a02d1959df322d9cded3aeecc1-CDN/lu2bu7/820016/12ta74/a2ff6aa845ffc9a1d22fe23d9ee791fc/_/download/contextbatch/css/jira.global.look-and-feel,-_super/batch.css","startTime":337.40000009536743,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":337.40000009536743,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":941.7000000476837,"responseStart":0,"secureConnectionStart":0},{"duration":604.2000000476837,"initiatorType":"script","name":"https://jira.mariadb.org/rest/api/1.0/shortcuts/820016/47140b6e0a9bc2e4913da06536125810/shortcuts.js?context=issuenavigation&context=issueaction","startTime":337.5,"connectEnd":337.5,"connectStart":337.5,"domainLookupEnd":337.5,"domainLookupStart":337.5,"fetchStart":337.5,"redirectEnd":0,"redirectStart":0,"requestStart":337.5,"responseEnd":941.7000000476837,"responseStart":941.7000000476837,"secureConnectionStart":337.5},{"duration":604.2999999523163,"initiatorType":"link","name":"https://jira.mariadb.org/s/3ac36323ba5e4eb0af2aa7ac7211b4bb-CDN/lu2bu7/820016/12ta74/d176f0986478cc64f24226b3d20c140d/_/download/contextbatch/css/com.atlassian.jira.projects.sidebar.init,-_super,-project.issue.navigator,-jira.view.issue/batch.css?jira.create.linked.issue=true","startTime":337.7000000476837,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":337.7000000476837,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":942,"responseStart":0,"secureConnectionStart":0},{"duration":604.2999999523163,"initiatorType":"script","name":"https://jira.mariadb.org/s/3339d87fa2538a859872f2df449bf8d0-CDN/lu2bu7/820016/12ta74/d176f0986478cc64f24226b3d20c140d/_/download/contextbatch/js/com.atlassian.jira.projects.sidebar.init,-_super,-project.issue.navigator,-jira.view.issue/batch.js?jira.create.linked.issue=true&locale=en","startTime":337.90000009536743,"connectEnd":337.90000009536743,"connectStart":337.90000009536743,"domainLookupEnd":337.90000009536743,"domainLookupStart":337.90000009536743,"fetchStart":337.90000009536743,"redirectEnd":0,"redirectStart":0,"requestStart":337.90000009536743,"responseEnd":942.2000000476837,"responseStart":942.2000000476837,"secureConnectionStart":337.90000009536743},{"duration":871.1999999284744,"initiatorType":"script","name":"https://jira.mariadb.org/s/d41d8cd98f00b204e9800998ecf8427e-CDN/lu2bu7/820016/12ta74/1.0/_/download/batch/jira.webresources:bigpipe-js/jira.webresources:bigpipe-js.js","startTime":338.40000009536743,"connectEnd":338.40000009536743,"connectStart":338.40000009536743,"domainLookupEnd":338.40000009536743,"domainLookupStart":338.40000009536743,"fetchStart":338.40000009536743,"redirectEnd":0,"redirectStart":0,"requestStart":338.40000009536743,"responseEnd":1209.6000000238419,"responseStart":1209.6000000238419,"secureConnectionStart":338.40000009536743},{"duration":1115.7000000476837,"initiatorType":"script","name":"https://jira.mariadb.org/s/d41d8cd98f00b204e9800998ecf8427e-CDN/lu2bu7/820016/12ta74/1.0/_/download/batch/jira.webresources:bigpipe-init/jira.webresources:bigpipe-init.js","startTime":338.5,"connectEnd":338.5,"connectStart":338.5,"domainLookupEnd":338.5,"domainLookupStart":338.5,"fetchStart":338.5,"redirectEnd":0,"redirectStart":0,"requestStart":338.5,"responseEnd":1454.2000000476837,"responseStart":1454.2000000476837,"secureConnectionStart":338.5},{"duration":370.60000002384186,"initiatorType":"xmlhttprequest","name":"https://jira.mariadb.org/rest/webResources/1.0/resources","startTime":1081.8000000715256,"connectEnd":1081.8000000715256,"connectStart":1081.8000000715256,"domainLookupEnd":1081.8000000715256,"domainLookupStart":1081.8000000715256,"fetchStart":1081.8000000715256,"redirectEnd":0,"redirectStart":0,"requestStart":1081.8000000715256,"responseEnd":1452.4000000953674,"responseStart":1452.4000000953674,"secureConnectionStart":1081.8000000715256},{"duration":199.39999997615814,"initiatorType":"script","name":"https://www.google-analytics.com/analytics.js","startTime":1266.9000000953674,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":1266.9000000953674,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":1466.3000000715256,"responseStart":0,"secureConnectionStart":0}],"fetchStart":0,"domainLookupStart":0,"domainLookupEnd":0,"connectStart":0,"connectEnd":0,"requestStart":154,"responseStart":331,"responseEnd":332,"domLoading":334,"domInteractive":1475,"domContentLoadedEventStart":1475,"domContentLoadedEventEnd":1511,"domComplete":2041,"loadEventStart":2041,"loadEventEnd":2042,"userAgent":"Mozilla/5.0 AppleWebKit/537.36 (KHTML, like Gecko; compatible; ClaudeBot/1.0; +claudebot@anthropic.com)","marks":[{"name":"bigPipe.sidebar-id.start","time":1457.7000000476837},{"name":"bigPipe.sidebar-id.end","time":1458.6000000238419},{"name":"bigPipe.activity-panel-pipe-id.start","time":1458.7000000476837},{"name":"bigPipe.activity-panel-pipe-id.end","time":1460.9000000953674},{"name":"activityTabFullyLoaded","time":1517.2000000476837}],"measures":[],"correlationId":"67f58a1fa20779","effectiveType":"4g","downlink":10,"rtt":0,"serverDuration":103,"dbReadsTimeInMs":14,"dbConnsTimeInMs":23,"applicationHash":"9d11dbea5f4be3d4cc21f03a88dd11d8c8687422","experiments":[]}}