On Mon, May 14, 2012 at 04:35:10PM +0400, Sergei Petrunia wrote:
A general question: was this expected to work:
MariaDB [j3]> explain extended select * from t1 where 33 in (select b from five) or c > 11; +------+--------------------+-------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +------+--------------------+-------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | PRIMARY | t1 | ALL | NULL | NULL | NULL | NULL | 10 | 100.00 | Using where | | 2 | DEPENDENT SUBQUERY | five | ALL | NULL | NULL | NULL | NULL | 5 | 100.00 | Using where | +------+--------------------+-------+------+---------------+------+---------+------+------+----------+-------------+ 2 rows in set, 1 warning (22 min 25.44 sec)
The subuqery is constant, table `five` has 5 records, and none of them has b=33. When debugging, I see JOIN::get_examined_rows() to be invoked and it returns 5 rows, but EXPLAIN still doesn't show "Impossible WHERE"?
Full example: create table ten (a int); insert into ten values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); create table t1 (a int, b int, c int); insert into t1 select a,a,a from ten; create table five (a int, b int, c int); insert into five select a,a,a from ten limit 5; BR Sergei -- Sergei Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog