[Maria-developers] Why is ExtractValue() not allowed for virtual columns?
Philip, Igor
Why is ExtractValue() not allowed for a virtual column?
Why can't I do this:
CREATE TABLE sbtest (
id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
doc TEXT,
username varchar(10) as (ExtractValue(doc, '/user/username')) virtual,
PRIMARY KEY (id),
)
...but can do this:
CREATE TABLE sbtest (
id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
doc TEXT,
username varchar(10) as (ExtractValue(doc, '/user/username')) virtual,
PRIMARY KEY (id)
)
(Just for fun, imagine "doc" containing an xml document like
<user>
<id>1</id>
<username>hingo</username>
<name>Henrik Ingo</name>
<status time="2010-21-10 13:16">I'm writing an example XML document</status>
<friends>
Henrik,
You are right, this function seems as deterministic as the rest of the
string functions, that is, it still depends on the default character set and
such.
I think the reason it is not allowed is that when this patch was
considered/developed/reviewed, no systematic review of all the functions was
made. When I did my own review (which resulted in bug #608641 , I forgot do
consider XML functions).
I have added your observation to bug 608641, which list other deficiencies
in mariadb virtual columns. My understanding is that the final decision was
to release virtual columns as they are, and fix any deficiencies that occur
as actual customer complaints in a future version.
Philip Stoev
----- Original Message -----
From: "Henrik Ingo"
Philip, Igor
Why is ExtractValue() not allowed for a virtual column?
Why can't I do this:
CREATE TABLE sbtest ( id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, doc TEXT, username varchar(10) as (ExtractValue(doc, '/user/username')) virtual, PRIMARY KEY (id), )
...but can do this:
CREATE TABLE sbtest ( id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, doc TEXT, username varchar(10) as (ExtractValue(doc, '/user/username')) virtual, PRIMARY KEY (id) )
(Just for fun, imagine "doc" containing an xml document like <user> <id>1</id> <username>hingo</username> <name>Henrik Ingo</name> <status time="2010-21-10 13:16">I'm writing an example XML document</status> <friends>
9 91 92 93 94 95 96 97 98 99 </friend> </user> )I don't see ExtractValue being in any way undeterministic or anything.
henrik
-- henrik.ingo@avoinelama.fi +358-40-5697354 www.openlife.cc
On Fri, Oct 22, 2010 at 12:30 PM, Philip Stoev
Henrik,
You are right, this function seems as deterministic as the rest of the string functions, that is, it still depends on the default character set and such.
I think the reason it is not allowed is that when this patch was considered/developed/reviewed, no systematic review of all the functions was made. When I did my own review (which resulted in bug #608641 , I forgot do consider XML functions).
I have added your observation to bug 608641, which list other deficiencies in mariadb virtual columns. My understanding is that the final decision was to release virtual columns as they are, and fix any deficiencies that occur as actual customer complaints in a future version.
Thanks, I'm fine with that. ..assuming of course that this counts as one of those complaints :-) Actually, SUBSTR() works as a good substitute for me in the meantime. henrik -- henrik.ingo@avoinelama.fi +358-40-5697354 www.openlife.cc
participants (2)
-
Henrik Ingo -
Philip Stoev