← Back to team overview

maria-discuss team mailing list archive

Re: The future of Dynamic Columns


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

Follow ups