MariaDB 5.3.3 causes 1093 error on Drupal

[metakel]

After I upgraded from MariaDB 5.2.10, my Drupal installation caught the below error log when I operated it (The Operation was: added a content to a nodequeue). After I downgraded back to 5.2.10, the problem was gone. The error reads:

PDOException: SQLSTATE[HY000]: General error: 1093 You can't specify target table 'nodequeue_nodes' for update in FROM clause: INSERT INTO {nodequeue_nodes} (sqid, qid, nid, position, timestamp) VALUES (:sqid, :qid, :nid, IFNULL((SELECT MAX(position)+1 FROM (SELECT * from {nodequeue_nodes} WHERE sqid = :sqid) as nn), 1), :time); Array ( [:sqid] => 7 [:qid] => 7 [:nid] => 162 [:time] => 1325256693 ) in nodequeue_subqueue_add() (line 1042 of /xxx/modules/nodequeue/nodequeue.module).

[Elena Stepanova (elenst)]

The queries similar to

CREATE TABLE t ( i INT );
INSERT INTO t VALUES ( (SELECT 1 FROM ( SELECT * FROM t ) as a) );

indeed produce ER_UPDATE_TABLE_USED in 5.3.3 with derived_merge=ON, and pass with derived_merge=OFF (in 5.3.3 derived_merge=ON by default).

It seems consistent with how it works, both in 5.2 and 5.3, with a view instead of the inner subquery:

CREATE TABLE t ( i INT );
CREATE ALGORITHM=<algorithm> VIEW v AS SELECT * FROM t;
INSERT INTO t VALUES ( (SELECT 1 FROM v) );

produces an error ER_VIEW_PREVENT_UPDATE if <algorithm>=MERGE, while with<algorithm>=TEMPTABLE it works all right.

I will assign it to Igor to confirm it's the expected behavior (or not confirm).

Meanwhile, you could disable derived_merge (generally or in the session) to keep using the old query:
SET optimizer_switch='derived_merge=off';

[Oleksandr "Sanja" Byelkin (sanja-byelkin)]

fix discovered such bad thing in:
create table t3 (a int);
select * from (select 1 as a) as t1 join (select * from test.t3) as t2;
drop table t3;

we have:
(gdb) p all_tables->select_lex
$4 = (st_select_lex *) 0x1c1aae8
(gdb) p all_tables->next_global->next_global->select_lex
$5 = (st_select_lex *) 0x1c1aae8
(gdb) p all_tables->alias
$6 = 0x1c9dc18 "t1"
(gdb) p all_tables->next_global->next_global->alias
$7 = 0x1c9e848 "t2"

i.e. two different derived tables refer to the same SELECT_LEX structure

[Oleksandr "Sanja" Byelkin (sanja-byelkin)]

The problem is present in 5.2 so should be fixed there first.

[Oleksandr "Sanja" Byelkin (sanja-byelkin)]

opened bug https://bugs.launchpad.net/maria/+bug/914224

[Oleksandr "Sanja" Byelkin (sanja-byelkin)]

It was my mistake the TABLE_LIST::select_lex shoudle be the same (in is where table is used).