User-friendly syntax for dynamic columns

(These are the notes taken from discussion with serg)

The idea is to make syntax more user-friendly: allow "direct" access to dynamic
columns:

SELECT dynamic_column1, dynamic_column2 FROM table1, table2 WHERE ... 

This can be made to work via a "field discovery" process, similar to a table discovery.

• During the name resolution, for all unresolved columns the server tries to "discover" them.
• Basically it asks all tables what type the column X has.
• The table can simply return an error (column not found)
• Or, if it is a dynamic column, the table can return its type
  • in a special case (that's how most NoSQL databases work) the table cannot know a column with a particular name exists in any of the rows. So it simply returns a default type, say, VARCHAR(255) or TEXT for any column X discovery request. If the column will later happen out to be numeric and it will be used in a numeric context, we use item->val_int() anyway, so there should be no number->string->number conversion.
• The server needs to ask all participating engines. It might happen that the column will be discovered in more than one table. This is a usual "ambiguous column name" SQL error. And the workaround is as usual - the user needs to qualify the column name with a table name.
• there might be a default implementation of dynamic columns in the base handler class. this implementation will create a shadow BLOB column and will use ADD_COLUMN/GET_COLUMN encoding to read and store the data.

After a column is resolved, a Field object is created for it, record[0] and read_map/etc are expanded accordingly and the rest of the server code doesn't see any difference between dynamically and statically declared columns.

DDL for tables with dynamic columns

There are two possibilities.

1. One can explicitly declare a table as having dynamic columns:

CREATE TABLE t1

( 

   column1 INT,

   ...  -- this means "and a set of dynamic columns"

);

Or

CREATE TABLE t1 ( ... ) DYNAMIC_COLUMNS=ON;

And here's a way to specify default column type for dynamic columns (see "a special case" above)

CREATE TABLE t1

( 

   column1 INT,

   ... CHAR(N)

);

2. One does not need to declare anything specially. If the engine supports dynamic columns (e.g. storage engine, based on a NoSQL database) - they are always available. If the engine does not support them - they aren't available. This does not work very well if we'll have a default implementation in the handler class.

various concerns

• Will row-based replication work with this?
• increasing record buffer and bitmaps may be expensive. Is there a cheaper solution for hiding dynamic columns from the rest of the server code?
• What to do for SELECT *?
  • variants: error. only show static columns
• How to see all columns in a row?
  • use LIST_COLUMNS function?