Assertion `real->type() == Item::FIELD_ITEM' failed in add_not_null_conds(JOIN*) with JOIN, ZEROFILL column, PK

[Elena Stepanova]

mysqld: sql_select.cc:8001: void add_not_null_conds(JOIN*): Assertion `real->type() == Item::FIELD_ITEM' failed.

#6 0x00007fe89131bd4d in __GI___assert_fail (assertion=0xdf4ff8 "real->type() == Item::FIELD_ITEM", file=<optimized out>, line=8001, function=<optimized out>) at assert.c:81
#7 0x00000000007602e3 in add_not_null_conds (join=0x35d1470) at sql_select.cc:8001
#8 0x0000000000760a92 in make_join_select (join=0x35d1470, select=0x3604f18, cond=0x35d1280) at sql_select.cc:8230
#9 0x000000000074d53c in JOIN::optimize (this=0x35d1470) at sql_select.cc:1306
#10 0x0000000000753461 in mysql_select (thd=0x354a618, rref_pointer_array=0x354d258, tables=0x35d0398, wild_num=1, fields=..., conds=0x35d1280, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147764736, result=0x35d1450, unit=0x354cb00, select_lex=0x354d008) at sql_select.cc:2938
#11 0x000000000074a049 in handle_select (thd=0x354a618, lex=0x354ca60, result=0x35d1450, setup_tables_done_option=0) at sql_select.cc:285
#12 0x00000000006d6e5d in execute_sqlcom_select (thd=0x354a618, all_tables=0x35d0398) at sql_parse.cc:5151
#13 0x00000000006cdc25 in mysql_execute_command (thd=0x354a618) at sql_parse.cc:2284
#14 0x00000000006d98c5 in mysql_parse (thd=0x354a618, rawbuf=0x35d0190 "SELECT * FROM t1, t2 WHERE a=3 AND a=b", length=38, found_semicolon=0x7fe88861f7e8) at sql_parse.cc:6152
#15 0x00000000006cb395 in dispatch_command (command=COM_QUERY, thd=0x354a618, packet=0x35c6d29 "SELECT * FROM t1, t2 WHERE a=3 AND a=b", packet_length=38) at sql_parse.cc:1228
#16 0x00000000006ca67c in do_command (thd=0x354a618) at sql_parse.cc:923
#17 0x00000000006c7514 in handle_one_connection (arg=0x354a618) at sql_connect.cc:1218
#18 0x00007fe891e28efc in start_thread (arg=0x7fe888620700) at pthread_create.c:304
#19 0x00007fe8913d059d in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:112

bzr version-info
revision-id: <email address hidden>
date: 2012-05-02 22:02:17 +0200
build-date: 2012-05-04 02:23:14 +0400
revno: 3516

Reproducible with the default optimizer_switch as well as with all OFF values.
EXPLAIN also fails.
A release build crashes.

On MariaDB 5.5 (revno 3393) it looks a bit different, instead of an assertion it crashes with sig 11 in

#5 0x00000000007930fc in Field::make_field (this=0x2adcf60, field=0x2adcf60) at /home/elenst/maria-5.5/sql/field.cc:1580
#6 0x000000000079357f in Field_num::make_field (this=0x2adcf60, field=0x2adcf60) at /home/elenst/maria-5.5/sql/field.cc:1694
#7 0x00000000007d97cf in Item_int::eq (this=0x2ac6408, arg=0x2adcf60, binary_cmp=160) at /home/elenst/maria-5.5/sql/item.cc:5948
#8 0x00000000007b609d in Copy_field::get_copy_func (this=0x2adcee0, to=0x2adcf60, from=0x2ac6408) at /home/elenst/maria-5.5/sql/field_conv.cc:735
#9 0x00000000007b5e1b in Copy_field::set (this=0x2adcee0, to=0x2adcf60, from=0x2ac6408, save=false) at /home/elenst/maria-5.5/sql/field_conv.cc:696
#10 0x0000000000676b6a in store_key_field::change_source_field (this=0x2adceb8, fld_item=0x2ac6408) at /home/elenst/maria-5.5/sql/sql_select.h:1528
#11 0x000000000063c40f in JOIN::optimize (this=0x2ac6b78) at /home/elenst/maria-5.5/sql/sql_select.cc:1337
#

No assertion or crash on MySQL-5.5 or MySQL-trunk.

# Test case:

CREATE TABLE t1 ( a INT(6) ZEROFILL );
INSERT INTO t1 VALUES (1),(2);

CREATE TABLE t2 ( b INT PRIMARY KEY );
INSERT INTO t2 VALUES (3),(4);
SELECT * FROM t1, t2 WHERE a=3 AND a=b;

# End of test case

[Elena Stepanova (elenst)]

Also filed in JIRA as https://mariadb.atlassian.net/browse/MDEV-248

[Oleksandr "Sanja" Byelkin (sanja-byelkin)]

The problem is that constant 3 substitutes t2.b and add_not_null_conds() in the ref access methods expects field instead of constant 3 Item (which cause the assertion).