[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> <friend_id>9</friend_id> <friend_id>91</friend_id> <friend_id>92</friend_id> <friend_id>93</friend_id> <friend_id>94</friend_id> <friend_id>95</friend_id> <friend_id>96</friend_id> <friend_id>97</friend_id> <friend_id>98</friend_id> <friend_id>99</friend_id> </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
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" <henrik.ingo@avoinelama.fi> To: "Maria Developers" <maria-developers@lists.launchpad.net>; "Philip Stoev" <pstoev@askmonty.org>; "Igor Babaev" <igor@askmonty.org> Sent: Thursday, October 21, 2010 10:17 PM Subject: 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> <friend_id>9</friend_id> <friend_id>91</friend_id> <friend_id>92</friend_id> <friend_id>93</friend_id> <friend_id>94</friend_id> <friend_id>95</friend_id> <friend_id>96</friend_id> <friend_id>97</friend_id> <friend_id>98</friend_id> <friend_id>99</friend_id> </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 <pstoev@askmonty.org> wrote:
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