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.

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