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

[MDEV-11893] Query execution plan depends on strict mode Created: 2017-01-23 Updated: 2017-05-15
Status:	Open
Project:	MariaDB Server
Component/s:	Optimizer
Affects Version/s:	5.5, 10.0, 10.1, 10.2
Fix Version/s:	10.2

Type:

Bug

Priority:

Major

Reporter:

Elena Stepanova

Assignee:

Sergei Petrunia

Resolution:

Unresolved

Votes:

Labels:

10.2-ga, upstream

Issue Links:

Relates
relates to	~~MDEV-7635~~	update defaults and simplify mysqld c...	Closed

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.

Comments

Comment by Elena Stepanova [ 2017-01-24 ]

Here is another example (taken from main.select).

MariaDB [test]> CREATE TABLE t1(a ENUM('') NOT NULL) ENGINE=MyISAM;

Query OK, 0 rows affected (0.07 sec)

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

Query OK, 4 rows affected (0.01 sec)

Records: 4  Duplicates: 0  Warnings: 0

MariaDB [test]> set sql_mode='';

Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> EXPLAIN SELECT 1 FROM t1 ORDER BY a COLLATE latin1_german2_ci LIMIT 1;

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

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

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

|    1 | SIMPLE      | t1    | ALL  | NULL          | NULL | NULL    | NULL |    4 |       |

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

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 1 FROM t1 ORDER BY a COLLATE latin1_german2_ci LIMIT 1;

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

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

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

|    1 | SIMPLE      | t1    | ALL  | NULL          | NULL | NULL    | NULL |    4 | Using filesort |

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

1 row in set (0.00 sec)

It becomes essential when data is big enough:

INSERT INTO t1 SELECT * FROM t1;

#... repeat

MariaDB [test]> SELECT COUNT(*) FROM t1;

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

| COUNT(*) |

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

|  4194304 |

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

1 row in set (0.00 sec)

MariaDB [test]> set sql_mode='';

Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> EXPLAIN SELECT 1 FROM t1 ORDER BY a COLLATE latin1_german2_ci LIMIT 1;

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

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

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

|    1 | SIMPLE      | t1    | ALL  | NULL          | NULL | NULL    | NULL | 4194304 |       |

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

1 row in set (0.00 sec)

MariaDB [test]> SELECT 1 FROM t1 ORDER BY a COLLATE latin1_german2_ci LIMIT 1;

+---+

| 1 |

+---+

| 1 |

+---+

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 1 FROM t1 ORDER BY a COLLATE latin1_german2_ci LIMIT 1;

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

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

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

|    1 | SIMPLE      | t1    | ALL  | NULL          | NULL | NULL    | NULL | 4194304 | Using filesort |

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

1 row in set (0.00 sec)

MariaDB [test]> SELECT 1 FROM t1 ORDER BY a COLLATE latin1_german2_ci LIMIT 1;

+---+

| 1 |

+---+

| 1 |

+---+

1 row in set (2.62 sec)

Comment by Nirbhay Choubey (Inactive) [ 2017-02-09 ]

For the record, following test (snipped from innodb_zip.16k test) also shows
different result with --mysqld=--sql-mode='STRICT_TRANS_TABLES':

--source include/have_innodb.inc

CREATE TABLE t2(d varchar(17) PRIMARY KEY) ENGINE=innodb DEFAULT CHARSET=utf8;

CREATE TABLE t3(a int PRIMARY KEY) ENGINE=innodb;

INSERT INTO t3 VALUES (22),(44),(33),(55),(66);

INSERT INTO t2 VALUES ('jejdkrun87'),('adfd72nh9k'),

('adfdpplkeock'),('adfdijnmnb78k'),('adfdijn0loKNHJik');

CREATE TABLE t1(a int, b blob, c text, d text NOT NULL)

ENGINE=innodb DEFAULT CHARSET=utf8 STATS_PERSISTENT=0;

INSERT INTO t1

SELECT a,LEFT(REPEAT(d,100*a),65535),REPEAT(d,20*a),d FROM t2,t3 order by a, d;

DELETE FROM t1 WHERE a%2;

ALTER TABLE t1 ADD PRIMARY KEY (a,b(255),c(255)), ADD KEY (b(767));

EXPLAIN SELECT * FROM t1 WHERE b LIKE 'adfd%';

DROP TABLE t1, t2, t3;

Output:

@@ -11,5 +11,5 @@

 ALTER TABLE t1 ADD PRIMARY KEY (a,b(255),c(255)), ADD KEY (b(767));

 EXPLAIN SELECT * FROM t1 WHERE b LIKE 'adfd%';

 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra

-1	SIMPLE	t1	ALL	b	NULL	NULL	NULL	15	Using where

+1	SIMPLE	t1	range	b	b	769	NULL	12	Using where

 DROP TABLE t1, t2, t3;

Comment by Sergei Petrunia [ 2017-05-12 ]

Looking at the first example,

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

The difference in query plan comes from this point in item_strfunc.cc

bool Item_str_func::fix_fields(THD *thd, Item **ref)

  bool res= Item_func::fix_fields(thd, ref);

/*

    In Item_str_func::check_well_formed_result() we may set null_value

    flag on the same condition as in test() below.

*/

  maybe_null= maybe_null || thd->is_strict_mode();

  return res;

This function is called for Item_func_concat.
We come here with maybe_null=FALSE (both arguments are NOT NULL)

and then, if strict mode is ON, maybe_null becomes TRUE.

When strict mode is OFF, maybe_null remains FALSE, which allows optimize_cond() to figure out that "not_null_item IS NULL" can never be true and produce "Impossible WHERE". This doesn't happen with strict mode.

Comment by Sergei Petrunia [ 2017-05-12 ]

The code was last touched by

commit 4923d19b7c3c4c034f70ac1411837afa75c4b6ad

Author:	Sergei Golubchik <sergii@pisem.net>  Sun Dec 16 22:45:45 2012

Committer:	Sergei Golubchik <sergii@pisem.net>  Sun Dec 16 22:45:45 2012

small code cleanup taken from MySQL 5.6

but that one just does rephrasing.

The part before rephrasing comes from:

commit af22eb35e577ef17226faf662f2cffc4705bde26

Author:	unknown <kroki/tomash@moonlight.intranet>  Thu Nov 16 13:06:51 2006

Committer:	unknown <kroki/tomash@moonlight.intranet>  Thu Nov 16 13:06:51 2006

Add 5.0 part of fix for bug 17047.

mysql-test/r/func_str.result:

  Add result for bug#17047: CHAR() and IN() can return NULL without

  signaling NULL result.

mysql-test/t/func_str.test:

  Add test case for bug#17047: CHAR() and IN() can return NULL without

  signaling NULL result.

sql/item_strfunc.cc:

  Add Item_str_func::fix_fields() implementation, and set maybe_null to

  TRUE if we are in the SQL mode that requires some functions to return

  null even if they normally do not.

sql/item_strfunc.h:

  Add declaration of Item_str_func::fix_fields().

  Do not reset maybe_null in Item_func_char::fix_length_and_dec().

Comment by Sergei Petrunia [ 2017-05-12 ]

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?
.

Comment by Sergei Petrunia [ 2017-05-14 ]

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.

Comment by Sergei Petrunia [ 2017-05-15 ]

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

Comment by Sergei Petrunia [ 2017-05-15 ]

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

Comment by Sergei Petrunia [ 2017-05-15 ]

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

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

[MDEV-11893] Query execution plan depends on strict mode Created: 2017-01-23 Updated: 2017-05-15