New in Dynamic columns

In Maria DB 10 we are going to get folowing improvements to dynamic columns.

Name support

Now everywhere you used number to refer column it is possible to use string:

• create dynamic columns record:

COLUMN_CREATE("int", -1212 as int, "uint", 12334 as unsigned int, "decimal", "23.344" as decimal, "double", 1.23444e50 as double, "string", 'gdgd\\dhdjh"dhdhd' as char);

• alter dynamic column record (remove column with name "int" and add one with name "intint"):

COLUMN_ADD(dynamiccolumns, "int", NULL, "intint", 12);

• change format (from numeric to column with names):

COLUMN_ADD(COLUMN_CREATE(1,"string"), "1", NULL, "int", 12);

• checking column existence

COLUMN_EXISTS(dynamic_column, "column1");

• getting column value:

COLUMN_GET(dynstr, "column1" as char(10));

Changes in behavior

• Column list output now include quoting:

select column_list(column_create(1, 22, 2, 23));
+------------------------------------------+
| column_list(column_create(1, 22, 2, 23)) |
+------------------------------------------+
| `1`,`2`                                  |
+------------------------------------------+
select column_list(column_create('column1', 22, 'column2', 23)); 
+----------------------------------------------------------+
| column_list(column_create('column1', 22, 'column2', 23)) |
+----------------------------------------------------------+
| `column1`,`column2`                                      |
+----------------------------------------------------------+

• Column name interpretation changed so that string now is not converted to number. So some "magic" trick will not work any more, for example, "1test" and "1" now become different column names:

select column_list(column_add(column_create('1a', 22), '1b', 23));
+------------------------------------------------------------+
| column_list(column_add(column_create('1a', 22), '1b', 23)) |
+------------------------------------------------------------+
| `1a`,`1b`                                                  |
+------------------------------------------------------------+

Old behavior:

select column_list(column_add(column_create('1a', 22), '1b', 23));
+------------------------------------------------------------+
| column_list(column_add(column_create('1a', 22), '1b', 23)) |
+------------------------------------------------------------+
| 1                                                          |
+------------------------------------------------------------+

New function added

COLUMN_CHECK

This function should be used to check a column integrity. It do not return illegal format error but return false instead. Also it check integrity more thoroughly and finds errors in the dynamic column internal structures which could be skipped by other functions.

select column_check(column_create('column1', 22));
+--------------------------------------------+
| column_check(column_create('column1', 22)) |
+--------------------------------------------+
|                                          1 |
+--------------------------------------------+
select column_check('abracadabra');
+-----------------------------+
| column_check('abracadabra') |
+-----------------------------+
|                           0 |
+-----------------------------+

COLUMN_JSON

This function converts all dynamic column record content to JSON array of objects which are pairs NAME:VALUE.

select column_json(column_create('column1', 1, 'column2', "two"));
+------------------------------------------------------------+
| column_json(column_create('column1', 1, 'column2', "two")) |
+------------------------------------------------------------+
| [{"column1":1},{"column2":"two"}]                          |
+------------------------------------------------------------+

Interface with Cassandra

Some internal changes was added to dynamic columns to allow them to serve as interface to Apache Cassandra dynamic columns. Cassandra engine may pack all column which was not mentioned in the MariaDB interface table definition and even brinch chages in the dynamic column contence back to the cassandra columns family (table analog in cassandra).