[MDEV-12134] Condition is evaluated on an empty table Created: 2017-02-27  Updated: 2021-03-19

Status: Stalled
Project: MariaDB Server
Component/s: None
Affects Version/s: 10.2
Fix Version/s: 10.2

Type: Bug Priority: Minor
Reporter: Alexander Barkov Assignee: Sergei Petrunia
Resolution: Unresolved Votes: 0
Labels: None

Sprint: 10.2.7-1

 Description    

DROP TABLE IF EXISTS t1;
 
CREATE TABLE t1 (a INT) ENGINE=MyISAM;
 
SELECT a FROM t1 WHERE a IN(1, (SELECT IF(1=0,1,2/0)));
 
SHOW WARNINGS;


+---------+------+---------------+
 
| Level   | Code | Message       |
 
+---------+------+---------------+
 
| Warning | 1365 | Division by 0 |
 
+---------+------+---------------+

Notice, the IN predicate is evaluated on an empty table. Looks wrong. The record buffer is not initialized to any valid data in case of an empty table, so calling Item_field::val_xxx() is not correct.

Note, if I change ENGINE from MYISAM to InnoDB, the warning goes away.

 Comments   
Comment by Varun Gupta (Inactive) [ 2017-03-08 ]

Explain while using MyISAM table

explain
 
SELECT a FROM t1 WHERE a IN(1, (SELECT IF(1=0,1,2/0)));
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables

Explain while using InnoDB

CREATE TABLE t1 (a INT) ENGINE=InnoDB;
 
explain
 
SELECT a FROM t1 WHERE a IN(1, (SELECT IF(1=0,1,2/0)));
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	1	Using where

Comment by Sergei Petrunia [ 2017-03-09 ]

Results of mine and varun' discussion:

Confirm, it is evaluating Item_func_in although there's no current row or value of t1.a to evaluate it for: (Q: can this cause a wrong query result?)

* thread #2: tid = 0xb4fae, 0x0000000100072de0 mysqld`Item_field::val_int(this=0x000000010a249598) + 16 at item.cc:2754, stop reason = breakpoint 7.1
 
  * frame #0: 0x0000000100072de0 mysqld`Item_field::val_int(this=0x000000010a249598) + 16 at item.cc:2754
 
    frame #1: 0x00000001000bed25 mysqld`cmp_item_int::store_value(this=0x000000010a24baa8, item=0x000000010a249598) + 37 at item_cmpfunc.h:1419
 
    frame #2: 0x00000001000b1f74 mysqld`Item_func_in::val_int(this=0x000000010a24aa68) + 660 at item_cmpfunc.cc:4395
 
    frame #3: 0x00000001000f9f97 mysqld`eval_const_cond(cond=0x000000010a24aa68) + 39 at item_func.cc:79
 
    frame #4: 0x000000010043f86d mysqld`Item::remove_eq_conds(this=0x000000010a24aa68, thd=0x000000010a217430, cond_value=0x000000010a24afa8, top_level_arg=true) + 93 at sql_select.cc:15483
 
    frame #5: 0x000000010041592d mysqld`make_join_statistics(join=0x000000010a24aca0, tables_list=0x000000010a21b810, keyuse_array=0x000000010a24af88) + 7133 at sql_select.cc:4190
 
    frame #6: 0x000000010040e4ed mysqld`JOIN::optimize_inner(this=0x000000010a24aca0) + 5917 at sql_select.cc:1501
 
    frame #7: 0x000000010040cba3 mysqld`JOIN::optimize(this=0x000000010a24aca0) + 67 at sql_select.cc:1081
 
    frame #8: 0x0000000100408c9f mysqld`mysql_select(thd=0x000000010a217430, tables=0x000000010a248f70, wild_num=0, fields=0x000000010a21b768, conds=0x000000010a24aa68, og_num=0, order=0x0000000000000000, group=0x0000000000000000, having=0x0000000000000000, proc_param=0x0000000000000000, select_options=2147748608, result=0x000000010a24ac80, unit=0x000000010a21af10, select_lex=0x000000010a21b640) + 1167 at sql_select.cc:3633
 
    frame #9: 0x000000010040859f mysqld`handle_select(thd=0x000000010a217430, lex=0x000000010a21ae48, result=0x000000010a24ac80, setup_tables_done_option=0) + 591 at sql_select.cc:361

Happens on MyISAM const-empty tables only, so this is not a very important bug.

Comment by Varun Gupta (Inactive) [ 2017-03-10 ]

I see same stack trace in 10.1 that is the where clause is evaluated for an empty table. The only difference is we dont get the warning in 10.1

CREATE TABLE t1 (a INT) ENGINE=MYISAM;
 
SELECT a FROM t1 WHERE a IN(1, (SELECT IF(1=0,1,2/0)));
 
a
 
SHOW WARNINGS;
 
Level	Code	Message
 
drop table t1;
 
main.bug-12134                           [ pass ]      3

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