Suggested way to resolve column name/type in a view

Question

I have the following problem that I'm trying to find the best solution for. Let's say I have a view such as the following:

CREATE VIEW myView AS (
    SELECT 
        country_code, 
        other_column,
        COUNT(1) as cnt 
    FROM mytable 
         JOIN otherDatabase.otherTable ON (id)
    GROUP BY 1,2 ORDER BY 1 LIMIT 1
)

What would be the fastest way to resolve the field names and types of the view? For example, on the above I am looking to get something along the lines of:

{
    country_code: VARCHAR, 
    other_column: BOOL,
    cnt:          INT
}

The first approach is just to run the query (with a limit, if necessary) and then get the types of the result-set from the driver. The downside of this is what if the query takes 50 minutes to resolve?
The second approach I thought of is to 'follow' the columns to get their types and then do some parsing to resolve any expressions/literals/etc. This would involve a lot of code but would be orders of magnitude faster than the above. However, the potential downside of this is we may have access to the view but not have access to a table (possibly in another database on the server) that contains the column type, so it's possible we might not be able to resolve all field names.

What would be the best way to resolve the types of a view? Note I have tagged this as MySQL, but I'm also wondering if there's a more generic way to resolve types or if it's something that is non-standard and more needs to be done on a per-database basis?

Update: I believe the correct answer is just to run a DESCRIBE myView, and that would give me the column names and types without running the query?

@jarlh I don't get it. Could you please elaborate on how that would work. I'm open to using any Programming API that helps to solve the issue... — David542, Apr 25 '22 at 20:40
E.g https://stackoverflow.com/questions/696782/retrieve-column-names-from-java-sql-resultset — jarlh, Apr 25 '22 at 20:43
@jarlh of course, that is the first approach I named. And then I said `The downside of this is what if the query takes 50 minutes to resolve?` — David542, Apr 25 '22 at 20:49
If a view column contains only numeric values but the underlying table column is a varchar (which, obviously, can contain numbers), is the view column numeric or varchar? — NickW, Apr 25 '22 at 20:54
@NickW it would be varchar in that case, in other words I'm looking for the underlying type. Same with if all the values were null. — David542, Apr 25 '22 at 20:55
(1) add `and 1=0` to the query that uses the view, (2) execute the query, (3) get the column definitions from the result set. — Jeff Holt, Apr 25 '22 at 20:59
What happens if a column is calculated in a view, and the calculation uses multiple columns of different datatypes? — NickW, Apr 25 '22 at 21:01
@NickW wouldn't that just be the same as any other query's result-set? `SELECT CAST(field AS othertype)...` — David542, Apr 25 '22 at 21:03
I've been trying to figure out why knowing the column descriptions *before* fetching the first row is necessary. Example: Oracle's Call Interface allows the OCI programmer to describe after a parse but I doubt anyone's writing OCI C programs these days. — Jeff Holt, Apr 25 '22 at 21:55
@JeffHolt it's for the query-editor as a helper to the user who may be entering in the query, for example what fields/columns that view exposes. — David542, Apr 25 '22 at 22:00
Dynamic embedded SQL has DESCRIBE OUTPUT, to get the result columns for an arbitrary SELECT. (Without executing the query.) — jarlh, Apr 26 '22 at 06:13

score 3 · Accepted Answer · answered Apr 25 '22 at 22:15

3

In the current version of MySQL at least, INFORMATION_SCHEMA.COLUMNS holds metadata for views as well as base tables:

mysql> create table mytable (id serial primary key, x int);
Query OK, 0 rows affected (0.01 sec)

mysql> create view v as select * from mytable;

mysql> select column_name, data_type from information_schema.columns where table_name='v';
+-------------+-----------+
| COLUMN_NAME | DATA_TYPE |
+-------------+-----------+
| id          | bigint    |
| x           | int       |
+-------------+-----------+

answered Apr 25 '22 at 22:15

Bill Karwin

538,548
86
673
828

cool, and that can also be done with doing `desc v` or `show columns from v` right? – David542 Apr 25 '22 at 22:16
Try it! `describe v;` – Bill Karwin Apr 25 '22 at 22:17

score 0 · Answer 2 · answered Apr 27 '22 at 22:08

0

A related issue...

SHOW CREATE TABLE myView;

or

SHOW CREATE VIEW myView;

will fully qualify all the columns.

(When writing a JOIN, it is wise to always qualify the column names.)

answered Apr 27 '22 at 22:08

Rick James

135,179
13
127
222

Suggested way to resolve column name/type in a view

2 Answers2