[MDEV-23631] As a subquery running the command "DESC <tempoprary table name>" Created: 2020-08-31  Updated: 2020-09-06  Resolved: 2020-08-31

Status: Closed
Project: MariaDB Server
Component/s: Data Manipulation - Subquery
Fix Version/s: N/A

Type: Task Priority: Minor
Reporter: Harun Tegmen Assignee: Sergei Golubchik
Resolution: Won't Fix Votes: 0
Labels: optimizer


 Description   

In subqueries SELECT command is supported.
Although DESC also provides a result set, it cannot be used as a subquery.

In order to evaluate the columns of a temporary table,
I need the columns names/types as cursor that
provided by DESC <table name> command.

If DESC command can be used as a subquery
it would be able to applicated to a "for loop" like this:

for REC in (DESC <table name>) do
– REC.Field, REC.Type, REC.Null
. . .
end for;

or to a "create temporary table" command like:

create temporary table Temp1 as
select * from (DESC <table name>) CC;

 Comments   
Comment by Sergei Golubchik [ 2020-08-31 ]

select from INFORMATION_SCHEMA.COLUMNS table

Comment by Harun Tegmen [ 2020-09-01 ]

select ... from information_schema.columns
provides column information only about STATIC tables,
but does not column information of TEMPORARY tables.

For example:
create TEMPORARY table Table1 (id int, exp char(30) );
select column_name Field,
column_type Type,
is_nullable "Null",
column_key "Key",
column_default "Default",
extra Extra
from information_schema.columns
where table_name = "Table1"
and table_schema = database();

returns an EMPTY RESULT SET,

only desc Table1; lists the column information of TEMPORARY table Table1 succesfully.

How can be obtained column information of a TEMPORARY table as a server side CURSOR ?

If the command "desc Table1;" can be used in subqueries, this will be achieved by direct way.

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