[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

Elena Stepanova added a comment - 2017-01-24 21:42 - edited

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)

Elena Stepanova added a comment - 2017-01-24 21:42 - edited 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)

Nirbhay Choubey (Inactive) added a comment - 2017-02-09 18:23 - edited

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;

Nirbhay Choubey (Inactive) added a comment - 2017-02-09 18:23 - edited 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;

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

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.

Sergei Petrunia added a comment - 2017-05-12 15:17 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.

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

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().

Sergei Petrunia added a comment - 2017-05-12 15:17 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().

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

People

Assignee:: Sergei Petrunia

Reporter:: Elena Stepanova

Votes:: 0 Vote for this issue

Watchers:: 4 Start watching this issue

Dates

Created:: 2017-01-23 23:34

Updated:: 2017-05-15 12:54

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB Server