[Maria-discuss] virtual columns
Hello I've read in the documentation that any legal SQL expression SHOULD be allowed in the Virtual Columns definition. I'm trying to use a (deterministic) stored function, but I get an error: ERROR 1642 (HY000): Function or expression is not allowed for column 'vc1' Here's my create table: CREATE TABLE t1 (c1 int, c2 int, vc1 INT AS (c1+functest()) VIRTUAL); Here's how functest() is defined: CREATE FUNCTION functest() RETURNS TINYINT DETERMINISTIC BEGIN UPDATE t2 SET c1 = c1 + 1; RETURN 0; END I think this clearly explains what I am trying to do: a function which logs accesses to a certain column. Why do I get error 1642? Is the bug in the documentation or in the software? Will future MariaDB versions allow us to create virtual columns which call stored functions? Federico
Hi, I don't believe that UDF or stored functions are compatible with virtual columns. This makes sense, since these functions could be dropped after the table is created. --Justin On Mon, Nov 8, 2010 at 7:03 PM, Federico Razzoli <federico_raz@yahoo.it> wrote:
Hello
I've read in the documentation that any legal SQL expression SHOULD be allowed in the Virtual Columns definition. I'm trying to use a (deterministic) stored function, but I get an error: ERROR 1642 (HY000): Function or expression is not allowed for column 'vc1'
Here's my create table: CREATE TABLE t1 (c1 int, c2 int, vc1 INT AS (c1+functest()) VIRTUAL);
Here's how functest() is defined:
CREATE FUNCTION functest() RETURNS TINYINT DETERMINISTIC BEGIN UPDATE t2 SET c1 = c1 + 1; RETURN 0; END
I think this clearly explains what I am trying to do: a function which logs accesses to a certain column.
Why do I get error 1642? Is the bug in the documentation or in the software? Will future MariaDB versions allow us to create virtual columns which call stored functions?
Federico
_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
Hi, Federico! On Nov 09, Federico Razzoli wrote:
Hello
I've read in the documentation that any legal SQL expression SHOULD be allowed in the Virtual Columns definition. I'm trying to use a (deterministic) stored function, but I get an error: ERROR 1642 (HY000): Function or expression is not allowed for column 'vc1'
The manual - http://kb.askmonty.org/v/virtual-columns - clearly says (under the "Implementation Differences"): * MariaDB does not allow user-defined functions, even those flagged as DETERMINISTIC Regards, Sergei
On Tue, Nov 9, 2010 at 5:03 AM, Federico Razzoli <federico_raz@yahoo.it> wrote:
Hello
I've read in the documentation that any legal SQL expression SHOULD be allowed in the Virtual Columns definition.
That opening statement (on http://kb.askmonty.org/v/virtual-columns) is misleading in many ways and I've commented on the article but it is not yet fixed. - subqueries or anything that depends on data outside the row is not possible - only deterministic functions are possible - UDF and stored functions are not possible henrik -- henrik.ingo@avoinelama.fi +358-40-5697354 www.openlife.cc
Hi Henrik, This is kind of unrelated, but it seems like a good thread to ask the question in. Are there any plans to make an indexed virtual column work automatically as an index for the table? Other databases support 'functional' indexing, that is, you can index the contents of a function call and the if you use that function call in the where clause, then the index is automatically used. For example: select * from some_table where UPPER(some_col) = 'ABC' In order to avoid a FTS you could create an indexed persistent virtual column for UPPER(some_col) and modify your SQL to use it. It would, however, be much nicer if the optimizer could detect a virtual column which matches the function call and use the index automatically if one exists on the virtual column, or scan the virtual column if it doesn't. Likely this could be done in the same phase as 'constant replacement'. If an expression exactly matches a virtual column, replace the expression with a reference to the virtual column. If the virtual column is indexed, then the index should get automatically used, otherwise it will get scanned. Regards, --Justin On Tue, Nov 9, 2010 at 1:00 AM, Henrik Ingo <henrik.ingo@avoinelama.fi> wrote:
On Tue, Nov 9, 2010 at 5:03 AM, Federico Razzoli <federico_raz@yahoo.it> wrote:
Hello
I've read in the documentation that any legal SQL expression SHOULD be allowed in the Virtual Columns definition.
That opening statement (on http://kb.askmonty.org/v/virtual-columns) is misleading in many ways and I've commented on the article but it is not yet fixed. - subqueries or anything that depends on data outside the row is not possible - only deterministic functions are possible - UDF and stored functions are not possible
henrik
-- henrik.ingo@avoinelama.fi +358-40-5697354 www.openlife.cc
_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
On Tue, Nov 9, 2010 at 7:47 PM, Justin Swanhart <greenlion@gmail.com> wrote:
This is kind of unrelated, but it seems like a good thread to ask the question in.
Are there any plans to make an indexed virtual column work automatically as an index for the table? Other databases support 'functional' indexing, that is, you can index the contents of a function call and the if you use that function call in the where clause, then the index is automatically used.
For example: select * from some_table where UPPER(some_col) = 'ABC'
In order to avoid a FTS you could create an indexed persistent virtual column for UPPER(some_col) and modify your SQL to use it. It would, however, be much nicer if the optimizer could detect a virtual column which matches the function call and use the index automatically if one exists on the virtual column, or scan the virtual column if it doesn't.
Likely this could be done in the same phase as 'constant replacement'. If an expression exactly matches a virtual column, replace the expression with a reference to the virtual column. If the virtual column is indexed, then the index should get automatically used, otherwise it will get scanned.
I'm not aware of additional work being done related to this right now. I understand what you suggest, but let's face it, this would just be a convenience feature for those that are too lazy to rewrite their queries to actually use the virtual column :-) Even if I'm not a minimalist when it comes to features, I think what you suggest is probably stretching the MySQL tradition of being simple and straightforward. henrik -- henrik.ingo@avoinelama.fi +358-40-5697354 www.openlife.cc
On Tue, 9 Nov 2010 11:00:36 +0200 Henrik Ingo <henrik.ingo@avoinelama.fi> wrote: Henrik> On Tue, Nov 9, 2010 at 5:03 AM, Federico Razzoli Henrik> <federico_raz@yahoo.it> wrote: Henrik> > Hello Henrik> > Henrik> > I've read in the documentation that any legal SQL expression Henrik> > SHOULD be allowed in the Virtual Columns definition. Henrik> Henrik> That opening statement (on Henrik> http://kb.askmonty.org/v/virtual-columns) is misleading in many Henrik> ways and I've commented on the article but it is not yet fixed. Henrik> - subqueries or anything that depends on data outside the row Henrik> is not possible Henrik> - only deterministic functions are possible Henrik> - UDF and stored functions are not possible Sorry for not getting the article updated earlier. I've changed the problematic line and added some clarifying bullet points. Let me know if further changes need to be made. http://kb.askmonty.org/v/virtual-columns Thanks. -- Daniel Bartholomew Monty Program - http://askmonty.org
participants (5)
-
Daniel Bartholomew -
Federico Razzoli -
Henrik Ingo -
Justin Swanhart -
Sergei Golubchik