← Back to team overview

maria-discuss team mailing list archive

Re: The future of Dynamic Columns


This discussion reminds me of 6-8 years back when XML was introduced in
MySQL ("LOAD DATA XML, "mysqldump --xml", XML functions (ExtractValue
etc.).  It was a *big hype* at the time it was introduced. Now it seems to
be an almost forgotten feature used by close_to_nobody.

What should a JSON option be used for? Passing data series to
javascript/HTML5 would be relevant (?). But there are probably many more
use cases - including sharing data with NoSQL systems (MongoDB etc.). And
each use case may have its own requirements. I think the first thing to
define is the*audience* and *target*/*purpose* for such feature and discuss
external interfaces rather than internals at this point.

-- Peter

On Wed, Jan 28, 2015 at 4:47 PM, Roberto Spadim <roberto@xxxxxxxxxxxxx>

> i will give some ideas , some could be stupid but that's just ideas...
> 1)could be nice a json language as data results, for example
> SELECT {'some array':'some value',column1:column2} AS column1
> FROM some_table
> the result of {} is a string utf8, but well know as json format, the sql
> server just interpret it and return to client as string, at client side we
> can include a json feature to auto translate or not, internally we could
> use MDC format, for example...
> 2)SELECT dynamic_column AS column1   <- that's the main problem
> FROM some_table
> it could return the json format of dynamic column
> how? i don't know..., maybe include a flag at column (CREATE TABLE) to
> check if the column value is dynamic column or not?
> or at client side, a funtion to translate it, check that in this example
> MDC format to client side isn't a "good" idea (since JSON is human
> readable), client side know better how to use json, but maybe at storage
> level MDC is smaller than json, must think about it...
> 3) INSERT INTO table (string_column) VALUES ({'some_array':'some value'});
> in this example {} is auto translated to MDC
> others problems....
> WHERE column = {'a':'b','c':'d'}   <- auto translate to MDC string, check
> that we will have problems since: {'c':'d','a':'b'}!=={'a':'b','c':'d'},
> maybe we should include a new operator? === <- identical, ==/= <- equal ?
> the diference is object keys order
> 4)WHERE column LIKE {'some_array':'abc'} <- what should be done? jpath
> search?! maybe we should include a new function "SEARCH" WHERE column
> SEARCH {'some_array':'abc'}?
> 2015-01-28 13:16 GMT-02:00 Sergei Golubchik <serg@xxxxxxxxxxx>:
>> Hi, Tom!
>> On Jan 27, Tom Worster wrote:
>> >  2. In at least two places (one was here
>> > <
>> https://mariadb.com/kb/en/mariadb/indexoptimizations-in-dynamic-columns/
>> >)
>> >  I've read text with the implied meaning, "we may improve such-and-such
>> > about MDC if and when people start actually using it."
>> While I know that there are users of dynamic columns, I don't remember
>> seeing any feature requests for them.
>> Okay, there was one, for JSON-like arrays, but it depends on having
>> array as a data type, like in regular columns. Not a standalone feature
>> for dynamic columns that we can just go no and implement.
>> >   3. The MDC syntax is so clunky I worry that people who might find it
>> > useful are turned away.
>> That could as well be. And it's bad. What is worse - they're turned away
>> without complaining so we don't know what to do to fix dynamic columns.
>> Any suggestions how we can make the syntax less clunky?
>> >   4. Did the 5.3 version with numbers for column names turn people off
>> >   MDC for good?
>> As far as I have heard, those people who wanted dynamic columns in the
>> firts place found the implementation with numbers quite sufficient for
>> their needs. But new users probably didn't, I know :)
>> >   5. The requirement to specify datatype when querying a dynamic column
>> is
>> > agonizing. The manual says it is a limitation of SQL itself (presumably
>> > immutable) but PostgreSQL manages to avoid it when querying jsonb doc
>> > properties.
>> We could workaround this requirement by assuming everything is a string,
>> for example. This can be implemented, if that's what users want.
>> >   6. The lack of indexing of dynamic columns is a severe limitation but
>> > PostgreSQL manages to index jsonb document properties.
>> Right, I know. We've heard this feature request a few times already.
>> That's why - no promises! - this feature is currently being discussed
>> for 10.2.
>> >   7. I head a rumor that some kind of JSON features are coming to Maria.
>> I wonder what exactly you've heard and where :)
>> There are many different ways of implementing "JSON features", some of
>> them actually use dynamic columns as the underlying storage format.
>> Currently there is no well defined JSON task in mariadb.org/jira,
>> so nobody knows what exactly will be implemented yet.
>> But now is the time to affect the 10.2 roadmap. What "JSON features"
>> would you like to see in MariaDB?
>> Regards,
>> Sergei
>> _______________________________________________
>> Mailing list: https://launchpad.net/~maria-discuss
>> Post to     : maria-discuss@xxxxxxxxxxxxxxxxxxx
>> Unsubscribe : https://launchpad.net/~maria-discuss
>> More help   : https://help.launchpad.net/ListHelp
> --
> Roberto Spadim
> SPAEmpresarial - Software ERP
> Eng. Automação e Controle
> _______________________________________________
> Mailing list: https://launchpad.net/~maria-discuss
> Post to     : maria-discuss@xxxxxxxxxxxxxxxxxxx
> Unsubscribe : https://launchpad.net/~maria-discuss
> More help   : https://help.launchpad.net/ListHelp

Follow ups