Elena Stepanova added a comment - 2019-12-03 13:15 Thanks for the report and test case. Reproducible as described on 10.3-10.5. Not reproducible on 10.2.

Oleksandr Byelkin added a comment - 2020-04-29 12:35 Something wrong with USING, but it is not just conversion: create table t (i int, constraint a check (i > 0)); select tc.TABLE_SCHEMA, tc.TABLE_NAME, cc.CONSTRAINT_NAME, cc.CHECK_CLAUSE from information_schema.TABLE_CONSTRAINTS tc join information_schema.CHECK_CONSTRAINTS cc using (CONSTRAINT_CATALOG, CONSTRAINT_SCHEMA, TABLE_NAME, CONSTRAINT_NAME) ; TABLE_SCHEMA TABLE_NAME CONSTRAINT_NAME CHECK_CLAUSE explain extended select tc.TABLE_SCHEMA, tc.TABLE_NAME, cc.CONSTRAINT_NAME, cc.CHECK_CLAUSE from information_schema.TABLE_CONSTRAINTS tc join information_schema.CHECK_CONSTRAINTS cc using (CONSTRAINT_CATALOG, CONSTRAINT_SCHEMA, TABLE_NAME, CONSTRAINT_NAME) ; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE tc ALL NULL NULL NULL NULL NULL NULL Open_full_table; Scanned all databases 1 SIMPLE cc ALL NULL NULL NULL NULL NULL NULL Using where; Open_full_table; Scanned all databases; Using join buffer (flat, BNL join) Warnings: Note 1003 select `tc`.`TABLE_SCHEMA` AS `TABLE_SCHEMA`,`tc`.`TABLE_NAME` AS `TABLE_NAME`,`cc`.`CONSTRAINT_NAME` AS `CONSTRAINT_NAME`,`cc`.`CHECK_CLAUSE` AS `CHECK_CLAUSE` from `information_schema`.`TABLE_CONSTRAINTS` `tc` join `information_schema`.`CHECK_CONSTRAINTS` `cc` where `cc`.`CONSTRAINT_CATALOG` = `tc`.`CONSTRAINT_CATALOG` and `cc`.`CONSTRAINT_SCHEMA` = `tc`.`CONSTRAINT_SCHEMA` and `cc`.`CONSTRAINT_NAME` = `tc`.`CONSTRAINT_NAME` and `cc`.`TABLE_NAME` = `tc`.`TABLE_NAME` select `tc`.`TABLE_SCHEMA` AS `TABLE_SCHEMA`,`tc`.`TABLE_NAME` AS `TABLE_NAME`,`cc`.`CONSTRAINT_NAME` AS `CONSTRAINT_NAME`,`cc`.`CHECK_CLAUSE` AS `CHECK_CLAUSE` from `information_schema`.`TABLE_CONSTRAINTS` `tc` join `information_schema`.`CHECK_CONSTRAINTS` `cc` where `cc`.`CONSTRAINT_CATALOG` = `tc`.`CONSTRAINT_CATALOG` and `cc`.`CONSTRAINT_SCHEMA` = `tc`.`CONSTRAINT_SCHEMA` and `cc`.`CONSTRAINT_NAME` = `tc`.`CONSTRAINT_NAME` and `cc`.`TABLE_NAME` = `tc`.`TABLE_NAME` ; TABLE_SCHEMA TABLE_NAME CONSTRAINT_NAME CHECK_CLAUSE test t a `i` > 0 select tc.TABLE_SCHEMA, tc.TABLE_NAME, cc.CONSTRAINT_NAME, cc.CHECK_CLAUSE from information_schema.TABLE_CONSTRAINTS tc, information_schema.CHECK_CONSTRAINTS cc where cc.CONSTRAINT_CATALOG=tc.CONSTRAINT_CATALOG and cc.CONSTRAINT_SCHEMA= tc.CONSTRAINT_SCHEMA and cc.TABLE_NAME = tc.TABLE_NAME and cc.CONSTRAINT_NAME = tc.CONSTRAINT_NAME ; TABLE_SCHEMA TABLE_NAME CONSTRAINT_NAME CHECK_CLAUSE test t a `i` > 0

Oleksandr Byelkin added a comment - 2020-04-30 11:08 in JOIN_TAB::calc_used_field_length I see very strange field referecces with 0 pack_length inherited from Field_null...

Oleksandr Byelkin added a comment - 2020-05-11 11:12 create_schema_table() creates Item_empty_stringwith maximum length 0 and following code creates Field_null for the Item Field *Item::create_field_for_schema(THD *thd, TABLE *table) { if (field_type() == MYSQL_TYPE_VARCHAR) { Field *field; if (max_length > MAX_FIELD_VARCHARLENGTH) field= new (thd->mem_root) Field_blob(max_length, maybe_null, &name, collation.collation); else if (max_length > 0) field= new (thd->mem_root) Field_varstring(max_length, maybe_null, &name, table->s, collation.collation); else field= new Field_null((uchar*) 0, 0, Field::NONE, &name, collation.collation); if (field) field->init(table); return field; } return tmp_table_field_from_field_type(table); }

Oleksandr Byelkin added a comment - 2020-05-11 12:04 It looks like the problem caused by e2664ee8362a94335df06edfc86936ff263d6dc0

Oleksandr Byelkin added a comment - 2020-05-11 12:13 mark_all_fields_used_in_query() take into acount all Items, but it do not take into account USING fields which have no items yet

Oleksandr Byelkin added a comment - 2020-05-12 11:06 Check natural join, probably we have probem with it

Oleksandr Byelkin added a comment - 2020-05-12 16:09 - edited probably we have to have to allow matching with other table fields names in case of natural join

Oleksandr Byelkin added a comment - 2020-07-08 14:31 gin/bb-10.3- MDEV-21201 ) Author: Oleksandr Byelkin <sanja@mariadb.com> Date: Wed Jul 8 16:26:34 2020 +0200 MDEV-21201 No records produced in information_schema query, depending on projection In case of USING check also join field list to mark fields as used. In case of NATURAL JOIN mark all field (one table can not be opened in any case so optimisation does not worth it). IMHO table should be checked for used fields and filled after prepare, when we will fave whole info about used fields but it is too big change for a bugfix.

Sergei Golubchik added a comment - 2020-07-28 08:45 for now, please, just add something like if (natural join or union ) birtmap_set_all(&bitmap); that is, disable the MDEV-14275 optimization in this case. I already have a fix that does it correctly in all applicable cases, I hope it can be pushed soon.

Oleksandr Byelkin added a comment - 2020-07-31 12:14 Temporary patch (not so efficient) pushed to 10.3. Proper fix will go later (next release).