[MDEV-21201] No records produced in information_schema query, depending on projection Created: 2019-12-03  Updated: 2023-11-27  Resolved: 2020-07-31

Status: Closed
Project: MariaDB Server
Component/s: Information Schema
Affects Version/s: 10.4.10, 10.3, 10.4
Fix Version/s: 10.3.24, 10.4.14, 10.5.5

Type: Bug Priority: Critical
Reporter: Lukas Eder Assignee: Oleksandr Byelkin
Resolution: Fixed Votes: 1
Labels: None

Attachments: PNG File screenshot-1.png     PNG File screenshot-2.png    
Issue Links:
Duplicate
duplicates MDEV-19990 JOIN USING not behaving like 'equals' Closed
is duplicated by MDEV-24466 logic bug when joining INFROMATION_SC... Closed

 Description   

Create a table with a check constraint:

create table t (i int, constraint a check (i > 0));

Now consider this query, which should produce the above check constraint:

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)
 
;

It does not produce any results.

Add two columns to the projection and ta-dah, we get the desired output:

select 
  tc.TABLE_SCHEMA, 
  tc.TABLE_NAME, 
  cc.CONSTRAINT_NAME, 
  cc.CHECK_CLAUSE,
 
  tc.CONSTRAINT_CATALOG, 
  tc.CONSTRAINT_SCHEMA
 
from information_schema.TABLE_CONSTRAINTS tc
 
  join information_schema.CHECK_CONSTRAINTS cc
 
    using (CONSTRAINT_CATALOG, CONSTRAINT_SCHEMA, TABLE_NAME, CONSTRAINT_NAME)
 
;



 Comments   
Comment by Elena Stepanova [ 2019-12-03 ]

Thanks for the report and test case. Reproducible as described on 10.3-10.5. Not reproducible on 10.2.

Comment by Oleksandr Byelkin [ 2020-04-29 ]

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

Comment by Oleksandr Byelkin [ 2020-04-30 ]

in JOIN_TAB::calc_used_field_length I see very strange field referecces with 0 pack_length inherited from Field_null...

Comment by Oleksandr Byelkin [ 2020-05-11 ]

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);
 
}

Comment by Oleksandr Byelkin [ 2020-05-11 ]

It looks like the problem caused by e2664ee8362a94335df06edfc86936ff263d6dc0

Comment by Oleksandr Byelkin [ 2020-05-11 ]

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

Comment by Oleksandr Byelkin [ 2020-05-12 ]

Check natural join, probably we have probem with it

Comment by Oleksandr Byelkin [ 2020-05-12 ]

probably we have to have to allow matching with other table fields names in case of natural join

Comment by Oleksandr Byelkin [ 2020-07-08 ]

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.

Comment by Sergei Golubchik [ 2020-07-28 ]

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.

Comment by Oleksandr Byelkin [ 2020-07-31 ]

Temporary patch (not so efficient) pushed to 10.3. Proper fix will go later (next release).

Generated at Thu Feb 08 09:05:21 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.