How do the fields in "SHOW COLUMNS" command map to specific tables?

Question

Here is a View called viewwithcommonfield :

SELECT 
        `schematopologytest01`.`talpha`.`CommonField` AS `CommonField_tAlpha`,
        `schematopologytest01`.`tbeta`.`CommonField` AS `CommonField_tBeta`
    FROM
        (`schematopologytest01`.`talpha`
        JOIN `schematopologytest01`.`tbeta`)

When I execute

SHOW FULL fields FROM viewwithcommonfield IN SchemaTopologyTest01

I get this:

How do I map the fields back to specific tables? Can I write a view against the tables in information_schema?

Here are the table structures that are referenced in the view. The tables share a common field called CommonField:

Bill Karwin · Answer 1 · 2017-11-26T01:09:14.523

No, there is no metadata available to map views of a column back to the original column in a base table. That would require multiple tables, because any given expression in the select-list may reference multiple columns from different tables.

Consider:

SELECT CONCAT(
  `schematopologytest01`.`talpha`.`AlphaFieldA`,
  `schematopologytest01`.`tbeta`.`BetaFieldE`) AS `ConcatenatedField`
FROM `schematopologytest01`.`talpha`
JOIN `schematopologytest01`.`tbeta` ON ...

Which table and column would ConcatenatedField list as its origin? It would have to be stored in two rows of another INFORMATION_SCHEMA table.

There are also select-list expressions possible in a view that don't reference any base table:

CREATE VIEW ViewNow AS SELECT NOW() AS `now`;

What about columns that are themselves scalar subqueries? Or references to stored functions? Or an aggregate function like COUNT() or SUM() where the value is not found in any base table?

Many views do not derive their data from base tables deterministically at all. Edit: What I mean is that it's not always possible to know which rows or columns are the source of data in a view, because they results are combined in some ways. It's probably more clear to say that reversing the query to get the original data is not always possible, depending on the query.

It's not possible to update those views. But if there were metadata about where the data "came from," there would have to be something in the metadata to indicate that. It would be impractical because it would be complex, and have little value.

"Many views do not derive their data from base tables deterministically at all. " whaaaa?? — nicomp, Nov 26 '17 at 00:57
For example, in any view with a GROUP BY or DISTINCT or UNION, and some kinds of JOINs, it's not possible to make the view updatable, because the source of the data is ambiguous or aggregated. This is what I mean. See my edit above for more clarification. — Bill Karwin, Nov 26 '17 at 01:07

How do the fields in "SHOW COLUMNS" command map to specific tables?

1 Answers1