" This might be better suited as a feature request on the MariaDB JIRA." I got chastised for doing that before so I'm trying to play nice! ;-) -----Original Message----- From: Geoff Montee [mailto:geoff.montee@gmail.com] Sent: 09 June 2015 20:16 To: Rhys Campbell; MariaDB discuss Subject: Re: [Maria-discuss] DELETE FROM RETURNING Hi Rhys, On Tue, Jun 9, 2015 at 8:29 AM, Rhys Campbell <Rhys.Campbell@tradingscreen.com> wrote:

Just regarding the RETURNING feature introduced into MariaDB 10.0.5 https://mariadb.com/kb/en/mariadb/delete/

It would be useful, similar to the OUTPUT clause in MSSQL, if we could INSERT the data sent back from a returning clause in a single statement. Something like…

CREATE TABEL deleted_ids

(

Id INT NOT NULL PRIMARY KEY

);

INSERT INTO deleted_ids

DELETE FROM t1

WHERE id < 999

RETURNING id;

This might be better suited as a feature request on the MariaDB JIRA. If you create an account, you should be able to submit it here: https://mariadb.atlassian.net Thanks, Geoff

Hi Rhys, On Wed, Jun 10, 2015 at 1:23 AM, Rhys Campbell <Rhys.Campbell@tradingscreen.com> wrote:

" This might be better suited as a feature request on the MariaDB JIRA."

I got chastised for doing that before so I'm trying to play nice! ;-)

I don't know why you were chastised in the past, but I've added this to the JIRA: https://mariadb.atlassian.net/browse/MDEV-8307 It sure sounds like a useful feature to me. Thanks for the suggestion! Geoff

Hi, Really, the SQL-2003 standard syntax would be nice (implementing non-recursive CTE is mostly trivial, btw): WITH moved_rows AS ( DELETE FROM products WHERE "date" >= '2010-10-01' AND "date" < '2010-11-01' RETURNING * ) INSERT INTO products_log SELECT * FROM moved_rows; On Thu, Jun 11, 2015 at 12:09 PM, Roberto Spadim <roberto@spadim.com.br> wrote:

+1, good idea, today i execute an insert, and a delete

2015-06-11 16:07 GMT-03:00 Geoff Montee <geoff.montee@gmail.com>:

...

Hi Rhys,

On Wed, Jun 10, 2015 at 1:23 AM, Rhys Campbell <Rhys.Campbell@tradingscreen.com> wrote:

...

" This might be better suited as a feature request on the MariaDB JIRA."

I got chastised for doing that before so I'm trying to play nice! ;-)

I don't know why you were chastised in the past, but I've added this to the JIRA:

https://mariadb.atlassian.net/browse/MDEV-8307

It sure sounds like a useful feature to me. Thanks for the suggestion!

Geoff

_______________________________________________ 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

-- Roberto Spadim SPAEmpresarial - Software ERP Eng. Automação e Controle

_______________________________________________ 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 would be very nice if I could exec SQL in the server without hacking that using a subclass which would be a big patch. Any updates on when that MDEV (don't know #) will be implemented? For many things the event system can be hijacked to run an event that runs a sp that executes arbitrary SQL and puts results in a table, but that is /really/hacky. It could work though, there is a stored proc from oracle CTE for recursive CTE :) Sent from my iPhone

On Jun 11, 2015, at 5:20 PM, Geoff Montee <geoff.montee@gmail.com> wrote:

Hi Justin,

...

On Thu, Jun 11, 2015 at 12:28 PM, Justin Swanhart <greenlion@gmail.com> wrote: Hi,

Really, the SQL-2003 standard syntax would be nice (implementing non-recursive CTE is mostly trivial, btw):

WITH moved_rows AS ( DELETE FROM products WHERE "date" >= '2010-10-01' AND "date" < '2010-11-01' RETURNING * ) INSERT INTO products_log SELECT * FROM moved_rows;

I agree. It would be great if MariaDB supported CTEs. I've submitted a feature request for that as well:

https://mariadb.atlassian.net/browse/MDEV-8308

If you have a patch, feel free to contribute it.

Thanks,

Geoff

Yeah, that is the one. Basically, inside the server there isn't an API for running a SQL statement and returning the results internally. The results of the select are automatically packed and sent over the wire. Because of this, there are no real good solutions for running SQL in the server. I'd like to be able to use mysql_internal_connect() (a new function that works like mysql_connect) and then mysql_query() to basically get a authentication-less (auth is already done) handle to the current connection and execute sql on it. For example, it would be great if a rewrite plugin could run some SQL to populate a table, then rewrite the query as select * from tmp_table. While I could write a rewrite plugin the uses the C client API to run queries as long as I use a username/password to connect, I can't run them in the context of the original THD, so it can't access temporary tables created by the C API connection through the THD, so it isn't very useful. --Justin On Thu, Jun 11, 2015 at 6:34 PM, Geoff Montee <geoff.montee@gmail.com> wrote:

...

It would be very nice if I could exec SQL in the server without hacking

On Thu, Jun 11, 2015 at 5:50 PM, Justin Swanhart <greenlion@gmail.com> wrote: that using a subclass which would be a big patch. Any updates on when that MDEV (don't know #) will be implemented?

I'm not completely sure which MDEV you are referring to. Is it this one?

https://mariadb.atlassian.net/browse/MDEV-4641

I'm not sure what the progress is on that, but it's still unassigned in JIRA.

Geoff