[MDEV-17017] Explain for query using derived table specified with a table value constructor shows wrong number of rows - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Major
Resolution: Fixed
Affects Version/s: 10.3(EOL)
Fix Version/s: 10.3.10
Component/s: Optimizer
Labels:
None

Description

If a derived table is specified by only table value constructors then the expected number of rows displayed by EXPLAIN is always 2.

MariaDB [test]> explain select * from (values (7), (5), (8), (1), (3), (8), (1)) t;

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

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

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

|    1 | PRIMARY     | <derived2> | ALL  | NULL          | NULL | NULL    | NULL |    2 |                |

|    2 | DERIVED     | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL | No tables used |

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

MariaDB [test]> explain select * from (values (1,11), (7,77), (3,31), (4,42)) t;

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

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

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

|    1 | PRIMARY     | <derived2> | ALL  | NULL          | NULL | NULL    | NULL |    2 |                |

|    2 | DERIVED     | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL | No tables used |

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

2MariaDB [test]> explain select * from (values (7), (5), (8), (1) union values (3), (8), (1)) t;

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

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

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

|    1 | PRIMARY      | <derived2> | ALL  | NULL          | NULL | NULL    | NULL |    2 |                |

|    2 | DERIVED      | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL | No tables used |

|    3 | UNION        | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL | No tables used |

| NULL | UNION RESULT | <union2,3> | ALL  | NULL          | NULL | NULL    | NULL | NULL |                |

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

If the specification of a derived table uses not only table value constructors the EXPLAIN
still shows a wrong number of expected rows in the derived table

MariaDB [test]> create table t1 (a int);

MariaDB [test]> insert into t1 values (9), (3), (2);

Records: 3  Duplicates: 0  Warnings: 0

MariaDB [test]> explain select * from (values (7), (5), (8), (1) union select * from t1) t;

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

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

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

|    1 | PRIMARY      | <derived2> | ALL  | NULL          | NULL | NULL    | NULL |    3 |                |

|    2 | DERIVED      | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL | No tables used |

|    3 | UNION        | t1         | ALL  | NULL          | NULL | NULL    | NULL |    3 |                |

| NULL | UNION RESULT | <union2,3> | ALL  | NULL          | NULL | NULL    | NULL | NULL |                |

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

Attachments

Activity

Ascending order - Click to sort in descending order

Igor Babaev (Inactive) added a comment - 2018-08-20 03:43

A fix for this bug was pushed into 10.3

Igor Babaev (Inactive) added a comment - 2018-08-20 03:43 A fix for this bug was pushed into 10.3

Daniel Black added a comment - 2018-08-21 23:08

Test failures:

https://api.travis-ci.org/v3/job/418873200/log.txt

main.range 'innodb'                      w4 [ fail ]

        Test ended at 2018-08-21 20:32:18

CURRENT_TEST: main.range

mysqltest: At line 873: query 'set in_predicate_conversion_threshold= 2000' failed: 1193: Unknown system variable 'in_predicate_conversion_threshold'

The result from queries just before the failure was:

< snip >

1	SIMPLE	t1	range	a	a	13	NULL	#	Using index condition; Using where

explain select * from t2 where a between 'a' and 'a ';

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra

1	SIMPLE	t2	ref	a	a	13	const	#	Using index condition

explain select * from t2 where a = 'a' or a='a ';

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra

1	SIMPLE	t2	ref	a	a	13	const	#	Using index condition; Using where

update t1 set a='b' where a<>'a';

explain select * from t1 where a not between 'b' and 'b';

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra

1	SIMPLE	t1	range	a	a	13	NULL	#	Using index condition

select a, hex(filler) from t1 where a not between 'b' and 'b';

a	hex(filler)

a	0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000

drop table t1,t2,t3;

create table t1 (a int);

insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);

create table t2 (a int, key(a));

insert into t2 select 2*(A.a + 10*(B.a + 10*C.a)) from t1 A, t1 B, t1 C;

set in_predicate_conversion_threshold= 2000;

More results from queries before failure can be found in /home/travis/build/MariaDB/server/mysql-test/var/4/log/range.log

 - saving '/home/travis/build/MariaDB/server/mysql-test/var/4/log/main.range-innodb/' to '/home/travis/build/MariaDB/server/mysql-test/var/log/main.range-innodb/'

main.join_outer_jcl6                     w1 [ pass ]   9035

main.1st                                 w1 [ pass ]      1

main.multi_update2                       w2 [ pass ]  24053

main.aborted_clients                     w1 [ pass ]    107

main.range_innodb 'innodb'               w4 [ pass ]    351

main.adddate_454                         w1 [ pass ]      5

main.flush                               w3 [ pass ]   2929

main.alias                               w1 [ pass ]     27

main.almost_full                         w1 [ pass ]     42

main.range_mrr_icp 'innodb'              w4 [ fail ]

        Test ended at 2018-08-21 20:32:19

CURRENT_TEST: main.range_mrr_icp

mysqltest: In included file "/home/travis/build/MariaDB/server/mysql-test/main/range.test":

included from /home/travis/build/MariaDB/server/mysql-test/main/range_mrr_icp.test at line 5:

At line 873: query 'set in_predicate_conversion_threshold= 2000' failed: 1193: Unknown system variable 'in_predicate_conversion_threshold'

The result from queries just before the failure was:

< snip >

1	SIMPLE	t1	range	a	a	13	NULL	#	Using index condition; Using where; Rowid-ordered scan

explain select * from t2 where a between 'a' and 'a ';

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra

1	SIMPLE	t2	ref	a	a	13	const	#	Using index condition

explain select * from t2 where a = 'a' or a='a ';

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra

1	SIMPLE	t2	ref	a	a	13	const	#	Using index condition; Using where

update t1 set a='b' where a<>'a';

explain select * from t1 where a not between 'b' and 'b';

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra

1	SIMPLE	t1	range	a	a	13	NULL	#	Using index condition; Rowid-ordered scan

select a, hex(filler) from t1 where a not between 'b' and 'b';

a	hex(filler)

a	0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000

drop table t1,t2,t3;

create table t1 (a int);

insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);

create table t2 (a int, key(a));

insert into t2 select 2*(A.a + 10*(B.a + 10*C.a)) from t1 A, t1 B, t1 C;

set in_predicate_conversion_threshold= 2000;

More results from queries before failure can be found in /home/travis/build/MariaDB/server/mysql-test/var/4/log/range_mrr_icp.log

 - saving '/home/travis/build/MariaDB/server/mysql-test/var/4/log/main.range_mrr_icp-innodb/' to '/home/travis/build/MariaDB/server/mysql-test/var/log/main.range_mrr_icp-innodb/'

Daniel Black added a comment - 2018-08-21 23:08 Test failures: https://api.travis-ci.org/v3/job/418873200/log.txt main.range 'innodb' w4 [ fail ] Test ended at 2018-08-21 20:32:18 CURRENT_TEST: main.range mysqltest: At line 873: query 'set in_predicate_conversion_threshold= 2000' failed: 1193: Unknown system variable 'in_predicate_conversion_threshold' The result from queries just before the failure was: < snip > 1 SIMPLE t1 range a a 13 NULL # Using index condition; Using where explain select * from t2 where a between 'a' and 'a '; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 ref a a 13 const # Using index condition explain select * from t2 where a = 'a' or a='a '; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 ref a a 13 const # Using index condition; Using where update t1 set a='b' where a<>'a'; explain select * from t1 where a not between 'b' and 'b'; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range a a 13 NULL # Using index condition select a, hex(filler) from t1 where a not between 'b' and 'b'; a hex(filler) a 0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 drop table t1,t2,t3; create table t1 (a int); insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); create table t2 (a int, key(a)); insert into t2 select 2*(A.a + 10*(B.a + 10*C.a)) from t1 A, t1 B, t1 C; set in_predicate_conversion_threshold= 2000; More results from queries before failure can be found in /home/travis/build/MariaDB/server/mysql-test/var/4/log/range.log - saving '/home/travis/build/MariaDB/server/mysql-test/var/4/log/main.range-innodb/' to '/home/travis/build/MariaDB/server/mysql-test/var/log/main.range-innodb/' main.join_outer_jcl6 w1 [ pass ] 9035 main.1st w1 [ pass ] 1 main.multi_update2 w2 [ pass ] 24053 main.aborted_clients w1 [ pass ] 107 main.range_innodb 'innodb' w4 [ pass ] 351 main.adddate_454 w1 [ pass ] 5 main.flush w3 [ pass ] 2929 main.alias w1 [ pass ] 27 main.almost_full w1 [ pass ] 42 main.range_mrr_icp 'innodb' w4 [ fail ] Test ended at 2018-08-21 20:32:19 CURRENT_TEST: main.range_mrr_icp mysqltest: In included file "/home/travis/build/MariaDB/server/mysql-test/main/range.test": included from /home/travis/build/MariaDB/server/mysql-test/main/range_mrr_icp.test at line 5: At line 873: query 'set in_predicate_conversion_threshold= 2000' failed: 1193: Unknown system variable 'in_predicate_conversion_threshold' The result from queries just before the failure was: < snip > 1 SIMPLE t1 range a a 13 NULL # Using index condition; Using where; Rowid-ordered scan explain select * from t2 where a between 'a' and 'a '; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 ref a a 13 const # Using index condition explain select * from t2 where a = 'a' or a='a '; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 ref a a 13 const # Using index condition; Using where update t1 set a='b' where a<>'a'; explain select * from t1 where a not between 'b' and 'b'; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range a a 13 NULL # Using index condition; Rowid-ordered scan select a, hex(filler) from t1 where a not between 'b' and 'b'; a hex(filler) a 0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 drop table t1,t2,t3; create table t1 (a int); insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); create table t2 (a int, key(a)); insert into t2 select 2*(A.a + 10*(B.a + 10*C.a)) from t1 A, t1 B, t1 C; set in_predicate_conversion_threshold= 2000; More results from queries before failure can be found in /home/travis/build/MariaDB/server/mysql-test/var/4/log/range_mrr_icp.log - saving '/home/travis/build/MariaDB/server/mysql-test/var/4/log/main.range_mrr_icp-innodb/' to '/home/travis/build/MariaDB/server/mysql-test/var/log/main.range_mrr_icp-innodb/'

Igor Babaev (Inactive) added a comment - 2018-08-26 17:16

After the patch for mdev-16930 buildbot shows some problems most probably to the pushed
fix for mdev-17017.

Igor Babaev (Inactive) added a comment - 2018-08-26 17:16 After the patch for mdev-16930 buildbot shows some problems most probably to the pushed fix for mdev-17017.

Igor Babaev (Inactive) added a comment - 2018-08-27 21:15

Another fix for this bug was pushed into 10.3 after which buildbot showed no problems with it.

Igor Babaev (Inactive) added a comment - 2018-08-27 21:15 Another fix for this bug was pushed into 10.3 after which buildbot showed no problems with it.

MariaDB Server

Explain for query using derived table specified with a table value constructor shows wrong number of rows

Details

Description

Attachments

Activity

People

Dates

Git Integration