
column_name as type requires that one specify the datatype of the dynamic
column they are reading.

This may seem counter-intuitive: why would one need to specify which datatype
they're retrieving? Can't the dynamic columns system figure the datatype from
the data being stored?

The answer is: SQL is a statically-typed language. The SQL interpreter needs
to know the datatypes of all expressions before the query is run (for example,
when one is using prepared statements and runs "select COLUMN_GET(...)", the
prepared statement API requires the server to inform the client about the
datatype of the column being read before the query is executed and the server
can see what datatype the column actually has).

See the Datatypes section for more information about datatypes.

COLUMN_DELETE
-------------

COLUMN_DELETE(dyncol_blob, column_nr, column_nr...);
COLUMN_DELETE(dyncol_blob, column_name, column_name...);

Delete a dynamic column with the specified name. Multiple names can be given.

The return value is a dynamic column blob after the modification.

COLUMN_EXISTS
-------------

COLUMN_EXISTS(dyncol_blob, column_nr);
COLUMN_EXISTS(dyncol_blob, column_name);

Check if a column with name column_name exists in dyncol_blob. If yes, return
1, otherwise return 0.

COLUMN_LIST
-----------

COLUMN_LIST(dyncol_blob);

Return a comma-separated list of column names. The names are quoted with
backticks.

SELECT column_list(column_create('col1','val1','col2','val2'));
+---------------------------------------------------------+
| column_list(column_create('col1','val1','col2','val2')) |
+---------------------------------------------------------+
| `col1`,`col2`                                           |
+---------------------------------------------------------+

COLUMN_CHECK
------------

COLUMN_CHECK(dyncol_blob);

Check if dyncol_blob is a valid packed dynamic columns blob. Return value of 1
means the blob is valid, return value of 0 means it is not.

Rationale: Normally, one works with valid dynamic column blobs. Functions like
COLUMN_CREATE, COLUMN_ADD, COLUMN_DELETE always return valid dynamic column
blobs. However, if a dynamic column blob is accidentally truncated, or
transcoded from one character set to another, it will be corrupted. This
function can be used to check if a value in a blob field is a valid dynamic
column blob.

Note: It is possible that a truncation cut a Dynamic Column "clearly" so that
COLUMN_CHECK will not notice the corruption, but in any case of truncation a
warning is issued during value storing.

COLUMN_JSON
-----------

COLUMN_JSON(dyncol_blob);

Return a JSON representation of data in dyncol_blob.

Example:

SELECT item_name, COLUMN_JSON(dynamic_cols) FROM assets;
+-----------------+----------------------------------------+
| item_name       | COLUMN_JSON(dynamic_cols)              |
+-----------------+----------------------------------------+
| MariaDB T-shirt | {"size":"XL","color":"blue"}           |
| Thinkpad Laptop | {"color":"black","warranty":"3 years"} |
+-----------------+----------------------------------------+

Limitation: COLUMN_JSON will decode nested dynamic columns at a nesting level
of not more than 10 levels deep. Dynamic columns that are nested deeper than
10 levels will be shown as BINARY string, without encoding.

Nesting Dynamic Columns
-----------------------

It is possible to use nested dynamic columns by putting one dynamic column
blob inside another. The COLUMN_JSON function will display nested columns.

SET @tmp= column_create('parent_column', 
 column_create('child_column', 12345));
Query OK, 0 rows affected (0.00 sec)

SELECT column_json(@tmp);
+------------------------------------------+
| column_json(@tmp)                        |
+------------------------------------------+
| {"parent_column":{"child_column":12345}} |
+------------------------------------------+

SELECT column_get(column_get(@tmp, 'parent_column' AS char), 
 'child_column' AS int);
+------------------------------------------------------------------------------

| column_get(column_get(@tmp, 'parent_column' as char), 'child_column' as int)
|
+------------------------------------------------------------------------------

|                                                                        12345
|
+------------------------------------------------------------------------------

If you are trying to get a nested dynamic column as a string use 'as BINARY'
as the last argument of COLUMN_GET (otherwise problems with character set
conversion and illegal symbols are possible):

select column_json( column_get(
 column_create('test1',
  column_create('key1','value1','key2','value2','key3','value3')),
 'test1' as BINARY));

Datatypes
---------

In SQL, one needs to define the type of each column in a table. Dynamic
columns do not provide any way to declare a type in advance ("whenever there
is a column 'weight', it should be integer" is not possible). However, each
particular dynamic column value is stored together with its datatype.

The set of possible datatypes is mostly the same as that used by the SQL CAST
and CONVERT functions. However, note that there are currently some differences
- see MDEV-597.

+--------+----------------------------------------------+-------------------+
| type   | dynamic column internal type                 | description       |