[MDEV-11893] Query execution plan depends on strict mode - Jira

Details

Type: Bug
Status: Open (View Workflow)
Priority: Major
Resolution: Unresolved
Affects Version/s: 5.5(EOL), 10.0(EOL), 10.1(EOL), 10.2(EOL)
Fix Version/s: 10.2(EOL)
Component/s: Optimizer
Labels:
- 10.2-ga
- upstream

Description

MariaDB [test]> CREATE TABLE t1 (dt DATETIME NOT NULL);

Query OK, 0 rows affected (0.29 sec)

MariaDB [test]> INSERT INTO t1 VALUES (NOW()),(NOW());

Query OK, 2 rows affected (0.06 sec)

Records: 2  Duplicates: 0  Warnings: 0

MariaDB [test]> set sql_mode='';

Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> explain  SELECT * FROM t1 WHERE concat( dt, '2012-12-21 12:12:12' ) IS NULL;

+------+-------------+-------+------+---------------+------+---------+------+------+------------------+

| id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra            |

+------+-------------+-------+------+---------------+------+---------+------+------+------------------+

|    1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | Impossible WHERE |

+------+-------------+-------+------+---------------+------+---------+------+------+------------------+

1 row in set (0.00 sec)

MariaDB [test]> set sql_mode='STRICT_TRANS_TABLES';

Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> explain  SELECT * FROM t1 WHERE concat( dt, '2012-12-21 12:12:12' ) IS NULL;

+------+-------------+-------+------+---------------+------+---------+------+------+-------------+

| id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |

+------+-------------+-------+------+---------------+------+---------+------+------+-------------+

|    1 | SIMPLE      | t1    | ALL  | NULL          | NULL | NULL    | NULL |    2 | Using where |

+------+-------------+-------+------+---------------+------+---------+------+------+-------------+

1 row in set (0.00 sec)

Also reproducible with MySQL 5.7.

Attachments

Issue Links

relates to

MDEV-7635 update defaults and simplify mysqld config parameters

Closed

Activity

Ascending order - Click to sort in descending order

View 4 older comments

Sergei Petrunia added a comment - 2017-05-12 15:18

Need to

check other examples
learn more about "some functions returning even null if they normally do not" . Does this make maybe_null totally meaningless?
.

Sergei Petrunia added a comment - 2017-05-12 15:18 Need to check other examples learn more about "some functions returning even null if they normally do not" . Does this make maybe_null totally meaningless? .

Sergei Petrunia added a comment - 2017-05-14 20:08 - edited

Checking Elena's example from the first comment,

EXPLAIN SELECT 1 FROM t1 ORDER BY a COLLATE latin1_german2_ci LIMIT 1;

The difference in ORDER BY optimization comes from this piece in JOIN::prepare():

/*

        Disregard sort order if there's only

        zero length NOT NULL fields (e.g. {VAR}CHAR(0) NOT NULL") or

        zero length NOT NULL string functions there.

        Such tuples don't contain any data to sort.

*/

      if (!real_order &&

           /* Not a zero length NOT NULL field */

          ((item->type() != Item::FIELD_ITEM ||

            ((Item_field *) item)->field->maybe_null() ||

            ((Item_field *) item)->field->sort_length()) &&

           /* AND not a zero length NOT NULL string function. */

           (item->type() != Item::FUNC_ITEM ||

            item->maybe_null ||

            item->result_type() != STRING_RESULT ||

            item->max_length)))

        real_order= TRUE;

(gdb) p item

  $69 = (Item_func_set_collation *) 0x7fffcb0dba40

(gdb) p item->type()

  $70 = Item::FUNC_ITEM

(gdb) p item->maybe_null

  $71 = true

## The above is TRUE  for sql_mode='';

##          and FALSE for sql_mode='STRICT_TRANS_TABLES'

(gdb) p item->result_type()

  $72 = STRING_RESULT

(gdb) p item->max_length

  $73 = 0

The difference in the value of item->maybe_null comes from the code in Item_str_func::fix_fields mentioned earlier.

Sergei Petrunia added a comment - 2017-05-14 20:08 - edited Checking Elena's example from the first comment, EXPLAIN SELECT 1 FROM t1 ORDER BY a COLLATE latin1_german2_ci LIMIT 1; The difference in ORDER BY optimization comes from this piece in JOIN::prepare(): /* Disregard sort order if there's only zero length NOT NULL fields (e.g. {VAR}CHAR(0) NOT NULL") or zero length NOT NULL string functions there. Such tuples don't contain any data to sort. */ if (!real_order && /* Not a zero length NOT NULL field */ ((item->type() != Item::FIELD_ITEM || ((Item_field *) item)->field->maybe_null() || ((Item_field *) item)->field->sort_length()) && /* AND not a zero length NOT NULL string function. */ (item->type() != Item::FUNC_ITEM || item->maybe_null || item->result_type() != STRING_RESULT || item->max_length))) real_order= TRUE; (gdb) p item $69 = (Item_func_set_collation *) 0x7fffcb0dba40 (gdb) p item->type() $70 = Item::FUNC_ITEM (gdb) p item->maybe_null $71 = true ## The above is TRUE for sql_mode=''; ## and FALSE for sql_mode='STRICT_TRANS_TABLES' (gdb) p item->result_type() $72 = STRING_RESULT (gdb) p item->max_length $73 = 0 The difference in the value of item->maybe_null comes from the code in Item_str_func::fix_fields mentioned earlier.

Sergei Petrunia added a comment - 2017-05-15 10:41

Investigating the difference in Nirbhay's example.

The query optimizer works the same regardless of the sql_mode setting.
The difference appears when SQL_SELECT::test_quick_select() calls
ha_innobase::scan_time():

(gdb) p prebuilt->table->stat_clustered_index_size

  $110 = 97

(gdb) p prebuilt->table->stat_clustered_index_size

  $93 = 1

which causes vastly different estimates for cost of table scan.
(records_in_range() estimates are the same).

The first place where the difference appears is in the ALTER TABLE command,
here:

  #4  0x0000000000655af7 in mysql_parse (thd=0x7fffd3ec1070, rawbuf=0x7fffcad24088 "ALTER TABLE t1 ADD PRIMARY KEY (a,b(255),c(255)), ADD KEY (b(767))", length=66, parser_state=0x7fffcb3fe620) at /home/psergey/dev-git/10.2/sql/sql_parse.cc:6583

  #5  0x00000000006480c4 in dispatch_command (command=COM_QUERY, thd=0x7fffd3ec1070, packet=0x7fffd3ec7071 "ALTER TABLE t1 ADD PRIMARY KEY (a,b(255),c(255)), ADD KEY (b(767))", packet_length=66) at /home/psergey/dev-git/10.2/sql/sql_parse.cc:1309

  #6  0x0000000000647343 in do_command (thd=0x7fffd3ec1070) at /home/psergey/dev-git/10.2/sql/sql_parse.cc:999

  #7  0x000000000077388c in do_handle_one_connection (thd_arg=0x7fffd3ec1070) at /home/psergey/dev-git/10.2/sql/sql_connect.cc:1377

  #8  0x00000000007735fe in handle_one_connection (arg=0x7fffd3ec1070) at /home/psergey/dev-git/10.2/sql/sql_connect.cc:1292

  #9  0x00007ffff733c182 in start_thread (arg=0x7fffcb3ff700) at pthread_create.c:312

  #10 0x00007ffff622330d in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:111

with sql_mode='STRICT_TRANS_TABLES':
ha_innobase::check_if_supported_inplace_alter() = HA_ALTER_INPLACE_NO_LOCK_AFTER_PREPARE

without it:
ha_innobase::check_if_supported_inplace_alter()= HA_ALTER_INPLACE_NOT_SUPPORTED

then the server with sql_mode=STRICT_TRANS_TABLES hits this

  Breakpoint 27, dict_stats_update_transient (table=0x7fffcac2fb78) at /home/psergey/dev-git/10.2/storage/xtradb/dict/dict0stats.cc:940

(gdb) p table->name

  $119 = 0x7fffcace60e0 "j22/t1"

(gdb) wher

  #0  dict_stats_update_transient (table=0x7fffcac2fb78) at /home/psergey/dev-git/10.2/storage/xtradb/dict/dict0stats.cc:940

  #1  0x0000000000dad906 in dict_stats_update (table=0x7fffcac2fb78, stats_upd_option=DICT_STATS_RECALC_TRANSIENT) at /home/psergey/dev-git/10.2/storage/xtradb/dict/dict0stats.cc:3332

  #2  0x0000000000b712de in dict_stats_init (table=0x7fffcac2fb78) at /home/psergey/dev-git/10.2/storage/xtradb/include/dict0stats.ic:174

  #3  0x0000000000b782ce in ha_innobase::open (this=0x7fffcac73888, name=0x7fffcac94178 "./j22/t1", mode=2, test_if_locked=18) at /home/psergey/dev-git/10.2/storage/xtradb/handler/ha_innodb.cc:6147

  #4  0x0000000000852a2e in handler::ha_open (this=0x7fffcac73888, table_arg=0x7fffcaca9870, name=0x7fffcac94178 "./j22/t1", mode=2, test_if_locked=18) at /home/psergey/dev-git/10.2/sql/handler.cc:2525

  #5  0x00000000007329b0 in open_table_from_share (thd=0x7fffd3ec1070, share=0x7fffcac93b88, alias=0x7fffcad241b0 "t1", db_stat=39, prgflag=44, ha_open_flags=18, outparam=0x7fffcaca9870, is_create_table=false) at /home/psergey/dev-git/10.2/sql/table.cc:2865

  #6  0x00000000005e7e39 in open_table (thd=0x7fffd3ec1070, table_list=0x7fffcad241b8, mem_root=0x7fffd3ec5d18, ot_ctx=0x7fffcb3fb990) at /home/psergey/dev-git/10.2/sql/sql_base.cc:2516

  #7  0x0000000000705e52 in mysql_inplace_alter_table (thd=0x7fffd3ec1070, table_list=0x7fffcad241b8, table=0x0, altered_table=0x7fffcae94070, ha_alter_info=0x7fffcb3fbb20, inplace_supported=HA_ALTER_INPLACE_NO_LOCK_AFTER_PREPARE, target_mdl_request=0x7fffcb3fbb90, alter_ctx=0x7fffcb3fc720) at /home/psergey/dev-git/10.2/sql/sql_table.cc:7175

  #8  0x000000000070a3f7 in mysql_alter_table (thd=0x7fffd3ec1070, new_db=0x7fffcad24798 "j22", new_name=0x0, create_info=0x7fffcb3fd320, table_list=0x7fffcad241b8, alter_info=0x7fffcb3fd290, order_num=0, order=0x0, ignore=false) at /home/psergey/dev-git/10.2/sql/sql_table.cc:8909

  #9  0x0000000000778366 in Sql_cmd_alter_table::execute (this=0x7fffcad24998, thd=0x7fffd3ec1070) at /home/psergey/dev-git/10.2/sql/sql_alter.cc:312

  #10 0x00000000006524a3 in mysql_execute_command (thd=0x7fffd3ec1070) at /home/psergey/dev-git/10.2/sql/sql_parse.cc:5122

  #11 0x0000000000655af7 in mysql_parse (thd=0x7fffd3ec1070, rawbuf=0x7fffcad24088 "ALTER TABLE t1 ADD PRIMARY KEY (a,b(255),c(255)), ADD KEY (b(767))", length=66, parser_state=0x7fffcb3fe620) at /home/psergey/dev-git/10.2/sql/sql_parse.cc:6583

  #12 0x00000000006480c4 in dispatch_command (command=COM_QUERY, thd=0x7fffd3ec1070, packet=0x7fffd3ec7071 "ALTER TABLE t1 ADD PRIMARY KEY (a,b(255),c(255)), ADD KEY (b(767))", packet_length=66) at /home/psergey/dev-git/10.2/sql/sql_parse.cc:1309

...

(gdb) p index->stat_index_size

  $120 = 97

while the server without one hits this:

  #0  dict_stats_empty_table (table=0x7fffcb063778) at /home/psergey/dev-git/10.2/storage/xtradb/dict/dict0stats.cc:556

  #1  0x0000000000dad50c in dict_stats_update (table=0x7fffcb063778, stats_upd_option=DICT_STATS_EMPTY_TABLE) at /home/psergey/dev-git/10.2/storage/xtradb/dict/dict0stats.cc:3193

  #2  0x0000000000b81eeb in ha_innobase::create (this=0x7fffcb1f8088, name=0x7ffff7f05fcc "./j22/#sql-3d96_f", form=0x7ffff7f03840, create_info=0x7ffff7f06320) at /home/psergey/dev-git/10.2/storage/xtradb/handler/ha_innodb.cc:11196

  #3  0x0000000000856c48 in handler::ha_create (this=0x7fffcb1f8088, name=0x7ffff7f05fcc "./j22/#sql-3d96_f", form=0x7ffff7f03840, info=0x7ffff7f06320) at /home/psergey/dev-git/10.2/sql/handler.cc:4339

  #4  0x0000000000857b34 in ha_create_table (thd=0x7fffd3abb070, path=0x7ffff7f05fcc "./j22/#sql-3d96_f", db=0x7fffcb0d8798 "j22", table_name=0x7ffff7f05760 "#sql-3d96_f", create_info=0x7ffff7f06320, frm=0x7ffff7f04af0) at /home/psergey/dev-git/10.2/sql/handler.cc:4708

  #5  0x000000000070a682 in mysql_alter_table (thd=0x7fffd3abb070, new_db=0x7fffcb0d8798 "j22", new_name=0x0, create_info=0x7ffff7f06320, table_list=0x7fffcb0d81b8, alter_info=0x7ffff7f06290, order_num=0, order=0x0, ignore=false) at /home/psergey/dev-git/10.2/sql/sql_table.cc:8964

  #6  0x0000000000778366 in Sql_cmd_alter_table::execute (this=0x7fffcb0d8998, thd=0x7fffd3abb070) at /home/psergey/dev-git/10.2/sql/sql_alter.cc:312

  #7  0x00000000006524a3 in mysql_execute_command (thd=0x7fffd3abb070) at /home/psergey/dev-git/10.2/sql/sql_parse.cc:5122

  #8  0x0000000000655af7 in mysql_parse (thd=0x7fffd3abb070, rawbuf=0x7fffcb0d8088 "ALTER TABLE t1 ADD PRIMARY KEY (a,b(255),c(255)), ADD KEY (b(767))", length=66, parser_state=0x7ffff7f07620) at /home/psergey/dev-git/10.2/sql/sql_parse.cc:6583

Sergei Petrunia added a comment - 2017-05-15 10:41 Investigating the difference in Nirbhay's example. The query optimizer works the same regardless of the sql_mode setting. The difference appears when SQL_SELECT::test_quick_select() calls ha_innobase::scan_time(): (gdb) p prebuilt->table->stat_clustered_index_size $110 = 97 (gdb) p prebuilt->table->stat_clustered_index_size $93 = 1 which causes vastly different estimates for cost of table scan. (records_in_range() estimates are the same). The first place where the difference appears is in the ALTER TABLE command, here: #4 0x0000000000655af7 in mysql_parse (thd=0x7fffd3ec1070, rawbuf=0x7fffcad24088 "ALTER TABLE t1 ADD PRIMARY KEY (a,b(255),c(255)), ADD KEY (b(767))", length=66, parser_state=0x7fffcb3fe620) at /home/psergey/dev-git/10.2/sql/sql_parse.cc:6583 #5 0x00000000006480c4 in dispatch_command (command=COM_QUERY, thd=0x7fffd3ec1070, packet=0x7fffd3ec7071 "ALTER TABLE t1 ADD PRIMARY KEY (a,b(255),c(255)), ADD KEY (b(767))", packet_length=66) at /home/psergey/dev-git/10.2/sql/sql_parse.cc:1309 #6 0x0000000000647343 in do_command (thd=0x7fffd3ec1070) at /home/psergey/dev-git/10.2/sql/sql_parse.cc:999 #7 0x000000000077388c in do_handle_one_connection (thd_arg=0x7fffd3ec1070) at /home/psergey/dev-git/10.2/sql/sql_connect.cc:1377 #8 0x00000000007735fe in handle_one_connection (arg=0x7fffd3ec1070) at /home/psergey/dev-git/10.2/sql/sql_connect.cc:1292 #9 0x00007ffff733c182 in start_thread (arg=0x7fffcb3ff700) at pthread_create.c:312 #10 0x00007ffff622330d in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:111 with sql_mode='STRICT_TRANS_TABLES': ha_innobase::check_if_supported_inplace_alter() = HA_ALTER_INPLACE_NO_LOCK_AFTER_PREPARE without it: ha_innobase::check_if_supported_inplace_alter()= HA_ALTER_INPLACE_NOT_SUPPORTED then the server with sql_mode=STRICT_TRANS_TABLES hits this Breakpoint 27, dict_stats_update_transient (table=0x7fffcac2fb78) at /home/psergey/dev-git/10.2/storage/xtradb/dict/dict0stats.cc:940 (gdb) p table->name $119 = 0x7fffcace60e0 "j22/t1" (gdb) wher #0 dict_stats_update_transient (table=0x7fffcac2fb78) at /home/psergey/dev-git/10.2/storage/xtradb/dict/dict0stats.cc:940 #1 0x0000000000dad906 in dict_stats_update (table=0x7fffcac2fb78, stats_upd_option=DICT_STATS_RECALC_TRANSIENT) at /home/psergey/dev-git/10.2/storage/xtradb/dict/dict0stats.cc:3332 #2 0x0000000000b712de in dict_stats_init (table=0x7fffcac2fb78) at /home/psergey/dev-git/10.2/storage/xtradb/include/dict0stats.ic:174 #3 0x0000000000b782ce in ha_innobase::open (this=0x7fffcac73888, name=0x7fffcac94178 "./j22/t1", mode=2, test_if_locked=18) at /home/psergey/dev-git/10.2/storage/xtradb/handler/ha_innodb.cc:6147 #4 0x0000000000852a2e in handler::ha_open (this=0x7fffcac73888, table_arg=0x7fffcaca9870, name=0x7fffcac94178 "./j22/t1", mode=2, test_if_locked=18) at /home/psergey/dev-git/10.2/sql/handler.cc:2525 #5 0x00000000007329b0 in open_table_from_share (thd=0x7fffd3ec1070, share=0x7fffcac93b88, alias=0x7fffcad241b0 "t1", db_stat=39, prgflag=44, ha_open_flags=18, outparam=0x7fffcaca9870, is_create_table=false) at /home/psergey/dev-git/10.2/sql/table.cc:2865 #6 0x00000000005e7e39 in open_table (thd=0x7fffd3ec1070, table_list=0x7fffcad241b8, mem_root=0x7fffd3ec5d18, ot_ctx=0x7fffcb3fb990) at /home/psergey/dev-git/10.2/sql/sql_base.cc:2516 #7 0x0000000000705e52 in mysql_inplace_alter_table (thd=0x7fffd3ec1070, table_list=0x7fffcad241b8, table=0x0, altered_table=0x7fffcae94070, ha_alter_info=0x7fffcb3fbb20, inplace_supported=HA_ALTER_INPLACE_NO_LOCK_AFTER_PREPARE, target_mdl_request=0x7fffcb3fbb90, alter_ctx=0x7fffcb3fc720) at /home/psergey/dev-git/10.2/sql/sql_table.cc:7175 #8 0x000000000070a3f7 in mysql_alter_table (thd=0x7fffd3ec1070, new_db=0x7fffcad24798 "j22", new_name=0x0, create_info=0x7fffcb3fd320, table_list=0x7fffcad241b8, alter_info=0x7fffcb3fd290, order_num=0, order=0x0, ignore=false) at /home/psergey/dev-git/10.2/sql/sql_table.cc:8909 #9 0x0000000000778366 in Sql_cmd_alter_table::execute (this=0x7fffcad24998, thd=0x7fffd3ec1070) at /home/psergey/dev-git/10.2/sql/sql_alter.cc:312 #10 0x00000000006524a3 in mysql_execute_command (thd=0x7fffd3ec1070) at /home/psergey/dev-git/10.2/sql/sql_parse.cc:5122 #11 0x0000000000655af7 in mysql_parse (thd=0x7fffd3ec1070, rawbuf=0x7fffcad24088 "ALTER TABLE t1 ADD PRIMARY KEY (a,b(255),c(255)), ADD KEY (b(767))", length=66, parser_state=0x7fffcb3fe620) at /home/psergey/dev-git/10.2/sql/sql_parse.cc:6583 #12 0x00000000006480c4 in dispatch_command (command=COM_QUERY, thd=0x7fffd3ec1070, packet=0x7fffd3ec7071 "ALTER TABLE t1 ADD PRIMARY KEY (a,b(255),c(255)), ADD KEY (b(767))", packet_length=66) at /home/psergey/dev-git/10.2/sql/sql_parse.cc:1309 ... (gdb) p index->stat_index_size $120 = 97 while the server without one hits this: #0 dict_stats_empty_table (table=0x7fffcb063778) at /home/psergey/dev-git/10.2/storage/xtradb/dict/dict0stats.cc:556 #1 0x0000000000dad50c in dict_stats_update (table=0x7fffcb063778, stats_upd_option=DICT_STATS_EMPTY_TABLE) at /home/psergey/dev-git/10.2/storage/xtradb/dict/dict0stats.cc:3193 #2 0x0000000000b81eeb in ha_innobase::create (this=0x7fffcb1f8088, name=0x7ffff7f05fcc "./j22/#sql-3d96_f", form=0x7ffff7f03840, create_info=0x7ffff7f06320) at /home/psergey/dev-git/10.2/storage/xtradb/handler/ha_innodb.cc:11196 #3 0x0000000000856c48 in handler::ha_create (this=0x7fffcb1f8088, name=0x7ffff7f05fcc "./j22/#sql-3d96_f", form=0x7ffff7f03840, info=0x7ffff7f06320) at /home/psergey/dev-git/10.2/sql/handler.cc:4339 #4 0x0000000000857b34 in ha_create_table (thd=0x7fffd3abb070, path=0x7ffff7f05fcc "./j22/#sql-3d96_f", db=0x7fffcb0d8798 "j22", table_name=0x7ffff7f05760 "#sql-3d96_f", create_info=0x7ffff7f06320, frm=0x7ffff7f04af0) at /home/psergey/dev-git/10.2/sql/handler.cc:4708 #5 0x000000000070a682 in mysql_alter_table (thd=0x7fffd3abb070, new_db=0x7fffcb0d8798 "j22", new_name=0x0, create_info=0x7ffff7f06320, table_list=0x7fffcb0d81b8, alter_info=0x7ffff7f06290, order_num=0, order=0x0, ignore=false) at /home/psergey/dev-git/10.2/sql/sql_table.cc:8964 #6 0x0000000000778366 in Sql_cmd_alter_table::execute (this=0x7fffcb0d8998, thd=0x7fffd3abb070) at /home/psergey/dev-git/10.2/sql/sql_alter.cc:312 #7 0x00000000006524a3 in mysql_execute_command (thd=0x7fffd3abb070) at /home/psergey/dev-git/10.2/sql/sql_parse.cc:5122 #8 0x0000000000655af7 in mysql_parse (thd=0x7fffd3abb070, rawbuf=0x7fffcb0d8088 "ALTER TABLE t1 ADD PRIMARY KEY (a,b(255),c(255)), ADD KEY (b(767))", length=66, parser_state=0x7ffff7f07620) at /home/psergey/dev-git/10.2/sql/sql_parse.cc:6583

Sergei Petrunia added a comment - 2017-05-15 10:44

... and adding ANALYZE TABLE t1 makes the results the same between the test with and without set sql_mode='STRICT_TRANS_TABLES'.

Sergei Petrunia added a comment - 2017-05-15 10:44 ... and adding ANALYZE TABLE t1 makes the results the same between the test with and without set sql_mode='STRICT_TRANS_TABLES' .

Sergei Petrunia added a comment - 2017-05-15 12:54

We could fix something here, but need to discuss this first:
https://lists.launchpad.net/maria-developers/msg10701.html

Sergei Petrunia added a comment - 2017-05-15 12:54 We could fix something here, but need to discuss this first: https://lists.launchpad.net/maria-developers/msg10701.html

MariaDB Server

Query execution plan depends on strict mode

Details

Description

Attachments

Issue Links

Activity

People

Dates

Git Integration