MDEV-21201 No records produced in information_schema query, depending on projectionbb-10.3-MDEV-21201

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.

5 files changed, 158 insertions, 2 deletions

diff --git a/mysql-test/main/information_schema.result b/mysql-test/main/information_schema.result
index 62a563bf483..20b5985deb6 100644
--- a/mysql-test/main/information_schema.result
+++ b/mysql-test/main/information_schema.result
@@ -2201,3 +2201,67 @@ SCHEMA_NAME
 #
 # End of 10.1 tests
 #
+#
+# MDEV-21201:No records produced in information_schema query,
+# depending on projection
+#
+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
+test	t	a	`i` > 0
+select
+tc.TABLE_SCHEMA,
+tc.TABLE_NAME,
+cc.CONSTRAINT_NAME,
+cc.CHECK_CLAUSE
+from information_schema.CHECK_CONSTRAINTS cc
+join information_schema.TABLE_CONSTRAINTS tc
+using (CONSTRAINT_CATALOG, CONSTRAINT_SCHEMA, TABLE_NAME, CONSTRAINT_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
+NATURAL join information_schema.CHECK_CONSTRAINTS cc
+;
+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.CHECK_CONSTRAINTS cc
+NATURAL join information_schema.TABLE_CONSTRAINTS tc
+;
+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,
+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)
+;
+TABLE_SCHEMA	TABLE_NAME	CONSTRAINT_NAME	CHECK_CLAUSE	CONSTRAINT_CATALOG	CONSTRAINT_SCHEMA
+test	t	a	`i` > 0	def	test
+drop table t;
+#
+# End of 10.3 tests
+#
diff --git a/mysql-test/main/information_schema.test b/mysql-test/main/information_schema.test
index 08eeef5aa90..d97aed0e0f2 100644
--- a/mysql-test/main/information_schema.test
+++ b/mysql-test/main/information_schema.test
@@ -1922,3 +1922,65 @@ SELECT SCHEMA_NAME from information_schema.schemata where schema_name=REPEAT('a'
 --echo #
 --echo # End of 10.1 tests
 --echo #
+
+
+--echo #
+--echo # MDEV-21201:No records produced in information_schema query,
+--echo # depending on projection
+--echo #
+
+create table t (i int, constraint a check (i > 0));
+
+--disable_warnings
+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)
+;
+select
+  tc.TABLE_SCHEMA,
+  tc.TABLE_NAME,
+  cc.CONSTRAINT_NAME,
+  cc.CHECK_CLAUSE
+from information_schema.CHECK_CONSTRAINTS cc
+  join information_schema.TABLE_CONSTRAINTS tc
+    using (CONSTRAINT_CATALOG, CONSTRAINT_SCHEMA, TABLE_NAME, CONSTRAINT_NAME)
+;
+select
+  tc.TABLE_SCHEMA,
+  tc.TABLE_NAME,
+  cc.CONSTRAINT_NAME,
+  cc.CHECK_CLAUSE
+from information_schema.TABLE_CONSTRAINTS tc
+  NATURAL join information_schema.CHECK_CONSTRAINTS cc
+;
+select
+  tc.TABLE_SCHEMA,
+  tc.TABLE_NAME,
+  cc.CONSTRAINT_NAME,
+  cc.CHECK_CLAUSE
+from information_schema.CHECK_CONSTRAINTS cc
+  NATURAL join information_schema.TABLE_CONSTRAINTS tc
+;
+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)
+;
+--enable_warnings
+
+drop table t;
+
+--echo #
+--echo # End of 10.3 tests
+--echo #
diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc
index ead6235461e..a7a6b660dd6 100644
--- a/sql/sql_parse.cc
+++ b/sql/sql_parse.cc
@@ -8894,6 +8894,9 @@ void add_join_natural(TABLE_LIST *a, TABLE_LIST *b, List<String> *using_fields,
                       SELECT_LEX *lex)
 {
   b->natural_join= a;
+  a->part_of_natural_join= TRUE;
+  b->join_using= using_fields;
+  a->join_using= using_fields;
   lex->prev_join_using= using_fields;
 }
 
diff --git a/sql/sql_show.cc b/sql/sql_show.cc
index 3dbc7724928..52b367dfe4b 100644
--- a/sql/sql_show.cc
+++ b/sql/sql_show.cc
@@ -8094,7 +8094,8 @@ static void
 mark_all_fields_used_in_query(THD *thd,
                               ST_FIELD_INFO *schema_fields,
                               MY_BITMAP *bitmap,
-                              Item *all_items)
+                              Item *all_items,
+                              List<String> *join_using)
 {
   Item *item;
   DBUG_ENTER("mark_all_fields_used_in_query");
@@ -8132,6 +8133,25 @@ mark_all_fields_used_in_query(THD *thd,
       }
     }
   }
+  if (join_using)
+  {
+    List_iterator_fast<String> it(*join_using);
+    String *nm;
+    while ((nm= it++))
+    {
+      ST_FIELD_INFO *fields= schema_fields;
+      uint count;
+      for (count=0; fields->field_name; fields++, count++)
+      {
+        if (!my_strcasecmp(system_charset_info, fields->field_name,
+                           nm->ptr()))
+        {
+          bitmap_set_bit(bitmap, count);
+          break;
+        }
+      }
+    }
+  }
   DBUG_VOID_RETURN;
 }
 
@@ -8183,7 +8203,12 @@ TABLE *create_schema_table(THD *thd, TABLE_LIST *table_list)
   else
     all_items= thd->free_list;
 
-  mark_all_fields_used_in_query(thd, fields_info, &bitmap, all_items);
+  if ((table_list->natural_join || table_list->part_of_natural_join) &&
+      table_list->join_using == NULL)
+    bitmap_set_all(&bitmap);
+  else
+    mark_all_fields_used_in_query(thd, fields_info, &bitmap, all_items,
+                                  table_list->join_using);
 
   for (field_count=0; fields_info->field_name; fields_info++)
   {
diff --git a/sql/table.h b/sql/table.h
index d289b6e0ab2..a0a535dbf31 100644
--- a/sql/table.h
+++ b/sql/table.h
@@ -2182,6 +2182,8 @@ struct TABLE_LIST
     parsing 'this' is a NATURAL/USING join iff (natural_join != NULL).
   */
   TABLE_LIST *natural_join;
+  List<String> *join_using;
+  bool part_of_natural_join;
   /*
     True if 'this' represents a nested join that is a NATURAL JOIN.
     For one of the operands of 'this', the member 'natural_join' points