[Maria-discuss] stored programs
Reading 10.0.3 release notes: https://mariadb.com/kb/en/mariadb/mariadb-1013-release-notes/ I see that IF EXISTS, IF NOT EXISTS and OR REPLACE are now almost consistent. "Almost" means that... OR REPLACE still doesn't apply to stored procedures, functions, triggers, events. Recently, during a public session, a PostgreSQL user asked me if MariaDB supports stored procedures - in his opinion, MySQL doesn't, no matter what the manual says. Unfortunately my answer was that MariaDB support for stored procedure is the same as MySQL ("so the answer is no", he said). I feel that these features are generally ignored. I don't understand the reason. While they have many problems in MariaDB/MySQL, some people still tries to use them. Even more people would use them if some developement was made on them. They are also used by important projects like Common Schema and Flexviews, but I'm sure that the authors have to struggle against limitations every time they change a line of code. I struggled hards for a much easier project - an SQL unit test tool. I have several problems in mind: performance, lack of variable number of arguments, cannot prepare a statement from a local variable, no debugger, no way to see the call stack... I'm writing this because I love MariaDB. This is not a hateful criticism: this is a request for something I waited for many years. I hope that you will keep procedures in mind, while planning the next major release. Regards Federico
Hi, Federico! On Mar 03, Federico Razzoli wrote:
Reading 10.0.3 release notes:
https://mariadb.com/kb/en/mariadb/mariadb-1013-release-notes/
I see that IF EXISTS, IF NOT EXISTS and OR REPLACE are now almost consistent. "Almost" means that... OR REPLACE still doesn't apply to stored procedures, functions, triggers, events.
Support for events is already pushed (albeit after 10.1.3). Support for triggers will be pushed any day now (already reviewed and approved, so there's no more work left on it). I suppose that stored procedures and functions will follow soon. This was a GSoC 2014 project that added support for these clauses to *all* objects. It's just being pushed piecewise, object by object.
Recently, during a public session, a PostgreSQL user asked me if MariaDB supports stored procedures - in his opinion, MySQL doesn't, no matter what the manual says. Unfortunately my answer was that MariaDB support for stored procedure is the same as MySQL ("so the answer is no", he said).
I don't understand what exactly missing feature that user had in mind. It couldn't have been "CREATE OR REPLACE", this seems so minor. Or was it? Regards, Sergei
Reading 10.0.3 release notes:
https://mariadb.com/kb/en/mariadb/mariadb-1013-release-notes/
I see that IF EXISTS, IF NOT EXISTS and OR REPLACE are now almost consistent. "Almost" means
Hi Sergej No, it was something more general. Performance is not sufficient, there is no debugging facility, and stored programs are not flexible enough. Arrays, variable number of arguments and the ability to prepare a statement from a local variable would help a lot. Or perhaps, the ability to use a language other than SQL would be a solution. I used the OR REPLACE clause to point out that both MariaDB and MySQL development is very slow (if any) when it comes to stored programs. As a user and a MariaDB lover, I think that this is a pity. Regards Federico -------------------------------------------- Mar 3/3/15, Sergei Golubchik <serg@mariadb.org> ha scritto: Oggetto: Re: [Maria-discuss] stored programs A: "Federico Razzoli" <federico_raz@yahoo.it> Cc: maria-discuss@lists.launchpad.net Data: Martedì 3 marzo 2015, 10:48 Hi, Federico! On Mar 03, Federico Razzoli wrote: that... OR REPLACE still doesn't apply to
stored procedures, functions, triggers, events.
Support for events is already pushed (albeit after 10.1.3). Support for triggers will be pushed any day now (already reviewed and approved, so there's no more work left on it). I suppose that stored procedures and functions will follow soon. This was a GSoC 2014 project that added support for these clauses to *all* objects. It's just being pushed piecewise, object by object.
Recently, during a public session, a PostgreSQL user asked me if MariaDB supports stored procedures - in his opinion, MySQL doesn't, no matter what the manual says. Unfortunately my answer was that MariaDB support for stored procedure is the same as MySQL ("so the answer is
no", he said). I don't understand what exactly missing feature that user had in mind. It couldn't have been "CREATE OR REPLACE", this seems so minor. Or was it? Regards, Sergei
1) +1 for "ability to prepare a statement from a local variable" what I requested 6½ years ago: http://bugs.mysql.com/bug.php?id=40038 2) I agree that no debugging facility is a serious limitation (though Oracle claim that their "MySQL for Visual Studio" can do it (with Visual Studio on Windows obviously). I have not tried). 3) another (supplementary) language for stored programs. This has been discussed before in severalcontexts. Problem is that there is no 'fit' language being cross-platform. PostgreSQl has Perl, SQL Server as C#. The current 'sp language' (for loops, conditions etc.) is based on ADA. 'Adascript' implementations (covering around 60% of full ADA, I think) exist and could be considered. Or LUA that looks somewhat similar. But the interpreter will need to ship with the server. Just my 5 cents on this! -- Peter -- Webyog On Tue, Mar 3, 2015 at 12:31 PM, Federico Razzoli <federico_raz@yahoo.it> wrote:
Hi Sergej
No, it was something more general. Performance is not sufficient, there is no debugging facility, and stored programs are not flexible enough. Arrays, variable number of arguments and the ability to prepare a statement from a local variable would help a lot. Or perhaps, the ability to use a language other than SQL would be a solution.
I used the OR REPLACE clause to point out that both MariaDB and MySQL development is very slow (if any) when it comes to stored programs. As a user and a MariaDB lover, I think that this is a pity.
Regards Federico
-------------------------------------------- Mar 3/3/15, Sergei Golubchik <serg@mariadb.org> ha scritto:
Oggetto: Re: [Maria-discuss] stored programs A: "Federico Razzoli" <federico_raz@yahoo.it> Cc: maria-discuss@lists.launchpad.net Data: Martedì 3 marzo 2015, 10:48
Hi, Federico!
Reading 10.0.3 release notes:
https://mariadb.com/kb/en/mariadb/mariadb-1013-release-notes/
I see that IF EXISTS, IF NOT EXISTS and OR REPLACE are now almost consistent. "Almost" means
On Mar 03, Federico Razzoli wrote: that... OR REPLACE still doesn't apply to
stored procedures, functions, triggers, events.
Support for events is already pushed (albeit after 10.1.3). Support for triggers will be pushed any day now (already reviewed and approved, so there's no more work left on it). I suppose that stored procedures and functions will follow soon.
This was a GSoC 2014 project that added support for these clauses to *all* objects. It's just being pushed piecewise, object by object.
Recently, during a public session, a PostgreSQL user asked me if MariaDB supports stored procedures - in his opinion, MySQL doesn't, no matter what the manual says. Unfortunately my answer was that MariaDB support for stored procedure is the same as MySQL ("so the answer is
no", he said).
I don't understand what exactly missing feature that user had in mind. It couldn't have been "CREATE OR REPLACE", this seems so minor. Or was it?
Regards, Sergei
_______________________________________________ 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
I made some quick test to show what I mean by "performance problem". Note that I'm not saying the the first and the second test cases should perform equally. But there is too much difference - see the conclusion. 1) MariaDB [test]> SELECT BENCHMARK(50000000, (SELECT 1)); +---------------------------------+ | BENCHMARK(50000000, (SELECT 1)) | +---------------------------------+ | 0 | +---------------------------------+ 1 row in set (0.42 sec) 2) DELIMITER || CREATE FUNCTION f() RETURNS TINYINT BEGIN RETURN 1; END || DELIMITER ; MariaDB [test]> SELECT BENCHMARK(50000000, (SELECT f())); +-----------------------------------+ | BENCHMARK(50000000, (SELECT f())) | +-----------------------------------+ | 0 | +-----------------------------------+ 1 row in set (2 min 5.70 sec) 3) DELIMITER || CREATE FUNCTION f(x TINYINT) RETURNS TINYINT BEGIN RETURN x; END || DELIMITER ; MariaDB [test]> SELECT BENCHMARK(50000000, (SELECT f(1))); +------------------------------------+ | BENCHMARK(50000000, (SELECT f(1))) | +------------------------------------+ | 0 | +------------------------------------+ 1 row in set (3 min 35.20 sec) -- Conclusions: Times in seconds: 0.42 125.70 215.20 I don't know which ratio would be acceptable, but the difference betweem a trivial query and a trivial function is too high. Also, the difference between 2) and 3) is that the function in 3) accepts and returns a parameter. Again, the performance difference seems to me too high. Regards Federico
Your 3rd test case takes 2:02 in MariaDB 10.1 and 2:13 in MySQL 5.6 on my system (when otherwise idle) . -- Peter On Tue, Mar 3, 2015 at 1:58 PM, Federico Razzoli <federico_raz@yahoo.it> wrote:
I made some quick test to show what I mean by "performance problem". Note that I'm not saying the the first and the second test cases should perform equally. But there is too much difference - see the conclusion.
1)
MariaDB [test]> SELECT BENCHMARK(50000000, (SELECT 1)); +---------------------------------+ | BENCHMARK(50000000, (SELECT 1)) | +---------------------------------+ | 0 | +---------------------------------+ 1 row in set (0.42 sec)
2)
DELIMITER || CREATE FUNCTION f() RETURNS TINYINT BEGIN RETURN 1; END || DELIMITER ;
MariaDB [test]> SELECT BENCHMARK(50000000, (SELECT f())); +-----------------------------------+ | BENCHMARK(50000000, (SELECT f())) | +-----------------------------------+ | 0 | +-----------------------------------+ 1 row in set (2 min 5.70 sec)
3)
DELIMITER || CREATE FUNCTION f(x TINYINT) RETURNS TINYINT BEGIN RETURN x; END || DELIMITER ;
MariaDB [test]> SELECT BENCHMARK(50000000, (SELECT f(1))); +------------------------------------+ | BENCHMARK(50000000, (SELECT f(1))) | +------------------------------------+ | 0 | +------------------------------------+ 1 row in set (3 min 35.20 sec)
-- Conclusions:
Times in seconds:
0.42 125.70 215.20
I don't know which ratio would be acceptable, but the difference betweem a trivial query and a trivial function is too high. Also, the difference between 2) and 3) is that the function in 3) accepts and returns a parameter. Again, the performance difference seems to me too high.
Regards 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
You probably have either a faster CPU or bigger cache on your cpu. How long does benchmark select 1 take? You should find it is faster on your system too, right? --Justin Sent from my iPhone
On Mar 3, 2015, at 6:18 AM, Peter Laursen <peter_laursen@webyog.com> wrote:
Your 3rd test case takes 2:02 in MariaDB 10.1 and 2:13 in MySQL 5.6 on my system (when otherwise idle) .
-- Peter
On Tue, Mar 3, 2015 at 1:58 PM, Federico Razzoli <federico_raz@yahoo.it> wrote: I made some quick test to show what I mean by "performance problem". Note that I'm not saying the the first and the second test cases should perform equally. But there is too much difference - see the conclusion.
1)
MariaDB [test]> SELECT BENCHMARK(50000000, (SELECT 1)); +---------------------------------+ | BENCHMARK(50000000, (SELECT 1)) | +---------------------------------+ | 0 | +---------------------------------+ 1 row in set (0.42 sec)
2)
DELIMITER || CREATE FUNCTION f() RETURNS TINYINT BEGIN RETURN 1; END || DELIMITER ;
MariaDB [test]> SELECT BENCHMARK(50000000, (SELECT f())); +-----------------------------------+ | BENCHMARK(50000000, (SELECT f())) | +-----------------------------------+ | 0 | +-----------------------------------+ 1 row in set (2 min 5.70 sec)
3)
DELIMITER || CREATE FUNCTION f(x TINYINT) RETURNS TINYINT BEGIN RETURN x; END || DELIMITER ;
MariaDB [test]> SELECT BENCHMARK(50000000, (SELECT f(1))); +------------------------------------+ | BENCHMARK(50000000, (SELECT f(1))) | +------------------------------------+ | 0 | +------------------------------------+ 1 row in set (3 min 35.20 sec)
-- Conclusions:
Times in seconds:
0.42 125.70 215.20
I don't know which ratio would be acceptable, but the difference betweem a trivial query and a trivial function is too high. Also, the difference between 2) and 3) is that the function in 3) accepts and returns a parameter. Again, the performance difference seems to me too high.
Regards 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
_______________________________________________ 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
With local MariaDB 10.0.13. out put from SQLyog "Messages"in erface: http://faq.webyog.com/content/8/147/en/how-shall-i-understand-the-_query-exe... *1 queries executed, 1 success, 0 errors, 0 warnings* *Query: SELECT BENCHMARK(50000000, (SELECT 1))* *1 row(s) returned* *Execution Time : 0.267 sec* *Transfer Time : 0 sec* *Total Time : 0.267 sec* Yes, I recently had a terrific machine. Intel socket 1155 (2 threads per core) 4 cores 3.5 Ghz,32 MB RAM. :-) GPU is nothing particlar. It is a 512 MB DDR2-RAMGeforce card (used in order to achive 2560*1440 screen resolution , what the intel CPU graphics does not provide. -- Peter On Tue, Mar 3, 2015 at 2:28 PM, Justin Swanhart <greenlion@gmail.com> wrote:
You probably have either a faster CPU or bigger cache on your cpu. How long does benchmark select 1 take? You should find it is faster on your system too, right?
--Justin
Sent from my iPhone
On Mar 3, 2015, at 6:18 AM, Peter Laursen <peter_laursen@webyog.com> wrote:
Your 3rd test case takes 2:02 in MariaDB 10.1 and 2:13 in MySQL 5.6 on my system (when otherwise idle) .
-- Peter
On Tue, Mar 3, 2015 at 1:58 PM, Federico Razzoli <federico_raz@yahoo.it> wrote:
I made some quick test to show what I mean by "performance problem". Note that I'm not saying the the first and the second test cases should perform equally. But there is too much difference - see the conclusion.
1)
MariaDB [test]> SELECT BENCHMARK(50000000, (SELECT 1)); +---------------------------------+ | BENCHMARK(50000000, (SELECT 1)) | +---------------------------------+ | 0 | +---------------------------------+ 1 row in set (0.42 sec)
2)
DELIMITER || CREATE FUNCTION f() RETURNS TINYINT BEGIN RETURN 1; END || DELIMITER ;
MariaDB [test]> SELECT BENCHMARK(50000000, (SELECT f())); +-----------------------------------+ | BENCHMARK(50000000, (SELECT f())) | +-----------------------------------+ | 0 | +-----------------------------------+ 1 row in set (2 min 5.70 sec)
3)
DELIMITER || CREATE FUNCTION f(x TINYINT) RETURNS TINYINT BEGIN RETURN x; END || DELIMITER ;
MariaDB [test]> SELECT BENCHMARK(50000000, (SELECT f(1))); +------------------------------------+ | BENCHMARK(50000000, (SELECT f(1))) | +------------------------------------+ | 0 | +------------------------------------+ 1 row in set (3 min 35.20 sec)
-- Conclusions:
Times in seconds:
0.42 125.70 215.20
I don't know which ratio would be acceptable, but the difference betweem a trivial query and a trivial function is too high. Also, the difference between 2) and 3) is that the function in 3) accepts and returns a parameter. Again, the performance difference seems to me too high.
Regards 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
_______________________________________________ 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
SELECT 1 was .42. What I find amazing is not the absolute numbers (it's an old computer with desktop installed, etc) but the difference between the tests. If SELECT 1 takes .42, test 2 shouldn't take more than 1 min... do you agree? Federico -------------------------------------------- Mar 3/3/15, Justin Swanhart <greenlion@gmail.com> ha scritto: Oggetto: Re: [Maria-discuss] stored programs A: "Peter Laursen" <peter_laursen@webyog.com> Cc: "Federico Razzoli" <federico_raz@yahoo.it>, "Maria Discuss" <maria-discuss@lists.launchpad.net> Data: Martedì 3 marzo 2015, 14:28 You probably have either a faster CPU or bigger cache on your cpu. How long does benchmark select 1 take? You should find it is faster on your system too, right? --Justin Sent from my iPhone On Mar 3, 2015, at 6:18 AM, Peter Laursen <peter_laursen@webyog.com> wrote: Your 3rd test case takes 2:02 in MariaDB 10.1 and 2:13 in MySQL 5.6 on my system (when otherwise idle) . -- Peter On Tue, Mar 3, 2015 at 1:58 PM, Federico Razzoli <federico_raz@yahoo.it> wrote: I made some quick test to show what I mean by "performance problem". Note that I'm not saying the the first and the second test cases should perform equally. But there is too much difference - see the conclusion. 1) MariaDB [test]> SELECT BENCHMARK(50000000, (SELECT 1)); +---------------------------------+ | BENCHMARK(50000000, (SELECT 1)) | +---------------------------------+ | 0 | +---------------------------------+ 1 row in set (0.42 sec) 2) DELIMITER || CREATE FUNCTION f() RETURNS TINYINT BEGIN RETURN 1; END || DELIMITER ; MariaDB [test]> SELECT BENCHMARK(50000000, (SELECT f())); +-----------------------------------+ | BENCHMARK(50000000, (SELECT f())) | +-----------------------------------+ | 0 | +-----------------------------------+ 1 row in set (2 min 5.70 sec) 3) DELIMITER || CREATE FUNCTION f(x TINYINT) RETURNS TINYINT BEGIN RETURN x; END || DELIMITER ; MariaDB [test]> SELECT BENCHMARK(50000000, (SELECT f(1))); +------------------------------------+ | BENCHMARK(50000000, (SELECT f(1))) | +------------------------------------+ | 0 | +------------------------------------+ 1 row in set (3 min 35.20 sec) -- Conclusions: Times in seconds: 0.42 125.70 215.20 I don't know which ratio would be acceptable, but the difference betweem a trivial query and a trivial function is too high. Also, the difference between 2) and 3) is that the function in 3) accepts and returns a parameter. Again, the performance difference seems to me too high. Regards 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 _______________________________________________ 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
I really don't know if it should take 2, 5 or 60 seconds. The numbers surprise me (in the bad maner). There is an incredible overhead when wrapping this query in a function. I wonder if there is an invocation of the function for every iteration. -- Peter On Tue, Mar 3, 2015 at 2:51 PM, Federico Razzoli <federico_raz@yahoo.it> wrote:
SELECT 1 was .42. What I find amazing is not the absolute numbers (it's an old computer with desktop installed, etc) but the difference between the tests. If SELECT 1 takes .42, test 2 shouldn't take more than 1 min... do you agree?
Federico
-------------------------------------------- Mar 3/3/15, Justin Swanhart <greenlion@gmail.com> ha scritto:
Oggetto: Re: [Maria-discuss] stored programs A: "Peter Laursen" <peter_laursen@webyog.com> Cc: "Federico Razzoli" <federico_raz@yahoo.it>, "Maria Discuss" < maria-discuss@lists.launchpad.net> Data: Martedì 3 marzo 2015, 14:28
You probably have either a faster CPU or bigger cache on your cpu. How long does benchmark select 1 take? You should find it is faster on your system too, right? --Justin
Sent from my iPhone On Mar 3, 2015, at 6:18 AM, Peter Laursen <peter_laursen@webyog.com> wrote:
Your 3rd test case takes 2:02 in MariaDB 10.1 and 2:13 in MySQL 5.6 on my system (when otherwise idle) . -- Peter On Tue, Mar 3, 2015 at 1:58 PM, Federico Razzoli <federico_raz@yahoo.it> wrote: I made some quick test to show what I mean by "performance problem". Note that I'm not saying the the first and the second test cases should perform equally. But there is too much difference - see the conclusion.
1)
MariaDB [test]> SELECT BENCHMARK(50000000, (SELECT 1));
+---------------------------------+
| BENCHMARK(50000000, (SELECT 1)) |
+---------------------------------+
| 0 |
+---------------------------------+
1 row in set (0.42 sec)
2)
DELIMITER ||
CREATE FUNCTION f()
RETURNS TINYINT
BEGIN
RETURN 1;
END ||
DELIMITER ;
MariaDB [test]> SELECT BENCHMARK(50000000, (SELECT f()));
+-----------------------------------+
| BENCHMARK(50000000, (SELECT f())) |
+-----------------------------------+
| 0 |
+-----------------------------------+
1 row in set (2 min 5.70 sec)
3)
DELIMITER ||
CREATE FUNCTION f(x TINYINT)
RETURNS TINYINT
BEGIN
RETURN x;
END ||
DELIMITER ;
MariaDB [test]> SELECT BENCHMARK(50000000, (SELECT f(1)));
+------------------------------------+
| BENCHMARK(50000000, (SELECT f(1))) |
+------------------------------------+
| 0 |
+------------------------------------+
1 row in set (3 min 35.20 sec)
-- Conclusions:
Times in seconds:
0.42
125.70
215.20
I don't know which ratio would be acceptable, but the difference betweem a trivial query and a trivial function is too high.
Also, the difference between 2) and 3) is that the function in 3) accepts and returns a parameter. Again, the performance difference seems to me too high.
Regards
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
_______________________________________________ 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, The server parses the function into a form of bytecode which is then cached until the routine changes. In the past any routine change would invalidate cache for all routines and that sometimes can still happen but it is rare. The server then interprets the bytecode for every call to the function and yes, the function is executed every time unless you memoize the function ( if(function_input != @last_input) then @last_input = function_input; return @last_result := function(function_input); else return @last_result; On Tue, Mar 3, 2015 at 6:59 AM, Peter Laursen <peter_laursen@webyog.com> wrote:
I really don't know if it should take 2, 5 or 60 seconds. The numbers surprise me (in the bad maner). There is an incredible overhead when wrapping this query in a function. I wonder if there is an invocation of the function for every iteration.
-- Peter
On Tue, Mar 3, 2015 at 2:51 PM, Federico Razzoli <federico_raz@yahoo.it> wrote:
SELECT 1 was .42. What I find amazing is not the absolute numbers (it's an old computer with desktop installed, etc) but the difference between the tests. If SELECT 1 takes .42, test 2 shouldn't take more than 1 min... do you agree?
Federico
-------------------------------------------- Mar 3/3/15, Justin Swanhart <greenlion@gmail.com> ha scritto:
Oggetto: Re: [Maria-discuss] stored programs A: "Peter Laursen" <peter_laursen@webyog.com> Cc: "Federico Razzoli" <federico_raz@yahoo.it>, "Maria Discuss" < maria-discuss@lists.launchpad.net> Data: Martedì 3 marzo 2015, 14:28
You probably have either a faster CPU or bigger cache on your cpu. How long does benchmark select 1 take? You should find it is faster on your system too, right? --Justin
Sent from my iPhone On Mar 3, 2015, at 6:18 AM, Peter Laursen <peter_laursen@webyog.com> wrote:
Your 3rd test case takes 2:02 in MariaDB 10.1 and 2:13 in MySQL 5.6 on my system (when otherwise idle) . -- Peter On Tue, Mar 3, 2015 at 1:58 PM, Federico Razzoli <federico_raz@yahoo.it> wrote: I made some quick test to show what I mean by "performance problem". Note that I'm not saying the the first and the second test cases should perform equally. But there is too much difference - see the conclusion.
1)
MariaDB [test]> SELECT BENCHMARK(50000000, (SELECT 1));
+---------------------------------+
| BENCHMARK(50000000, (SELECT 1)) |
+---------------------------------+
| 0 |
+---------------------------------+
1 row in set (0.42 sec)
2)
DELIMITER ||
CREATE FUNCTION f()
RETURNS TINYINT
BEGIN
RETURN 1;
END ||
DELIMITER ;
MariaDB [test]> SELECT BENCHMARK(50000000, (SELECT f()));
+-----------------------------------+
| BENCHMARK(50000000, (SELECT f())) |
+-----------------------------------+
| 0 |
+-----------------------------------+
1 row in set (2 min 5.70 sec)
3)
DELIMITER ||
CREATE FUNCTION f(x TINYINT)
RETURNS TINYINT
BEGIN
RETURN x;
END ||
DELIMITER ;
MariaDB [test]> SELECT BENCHMARK(50000000, (SELECT f(1)));
+------------------------------------+
| BENCHMARK(50000000, (SELECT f(1))) |
+------------------------------------+
| 0 |
+------------------------------------+
1 row in set (3 min 35.20 sec)
-- Conclusions:
Times in seconds:
0.42
125.70
215.20
I don't know which ratio would be acceptable, but the difference betweem a trivial query and a trivial function is too high.
Also, the difference between 2) and 3) is that the function in 3) accepts and returns a parameter. Again, the performance difference seems to me too high.
Regards
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
_______________________________________________ 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, Just to avoid ambiguity: the stored function is executed as byte code in each call, and the native function is called by the byte code for each call. --Justin On Tue, Mar 3, 2015 at 7:04 AM, Justin Swanhart <greenlion@gmail.com> wrote:
Hi,
The server parses the function into a form of bytecode which is then cached until the routine changes. In the past any routine change would invalidate cache for all routines and that sometimes can still happen but it is rare. The server then interprets the bytecode for every call to the function and yes, the function is executed every time unless you memoize the function ( if(function_input != @last_input) then @last_input = function_input; return @last_result := function(function_input); else return @last_result;
On Tue, Mar 3, 2015 at 6:59 AM, Peter Laursen <peter_laursen@webyog.com> wrote:
I really don't know if it should take 2, 5 or 60 seconds. The numbers surprise me (in the bad maner). There is an incredible overhead when wrapping this query in a function. I wonder if there is an invocation of the function for every iteration.
-- Peter
On Tue, Mar 3, 2015 at 2:51 PM, Federico Razzoli <federico_raz@yahoo.it> wrote:
SELECT 1 was .42. What I find amazing is not the absolute numbers (it's an old computer with desktop installed, etc) but the difference between the tests. If SELECT 1 takes .42, test 2 shouldn't take more than 1 min... do you agree?
Federico
-------------------------------------------- Mar 3/3/15, Justin Swanhart <greenlion@gmail.com> ha scritto:
Oggetto: Re: [Maria-discuss] stored programs A: "Peter Laursen" <peter_laursen@webyog.com> Cc: "Federico Razzoli" <federico_raz@yahoo.it>, "Maria Discuss" < maria-discuss@lists.launchpad.net> Data: Martedì 3 marzo 2015, 14:28
You probably have either a faster CPU or bigger cache on your cpu. How long does benchmark select 1 take? You should find it is faster on your system too, right? --Justin
Sent from my iPhone On Mar 3, 2015, at 6:18 AM, Peter Laursen <peter_laursen@webyog.com> wrote:
Your 3rd test case takes 2:02 in MariaDB 10.1 and 2:13 in MySQL 5.6 on my system (when otherwise idle) . -- Peter On Tue, Mar 3, 2015 at 1:58 PM, Federico Razzoli <federico_raz@yahoo.it> wrote: I made some quick test to show what I mean by "performance problem". Note that I'm not saying the the first and the second test cases should perform equally. But there is too much difference - see the conclusion.
1)
MariaDB [test]> SELECT BENCHMARK(50000000, (SELECT 1));
+---------------------------------+
| BENCHMARK(50000000, (SELECT 1)) |
+---------------------------------+
| 0 |
+---------------------------------+
1 row in set (0.42 sec)
2)
DELIMITER ||
CREATE FUNCTION f()
RETURNS TINYINT
BEGIN
RETURN 1;
END ||
DELIMITER ;
MariaDB [test]> SELECT BENCHMARK(50000000, (SELECT f()));
+-----------------------------------+
| BENCHMARK(50000000, (SELECT f())) |
+-----------------------------------+
| 0 |
+-----------------------------------+
1 row in set (2 min 5.70 sec)
3)
DELIMITER ||
CREATE FUNCTION f(x TINYINT)
RETURNS TINYINT
BEGIN
RETURN x;
END ||
DELIMITER ;
MariaDB [test]> SELECT BENCHMARK(50000000, (SELECT f(1)));
+------------------------------------+
| BENCHMARK(50000000, (SELECT f(1))) |
+------------------------------------+
| 0 |
+------------------------------------+
1 row in set (3 min 35.20 sec)
-- Conclusions:
Times in seconds:
0.42
125.70
215.20
I don't know which ratio would be acceptable, but the difference betweem a trivial query and a trivial function is too high.
Also, the difference between 2) and 3) is that the function in 3) accepts and returns a parameter. Again, the performance difference seems to me too high.
Regards
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
_______________________________________________ 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, This is the kind of thing you can do in PostgreSQL and it is amazingly very fast. I like MySQL but come on guys, this is 2015. SQL92 is 23 years old. By the way this adds two more requests: stored aggregate functions and stored table functions -- Table stored function which combines shuttle radar topography raster data (SRTM) -- and Open street map geometry data CREATE FUNCTION cache_1m_tile(integer) RETURNS SETOF tile LANGUAGE sql AS $_$ with lands as -- common table expressions -- Use raster and geometry objects together with GiST index (select (st_pixelaspoints(st_clip(rast, geometry),1,false)).x, (st_pixelaspoints(st_clip(rast,geometry),1,false)).y, array_accum(type) as "types", array_accum(name) as names, array_accum(z_order) as z_orders from import.osm_landusages join srtm_1m on st_intersects(rast, geometry) where rid = $1 group by 1,2 ), buildings as ( select * from srtm_buildings where rid = $1), places_and_amenities as -- array_accum is a stored AGGREGATE function that works like group_concat (select x,y, array_accum(name) thing_name, array_accum(type) thing_type from ( select (st_pixelaspoints(st_clip(rast, geometry),1,false)).x, (st_pixelaspoints(st_clip(rast,geometry),1,false)).y, 'place::' || name as name, 'place::' || type as type from import.osm_places join srtm_1m on st_intersects(rast, geometry) where rid = $1 union all select (st_pixelaspoints(st_clip(rast, geometry),1,false)).x, (st_pixelaspoints(st_clip(rast,geometry),1,false)).y, 'place::' || name as name, 'place::' || type as type from import.osm_amenities join srtm_1m on st_intersects(rast, geometry) where rid = $1 ) foo group by 1,2 ) insert into srtm_1m_tiles select p.rid,p.x, p.y, p.val height, p.geom, floor(b.area) building_area, b.id building_id, r.id road_ids , r.z_order road_z_order, r.bridge road_bridge, r.tunnel road_tunnel, r.rtype road_type, w.val is not null has_water, lands.names as land_names, lands.types as land_types, lands.z_orders as land_z, pa.thing_name, pa.thing_type from srtm_1m_pixels p left join buildings b on b.rast_geom && p.geom and st_contains(b.geometry, p.geom) and p.rid = b.rid left join srtm_road_accum r on b.rid = r.rid and p.x = r.x and p.y = r.y left join srtm_water_pixels w on b.rid = w.rid and p.x = w.x and p.y = w.y left join srtm_transport_pixels tp on b.rid = tp.rid and p.x = tp.x and p.y = tp.y left join lands on p.x = lands.x and p.y = lands.y left join places_and_amenities pa on p.x = pa.x and p.y = pa.y where p.rid=$1 and not exists (select 1 from srtm_1m_tiles t2 where p.rid = t2.rid and p.x = t2.x and p.y = t2.y) returning *; $_$; On Tue, Mar 3, 2015 at 7:05 AM, Justin Swanhart <greenlion@gmail.com> wrote:
Hi,
Just to avoid ambiguity: the stored function is executed as byte code in each call, and the native function is called by the byte code for each call.
--Justin
On Tue, Mar 3, 2015 at 7:04 AM, Justin Swanhart <greenlion@gmail.com> wrote:
Hi,
The server parses the function into a form of bytecode which is then cached until the routine changes. In the past any routine change would invalidate cache for all routines and that sometimes can still happen but it is rare. The server then interprets the bytecode for every call to the function and yes, the function is executed every time unless you memoize the function ( if(function_input != @last_input) then @last_input = function_input; return @last_result := function(function_input); else return @last_result;
On Tue, Mar 3, 2015 at 6:59 AM, Peter Laursen <peter_laursen@webyog.com> wrote:
I really don't know if it should take 2, 5 or 60 seconds. The numbers surprise me (in the bad maner). There is an incredible overhead when wrapping this query in a function. I wonder if there is an invocation of the function for every iteration.
-- Peter
On Tue, Mar 3, 2015 at 2:51 PM, Federico Razzoli <federico_raz@yahoo.it> wrote:
SELECT 1 was .42. What I find amazing is not the absolute numbers (it's an old computer with desktop installed, etc) but the difference between the tests. If SELECT 1 takes .42, test 2 shouldn't take more than 1 min... do you agree?
Federico
-------------------------------------------- Mar 3/3/15, Justin Swanhart <greenlion@gmail.com> ha scritto:
Oggetto: Re: [Maria-discuss] stored programs A: "Peter Laursen" <peter_laursen@webyog.com> Cc: "Federico Razzoli" <federico_raz@yahoo.it>, "Maria Discuss" < maria-discuss@lists.launchpad.net> Data: Martedì 3 marzo 2015, 14:28
You probably have either a faster CPU or bigger cache on your cpu. How long does benchmark select 1 take? You should find it is faster on your system too, right? --Justin
Sent from my iPhone On Mar 3, 2015, at 6:18 AM, Peter Laursen <peter_laursen@webyog.com> wrote:
Your 3rd test case takes 2:02 in MariaDB 10.1 and 2:13 in MySQL 5.6 on my system (when otherwise idle) . -- Peter On Tue, Mar 3, 2015 at 1:58 PM, Federico Razzoli <federico_raz@yahoo.it> wrote: I made some quick test to show what I mean by "performance problem". Note that I'm not saying the the first and the second test cases should perform equally. But there is too much difference - see the conclusion.
1)
MariaDB [test]> SELECT BENCHMARK(50000000, (SELECT 1));
+---------------------------------+
| BENCHMARK(50000000, (SELECT 1)) |
+---------------------------------+
| 0 |
+---------------------------------+
1 row in set (0.42 sec)
2)
DELIMITER ||
CREATE FUNCTION f()
RETURNS TINYINT
BEGIN
RETURN 1;
END ||
DELIMITER ;
MariaDB [test]> SELECT BENCHMARK(50000000, (SELECT f()));
+-----------------------------------+
| BENCHMARK(50000000, (SELECT f())) |
+-----------------------------------+
| 0 |
+-----------------------------------+
1 row in set (2 min 5.70 sec)
3)
DELIMITER ||
CREATE FUNCTION f(x TINYINT)
RETURNS TINYINT
BEGIN
RETURN x;
END ||
DELIMITER ;
MariaDB [test]> SELECT BENCHMARK(50000000, (SELECT f(1)));
+------------------------------------+
| BENCHMARK(50000000, (SELECT f(1))) |
+------------------------------------+
| 0 |
+------------------------------------+
1 row in set (3 min 35.20 sec)
-- Conclusions:
Times in seconds:
0.42
125.70
215.20
I don't know which ratio would be acceptable, but the difference betweem a trivial query and a trivial function is too high.
Also, the difference between 2) and 3) is that the function in 3) accepts and returns a parameter. Again, the performance difference seems to me too high.
Regards
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
_______________________________________________ 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
However I wasn't able to produce any bad/good speed change by playing with @@stored_program_cache. Anyone knows the reason? (this is just a curiosity) Federico -------------------------------------------- Mar 3/3/15, Justin Swanhart <greenlion@gmail.com> ha scritto: Oggetto: Re: [Maria-discuss] stored programs A: "Peter Laursen" <peter_laursen@webyog.com> Cc: "Federico Razzoli" <federico_raz@yahoo.it>, "Maria Discuss" <maria-discuss@lists.launchpad.net> Data: Martedì 3 marzo 2015, 15:04 Hi, The server parses the function into a form of bytecode which is then cached until the routine changes. In the past any routine change would invalidate cache for all routines and that sometimes can still happen but it is rare. The server then interprets the bytecode for every call to the function and yes, the function is executed every time unless you memoize the function ( if(function_input != @last_input) then @last_input = function_input; return @last_result := function(function_input); else return @last_result; On Tue, Mar 3, 2015 at 6:59 AM, Peter Laursen <peter_laursen@webyog.com> wrote: I really don't know if it should take 2, 5 or 60 seconds. The numbers surprise me (in the bad maner). There is an incredible overhead when wrapping this query in a function. I wonder if there is an invocation of the function for every iteration. -- Peter On Tue, Mar 3, 2015 at 2:51 PM, Federico Razzoli <federico_raz@yahoo.it> wrote: SELECT 1 was .42. What I find amazing is not the absolute numbers (it's an old computer with desktop installed, etc) but the difference between the tests. If SELECT 1 takes .42, test 2 shouldn't take more than 1 min... do you agree? Federico -------------------------------------------- Mar 3/3/15, Justin Swanhart <greenlion@gmail.com> ha scritto: Oggetto: Re: [Maria-discuss] stored programs A: "Peter Laursen" <peter_laursen@webyog.com> Cc: "Federico Razzoli" <federico_raz@yahoo.it>, "Maria Discuss" <maria-discuss@lists.launchpad.net> Data: Martedì 3 marzo 2015, 14:28 You probably have either a faster CPU or bigger cache on your cpu. How long does benchmark select 1 take? You should find it is faster on your system too, right? --Justin Sent from my iPhone On Mar 3, 2015, at 6:18 AM, Peter Laursen <peter_laursen@webyog.com> wrote: Your 3rd test case takes 2:02 in MariaDB 10.1 and 2:13 in MySQL 5.6 on my system (when otherwise idle) . -- Peter On Tue, Mar 3, 2015 at 1:58 PM, Federico Razzoli <federico_raz@yahoo.it> wrote: I made some quick test to show what I mean by "performance problem". Note that I'm not saying the the first and the second test cases should perform equally. But there is too much difference - see the conclusion. 1) MariaDB [test]> SELECT BENCHMARK(50000000, (SELECT 1)); +---------------------------------+ | BENCHMARK(50000000, (SELECT 1)) | +---------------------------------+ | 0 | +---------------------------------+ 1 row in set (0.42 sec) 2) DELIMITER || CREATE FUNCTION f() RETURNS TINYINT BEGIN RETURN 1; END || DELIMITER ; MariaDB [test]> SELECT BENCHMARK(50000000, (SELECT f())); +-----------------------------------+ | BENCHMARK(50000000, (SELECT f())) | +-----------------------------------+ | 0 | +-----------------------------------+ 1 row in set (2 min 5.70 sec) 3) DELIMITER || CREATE FUNCTION f(x TINYINT) RETURNS TINYINT BEGIN RETURN x; END || DELIMITER ; MariaDB [test]> SELECT BENCHMARK(50000000, (SELECT f(1))); +------------------------------------+ | BENCHMARK(50000000, (SELECT f(1))) | +------------------------------------+ | 0 | +------------------------------------+ 1 row in set (3 min 35.20 sec) -- Conclusions: Times in seconds: 0.42 125.70 215.20 I don't know which ratio would be acceptable, but the difference betweem a trivial query and a trivial function is too high. Also, the difference between 2) and 3) is that the function in 3) accepts and returns a parameter. Again, the performance difference seems to me too high. Regards 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 _______________________________________________ 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
Just a note about Oracle's "debugger": it is not a debugger. A debugger is a server feature that exposes an API for the execution control and variables inspecting. Oracle's "debugger" is just a tool that adds debug code to our stored procedure. Which is acceptable from third-party "debuggers", but quite ridicolous if it comes from MySQL's vendor. I don't consider this kind of solutions reliable... and I don't think that stored procedure developers should be forced to buy Microsoft products. Regards Federico -------------------------------------------- Mar 3/3/15, Peter Laursen <peter_laursen@webyog.com> ha scritto: Oggetto: Re: [Maria-discuss] stored programs A: "Federico Razzoli" <federico_raz@yahoo.it> Cc: "Maria Discuss" <maria-discuss@lists.launchpad.net> Data: Martedì 3 marzo 2015, 12:48 1) +1 for "ability to prepare a statement from a local variable" what I requested 6½ years ago: http://bugs.mysql.com/bug.php?id=40038 2) I agree that no debugging facility is a serious limitation (though Oracle claim that their "MySQL for Visual Studio" can do it (with Visual Studio on Windows obviously). I have not tried). 3) another (supplementary) language for stored programs. This has been discussed before in severalcontexts. Problem is that there is no 'fit' language being cross-platform. PostgreSQl has Perl, SQL Server as C#. The current 'sp language' (for loops, conditions etc.) is based on ADA. 'Adascript' implementations (covering around 60% of full ADA, I think) exist and could be considered. Or LUA that looks somewhat similar. But the interpreter will need to ship with the server. Just my 5 cents on this! -- Peter-- Webyog On Tue, Mar 3, 2015 at 12:31 PM, Federico Razzoli <federico_raz@yahoo.it> wrote: Hi Sergej No, it was something more general. Performance is not sufficient, there is no debugging facility, and stored programs are not flexible enough. Arrays, variable number of arguments and the ability to prepare a statement from a local variable would help a lot. Or perhaps, the ability to use a language other than SQL would be a solution. I used the OR REPLACE clause to point out that both MariaDB and MySQL development is very slow (if any) when it comes to stored programs. As a user and a MariaDB lover, I think that this is a pity. Regards Federico -------------------------------------------- Mar 3/3/15, Sergei Golubchik <serg@mariadb.org> ha scritto: Oggetto: Re: [Maria-discuss] stored programs A: "Federico Razzoli" <federico_raz@yahoo.it> Cc: maria-discuss@lists.launchpad.net Data: Martedì 3 marzo 2015, 10:48 Hi, Federico! On Mar 03, Federico Razzoli wrote: > Reading 10.0.3 release notes: > > https://mariadb.com/kb/en/mariadb/mariadb-1013-release-notes/ > > I see that IF EXISTS, IF NOT EXISTS and OR REPLACE are now almost > consistent. "Almost" means that... OR REPLACE still doesn't apply to > stored procedures, functions, triggers, events. Support for events is already pushed (albeit after 10.1.3). Support for triggers will be pushed any day now (already reviewed and approved, so there's no more work left on it). I suppose that stored procedures and functions will follow soon. This was a GSoC 2014 project that added support for these clauses to *all* objects. It's just being pushed piecewise, object by object. > Recently, during a public session, a PostgreSQL user asked me if > MariaDB supports stored procedures - in his opinion, MySQL doesn't, no > matter what the manual says. Unfortunately my answer was that MariaDB > support for stored procedure is the same as MySQL ("so the answer is > no", he said). I don't understand what exactly missing feature that user had in mind. It couldn't have been "CREATE OR REPLACE", this seems so minor. Or was it? Regards, Sergei _______________________________________________ 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
I wrote that I did not try it and did not know how it works. I thought it was less prmitive. BTW: wrote this blog long time ago too: http://blog.webyog.com/2011/12/06/debugging-stored-programs-in-mysql/ - so I fully agree! - Peter On Tue, Mar 3, 2015 at 2:05 PM, Federico Razzoli <federico_raz@yahoo.it> wrote:
Just a note about Oracle's "debugger": it is not a debugger. A debugger is a server feature that exposes an API for the execution control and variables inspecting. Oracle's "debugger" is just a tool that adds debug code to our stored procedure. Which is acceptable from third-party "debuggers", but quite ridicolous if it comes from MySQL's vendor.
I don't consider this kind of solutions reliable... and I don't think that stored procedure developers should be forced to buy Microsoft products.
Regards Federico
-------------------------------------------- Mar 3/3/15, Peter Laursen <peter_laursen@webyog.com> ha scritto:
Oggetto: Re: [Maria-discuss] stored programs A: "Federico Razzoli" <federico_raz@yahoo.it> Cc: "Maria Discuss" <maria-discuss@lists.launchpad.net> Data: Martedì 3 marzo 2015, 12:48
1) +1 for "ability to prepare a statement from a local variable" what I requested 6½ years ago: http://bugs.mysql.com/bug.php?id=40038 2) I agree that no debugging facility is a serious limitation (though Oracle claim that their "MySQL for Visual Studio" can do it (with Visual Studio on Windows obviously). I have not tried). 3) another (supplementary) language for stored programs. This has been discussed before in severalcontexts. Problem is that there is no 'fit' language being cross-platform. PostgreSQl has Perl, SQL Server as C#. The current 'sp language' (for loops, conditions etc.) is based on ADA. 'Adascript' implementations (covering around 60% of full ADA, I think) exist and could be considered. Or LUA that looks somewhat similar. But the interpreter will need to ship with the server.
Just my 5 cents on this!
-- Peter-- Webyog
On Tue, Mar 3, 2015 at 12:31 PM, Federico Razzoli <federico_raz@yahoo.it> wrote: Hi Sergej
No, it was something more general. Performance is not sufficient, there is no debugging facility, and stored programs are not flexible enough. Arrays, variable number of arguments and the ability to prepare a statement from a local variable would help a lot. Or perhaps, the ability to use a language other than SQL would be a solution.
I used the OR REPLACE clause to point out that both MariaDB and MySQL development is very slow (if any) when it comes to stored programs. As a user and a MariaDB lover, I think that this is a pity.
Regards
Federico
--------------------------------------------
Mar 3/3/15, Sergei Golubchik <serg@mariadb.org> ha scritto:
Oggetto: Re: [Maria-discuss] stored programs
A: "Federico Razzoli" <federico_raz@yahoo.it>
Cc: maria-discuss@lists.launchpad.net
Data: Martedì 3 marzo 2015, 10:48
Hi, Federico!
On Mar 03, Federico Razzoli
wrote:
Reading 10.0.3 release notes:
https://mariadb.com/kb/en/mariadb/mariadb-1013-release-notes/
I see that IF
EXISTS, IF NOT EXISTS and OR REPLACE are now almost
consistent. "Almost" means
that... OR REPLACE still doesn't apply to
stored procedures, functions, triggers,
events.
Support for events
is already pushed (albeit after 10.1.3).
Support for triggers will be pushed any day now
(already reviewed and
approved, so
there's no more work left on it). I suppose that
stored
procedures and functions will follow
soon.
This was a GSoC 2014
project that added support for these clauses to
*all* objects. It's just being pushed
piecewise, object by object.
Recently, during a public session, a
PostgreSQL user asked me if
MariaDB
supports stored procedures - in his opinion, MySQL
doesn't, no
matter what the manual
says. Unfortunately my answer was that MariaDB
support for stored procedure is the same
as MySQL ("so the answer is
no", he said).
I
don't understand what exactly missing feature that user
had in mind.
It couldn't have been
"CREATE OR REPLACE", this seems so minor.
Or was it?
Regards,
Sergei
_______________________________________________
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
_______________________________________________ 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
You can't drop a busy proc in production to replace it. Many users would get an error. This is the same reason views have had CoR for so long. So yes it is a big deal! Anyway, what I want most are Antony Curtis' stored proc / parser changes (mdev 820 if I'm not mistaken). I am especially interested in table functions and external stored routines. I'd also like to discuss window functions too. I've implemented them in shard-query and have ideas about how to implement them in the server, but pluggable parser would be really useful here. --Justin Sent from my iPhone
On Mar 3, 2015, at 2:48 AM, Sergei Golubchik <serg@mariadb.org> wrote:
Hi, Federico!
On Mar 03, Federico Razzoli wrote: Reading 10.0.3 release notes:
https://mariadb.com/kb/en/mariadb/mariadb-1013-release-notes/
I see that IF EXISTS, IF NOT EXISTS and OR REPLACE are now almost consistent. "Almost" means that... OR REPLACE still doesn't apply to stored procedures, functions, triggers, events.
Support for events is already pushed (albeit after 10.1.3). Support for triggers will be pushed any day now (already reviewed and approved, so there's no more work left on it). I suppose that stored procedures and functions will follow soon.
This was a GSoC 2014 project that added support for these clauses to *all* objects. It's just being pushed piecewise, object by object.
Recently, during a public session, a PostgreSQL user asked me if MariaDB supports stored procedures - in his opinion, MySQL doesn't, no matter what the manual says. Unfortunately my answer was that MariaDB support for stored procedure is the same as MySQL ("so the answer is no", he said).
I don't understand what exactly missing feature that user had in mind. It couldn't have been "CREATE OR REPLACE", this seems so minor. Or was it?
Regards, Sergei
_______________________________________________ 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
If this "You can't drop a busy proc in production to replace it" was for me, then note the passage from my blog: "you can add a IN-parameter *(debug: integer)* to a Stored Procedure paramer-list and *CALL mysp(….,0|1)* what would then control if the stored program should enter or bypass debugging code when executing". This is the best workaround I've found. On Tue, Mar 3, 2015 at 2:17 PM, Justin Swanhart <greenlion@gmail.com> wrote:
You can't drop a busy proc in production to replace it. Many users would get an error. This is the same reason views have had CoR for so long. So yes it is a big deal!
Anyway, what I want most are Antony Curtis' stored proc / parser changes (mdev 820 if I'm not mistaken). I am especially interested in table functions and external stored routines.
I'd also like to discuss window functions too. I've implemented them in shard-query and have ideas about how to implement them in the server, but pluggable parser would be really useful here.
--Justin
Sent from my iPhone
On Mar 3, 2015, at 2:48 AM, Sergei Golubchik <serg@mariadb.org> wrote:
Hi, Federico!
On Mar 03, Federico Razzoli wrote: Reading 10.0.3 release notes:
https://mariadb.com/kb/en/mariadb/mariadb-1013-release-notes/
I see that IF EXISTS, IF NOT EXISTS and OR REPLACE are now almost consistent. "Almost" means that... OR REPLACE still doesn't apply to stored procedures, functions, triggers, events.
Support for events is already pushed (albeit after 10.1.3). Support for triggers will be pushed any day now (already reviewed and approved, so there's no more work left on it). I suppose that stored procedures and functions will follow soon.
This was a GSoC 2014 project that added support for these clauses to *all* objects. It's just being pushed piecewise, object by object.
Recently, during a public session, a PostgreSQL user asked me if MariaDB supports stored procedures - in his opinion, MySQL doesn't, no matter what the manual says. Unfortunately my answer was that MariaDB support for stored procedure is the same as MySQL ("so the answer is no", he said).
I don't understand what exactly missing feature that user had in mind. It couldn't have been "CREATE OR REPLACE", this seems so minor. Or was it?
Regards, Sergei
_______________________________________________ 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
_______________________________________________ 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
I was responding to Sergei Sent from my iPhone
On Mar 3, 2015, at 6:22 AM, Peter Laursen <peter_laursen@webyog.com> wrote:
If this "You can't drop a busy proc in production to replace it" was for me, then note the passage from my blog:
"you can add a IN-parameter (debug: integer) to a Stored Procedure paramer-list and CALL mysp(….,0|1) what would then control if the stored program should enter or bypass debugging code when executing". This is the best workaround I've found.
On Tue, Mar 3, 2015 at 2:17 PM, Justin Swanhart <greenlion@gmail.com> wrote: You can't drop a busy proc in production to replace it. Many users would get an error. This is the same reason views have had CoR for so long. So yes it is a big deal!
Anyway, what I want most are Antony Curtis' stored proc / parser changes (mdev 820 if I'm not mistaken). I am especially interested in table functions and external stored routines.
I'd also like to discuss window functions too. I've implemented them in shard-query and have ideas about how to implement them in the server, but pluggable parser would be really useful here.
--Justin
Sent from my iPhone
On Mar 3, 2015, at 2:48 AM, Sergei Golubchik <serg@mariadb.org> wrote:
Hi, Federico!
On Mar 03, Federico Razzoli wrote: Reading 10.0.3 release notes:
https://mariadb.com/kb/en/mariadb/mariadb-1013-release-notes/
I see that IF EXISTS, IF NOT EXISTS and OR REPLACE are now almost consistent. "Almost" means that... OR REPLACE still doesn't apply to stored procedures, functions, triggers, events.
Support for events is already pushed (albeit after 10.1.3). Support for triggers will be pushed any day now (already reviewed and approved, so there's no more work left on it). I suppose that stored procedures and functions will follow soon.
This was a GSoC 2014 project that added support for these clauses to *all* objects. It's just being pushed piecewise, object by object.
Recently, during a public session, a PostgreSQL user asked me if MariaDB supports stored procedures - in his opinion, MySQL doesn't, no matter what the manual says. Unfortunately my answer was that MariaDB support for stored procedure is the same as MySQL ("so the answer is no", he said).
I don't understand what exactly missing feature that user had in mind. It couldn't have been "CREATE OR REPLACE", this seems so minor. Or was it?
Regards, Sergei
_______________________________________________ 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
_______________________________________________ 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
After you debug you proc you kinda have to replace the non buggy one. In production. While people are using it. Unless you version your whole code base and push out a new version to use a new name. Sent from my iPhone
On Mar 3, 2015, at 6:22 AM, Peter Laursen <peter_laursen@webyog.com> wrote:
If this "You can't drop a busy proc in production to replace it" was for me, then note the passage from my blog:
"you can add a IN-parameter (debug: integer) to a Stored Procedure paramer-list and CALL mysp(….,0|1) what would then control if the stored program should enter or bypass debugging code when executing". This is the best workaround I've found.
On Tue, Mar 3, 2015 at 2:17 PM, Justin Swanhart <greenlion@gmail.com> wrote: You can't drop a busy proc in production to replace it. Many users would get an error. This is the same reason views have had CoR for so long. So yes it is a big deal!
Anyway, what I want most are Antony Curtis' stored proc / parser changes (mdev 820 if I'm not mistaken). I am especially interested in table functions and external stored routines.
I'd also like to discuss window functions too. I've implemented them in shard-query and have ideas about how to implement them in the server, but pluggable parser would be really useful here.
--Justin
Sent from my iPhone
On Mar 3, 2015, at 2:48 AM, Sergei Golubchik <serg@mariadb.org> wrote:
Hi, Federico!
On Mar 03, Federico Razzoli wrote: Reading 10.0.3 release notes:
https://mariadb.com/kb/en/mariadb/mariadb-1013-release-notes/
I see that IF EXISTS, IF NOT EXISTS and OR REPLACE are now almost consistent. "Almost" means that... OR REPLACE still doesn't apply to stored procedures, functions, triggers, events.
Support for events is already pushed (albeit after 10.1.3). Support for triggers will be pushed any day now (already reviewed and approved, so there's no more work left on it). I suppose that stored procedures and functions will follow soon.
This was a GSoC 2014 project that added support for these clauses to *all* objects. It's just being pushed piecewise, object by object.
Recently, during a public session, a PostgreSQL user asked me if MariaDB supports stored procedures - in his opinion, MySQL doesn't, no matter what the manual says. Unfortunately my answer was that MariaDB support for stored procedure is the same as MySQL ("so the answer is no", he said).
I don't understand what exactly missing feature that user had in mind. It couldn't have been "CREATE OR REPLACE", this seems so minor. Or was it?
Regards, Sergei
_______________________________________________ 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
_______________________________________________ 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
It is actually a 512 MB DDR3-RAM Geforce card - but it hardly matters, I think. --Peter On Tue, Mar 3, 2015 at 2:30 PM, Justin Swanhart <greenlion@gmail.com> wrote:
After you debug you proc you kinda have to replace the non buggy one. In production. While people are using it. Unless you version your whole code base and push out a new version to use a new name.
Sent from my iPhone
On Mar 3, 2015, at 6:22 AM, Peter Laursen <peter_laursen@webyog.com> wrote:
If this "You can't drop a busy proc in production to replace it" was for me, then note the passage from my blog:
"you can add a IN-parameter *(debug: integer)* to a Stored Procedure paramer-list and *CALL mysp(….,0|1)* what would then control if the stored program should enter or bypass debugging code when executing". This is the best workaround I've found.
On Tue, Mar 3, 2015 at 2:17 PM, Justin Swanhart <greenlion@gmail.com> wrote:
You can't drop a busy proc in production to replace it. Many users would get an error. This is the same reason views have had CoR for so long. So yes it is a big deal!
Anyway, what I want most are Antony Curtis' stored proc / parser changes (mdev 820 if I'm not mistaken). I am especially interested in table functions and external stored routines.
I'd also like to discuss window functions too. I've implemented them in shard-query and have ideas about how to implement them in the server, but pluggable parser would be really useful here.
--Justin
Sent from my iPhone
On Mar 3, 2015, at 2:48 AM, Sergei Golubchik <serg@mariadb.org> wrote:
Hi, Federico!
On Mar 03, Federico Razzoli wrote: Reading 10.0.3 release notes:
https://mariadb.com/kb/en/mariadb/mariadb-1013-release-notes/
I see that IF EXISTS, IF NOT EXISTS and OR REPLACE are now almost consistent. "Almost" means that... OR REPLACE still doesn't apply to stored procedures, functions, triggers, events.
Support for events is already pushed (albeit after 10.1.3). Support for triggers will be pushed any day now (already reviewed and approved, so there's no more work left on it). I suppose that stored procedures and functions will follow soon.
This was a GSoC 2014 project that added support for these clauses to *all* objects. It's just being pushed piecewise, object by object.
Recently, during a public session, a PostgreSQL user asked me if MariaDB supports stored procedures - in his opinion, MySQL doesn't, no matter what the manual says. Unfortunately my answer was that MariaDB support for stored procedure is the same as MySQL ("so the answer is no", he said).
I don't understand what exactly missing feature that user had in mind. It couldn't have been "CREATE OR REPLACE", this seems so minor. Or was it?
Regards, Sergei
_______________________________________________ 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
_______________________________________________ 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
And a little more RAM than I wrote: 32 GB! On Tue, Mar 3, 2015 at 2:41 PM, Peter Laursen <peter_laursen@webyog.com> wrote:
It is actually a 512 MB DDR3-RAM Geforce card - but it hardly matters, I think. --Peter
On Tue, Mar 3, 2015 at 2:30 PM, Justin Swanhart <greenlion@gmail.com> wrote:
After you debug you proc you kinda have to replace the non buggy one. In production. While people are using it. Unless you version your whole code base and push out a new version to use a new name.
Sent from my iPhone
On Mar 3, 2015, at 6:22 AM, Peter Laursen <peter_laursen@webyog.com> wrote:
If this "You can't drop a busy proc in production to replace it" was for me, then note the passage from my blog:
"you can add a IN-parameter *(debug: integer)* to a Stored Procedure paramer-list and *CALL mysp(….,0|1)* what would then control if the stored program should enter or bypass debugging code when executing". This is the best workaround I've found.
On Tue, Mar 3, 2015 at 2:17 PM, Justin Swanhart <greenlion@gmail.com> wrote:
You can't drop a busy proc in production to replace it. Many users would get an error. This is the same reason views have had CoR for so long. So yes it is a big deal!
Anyway, what I want most are Antony Curtis' stored proc / parser changes (mdev 820 if I'm not mistaken). I am especially interested in table functions and external stored routines.
I'd also like to discuss window functions too. I've implemented them in shard-query and have ideas about how to implement them in the server, but pluggable parser would be really useful here.
--Justin
Sent from my iPhone
On Mar 3, 2015, at 2:48 AM, Sergei Golubchik <serg@mariadb.org> wrote:
Hi, Federico!
On Mar 03, Federico Razzoli wrote: Reading 10.0.3 release notes:
https://mariadb.com/kb/en/mariadb/mariadb-1013-release-notes/
I see that IF EXISTS, IF NOT EXISTS and OR REPLACE are now almost consistent. "Almost" means that... OR REPLACE still doesn't apply to stored procedures, functions, triggers, events.
Support for events is already pushed (albeit after 10.1.3). Support for triggers will be pushed any day now (already reviewed and approved, so there's no more work left on it). I suppose that stored procedures and functions will follow soon.
This was a GSoC 2014 project that added support for these clauses to *all* objects. It's just being pushed piecewise, object by object.
Recently, during a public session, a PostgreSQL user asked me if MariaDB supports stored procedures - in his opinion, MySQL doesn't, no matter what the manual says. Unfortunately my answer was that MariaDB support for stored procedure is the same as MySQL ("so the answer is no", he said).
I don't understand what exactly missing feature that user had in mind. It couldn't have been "CREATE OR REPLACE", this seems so minor. Or was it?
Regards, Sergei
_______________________________________________ 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
_______________________________________________ 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
I totally agree, it's an important feature. As a side note, when changing the logic of a database we may need to modify more than one object (tables, procedures, views, triggers...). In those cases, it would be great to be able to make all the changes atomically. But I suppose that this would require a lot of work... Federico -------------------------------------------- Mar 3/3/15, Justin Swanhart <greenlion@gmail.com> ha scritto: Oggetto: Re: [Maria-discuss] stored programs A: "Peter Laursen" <peter_laursen@webyog.com> Cc: "maria-discuss@lists.launchpad.net" <maria-discuss@lists.launchpad.net> Data: Martedì 3 marzo 2015, 14:30 After you debug you proc you kinda have to replace the non buggy one. In production. While people are using it. Unless you version your whole code base and push out a new version to use a new name. Sent from my iPhone On Mar 3, 2015, at 6:22 AM, Peter Laursen <peter_laursen@webyog.com> wrote: If this "You can't drop a busy proc in production to replace it" was for me, then note the passage from my blog: "you can add a IN-parameter (debug: integer) to a Stored Procedure paramer-list and CALL mysp(….,0|1) what would then control if the stored program should enter or bypass debugging code when executing". This is the best workaround I've found. On Tue, Mar 3, 2015 at 2:17 PM, Justin Swanhart <greenlion@gmail.com> wrote: You can't drop a busy proc in production to replace it. Many users would get an error. This is the same reason views have had CoR for so long. So yes it is a big deal! Anyway, what I want most are Antony Curtis' stored proc / parser changes (mdev 820 if I'm not mistaken). I am especially interested in table functions and external stored routines. I'd also like to discuss window functions too. I've implemented them in shard-query and have ideas about how to implement them in the server, but pluggable parser would be really useful here. --Justin Sent from my iPhone
On Mar 3, 2015, at 2:48 AM, Sergei Golubchik <serg@mariadb.org> wrote:
Hi, Federico!
On Mar 03, Federico Razzoli wrote:
Reading 10.0.3 release notes:
https://mariadb.com/kb/en/mariadb/mariadb-1013-release-notes/
I see that IF EXISTS, IF NOT EXISTS and OR REPLACE are now almost
consistent. "Almost" means that... OR REPLACE still doesn't apply to
stored procedures, functions, triggers, events.
Support for events is already pushed (albeit after 10.1.3).
Support for triggers will be pushed any day now (already reviewed and
approved, so there's no more work left on it). I suppose that stored
procedures and functions will follow soon.
This was a GSoC 2014 project that added support for
these clauses to
*all* objects. It's just being pushed piecewise, object by object.
Recently, during a public session, a PostgreSQL user asked me if
MariaDB supports stored procedures - in his opinion, MySQL doesn't, no
matter what the manual says. Unfortunately my answer was that MariaDB
support for stored procedure is the same as MySQL ("so the answer is
no", he said).
I don't understand what exactly missing feature
that user had in mind.
It couldn't have been "CREATE OR REPLACE", this seems so minor.
Or was it?
Regards,
Sergei
_______________________________________________
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
_______________________________________________ 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 -----Segue allegato----- _______________________________________________ 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, Yes, someday, hopefully, MariaDB can get transactional DDL. I want to make a good case for a pluggable parser, so I am going to briefly describe the tools I write and how they could benefit from one. I need a pluggable parser or at the least, table functions(which can return more than one column) for Shard-Query, which provides transparent sharding, parallel query, and window functions. Shard-Query puts a lot of work into rewriting a query. I does things like semijoin materialization, after all how else could a semijoin work on a sharded data set? Shard-Query is much more powerful than Spider for reads, though spider is probably better for writes. There is no reason they couldn't work together. Shard-Query can be called from a stored procedure wrapping gearman UDF, but I would MUCH prefer if I could implement the logic in the server for parallel query. This is something that MariaDB really needs and it would set it apart from MySQL in a major way. Much of the work on Shard-Query was done in cooperation with an employee of the Ericsson corporation, which was (and still is to my knowledge) used for analysis and billing of cell phone charges. One of the changes for ericsson was pluggable aggregate functions like percentile. Another prerequisite for putting parallel query in the database is the unified interface for running SQL in the server (there is an MDEV for it). Basically I envision a parser plugin which would rewrite the query internally to each partition, each query being an insert into one temporary table. Once all inserts complete the query will be rewritten to use the temp table and sent through the normal query pipeline where the results will be sent to the end user, and a deinit function will happen to drop the temporary table and free up any memory that was allocated, etc. I also need the pluggable parser for Flexviews, which implements incrementally refreshable materialized views. It includes FlexCDC, a complete change data capture tool that supports ALTER TABLE and that supports plugins, so you can replicate to other systems or send rows to a message queue. FlexCDC processes the binary log and inserts data into log tables. The log tables are then used to refresh the views. Joins and all major aggregate functions are supported and it is in use in production in major internet properties. Flexviews needs a pluggable parser to rewrite queries to access the views transparent. FlexCDC could be rewritten as a daemon plugin which acts as a replication slave inside the database. The pluggable parser could support CREATE MATERIALIZED VIEW. I think these are good cases for a pluggable parser. Let me know if you have any questions or ideas. Btw, there are KB articles on both of the tools. I'm going to work up a tutorial for both soon. Let me know if you have questions. You can find my tools here: http://github.com/greenlion/swanhart-tools Also, you might find this UDF useful, or a comical abuse of a plugin, or both: http://github.com/greenlion/FastBit_UDF --Justin On Tue, Mar 3, 2015 at 7:16 AM, Federico Razzoli <federico_raz@yahoo.it> wrote:
I totally agree, it's an important feature. As a side note, when changing the logic of a database we may need to modify more than one object (tables, procedures, views, triggers...). In those cases, it would be great to be able to make all the changes atomically. But I suppose that this would require a lot of work...
Federico
-------------------------------------------- Mar 3/3/15, Justin Swanhart <greenlion@gmail.com> ha scritto:
Oggetto: Re: [Maria-discuss] stored programs A: "Peter Laursen" <peter_laursen@webyog.com> Cc: "maria-discuss@lists.launchpad.net" < maria-discuss@lists.launchpad.net> Data: Martedì 3 marzo 2015, 14:30
After you debug you proc you kinda have to replace the non buggy one. In production. While people are using it. Unless you version your whole code base and push out a new version to use a new name.
Sent from my iPhone On Mar 3, 2015, at 6:22 AM, Peter Laursen <peter_laursen@webyog.com> wrote:
If this "You can't drop a busy proc in production to replace it" was for me, then note the passage from my blog: "you can add a IN-parameter (debug: integer) to a Stored Procedure paramer-list and CALL mysp(….,0|1) what would then control if the stored program should enter or bypass debugging code when executing". This is the best workaround I've found. On Tue, Mar 3, 2015 at 2:17 PM, Justin Swanhart <greenlion@gmail.com> wrote: You can't drop a busy proc in production to replace it. Many users would get an error. This is the same reason views have had CoR for so long. So yes it is a big deal!
Anyway, what I want most are Antony Curtis' stored proc / parser changes (mdev 820 if I'm not mistaken). I am especially interested in table functions and external stored routines.
I'd also like to discuss window functions too. I've implemented them in shard-query and have ideas about how to implement them in the server, but pluggable parser would be really useful here.
--Justin
Sent from my iPhone
On Mar 3, 2015, at 2:48 AM, Sergei Golubchik <serg@mariadb.org> wrote:
Hi, Federico!
On Mar 03, Federico Razzoli wrote:
Reading 10.0.3 release notes:
https://mariadb.com/kb/en/mariadb/mariadb-1013-release-notes/
I see that IF EXISTS, IF NOT EXISTS and OR REPLACE are now almost
consistent. "Almost" means that... OR REPLACE still doesn't apply to
stored procedures, functions, triggers, events.
Support for events is already pushed (albeit after 10.1.3).
Support for triggers will be pushed any day now (already reviewed and
approved, so there's no more work left on it). I suppose that stored
procedures and functions will follow soon.
This was a GSoC 2014 project that added support for these clauses to
*all* objects. It's just being pushed piecewise, object by object.
Recently, during a public session, a PostgreSQL user asked me if
MariaDB supports stored procedures - in his opinion, MySQL doesn't, no
matter what the manual says. Unfortunately my answer was that MariaDB
support for stored procedure is the same as MySQL ("so the answer is
no", he said).
I don't understand what exactly missing feature that user had in mind.
It couldn't have been "CREATE OR REPLACE", this seems so minor.
Or was it?
Regards,
Sergei
_______________________________________________
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
_______________________________________________
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
-----Segue allegato-----
_______________________________________________ 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
_______________________________________________ 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, Justin! On Mar 03, Justin Swanhart wrote:
You can't drop a busy proc in production to replace it. Many users would get an error. This is the same reason views have had CoR for so long. So yes it is a big deal!
Okay, I'm happy to know it. My main reason for offering this GSoC project was syntax consistency. Same syntax works identically for all objects, just as a user expects, no gotchas or unpleasant surprises.
Anyway, what I want most are Antony Curtis' stored proc / parser changes (mdev 820 if I'm not mistaken). I am especially interested in table functions and external stored routines.
Yes, I'd be happy to see them in 10.2. It's too early to tell what is going to be there, but I'll certainly put them at the top of list.
I'd also like to discuss window functions too. I've implemented them in shard-query and have ideas about how to implement them in the server, but pluggable parser would be really useful here.
Window functions have a good chance of being in 10.2, it's MDEV-6115. But I don't think that somebody is working on MDEV-6115 yet. I'm afraid that a pluggable parser is practically impossible to do with bison generated parsers. For a recursive descent parser - yes, but not with bison parsers :( The best we can do, I think, is to generalize certain parts of the parser by using a generic 'identifier' rule instead of a specific hard-coded keyword token. We've done it a couple of times for plugins: in CREATE TABLE: https://mariadb.com/kb/en/engine-defined-new-tablefieldindex-attributes/ and in SHOW and FLUSH: https://mariadb.com/kb/en/information-schema-plugins-show-and-flush-statemen... Regards, Sergei
So uhmm, use a new parser. Like the one antony wrote in LLVM? :) I mean, we would like to have window functions, CTE, and all those other fun things. SQL-92 is a not relevant anymore. On Tue, Mar 3, 2015 at 6:57 AM, Sergei Golubchik <serg@mariadb.org> wrote:
Hi, Justin!
On Mar 03, Justin Swanhart wrote:
You can't drop a busy proc in production to replace it. Many users would get an error. This is the same reason views have had CoR for so long. So yes it is a big deal!
Okay, I'm happy to know it. My main reason for offering this GSoC project was syntax consistency. Same syntax works identically for all objects, just as a user expects, no gotchas or unpleasant surprises.
Anyway, what I want most are Antony Curtis' stored proc / parser changes (mdev 820 if I'm not mistaken). I am especially interested in table functions and external stored routines.
Yes, I'd be happy to see them in 10.2. It's too early to tell what is going to be there, but I'll certainly put them at the top of list.
I'd also like to discuss window functions too. I've implemented them in shard-query and have ideas about how to implement them in the server, but pluggable parser would be really useful here.
Window functions have a good chance of being in 10.2, it's MDEV-6115. But I don't think that somebody is working on MDEV-6115 yet.
I'm afraid that a pluggable parser is practically impossible to do with bison generated parsers. For a recursive descent parser - yes, but not with bison parsers :(
The best we can do, I think, is to generalize certain parts of the parser by using a generic 'identifier' rule instead of a specific hard-coded keyword token. We've done it a couple of times for plugins:
in CREATE TABLE: https://mariadb.com/kb/en/engine-defined-new-tablefieldindex-attributes/ and in SHOW and FLUSH: https://mariadb.com/kb/en/information-schema-plugins-show-and-flush-statemen...
Regards, Sergei
Hi, Justin! On Mar 03, Justin Swanhart wrote:
So uhmm, use a new parser. Like the one antony wrote in LLVM? :) I mean, we would like to have window functions, CTE, and all those other fun things. SQL-92 is a not relevant anymore.
In MySQL times we've tried to replace a parser. Twice. Antony has spent a year on that - that was one of the attempts. We're still using the old one, so neither project was a success. And Oracle started "parser refactoring" in 5.7, but they're doing it piece-wise now, not trying to replace everything at once. And they'll have to stay with bison this way. What I mean - we very much want to use a new parser. We really do. But it's just such a huge project :( Regards, Sergei
Hi, What about providing an alternate parser in the server but only for plugins to use? The plugins rewrite the whole query, not modify the syntax tree so there is no fiddling with the query on THD, it is rewritten before the bison parser gets it. --Justin On Tue, Mar 3, 2015 at 8:07 AM, Sergei Golubchik <serg@mariadb.org> wrote:
Hi, Justin!
On Mar 03, Justin Swanhart wrote:
So uhmm, use a new parser. Like the one antony wrote in LLVM? :) I mean, we would like to have window functions, CTE, and all those other fun things. SQL-92 is a not relevant anymore.
In MySQL times we've tried to replace a parser. Twice. Antony has spent a year on that - that was one of the attempts.
We're still using the old one, so neither project was a success.
And Oracle started "parser refactoring" in 5.7, but they're doing it piece-wise now, not trying to replace everything at once. And they'll have to stay with bison this way.
What I mean - we very much want to use a new parser. We really do. But it's just such a huge project :(
Regards, Sergei
Am 03.03.2015 um 16:19 schrieb Justin Swanhart:
What about providing an alternate parser in the server but only for plugins to use? The plugins rewrite the whole query, not modify the syntax tree so there is no fiddling with the query on THD, it is rewritten before the bison parser gets it
don't feels like a good idea to have different behavior by design
Hi, Well you can wait forever for a parser that is pluggable, or you can have rewrite plugins that are so much more awesome than Oracle's rewrite plugin. It is effectively putting the power of MySQL proxy into the database. The SQL92 parser can do all the things needed under the covers. Fancy things like CTE and window functions become available. --Justin On Tue, Mar 3, 2015 at 8:23 AM, Reindl Harald <h.reindl@thelounge.net> wrote:
Am 03.03.2015 um 16:19 schrieb Justin Swanhart:
What about providing an alternate parser in the server but only for plugins to use? The plugins rewrite the whole query, not modify the syntax tree so there is no fiddling with the query on THD, it is rewritten before the bison parser gets it
don't feels like a good idea to have different behavior by design
_______________________________________________ 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, I have a mockup PHP interface for exactly such a thing. There are 'filters' which feed into other filters. A set of queries is created which are executed, and when queries can be executed in parallel the query is represented as an array of queries. This query passed through two filters, each filter sees the whole rewritten query from the previous filter. You would determine the order of filters when installing the plugin. This is the input: select a, count(*) from some_table st join c1 where c2 = 'open' group by a This is what the directed acyclic graph (query plan) looks like: Array( [has_rewrites] => 1 [plan] => Array ( [0] => CREATE TABLE IF NOT EXISTS p303_agg_16a836807550a78ff9793b7636ee4cbe (UNIQUE KEY gb_key (`expr_51179`)) ENGINE= AS SELECT a AS expr$0,COUNT(*) AS expr_463809566,a AS `expr_51179`FROM some_table PARTITION(p1) AS `st` JOIN table2 AS `t2` USING(c1) WHERE c2 = 'open' AND 0=1 GROUP BY `expr_51179` [1] => Array ( [0] => INSERT INTO p303_agg_16a836807550a78ff9793b7636ee4cbe SELECT a AS expr$0,COUNT(*) AS expr_463809566,a AS `expr_51179`FROM some_table PARTITION(p1) AS `st` JOIN table2 AS `t2` USING(c1) WHERE c2 = 'open' AND 1=1 GROUP BY `expr_51179` [1] => INSERT INTO p303_agg_16a836807550a78ff9793b7636ee4cbe SELECT a AS expr$0,COUNT(*) AS expr_463809566,a AS `expr_51179`FROM some_table PARTITION(p2) AS `st` JOIN table2 AS `t2` USING(c1) WHERE c2 = 'open' AND 1=1 GROUP BY `expr_51179` [2] => INSERT INTO p303_agg_16a836807550a78ff9793b7636ee4cbe SELECT a AS expr$0,COUNT(*) AS expr_463809566,a AS `expr_51179`FROM some_table PARTITION(p3) AS `st` JOIN table2 AS `t2` USING(c1) WHERE c2 = 'open' AND 1=1 GROUP BY `expr_51179` ) [2] => SELECT expr$0 AS `a`,SUM(expr_463809566) AS `count(*)`FROM `p303_agg_16a836807550a78ff9793b7636ee4cbe` GROUP BY a [4] => DROP TABLE IF EXISTS p303_agg_16a836807550a78ff9793b7636ee4cbe ) [errors] => Array ( )) On Tue, Mar 3, 2015 at 8:19 AM, Justin Swanhart <greenlion@gmail.com> wrote:
Hi,
What about providing an alternate parser in the server but only for plugins to use? The plugins rewrite the whole query, not modify the syntax tree so there is no fiddling with the query on THD, it is rewritten before the bison parser gets it.
--Justin
On Tue, Mar 3, 2015 at 8:07 AM, Sergei Golubchik <serg@mariadb.org> wrote:
Hi, Justin!
On Mar 03, Justin Swanhart wrote:
So uhmm, use a new parser. Like the one antony wrote in LLVM? :) I mean, we would like to have window functions, CTE, and all those other fun things. SQL-92 is a not relevant anymore.
In MySQL times we've tried to replace a parser. Twice. Antony has spent a year on that - that was one of the attempts.
We're still using the old one, so neither project was a success.
And Oracle started "parser refactoring" in 5.7, but they're doing it piece-wise now, not trying to replace everything at once. And they'll have to stay with bison this way.
What I mean - we very much want to use a new parser. We really do. But it's just such a huge project :(
Regards, Sergei
On 03/03/2015 05:57 AM, Sergei Golubchik wrote:
Hi, Justin!
On Mar 03, Justin Swanhart wrote:
You can't drop a busy proc in production to replace it. Many users would get an error. This is the same reason views have had CoR for so long. So yes it is a big deal!
Okay, I'm happy to know it. My main reason for offering this GSoC project was syntax consistency. Same syntax works identically for all objects, just as a user expects, no gotchas or unpleasant surprises.
Anyway, what I want most are Antony Curtis' stored proc / parser changes (mdev 820 if I'm not mistaken). I am especially interested in table functions and external stored routines.
Yes, I'd be happy to see them in 10.2. It's too early to tell what is going to be there, but I'll certainly put them at the top of list.
I'd also like to discuss window functions too. I've implemented them in shard-query and have ideas about how to implement them in the server, but pluggable parser would be really useful here.
Window functions have a good chance of being in 10.2, it's MDEV-6115. But I don't think that somebody is working on MDEV-6115 yet.
I started working on MDEV-6115 some time ago. Regards, Igor.
I'm afraid that a pluggable parser is practically impossible to do with bison generated parsers. For a recursive descent parser - yes, but not with bison parsers :(
The best we can do, I think, is to generalize certain parts of the parser by using a generic 'identifier' rule instead of a specific hard-coded keyword token. We've done it a couple of times for plugins:
in CREATE TABLE: https://mariadb.com/kb/en/engine-defined-new-tablefieldindex-attributes/ and in SHOW and FLUSH: https://mariadb.com/kb/en/information-schema-plugins-show-and-flush-statemen...
Regards, Sergei
_______________________________________________ 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, Igor! On Mar 03, Igor Babaev wrote:
I'd also like to discuss window functions too. I've implemented them in shard-query and have ideas about how to implement them in the server, but pluggable parser would be really useful here.
Window functions have a good chance of being in 10.2, it's MDEV-6115. But I don't think that somebody is working on MDEV-6115 yet.
I started working on MDEV-6115 some time ago.
Ah, great. Sorry, I didn't know it. Could you then discuss it with Justin, please? See above, he has some ideas about the implementation. Regards, Sergei
To the (now background)-thread of an (alternative) language for programming 'sp's I forgot to mention javascript. I believe both the licenses for the Mozilla and the Google/Chromium javascript engines would allow for porting to MariaDB. Some extensions would probably be necessary to support database objects/functionalities. But actually I think that javascript is the best option for a cross-platform implementation (even though it does not look 'ADA-style´'). And it would provide arrays and more programming options for 'sp's. Buti t would not be trivial to implement, of course. -- Peter On Tue, Mar 3, 2015 at 7:25 PM, Sergei Golubchik <serg@mariadb.org> wrote:
Hi, Igor!
On Mar 03, Igor Babaev wrote:
I'd also like to discuss window functions too. I've implemented them in shard-query and have ideas about how to implement them in the server, but pluggable parser would be really useful here.
Window functions have a good chance of being in 10.2, it's MDEV-6115. But I don't think that somebody is working on MDEV-6115 yet.
I started working on MDEV-6115 some time ago.
Ah, great. Sorry, I didn't know it. Could you then discuss it with Justin, please? See above, he has some ideas about the implementation.
Regards, Sergei
_______________________________________________ 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 Peter, Did you look at MDEV-820 that we've been referencing? It is a language agnostic interface. you can have C, or Perl, or Java stored procedures. It also adds table functions, a reentant parser and other goodies. Right now if you want to execute Javascript via V8, use Roland's UDF: https://github.com/rpbouman/mysqlv8udfs --Justin On Tue, Mar 3, 2015 at 11:45 AM, Peter Laursen <peter_laursen@webyog.com> wrote:
To the (now background)-thread of an (alternative) language for programming 'sp's I forgot to mention javascript. I believe both the licenses for the Mozilla and the Google/Chromium javascript engines would allow for porting to MariaDB. Some extensions would probably be necessary to support database objects/functionalities. But actually I think that javascript is the best option for a cross-platform implementation (even though it does not look 'ADA-style´'). And it would provide arrays and more programming options for 'sp's.
Buti t would not be trivial to implement, of course.
-- Peter
On Tue, Mar 3, 2015 at 7:25 PM, Sergei Golubchik <serg@mariadb.org> wrote:
Hi, Igor!
On Mar 03, Igor Babaev wrote:
I'd also like to discuss window functions too. I've implemented them in shard-query and have ideas about how to implement them in the server, but pluggable parser would be really useful here.
Window functions have a good chance of being in 10.2, it's MDEV-6115. But I don't think that somebody is working on MDEV-6115 yet.
I started working on MDEV-6115 some time ago.
Ah, great. Sorry, I didn't know it. Could you then discuss it with Justin, please? See above, he has some ideas about the implementation.
Regards, Sergei
_______________________________________________ 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
_______________________________________________ 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 03/03/2015 10:25 AM, Sergei Golubchik wrote:
Hi, Igor!
On Mar 03, Igor Babaev wrote:
I'd also like to discuss window functions too. I've implemented them in shard-query and have ideas about how to implement them in the server, but pluggable parser would be really useful here.
Window functions have a good chance of being in 10.2, it's MDEV-6115. But I don't think that somebody is working on MDEV-6115 yet.
I started working on MDEV-6115 some time ago.
Ah, great. Sorry, I didn't know it. Could you then discuss it with Justin, please? See above, he has some ideas about the implementation.
Justin, How do you prefer discussing your ideas? On IRC (#maria) or by phone? (I don't have skype at the moment) Regards, Igor.
Regards, Sergei
Hi, Well here is how Shard-Query does them. I assume you would do something very similarly internally with a temp table. a) it create a temporary table for the query reserving null rows for the window functions b) it adds to the temporary a unique id for each row of the resultset. This is used for framing. c) it adds a hash of the order by columns for the window function for ordering d) it adds a hash of the partition columns for partitioning After the regular resultset is stored in the temp table, a function sweeps the table for each window function, calculating the result of the function based on the framing clause, then the column in the resultset is updated to reflect the computed value. Finally, the resultset is returned to the client. Here is the SQL log of the following query: mysql> call shard_query.sq_helper("SELECT depname, empno, salary, cume_dist() OVER (PARTITION BY depname ORDER by salary rows between 1 following and 1 following) ss FROM empsalary", "", 'test','testtab',1,1); +-----------+-------+--------+------------------+ | depname | empno | salary | ss | +-----------+-------+--------+------------------+ | develop | 7 | 4200 | 0.2 | | develop | 9 | 4500 | 0.4 | | develop | 11 | 5200 | 0.8 | | develop | 10 | 5200 | 0.8 | | develop | 8 | 6000 | 1 | | sales | 3 | 4800 | 0.66666666666667 | | sales | 4 | 4800 | 0.66666666666667 | | sales | 1 | 5000 | 1 | | personnel | 5 | 3500 | 0.5 | | personnel | 2 | 3900 | 1 | +-----------+-------+--------+------------------+ 10 rows in set (0.08 sec) Query OK, 0 rows affected (0.09 sec) 150303 13:12:05 1533 Query call shard_query.sq_helper("SELECT depname, empno, salary, cume_dist() OVER (PARTITION BY depname ORDER by salary rows between 1 following and 1 following) ss FROM empsalary", "", 'test','testtab',1,1) -- get meta data for resultset (notice 0=1 in where clause) 1553 Query SELECT NULL as wf_rownum, depname AS expr$0,empno AS expr$1,salary AS expr$2,NULL as wf0,depname,SHA1(CONCAT_WS('#',depname)) as wf0_hash,salary,SHA1(CONCAT_WS('#',salary)) as wf0_obhash FROM empsalary AS `empsalary` WHERE 1=1 LIMIT 0 -- create temp table 1553 Query CREATE TABLE aggregation_tmp_45403179 (wf_rownum bigint auto_increment primary key,expr$0 VARCHAR(255),expr$1 VARCHAR(255),expr$2 VARCHAR(255),wf0 VARCHAR(255),depname VARCHAR(255),wf0_hash VARCHAR(255),salary VARCHAR(255),wf0_obhash VARCHAR(255)) ENGINE=MYISAM -- get resultset 1556 Query SELECT NULL as wf_rownum, depname AS expr$0,empno AS expr$1,salary AS expr$2,NULL as wf0,depname,SHA1(CONCAT_WS('#',depname)) as wf0_hash,salary,SHA1(CONCAT_WS('#',salary)) as wf0_obhash FROM empsalary AS `empsalary` WHERE 1=1 -- store resultset 1557 Query INSERT INTO `aggregation_tmp_45403179` VALUES (NULL,'develop',11,5200,NULL,'develop','418a6bc4deccf0f7d5182192d51a54e504b3f3c9',5200,'2fad4efb8e6aaaa53ecdf638137d390e002f9783'),(NULL,'develop',7,4200,NULL,'develop','418a6bc4deccf0f7d5182192d51a54e504b3f3c9',4200,'c79fce75b1583ddd36a96178757e0d8d0ac91228'),(NULL,'develop',9,4500,NULL,'develop','418a6bc4deccf0f7d5182192d51a54e504b3f3c9',4500,'97a87b470fe9ed5ff51ff9b8543e937e6016d48c'),(NULL,'develop',8,6000,NULL,'develop','418a6bc4deccf0f7d5182192d51a54e504b3f3c9',6000,'31d9ddeaa80bc88c1f3117b9724726ebcc7fc72d'),(NULL,'develop',10,5200,NULL,'develop','418a6bc4deccf0f7d5182192d51a54e504b3f3c9',5200,'2fad4efb8e6aaaa53ecdf638137d390e002f9783'),(NULL,'personnel',5,3500,NULL,'personnel','fc08ce9ebee8734c2dc883c0dbd607686bdce8f3',3500,'65609286cc04ece831a844984a6bc9eb80450cf7'),(NULL,'personnel',2,3900,NULL,'personnel','fc08ce9ebee8734c2dc883c0dbd607686bdce8f3',3900,'5446569e8572251dcac168152d6c37074427eae3'),(NULL,'sales',3,4800,NULL,'sales','59248c4dae276a021cb296d2ee0e6a0c962a8d7f',4800,'98e9f55262269a05dd4f1ed788626580fdef2e95'),(NULL,'sales',1,5000,NULL,'sales','59248c4dae276a021cb296d2ee0e6a0c962a8d7f',5000,'f8237d8959e03355010bb85cc3dc46a46fb31110'),(NULL,'sales',4,4800,NULL,'sales','59248c4dae276a021cb296d2ee0e6a0c962a8d7f',4800,'98e9f55262269a05dd4f1ed788626580fdef2e95') -- handle window function 0 (the only one in this case) -- get the hashes for each partition 1553 Query SELECT distinct wf0_hash h from aggregation_tmp_45403179 ORDER BY salary asc -- compute the values for each partition (three in this case) -- process the window and update the temp table (see code at the end for wf_cume) for each partition in turn. -- as you can see there is a select followed by updates 1553 Query SELECT *,NULL as wf0 FROM `aggregation_tmp_45403179` where wf0_hash='fc08ce9ebee8734c2dc883c0dbd607686bdce8f3' ORDER BY salary asc 1553 Query UPDATE aggregation_tmp_45403179 SET wf0 = 0.5 WHERE wf_rownum in (6) 1553 Query UPDATE aggregation_tmp_45403179 SET wf0 = 1 WHERE wf_rownum in (7) 1553 Query SELECT *,NULL as wf0 FROM `aggregation_tmp_45403179` where wf0_hash='59248c4dae276a021cb296d2ee0e6a0c962a8d7f' ORDER BY salary asc 1553 Query UPDATE aggregation_tmp_45403179 SET wf0 = 0.66666666666667 WHERE wf_rownum in (8,10) 1553 Query UPDATE aggregation_tmp_45403179 SET wf0 = 1 WHERE wf_rownum in (9) 1553 Query SELECT *,NULL as wf0 FROM `aggregation_tmp_45403179` where wf0_hash='418a6bc4deccf0f7d5182192d51a54e504b3f3c9' ORDER BY salary asc 1553 Query UPDATE aggregation_tmp_45403179 SET wf0 = 0.2 WHERE wf_rownum in (2) 1553 Query UPDATE aggregation_tmp_45403179 SET wf0 = 0.4 WHERE wf_rownum in (3) 1553 Query UPDATE aggregation_tmp_45403179 SET wf0 = 0.8 WHERE wf_rownum in (1,5) 1553 Query UPDATE aggregation_tmp_45403179 SET wf0 = 1 WHERE wf_rownum in (4) -- return resultset to client 1559 Query SELECT expr$0 AS `depname`,expr$1 AS `empno`,expr$2 AS `salary`,wf0 as `ss` FROM `aggregation_tmp_45403179` ORDER BY wf0_hash,salary asc -- remove temp table 1559 Query DROP TABLE IF EXISTS aggregation_tmp_45403179 1559 Quit 1550 Quit 150303 13:12:12 1533 Query set global general_log=0 protected function wf_cume_dist($num,$state) { static $sum; $win = $state->windows[$num]; if(empty($win['order'])) { if($percent) $sql = "update " . $state->table_name . " set wf{$num}=1"; else $sql = "update " . $state->table_name . " set wf{$num}=0"; $state->DAL->my_query($sql); if($err = $state->DAL->my_error()) { $this->errors[] = $err; return false; } return true; } else { /* running sum*/ $sql = "SELECT distinct wf{$num}_hash h from " . $state->table_name . " ORDER BY " . $win['order_by']; $stmt = $state->DAL->my_query($sql); if($err = $state->DAL->my_error()) { $this->errors[] = $err; return false; } $last_hash = ""; $hash = ""; $last_ob_hash = ""; $ob_hash = ""; while($row = $state->DAL->my_fetch_assoc($stmt)) { #$sql = "select * from " . $state->table_name . " where wf{$num}_hash='" . $row['h'] . "' ORDER BY " . $win['order_by']; $sql = "SELECT *," . $state->winfunc_sql . " where wf{$num}_hash='" . $row['h'] . "' ORDER BY " . $win['order_by']; $stmt2 = $state->DAL->my_query($sql); if($err = $state->DAL->my_error()) { $this->errors[] = $err; return false; } $done=array(); $rows=array(); while($row2=$state->DAL->my_fetch_assoc($stmt2)) { $rows[] = $row2; } $last_hash = ""; $last_ob_hash = ""; $i = 0; $rowlist=""; $rank = 0; while($i<count($rows)) { $row2 = $rows[$i]; ++$rank; $ob_hash = $row2["wf{$num}_obhash"]; $rowlist=$row2['wf_rownum']; for($n=$i+1;$n<count($rows);++$n) { $row3 = $rows[$n]; $new_ob_hash = $row3["wf{$num}_obhash"]; if($new_ob_hash != $ob_hash) { break; } $rowlist .= "," . $row3['wf_rownum']; ++$i; ++$rank; } $dist = $rank/count($rows); $sql = "UPDATE " . $state->table_name . " SET wf{$num} = {$dist} WHERE wf_rownum in ({$rowlist})"; $state->DAL->my_query($sql); if($err = $state->DAL->my_error()) { $this->errors[] = $err; return false; } ++$i; } } } return true; } On Tue, Mar 3, 2015 at 12:55 PM, Igor Babaev <igor@askmonty.org> wrote:
On 03/03/2015 10:25 AM, Sergei Golubchik wrote:
Hi, Igor!
On Mar 03, Igor Babaev wrote:
I'd also like to discuss window functions too. I've implemented them in shard-query and have ideas about how to implement them in the server, but pluggable parser would be really useful here.
Window functions have a good chance of being in 10.2, it's MDEV-6115. But I don't think that somebody is working on MDEV-6115 yet.
I started working on MDEV-6115 some time ago.
Ah, great. Sorry, I didn't know it. Could you then discuss it with Justin, please? See above, he has some ideas about the implementation.
Justin, How do you prefer discussing your ideas? On IRC (#maria) or by phone? (I don't have skype at the moment)
Regards, Igor.
Regards, Sergei
_______________________________________________ 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, I was a little confusing. The query orders of the order by columns (salary is project out into the temporary table) but, later ob_hash is used. This is because the frame extends to all the values the order the same, and comparing multiple columns is hard, so a hash is used instead. --Justin On Tue, Mar 3, 2015 at 1:28 PM, Justin Swanhart <greenlion@gmail.com> wrote:
Hi,
Well here is how Shard-Query does them. I assume you would do something very similarly internally with a temp table.
a) it create a temporary table for the query reserving null rows for the window functions b) it adds to the temporary a unique id for each row of the resultset. This is used for framing. c) it adds a hash of the order by columns for the window function for ordering d) it adds a hash of the partition columns for partitioning
After the regular resultset is stored in the temp table, a function sweeps the table for each window function, calculating the result of the function based on the framing clause, then the column in the resultset is updated to reflect the computed value.
Finally, the resultset is returned to the client.
Here is the SQL log of the following query: mysql> call shard_query.sq_helper("SELECT depname, empno, salary, cume_dist() OVER (PARTITION BY depname ORDER by salary rows between 1 following and 1 following) ss FROM empsalary", "", 'test','testtab',1,1); +-----------+-------+--------+------------------+ | depname | empno | salary | ss | +-----------+-------+--------+------------------+ | develop | 7 | 4200 | 0.2 | | develop | 9 | 4500 | 0.4 | | develop | 11 | 5200 | 0.8 | | develop | 10 | 5200 | 0.8 | | develop | 8 | 6000 | 1 | | sales | 3 | 4800 | 0.66666666666667 | | sales | 4 | 4800 | 0.66666666666667 | | sales | 1 | 5000 | 1 | | personnel | 5 | 3500 | 0.5 | | personnel | 2 | 3900 | 1 | +-----------+-------+--------+------------------+ 10 rows in set (0.08 sec)
Query OK, 0 rows affected (0.09 sec)
150303 13:12:05 1533 Query call shard_query.sq_helper("SELECT depname, empno, salary, cume_dist() OVER (PARTITION BY depname ORDER by salary rows between 1 following and 1 following) ss FROM empsalary", "", 'test','testtab',1,1)
-- get meta data for resultset (notice 0=1 in where clause) 1553 Query SELECT NULL as wf_rownum, depname AS expr$0,empno AS expr$1,salary AS expr$2,NULL as wf0,depname,SHA1(CONCAT_WS('#',depname)) as wf0_hash,salary,SHA1(CONCAT_WS('#',salary)) as wf0_obhash FROM empsalary AS `empsalary` WHERE 1=1 LIMIT 0
-- create temp table 1553 Query CREATE TABLE aggregation_tmp_45403179 (wf_rownum bigint auto_increment primary key,expr$0 VARCHAR(255),expr$1 VARCHAR(255),expr$2 VARCHAR(255),wf0 VARCHAR(255),depname VARCHAR(255),wf0_hash VARCHAR(255),salary VARCHAR(255),wf0_obhash VARCHAR(255)) ENGINE=MYISAM
-- get resultset 1556 Query SELECT NULL as wf_rownum, depname AS expr$0,empno AS expr$1,salary AS expr$2,NULL as wf0,depname,SHA1(CONCAT_WS('#',depname)) as wf0_hash,salary,SHA1(CONCAT_WS('#',salary)) as wf0_obhash FROM empsalary AS `empsalary` WHERE 1=1
-- store resultset 1557 Query INSERT INTO `aggregation_tmp_45403179` VALUES (NULL,'develop',11,5200,NULL,'develop','418a6bc4deccf0f7d5182192d51a54e504b3f3c9',5200,'2fad4efb8e6aaaa53ecdf638137d390e002f9783'),(NULL,'develop',7,4200,NULL,'develop','418a6bc4deccf0f7d5182192d51a54e504b3f3c9',4200,'c79fce75b1583ddd36a96178757e0d8d0ac91228'),(NULL,'develop',9,4500,NULL,'develop','418a6bc4deccf0f7d5182192d51a54e504b3f3c9',4500,'97a87b470fe9ed5ff51ff9b8543e937e6016d48c'),(NULL,'develop',8,6000,NULL,'develop','418a6bc4deccf0f7d5182192d51a54e504b3f3c9',6000,'31d9ddeaa80bc88c1f3117b9724726ebcc7fc72d'),(NULL,'develop',10,5200,NULL,'develop','418a6bc4deccf0f7d5182192d51a54e504b3f3c9',5200,'2fad4efb8e6aaaa53ecdf638137d390e002f9783'),(NULL,'personnel',5,3500,NULL,'personnel','fc08ce9ebee8734c2dc883c0dbd607686bdce8f3',3500,'65609286cc04ece831a844984a6bc9eb80450cf7'),(NULL,'personnel',2,3900,NULL,'personnel','fc08ce9ebee8734c2dc883c0dbd607686bdce8f3',3900,'5446569e8572251dcac168152d6c37074427eae3'),(NULL,'sales',3,4800,NULL,'sales','59248c4dae276a021cb296d2ee0e6a0c962a8d7f',4800,'98e9f55262269a05dd4f1ed788626580fdef2e95'),(NULL,'sales',1,5000,NULL,'sales','59248c4dae276a021cb296d2ee0e6a0c962a8d7f',5000,'f8237d8959e03355010bb85cc3dc46a46fb31110'),(NULL,'sales',4,4800,NULL,'sales','59248c4dae276a021cb296d2ee0e6a0c962a8d7f',4800,'98e9f55262269a05dd4f1ed788626580fdef2e95')
-- handle window function 0 (the only one in this case)
-- get the hashes for each partition 1553 Query SELECT distinct wf0_hash h from aggregation_tmp_45403179 ORDER BY salary asc
-- compute the values for each partition (three in this case)
-- process the window and update the temp table (see code at the end for wf_cume) for each partition in turn. -- as you can see there is a select followed by updates
1553 Query SELECT *,NULL as wf0 FROM `aggregation_tmp_45403179` where wf0_hash='fc08ce9ebee8734c2dc883c0dbd607686bdce8f3' ORDER BY salary asc
1553 Query UPDATE aggregation_tmp_45403179 SET wf0 = 0.5 WHERE wf_rownum in (6) 1553 Query UPDATE aggregation_tmp_45403179 SET wf0 = 1 WHERE wf_rownum in (7) 1553 Query SELECT *,NULL as wf0 FROM `aggregation_tmp_45403179` where wf0_hash='59248c4dae276a021cb296d2ee0e6a0c962a8d7f' ORDER BY salary asc 1553 Query UPDATE aggregation_tmp_45403179 SET wf0 = 0.66666666666667 WHERE wf_rownum in (8,10) 1553 Query UPDATE aggregation_tmp_45403179 SET wf0 = 1 WHERE wf_rownum in (9) 1553 Query SELECT *,NULL as wf0 FROM `aggregation_tmp_45403179` where wf0_hash='418a6bc4deccf0f7d5182192d51a54e504b3f3c9' ORDER BY salary asc 1553 Query UPDATE aggregation_tmp_45403179 SET wf0 = 0.2 WHERE wf_rownum in (2) 1553 Query UPDATE aggregation_tmp_45403179 SET wf0 = 0.4 WHERE wf_rownum in (3) 1553 Query UPDATE aggregation_tmp_45403179 SET wf0 = 0.8 WHERE wf_rownum in (1,5) 1553 Query UPDATE aggregation_tmp_45403179 SET wf0 = 1 WHERE wf_rownum in (4)
-- return resultset to client
1559 Query SELECT expr$0 AS `depname`,expr$1 AS `empno`,expr$2 AS `salary`,wf0 as `ss` FROM `aggregation_tmp_45403179` ORDER BY wf0_hash,salary asc
-- remove temp table
1559 Query DROP TABLE IF EXISTS aggregation_tmp_45403179 1559 Quit 1550 Quit
150303 13:12:12 1533 Query set global general_log=0
protected function wf_cume_dist($num,$state) { static $sum; $win = $state->windows[$num]; if(empty($win['order'])) { if($percent) $sql = "update " . $state->table_name . " set wf{$num}=1"; else $sql = "update " . $state->table_name . " set wf{$num}=0";
$state->DAL->my_query($sql); if($err = $state->DAL->my_error()) { $this->errors[] = $err; return false; } return true; } else { /* running sum*/ $sql = "SELECT distinct wf{$num}_hash h from " . $state->table_name . " ORDER BY " . $win['order_by']; $stmt = $state->DAL->my_query($sql); if($err = $state->DAL->my_error()) { $this->errors[] = $err; return false; } $last_hash = ""; $hash = ""; $last_ob_hash = ""; $ob_hash = ""; while($row = $state->DAL->my_fetch_assoc($stmt)) { #$sql = "select * from " . $state->table_name . " where wf{$num}_hash='" . $row['h'] . "' ORDER BY " . $win['order_by']; $sql = "SELECT *," . $state->winfunc_sql . " where wf{$num}_hash='" . $row['h'] . "' ORDER BY " . $win['order_by']; $stmt2 = $state->DAL->my_query($sql); if($err = $state->DAL->my_error()) { $this->errors[] = $err; return false; } $done=array(); $rows=array(); while($row2=$state->DAL->my_fetch_assoc($stmt2)) { $rows[] = $row2; } $last_hash = ""; $last_ob_hash = ""; $i = 0; $rowlist=""; $rank = 0;
while($i<count($rows)) { $row2 = $rows[$i]; ++$rank; $ob_hash = $row2["wf{$num}_obhash"]; $rowlist=$row2['wf_rownum']; for($n=$i+1;$n<count($rows);++$n) { $row3 = $rows[$n]; $new_ob_hash = $row3["wf{$num}_obhash"]; if($new_ob_hash != $ob_hash) { break; } $rowlist .= "," . $row3['wf_rownum']; ++$i; ++$rank; } $dist = $rank/count($rows); $sql = "UPDATE " . $state->table_name . " SET wf{$num} = {$dist} WHERE wf_rownum in ({$rowlist})"; $state->DAL->my_query($sql); if($err = $state->DAL->my_error()) { $this->errors[] = $err; return false; } ++$i; } } } return true; }
On Tue, Mar 3, 2015 at 12:55 PM, Igor Babaev <igor@askmonty.org> wrote:
On 03/03/2015 10:25 AM, Sergei Golubchik wrote:
Hi, Igor!
On Mar 03, Igor Babaev wrote:
I'd also like to discuss window functions too. I've implemented them in shard-query and have ideas about how to implement them in the server, but pluggable parser would be really useful here.
Window functions have a good chance of being in 10.2, it's MDEV-6115. But I don't think that somebody is working on MDEV-6115 yet.
I started working on MDEV-6115 some time ago.
Ah, great. Sorry, I didn't know it. Could you then discuss it with Justin, please? See above, he has some ideas about the implementation.
Justin, How do you prefer discussing your ideas? On IRC (#maria) or by phone? (I don't have skype at the moment)
Regards, Igor.
Regards, Sergei
_______________________________________________ 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, One other thing, in that example I sent, there is $state->winfunc_sql. For those window functions that take column input, it will be the column to input into the function, and is always of form "expr$[0-9]+ as wf[0-9]+" For example, for the following query it is "expr$0 as wf0 " SELECT salary, sum(salary) OVER (ORDER BY salary) as ss FROM empsalary; The SQL looks like this: 1610 Query SELECT NULL as wf_rownum, salary AS expr$0,NULL as wf0,SHA1(CONCAT_WS('#','ONE_PARTITION')) as wf0_hash,salary,SHA1(CONCAT_WS('#',salary)) as wf0_obhash FROM empsalary AS `empsalary` WHERE 1=1 1611 Query INSERT INTO `aggregation_tmp_86564985` VALUES (NULL,5200,NULL,'3dce9641066f316f334c8008fd3e364274470068',5200,'2fad4efb8e6aaaa53ecdf638137d390e002f9783'),(NULL,4200,NULL,'3dce9641066f316f334c8008fd3e364274470068',4200,'c79fce75b1583ddd36a96178757e0d8d0ac91228'),(NULL,4500,NULL,'3dce9641066f316f334c8008fd3e364274470068',4500,'97a87b470fe9ed5ff51ff9b8543e937e6016d48c'),(NULL,6000,NULL,'3dce9641066f316f334c8008fd3e364274470068',6000,'31d9ddeaa80bc88c1f3117b9724726ebcc7fc72d'),(NULL,5200,NULL,'3dce9641066f316f334c8008fd3e364274470068',5200,'2fad4efb8e6aaaa53ecdf638137d390e002f9783'),(NULL,3500,NULL,'3dce9641066f316f334c8008fd3e364274470068',3500,'65609286cc04ece831a844984a6bc9eb80450cf7'),(NULL,3900,NULL,'3dce9641066f316f334c8008fd3e364274470068',3900,'5446569e8572251dcac168152d6c37074427eae3'),(NULL,4800,NULL,'3dce9641066f316f334c8008fd3e364274470068',4800,'98e9f55262269a05dd4f1ed788626580fdef2e95'),(NULL,5000,NULL,'3dce9641066f316f334c8008fd3e364274470068',5000,'f8237d8959e03355010bb85cc3dc46a46fb31110'),(NULL,4800,NULL,'3dce9641066f316f334c8008fd3e364274470068',4800,'98e9f55262269a05dd4f1ed788626580fdef2e95') 1607 Query SELECT distinct wf0_hash h from aggregation_tmp_86564985 ORDER BY salary asc 1607 Query SELECT *,expr$0 as wf0 FROM `aggregation_tmp_86564985` where wf0_hash='3dce9641066f316f334c8008fd3e364274470068' ORDER BY salary asc 1607 Query UPDATE aggregation_tmp_86564985 SET wf0 = 3500 WHERE wf_rownum = 6 1607 Query UPDATE aggregation_tmp_86564985 SET wf0 = 7400 WHERE wf_rownum = 7 1607 Query UPDATE aggregation_tmp_86564985 SET wf0 = 11600 WHERE wf_rownum = 2 1607 Query UPDATE aggregation_tmp_86564985 SET wf0 = 16100 WHERE wf_rownum = 3 1607 Query UPDATE aggregation_tmp_86564985 SET wf0 = 25700 WHERE wf_rownum = 8 1607 Query UPDATE aggregation_tmp_86564985 SET wf0 = 25700 WHERE wf_rownum = 10 1607 Query UPDATE aggregation_tmp_86564985 SET wf0 = 30700 WHERE wf_rownum = 9 1607 Query UPDATE aggregation_tmp_86564985 SET wf0 = 41100 WHERE wf_rownum = 1 1607 Query UPDATE aggregation_tmp_86564985 SET wf0 = 41100 WHERE wf_rownum = 5 1607 Query UPDATE aggregation_tmp_86564985 SET wf0 = 47100 WHERE wf_rownum = 4 The cumulative distance example doesn't use any column input so in the SQL examples you see "NULL as wf0." for the window sql --Justin On Tue, Mar 3, 2015 at 1:40 PM, Justin Swanhart <greenlion@gmail.com> wrote:
Hi,
I was a little confusing. The query orders of the order by columns (salary is project out into the temporary table) but, later ob_hash is used. This is because the frame extends to all the values the order the same, and comparing multiple columns is hard, so a hash is used instead.
--Justin
On Tue, Mar 3, 2015 at 1:28 PM, Justin Swanhart <greenlion@gmail.com> wrote:
Hi,
Well here is how Shard-Query does them. I assume you would do something very similarly internally with a temp table.
a) it create a temporary table for the query reserving null rows for the window functions b) it adds to the temporary a unique id for each row of the resultset. This is used for framing. c) it adds a hash of the order by columns for the window function for ordering d) it adds a hash of the partition columns for partitioning
After the regular resultset is stored in the temp table, a function sweeps the table for each window function, calculating the result of the function based on the framing clause, then the column in the resultset is updated to reflect the computed value.
Finally, the resultset is returned to the client.
Here is the SQL log of the following query: mysql> call shard_query.sq_helper("SELECT depname, empno, salary, cume_dist() OVER (PARTITION BY depname ORDER by salary rows between 1 following and 1 following) ss FROM empsalary", "", 'test','testtab',1,1); +-----------+-------+--------+------------------+ | depname | empno | salary | ss | +-----------+-------+--------+------------------+ | develop | 7 | 4200 | 0.2 | | develop | 9 | 4500 | 0.4 | | develop | 11 | 5200 | 0.8 | | develop | 10 | 5200 | 0.8 | | develop | 8 | 6000 | 1 | | sales | 3 | 4800 | 0.66666666666667 | | sales | 4 | 4800 | 0.66666666666667 | | sales | 1 | 5000 | 1 | | personnel | 5 | 3500 | 0.5 | | personnel | 2 | 3900 | 1 | +-----------+-------+--------+------------------+ 10 rows in set (0.08 sec)
Query OK, 0 rows affected (0.09 sec)
150303 13:12:05 1533 Query call shard_query.sq_helper("SELECT depname, empno, salary, cume_dist() OVER (PARTITION BY depname ORDER by salary rows between 1 following and 1 following) ss FROM empsalary", "", 'test','testtab',1,1)
-- get meta data for resultset (notice 0=1 in where clause) 1553 Query SELECT NULL as wf_rownum, depname AS expr$0,empno AS expr$1,salary AS expr$2,NULL as wf0,depname,SHA1(CONCAT_WS('#',depname)) as wf0_hash,salary,SHA1(CONCAT_WS('#',salary)) as wf0_obhash FROM empsalary AS `empsalary` WHERE 1=1 LIMIT 0
-- create temp table 1553 Query CREATE TABLE aggregation_tmp_45403179 (wf_rownum bigint auto_increment primary key,expr$0 VARCHAR(255),expr$1 VARCHAR(255),expr$2 VARCHAR(255),wf0 VARCHAR(255),depname VARCHAR(255),wf0_hash VARCHAR(255),salary VARCHAR(255),wf0_obhash VARCHAR(255)) ENGINE=MYISAM
-- get resultset 1556 Query SELECT NULL as wf_rownum, depname AS expr$0,empno AS expr$1,salary AS expr$2,NULL as wf0,depname,SHA1(CONCAT_WS('#',depname)) as wf0_hash,salary,SHA1(CONCAT_WS('#',salary)) as wf0_obhash FROM empsalary AS `empsalary` WHERE 1=1
-- store resultset 1557 Query INSERT INTO `aggregation_tmp_45403179` VALUES (NULL,'develop',11,5200,NULL,'develop','418a6bc4deccf0f7d5182192d51a54e504b3f3c9',5200,'2fad4efb8e6aaaa53ecdf638137d390e002f9783'),(NULL,'develop',7,4200,NULL,'develop','418a6bc4deccf0f7d5182192d51a54e504b3f3c9',4200,'c79fce75b1583ddd36a96178757e0d8d0ac91228'),(NULL,'develop',9,4500,NULL,'develop','418a6bc4deccf0f7d5182192d51a54e504b3f3c9',4500,'97a87b470fe9ed5ff51ff9b8543e937e6016d48c'),(NULL,'develop',8,6000,NULL,'develop','418a6bc4deccf0f7d5182192d51a54e504b3f3c9',6000,'31d9ddeaa80bc88c1f3117b9724726ebcc7fc72d'),(NULL,'develop',10,5200,NULL,'develop','418a6bc4deccf0f7d5182192d51a54e504b3f3c9',5200,'2fad4efb8e6aaaa53ecdf638137d390e002f9783'),(NULL,'personnel',5,3500,NULL,'personnel','fc08ce9ebee8734c2dc883c0dbd607686bdce8f3',3500,'65609286cc04ece831a844984a6bc9eb80450cf7'),(NULL,'personnel',2,3900,NULL,'personnel','fc08ce9ebee8734c2dc883c0dbd607686bdce8f3',3900,'5446569e8572251dcac168152d6c37074427eae3'),(NULL,'sales',3,4800,NULL,'sales','59248c4dae276a021cb296d2ee0e6a0c962a8d7f',4800,'98e9f55262269a05dd4f1ed788626580fdef2e95'),(NULL,'sales',1,5000,NULL,'sales','59248c4dae276a021cb296d2ee0e6a0c962a8d7f',5000,'f8237d8959e03355010bb85cc3dc46a46fb31110'),(NULL,'sales',4,4800,NULL,'sales','59248c4dae276a021cb296d2ee0e6a0c962a8d7f',4800,'98e9f55262269a05dd4f1ed788626580fdef2e95')
-- handle window function 0 (the only one in this case)
-- get the hashes for each partition 1553 Query SELECT distinct wf0_hash h from aggregation_tmp_45403179 ORDER BY salary asc
-- compute the values for each partition (three in this case)
-- process the window and update the temp table (see code at the end for wf_cume) for each partition in turn. -- as you can see there is a select followed by updates
1553 Query SELECT *,NULL as wf0 FROM `aggregation_tmp_45403179` where wf0_hash='fc08ce9ebee8734c2dc883c0dbd607686bdce8f3' ORDER BY salary asc
1553 Query UPDATE aggregation_tmp_45403179 SET wf0 = 0.5 WHERE wf_rownum in (6) 1553 Query UPDATE aggregation_tmp_45403179 SET wf0 = 1 WHERE wf_rownum in (7) 1553 Query SELECT *,NULL as wf0 FROM `aggregation_tmp_45403179` where wf0_hash='59248c4dae276a021cb296d2ee0e6a0c962a8d7f' ORDER BY salary asc 1553 Query UPDATE aggregation_tmp_45403179 SET wf0 = 0.66666666666667 WHERE wf_rownum in (8,10) 1553 Query UPDATE aggregation_tmp_45403179 SET wf0 = 1 WHERE wf_rownum in (9) 1553 Query SELECT *,NULL as wf0 FROM `aggregation_tmp_45403179` where wf0_hash='418a6bc4deccf0f7d5182192d51a54e504b3f3c9' ORDER BY salary asc 1553 Query UPDATE aggregation_tmp_45403179 SET wf0 = 0.2 WHERE wf_rownum in (2) 1553 Query UPDATE aggregation_tmp_45403179 SET wf0 = 0.4 WHERE wf_rownum in (3) 1553 Query UPDATE aggregation_tmp_45403179 SET wf0 = 0.8 WHERE wf_rownum in (1,5) 1553 Query UPDATE aggregation_tmp_45403179 SET wf0 = 1 WHERE wf_rownum in (4)
-- return resultset to client
1559 Query SELECT expr$0 AS `depname`,expr$1 AS `empno`,expr$2 AS `salary`,wf0 as `ss` FROM `aggregation_tmp_45403179` ORDER BY wf0_hash,salary asc
-- remove temp table
1559 Query DROP TABLE IF EXISTS aggregation_tmp_45403179 1559 Quit 1550 Quit
150303 13:12:12 1533 Query set global general_log=0
protected function wf_cume_dist($num,$state) { static $sum; $win = $state->windows[$num]; if(empty($win['order'])) { if($percent) $sql = "update " . $state->table_name . " set wf{$num}=1"; else $sql = "update " . $state->table_name . " set wf{$num}=0";
$state->DAL->my_query($sql); if($err = $state->DAL->my_error()) { $this->errors[] = $err; return false; } return true; } else { /* running sum*/ $sql = "SELECT distinct wf{$num}_hash h from " . $state->table_name . " ORDER BY " . $win['order_by']; $stmt = $state->DAL->my_query($sql); if($err = $state->DAL->my_error()) { $this->errors[] = $err; return false; } $last_hash = ""; $hash = ""; $last_ob_hash = ""; $ob_hash = ""; while($row = $state->DAL->my_fetch_assoc($stmt)) { #$sql = "select * from " . $state->table_name . " where wf{$num}_hash='" . $row['h'] . "' ORDER BY " . $win['order_by']; $sql = "SELECT *," . $state->winfunc_sql . " where wf{$num}_hash='" . $row['h'] . "' ORDER BY " . $win['order_by']; $stmt2 = $state->DAL->my_query($sql); if($err = $state->DAL->my_error()) { $this->errors[] = $err; return false; } $done=array(); $rows=array(); while($row2=$state->DAL->my_fetch_assoc($stmt2)) { $rows[] = $row2; } $last_hash = ""; $last_ob_hash = ""; $i = 0; $rowlist=""; $rank = 0;
while($i<count($rows)) { $row2 = $rows[$i]; ++$rank; $ob_hash = $row2["wf{$num}_obhash"]; $rowlist=$row2['wf_rownum']; for($n=$i+1;$n<count($rows);++$n) { $row3 = $rows[$n]; $new_ob_hash = $row3["wf{$num}_obhash"]; if($new_ob_hash != $ob_hash) { break; } $rowlist .= "," . $row3['wf_rownum']; ++$i; ++$rank; } $dist = $rank/count($rows); $sql = "UPDATE " . $state->table_name . " SET wf{$num} = {$dist} WHERE wf_rownum in ({$rowlist})"; $state->DAL->my_query($sql); if($err = $state->DAL->my_error()) { $this->errors[] = $err; return false; } ++$i; } } } return true; }
On Tue, Mar 3, 2015 at 12:55 PM, Igor Babaev <igor@askmonty.org> wrote:
Hi, Igor!
On Mar 03, Igor Babaev wrote:
> I'd also like to discuss window functions too. I've implemented
On 03/03/2015 10:25 AM, Sergei Golubchik wrote: them
> in shard-query and have ideas about how to implement them in the > server, but pluggable parser would be really useful here.
Window functions have a good chance of being in 10.2, it's MDEV-6115. But I don't think that somebody is working on MDEV-6115 yet.
I started working on MDEV-6115 some time ago.
Ah, great. Sorry, I didn't know it. Could you then discuss it with Justin, please? See above, he has some ideas about the implementation.
Justin, How do you prefer discussing your ideas? On IRC (#maria) or by phone? (I don't have skype at the moment)
Regards, Igor.
Regards, Sergei
_______________________________________________ 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
One other thing (and this is the last thing, I promise!) some functions, like cume_dist don't take a framing clause, but others like sum do. Here is how I handle it: $state->windows[$num]['mode'] 'RANGE'; or 'ROWS' $state->windows[$num]['start'] // false means unbounded preceeding, otherwise numeric means offset into resultset, zero is "current row" $state->windows[$num]['end'] // false means unbounded following, otherwise it numeric is an offset, zero is "current row" Here is the code for wf_sum, which uses it: protected function wf_sum($num,$state) { static $sum; $win = $state->windows[$num]; if($win['order_by'] == "") { $sql = "SELECT distinct wf{$num}_hash h from " . $state->table_name; $stmt = $state->DAL->my_query($sql); if($err = $state->DAL->my_error()) { $this->errors[] = $err; return false; } while($row = $state->DAL->my_fetch_assoc($stmt)) { $colref = "wf{$num}"; $sql = "select sum($colref) as s from (select " . $state->winfunc_sql . " WHERE wf{$num}_hash = '{$row['h']}') sq"; $stmt2 = $state->DAL->my_query($sql); $row2 = $state->DAL->my_fetch_assoc($stmt2); $sum = $row2['s']; $sql = "UPDATE " . $state->table_name . " SET wf$num = $sum WHERE wf{$num}_hash = '{$row['h']}'"; $state->DAL->my_query($sql); if($err = $state->DAL->my_error()) { $this->errors[] = $err; return false; } } return true; } else { $sql = "SELECT distinct wf{$num}_hash h from " . $state->table_name . " ORDER BY " . $win['order_by']; $stmt = $state->DAL->my_query($sql); if($err = $state->DAL->my_error()) { $this->errors[] = $err; return false; } while($row = $state->DAL->my_fetch_assoc($stmt)) { /* loop over each partition */ $sql = "SELECT *," . $state->winfunc_sql . " where wf{$num}_hash='" . $row['h'] . "'" . $state->winfunc_group . " ORDER BY " . $win['order_by']; $partition_rows = $this->get_all_rows($sql, $state); if(!$partition_rows) return false; $colref = "wf{$num}"; for($i=0;$i<count($partition_rows);++$i) { $row3 = $partition_rows[$i]; $frame = $this->frame_window($partition_rows, $win, $i, $colref, "wf{$num}_obhash"); if($this->all_null($frame)) { // will also return true on empty set $sum = "NULL"; } else { $sum = array_sum($frame); } $sql = "UPDATE " . $state->table_name . " SET wf{$num} = {$sum} WHERE wf_rownum = {$row3['wf_rownum']}"; $state->DAL->my_query($sql); if($err = $state->DAL->my_error()) { $this->errors[] = $err; return false; } } } } return true; } /* This function calculates the 'frame' for a window */ protected function &frame_window(&$rows,$win, $cur=0,$key = "wf_rownum", $ob_key="",$default=null) { $key = ltrim($key,","); $start = $win['start']; $end = $win['end']; $mode = $win['mode']; $peers = true; if($start === 0) { // 0 is current_row $start = $cur; } elseif($start === false) { //unbounded preceeding $start =0; } else { $start = $cur + $start; // positive if "value following" or negative for "value preceeding" } if($end === false) { $end = count($rows); // unbounded following } elseif($end === 0) { $end = $cur; } else { $end = $cur + $end; // positive for "value following", negative for "value preceeding" } if($mode == 'ROWS') $peers = false; $vals = array(); /* The frame can extend from before the resultset or past the end of it, but the values are NULL when that happens. */ if($start < 0) { $rows_to_add = abs($start); for($i=1;$i<=$rows_to_add;$i++) { $rows[-1*$i][$key] = $default; } } if($end < 0) { $rows_to_add = abs($end); for($i=1;$i<=$rows_to_add;$i++) { $rows[count($rows)+($i-1)][$key] = $default; } } if($start > count($rows)) { $rows_to_add = $start - count($rows); for($i=1;$i<=$rows_to_add;$i++) { $rows[count($rows)+($i-1)][$key] = $default; } } if($end> count($rows)) { $rows_to_add = $end - count($rows); for($i=1;$i<=$rows_to_add;$i++) { $rows[count($rows)+($i-1)][$key] = $default; } } $i = $start; while($i<count($rows)) { $row = $rows[$i]; $val = $row[$key]; if(!empty($row[$ob_key])) $sort = $row[$ob_key]; $vals[] = $val; if($i == $end && !$peers) break; if($i == $end && !empty($row[$ob_key])) { for($n=$i+1;$n<count($rows);++$n) { // continue through peers $row2 = $rows[$n]; $val2 = $row2[$key]; $sort2 = $row2[$ob_key]; if($sort != $sort2) break 2; $vals[] = $val; ++$i; } } ++$i; } return $vals; } On Tue, Mar 3, 2015 at 2:20 PM, Justin Swanhart <greenlion@gmail.com> wrote:
Hi,
One other thing, in that example I sent, there is $state->winfunc_sql. For those window functions that take column input, it will be the column to input into the function, and is always of form "expr$[0-9]+ as wf[0-9]+" For example, for the following query it is "expr$0 as wf0 " SELECT salary, sum(salary) OVER (ORDER BY salary) as ss FROM empsalary;
The SQL looks like this: 1610 Query SELECT NULL as wf_rownum, salary AS expr$0,NULL as wf0,SHA1(CONCAT_WS('#','ONE_PARTITION')) as wf0_hash,salary,SHA1(CONCAT_WS('#',salary)) as wf0_obhash FROM empsalary AS `empsalary` WHERE 1=1 1611 Query INSERT INTO `aggregation_tmp_86564985` VALUES (NULL,5200,NULL,'3dce9641066f316f334c8008fd3e364274470068',5200,'2fad4efb8e6aaaa53ecdf638137d390e002f9783'),(NULL,4200,NULL,'3dce9641066f316f334c8008fd3e364274470068',4200,'c79fce75b1583ddd36a96178757e0d8d0ac91228'),(NULL,4500,NULL,'3dce9641066f316f334c8008fd3e364274470068',4500,'97a87b470fe9ed5ff51ff9b8543e937e6016d48c'),(NULL,6000,NULL,'3dce9641066f316f334c8008fd3e364274470068',6000,'31d9ddeaa80bc88c1f3117b9724726ebcc7fc72d'),(NULL,5200,NULL,'3dce9641066f316f334c8008fd3e364274470068',5200,'2fad4efb8e6aaaa53ecdf638137d390e002f9783'),(NULL,3500,NULL,'3dce9641066f316f334c8008fd3e364274470068',3500,'65609286cc04ece831a844984a6bc9eb80450cf7'),(NULL,3900,NULL,'3dce9641066f316f334c8008fd3e364274470068',3900,'5446569e8572251dcac168152d6c37074427eae3'),(NULL,4800,NULL,'3dce9641066f316f334c8008fd3e364274470068',4800,'98e9f55262269a05dd4f1ed788626580fdef2e95'),(NULL,5000,NULL,'3dce9641066f316f334c8008fd3e364274470068',5000,'f8237d8959e03355010bb85cc3dc46a46fb31110'),(NULL,4800,NULL,'3dce9641066f316f334c8008fd3e364274470068',4800,'98e9f55262269a05dd4f1ed788626580fdef2e95')
1607 Query SELECT distinct wf0_hash h from aggregation_tmp_86564985 ORDER BY salary asc 1607 Query SELECT *,expr$0 as wf0 FROM `aggregation_tmp_86564985` where wf0_hash='3dce9641066f316f334c8008fd3e364274470068' ORDER BY salary asc 1607 Query UPDATE aggregation_tmp_86564985 SET wf0 = 3500 WHERE wf_rownum = 6 1607 Query UPDATE aggregation_tmp_86564985 SET wf0 = 7400 WHERE wf_rownum = 7 1607 Query UPDATE aggregation_tmp_86564985 SET wf0 = 11600 WHERE wf_rownum = 2 1607 Query UPDATE aggregation_tmp_86564985 SET wf0 = 16100 WHERE wf_rownum = 3 1607 Query UPDATE aggregation_tmp_86564985 SET wf0 = 25700 WHERE wf_rownum = 8 1607 Query UPDATE aggregation_tmp_86564985 SET wf0 = 25700 WHERE wf_rownum = 10 1607 Query UPDATE aggregation_tmp_86564985 SET wf0 = 30700 WHERE wf_rownum = 9 1607 Query UPDATE aggregation_tmp_86564985 SET wf0 = 41100 WHERE wf_rownum = 1 1607 Query UPDATE aggregation_tmp_86564985 SET wf0 = 41100 WHERE wf_rownum = 5 1607 Query UPDATE aggregation_tmp_86564985 SET wf0 = 47100 WHERE wf_rownum = 4
The cumulative distance example doesn't use any column input so in the SQL examples you see "NULL as wf0." for the window sql
--Justin
On Tue, Mar 3, 2015 at 1:40 PM, Justin Swanhart <greenlion@gmail.com> wrote:
Hi,
I was a little confusing. The query orders of the order by columns (salary is project out into the temporary table) but, later ob_hash is used. This is because the frame extends to all the values the order the same, and comparing multiple columns is hard, so a hash is used instead.
--Justin
On Tue, Mar 3, 2015 at 1:28 PM, Justin Swanhart <greenlion@gmail.com> wrote:
Hi,
Well here is how Shard-Query does them. I assume you would do something very similarly internally with a temp table.
a) it create a temporary table for the query reserving null rows for the window functions b) it adds to the temporary a unique id for each row of the resultset. This is used for framing. c) it adds a hash of the order by columns for the window function for ordering d) it adds a hash of the partition columns for partitioning
After the regular resultset is stored in the temp table, a function sweeps the table for each window function, calculating the result of the function based on the framing clause, then the column in the resultset is updated to reflect the computed value.
Finally, the resultset is returned to the client.
Here is the SQL log of the following query: mysql> call shard_query.sq_helper("SELECT depname, empno, salary, cume_dist() OVER (PARTITION BY depname ORDER by salary rows between 1 following and 1 following) ss FROM empsalary", "", 'test','testtab',1,1); +-----------+-------+--------+------------------+ | depname | empno | salary | ss | +-----------+-------+--------+------------------+ | develop | 7 | 4200 | 0.2 | | develop | 9 | 4500 | 0.4 | | develop | 11 | 5200 | 0.8 | | develop | 10 | 5200 | 0.8 | | develop | 8 | 6000 | 1 | | sales | 3 | 4800 | 0.66666666666667 | | sales | 4 | 4800 | 0.66666666666667 | | sales | 1 | 5000 | 1 | | personnel | 5 | 3500 | 0.5 | | personnel | 2 | 3900 | 1 | +-----------+-------+--------+------------------+ 10 rows in set (0.08 sec)
Query OK, 0 rows affected (0.09 sec)
150303 13:12:05 1533 Query call shard_query.sq_helper("SELECT depname, empno, salary, cume_dist() OVER (PARTITION BY depname ORDER by salary rows between 1 following and 1 following) ss FROM empsalary", "", 'test','testtab',1,1)
-- get meta data for resultset (notice 0=1 in where clause) 1553 Query SELECT NULL as wf_rownum, depname AS expr$0,empno AS expr$1,salary AS expr$2,NULL as wf0,depname,SHA1(CONCAT_WS('#',depname)) as wf0_hash,salary,SHA1(CONCAT_WS('#',salary)) as wf0_obhash FROM empsalary AS `empsalary` WHERE 1=1 LIMIT 0
-- create temp table 1553 Query CREATE TABLE aggregation_tmp_45403179 (wf_rownum bigint auto_increment primary key,expr$0 VARCHAR(255),expr$1 VARCHAR(255),expr$2 VARCHAR(255),wf0 VARCHAR(255),depname VARCHAR(255),wf0_hash VARCHAR(255),salary VARCHAR(255),wf0_obhash VARCHAR(255)) ENGINE=MYISAM
-- get resultset 1556 Query SELECT NULL as wf_rownum, depname AS expr$0,empno AS expr$1,salary AS expr$2,NULL as wf0,depname,SHA1(CONCAT_WS('#',depname)) as wf0_hash,salary,SHA1(CONCAT_WS('#',salary)) as wf0_obhash FROM empsalary AS `empsalary` WHERE 1=1
-- store resultset 1557 Query INSERT INTO `aggregation_tmp_45403179` VALUES (NULL,'develop',11,5200,NULL,'develop','418a6bc4deccf0f7d5182192d51a54e504b3f3c9',5200,'2fad4efb8e6aaaa53ecdf638137d390e002f9783'),(NULL,'develop',7,4200,NULL,'develop','418a6bc4deccf0f7d5182192d51a54e504b3f3c9',4200,'c79fce75b1583ddd36a96178757e0d8d0ac91228'),(NULL,'develop',9,4500,NULL,'develop','418a6bc4deccf0f7d5182192d51a54e504b3f3c9',4500,'97a87b470fe9ed5ff51ff9b8543e937e6016d48c'),(NULL,'develop',8,6000,NULL,'develop','418a6bc4deccf0f7d5182192d51a54e504b3f3c9',6000,'31d9ddeaa80bc88c1f3117b9724726ebcc7fc72d'),(NULL,'develop',10,5200,NULL,'develop','418a6bc4deccf0f7d5182192d51a54e504b3f3c9',5200,'2fad4efb8e6aaaa53ecdf638137d390e002f9783'),(NULL,'personnel',5,3500,NULL,'personnel','fc08ce9ebee8734c2dc883c0dbd607686bdce8f3',3500,'65609286cc04ece831a844984a6bc9eb80450cf7'),(NULL,'personnel',2,3900,NULL,'personnel','fc08ce9ebee8734c2dc883c0dbd607686bdce8f3',3900,'5446569e8572251dcac168152d6c37074427eae3'),(NULL,'sales',3,4800,NULL,'sales','59248c4dae276a021cb296d2ee0e6a0c962a8d7f',4800,'98e9f55262269a05dd4f1ed788626580fdef2e95'),(NULL,'sales',1,5000,NULL,'sales','59248c4dae276a021cb296d2ee0e6a0c962a8d7f',5000,'f8237d8959e03355010bb85cc3dc46a46fb31110'),(NULL,'sales',4,4800,NULL,'sales','59248c4dae276a021cb296d2ee0e6a0c962a8d7f',4800,'98e9f55262269a05dd4f1ed788626580fdef2e95')
-- handle window function 0 (the only one in this case)
-- get the hashes for each partition 1553 Query SELECT distinct wf0_hash h from aggregation_tmp_45403179 ORDER BY salary asc
-- compute the values for each partition (three in this case)
-- process the window and update the temp table (see code at the end for wf_cume) for each partition in turn. -- as you can see there is a select followed by updates
1553 Query SELECT *,NULL as wf0 FROM `aggregation_tmp_45403179` where wf0_hash='fc08ce9ebee8734c2dc883c0dbd607686bdce8f3' ORDER BY salary asc
1553 Query UPDATE aggregation_tmp_45403179 SET wf0 = 0.5 WHERE wf_rownum in (6) 1553 Query UPDATE aggregation_tmp_45403179 SET wf0 = 1 WHERE wf_rownum in (7) 1553 Query SELECT *,NULL as wf0 FROM `aggregation_tmp_45403179` where wf0_hash='59248c4dae276a021cb296d2ee0e6a0c962a8d7f' ORDER BY salary asc 1553 Query UPDATE aggregation_tmp_45403179 SET wf0 = 0.66666666666667 WHERE wf_rownum in (8,10) 1553 Query UPDATE aggregation_tmp_45403179 SET wf0 = 1 WHERE wf_rownum in (9) 1553 Query SELECT *,NULL as wf0 FROM `aggregation_tmp_45403179` where wf0_hash='418a6bc4deccf0f7d5182192d51a54e504b3f3c9' ORDER BY salary asc 1553 Query UPDATE aggregation_tmp_45403179 SET wf0 = 0.2 WHERE wf_rownum in (2) 1553 Query UPDATE aggregation_tmp_45403179 SET wf0 = 0.4 WHERE wf_rownum in (3) 1553 Query UPDATE aggregation_tmp_45403179 SET wf0 = 0.8 WHERE wf_rownum in (1,5) 1553 Query UPDATE aggregation_tmp_45403179 SET wf0 = 1 WHERE wf_rownum in (4)
-- return resultset to client
1559 Query SELECT expr$0 AS `depname`,expr$1 AS `empno`,expr$2 AS `salary`,wf0 as `ss` FROM `aggregation_tmp_45403179` ORDER BY wf0_hash,salary asc
-- remove temp table
1559 Query DROP TABLE IF EXISTS aggregation_tmp_45403179 1559 Quit 1550 Quit
150303 13:12:12 1533 Query set global general_log=0
protected function wf_cume_dist($num,$state) { static $sum; $win = $state->windows[$num]; if(empty($win['order'])) { if($percent) $sql = "update " . $state->table_name . " set wf{$num}=1"; else $sql = "update " . $state->table_name . " set wf{$num}=0";
$state->DAL->my_query($sql); if($err = $state->DAL->my_error()) { $this->errors[] = $err; return false; } return true; } else { /* running sum*/ $sql = "SELECT distinct wf{$num}_hash h from " . $state->table_name . " ORDER BY " . $win['order_by']; $stmt = $state->DAL->my_query($sql); if($err = $state->DAL->my_error()) { $this->errors[] = $err; return false; } $last_hash = ""; $hash = ""; $last_ob_hash = ""; $ob_hash = ""; while($row = $state->DAL->my_fetch_assoc($stmt)) { #$sql = "select * from " . $state->table_name . " where wf{$num}_hash='" . $row['h'] . "' ORDER BY " . $win['order_by']; $sql = "SELECT *," . $state->winfunc_sql . " where wf{$num}_hash='" . $row['h'] . "' ORDER BY " . $win['order_by']; $stmt2 = $state->DAL->my_query($sql); if($err = $state->DAL->my_error()) { $this->errors[] = $err; return false; } $done=array(); $rows=array(); while($row2=$state->DAL->my_fetch_assoc($stmt2)) { $rows[] = $row2; } $last_hash = ""; $last_ob_hash = ""; $i = 0; $rowlist=""; $rank = 0;
while($i<count($rows)) { $row2 = $rows[$i]; ++$rank; $ob_hash = $row2["wf{$num}_obhash"]; $rowlist=$row2['wf_rownum']; for($n=$i+1;$n<count($rows);++$n) { $row3 = $rows[$n]; $new_ob_hash = $row3["wf{$num}_obhash"]; if($new_ob_hash != $ob_hash) { break; } $rowlist .= "," . $row3['wf_rownum']; ++$i; ++$rank; } $dist = $rank/count($rows); $sql = "UPDATE " . $state->table_name . " SET wf{$num} = {$dist} WHERE wf_rownum in ({$rowlist})"; $state->DAL->my_query($sql); if($err = $state->DAL->my_error()) { $this->errors[] = $err; return false; } ++$i; } } } return true; }
On Tue, Mar 3, 2015 at 12:55 PM, Igor Babaev <igor@askmonty.org> wrote:
Hi, Igor!
On Mar 03, Igor Babaev wrote:
> >> I'd also like to discuss window functions too. I've implemented
On 03/03/2015 10:25 AM, Sergei Golubchik wrote: them
>> in shard-query and have ideas about how to implement them in the >> server, but pluggable parser would be really useful here. > > Window functions have a good chance of being in 10.2, it's MDEV-6115. > But I don't think that somebody is working on MDEV-6115 yet.
I started working on MDEV-6115 some time ago.
Ah, great. Sorry, I didn't know it. Could you then discuss it with Justin, please? See above, he has some ideas about the implementation.
Justin, How do you prefer discussing your ideas? On IRC (#maria) or by phone? (I don't have skype at the moment)
Regards, Igor.
Regards, Sergei
_______________________________________________ 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, Igor, sorry if that was too much info or not what you are looking for. I get carried away sometimes :) Sent from my iPhone
On Mar 3, 2015, at 12:55 PM, Igor Babaev <igor@askmonty.org> wrote:
On 03/03/2015 10:25 AM, Sergei Golubchik wrote: Hi, Igor!
On Mar 03, Igor Babaev wrote:
I'd also like to discuss window functions too. I've implemented them in shard-query and have ideas about how to implement them in the server, but pluggable parser would be really useful here.
Window functions have a good chance of being in 10.2, it's MDEV-6115. But I don't think that somebody is working on MDEV-6115 yet.
I started working on MDEV-6115 some time ago.
Ah, great. Sorry, I didn't know it. Could you then discuss it with Justin, please? See above, he has some ideas about the implementation.
Justin, How do you prefer discussing your ideas? On IRC (#maria) or by phone? (I don't have skype at the moment)
Regards, Igor.
Regards, Sergei
_______________________________________________ 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
participants (6)
-
Federico Razzoli -
Igor Babaev -
Justin Swanhart -
Peter Laursen -
Reindl Harald -
Sergei Golubchik