Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.5.8, 10.4(EOL), 10.6
-
Debian Buster x86
Description
Hi using spider as federated tables each table on same backend
MariaDB [SANDBOX]> SELECT * from TEST_RDT_2 JOIN TEST_RDT_3 ON TEST_RDT_2.ID = TEST_RDT_3.ID LEFT OUTER JOIN TEST_RDT_1 ON TEST_RDT_1.ID = TEST_RDT_2.ID; |
ERROR 1054 (42S22): Unknown column 't0.ID' in 'on clause' |
MariaDB [SANDBOX]> SELECT * from TEST_RDT_2 LEFT OUTER JOIN TEST_RDT_1 ON TEST_RDT_1.ID = TEST_RDT_2.ID JOIN TEST_RDT_3 ON TEST_RDT_2.ID = TEST_RDT_3.ID ; |
+----+-----------+------+-----------+----+---------+ |
| ID | LASTNAME | ID | FIRSTNAME | ID | SURNAME |
|
+----+-----------+------+-----------+----+---------+ |
| 1 | DEMONGEOT | 1 | RICHARD | 1 | CON |
|
| 2 | VAROQUI | 2 | STEPHANE | 2 | MOYEN |
|
+----+-----------+------+-----------+----+---------+ |
2 rows in set (0.008 sec) |
Depending on the pos of LEFT join in FROM clause it failed or not .
Attachments
Activity
It seems that the engine deals with LEFT JOIN before the "JOIN".
MariaDB [SANDBOX]> -- First join TEST_RDT_2 AND then TEST_RDT_3 : does not works
|
MariaDB [SANDBOX]> SELECT * from TEST_RDT_2, TEST_RDT_3 LEFT OUTER JOIN TEST_RDT_1 ON TEST_RDT_1.ID = TEST_RDT_2.ID WHERE TEST_RDT_2.ID = TEST_RDT_3.ID;
|
ERROR 1054 (42S22): Unknown column 'TEST_RDT_2.ID' in 'on clause'
|
MariaDB [SANDBOX]> SELECT * FROM TEST_RDT_2;
|
+----+-----------+
|
| ID | LASTNAME |
|
+----+-----------+
|
| 1 | DEMONGEOT |
|
| 2 | VAROQUI |
|
+----+-----------+
|
2 rows in set (0.006 sec)
|
|
|
MariaDB [SANDBOX]> -- First join TEST_RDT_3 AND then TEST_RDT_2 works.
|
MariaDB [SANDBOX]> SELECT * from TEST_RDT_3, TEST_RDT_2 LEFT OUTER JOIN TEST_RDT_1 ON TEST_RDT_1.ID = TEST_RDT_2.ID WHERE TEST_RDT_2.ID = TEST_RDT_3.ID;
|
+----+---------+----+-----------+------+-----------+
|
| ID | SURNAME | ID | LASTNAME | ID | FIRSTNAME |
|
+----+---------+----+-----------+------+-----------+
|
| 1 | CON | 1 | DEMONGEOT | 1 | RICHARD |
|
| 2 | MOYEN | 2 | VAROQUI | 2 | STEPHANE |
|
+----+---------+----+-----------+------+-----------+
|
2 rows in set (0.009 sec)
|
|
|
MariaDB [SANDBOX]> SELECT * from TEST_RDT_3 JOIN TEST_RDT_2 ON TEST_RDT_2.ID = TEST_RDT_3.ID LEFT OUTER JOIN TEST_RDT_1 ON TEST_RDT_1.ID = TEST_RDT_2.ID;
|
+----+---------+----+-----------+------+-----------+
|
| ID | SURNAME | ID | LASTNAME | ID | FIRSTNAME |
|
+----+---------+----+-----------+------+-----------+
|
| 1 | CON | 1 | DEMONGEOT | 1 | RICHARD |
|
| 2 | MOYEN | 2 | VAROQUI | 2 | STEPHANE |
|
+----+---------+----+-----------+------+-----------+
|
2 rows in set (0.009 sec)
|
|
|
MariaDB [SANDBOX]> -- With a subqueries (but can't use SELECT *, loosing an ID)
|
MariaDB [SANDBOX]> SELECT * FROM ( SELECT TEST_RDT_2.ID, SURNAME, LASTNAME FROM TEST_RDT_2 JOIN TEST_RDT_3 ON TEST_RDT_2.ID = TEST_RDT_3.ID) A LEFT OUTER JOIN TEST_RDT_1 ON TEST_RDT_1.ID = A.ID;
|
+----+---------+-----------+------+-----------+
|
| ID | SURNAME | LASTNAME | ID | FIRSTNAME |
|
+----+---------+-----------+------+-----------+
|
| 1 | CON | DEMONGEOT | 1 | RICHARD |
|
| 2 | MOYEN | VAROQUI | 2 | STEPHANE |
|
+----+---------+-----------+------+-----------+
|
2 rows in set (0.011 sec)
|
|
|
MariaDB [SANDBOX]>
|
|
I confirmed that the bug is reproducible on 10.5 HEAD.
The query executed on the data node is the following:
info: spider query=select t0.`ID` `ID`,t0.`LASTNAME` `LASTNAME`,t1.`ID` `ID`,t1.`SURNAME` `SURNAME`,t2.`ID` `ID`,t2.`FIRSTNAME` `FIRSTNAME` from `test`.`TEST_RDT_2` t0,`test`.`TEST_RDT_3` t1 left join `test`.`TEST_RDT_1` t2 on (t2.`ID` = t0.`ID`) where (t1.`ID` = t0.`ID`) |
I think this is due to the precedence of the operators. So, possible solution would be use 'JOIN' instead of the comma, or add parentheses like (t0, t1).
However, the precedence of the comma operator is less than that of INNER JOIN, CROSS JOIN, LEFT JOIN, and so on. If you mix comma joins with the other join types when there is a join condition, an error of the form Unknown column 'col_name' in 'on clause' may occur. Information about dealing with this problem is given later in this section. bq.
The bug is reproducible on 10.4 and 10.6, but not on 10.2. 10.3 HEAD.
MTR test case:
--disable_query_log
|
--disable_result_log
|
--source ../t/test_init.inc
|
--enable_result_log
|
--enable_query_log
|
|
|
--connection child2_1
|
CREATE DATABASE auto_test_remote; |
USE auto_test_remote; |
|
|
eval CREATE TABLE tbl_a ( |
`id` int(10) unsigned NOT NULL AUTO_INCREMENT, |
`first_name` varchar(255) DEFAULT NULL, |
PRIMARY KEY (`id`) |
) $CHILD2_1_ENGINE $CHILD2_1_CHARSET;
|
|
|
INSERT INTO `tbl_a` VALUES (1,'RICHARD'), (2,'STEPHANE'), (3,'ALAIN'); |
|
|
eval CREATE TABLE `tbl_b` ( |
`id` int(10) unsigned NOT NULL AUTO_INCREMENT, |
`last_name` varchar(255) DEFAULT NULL, |
PRIMARY KEY (`ID`) |
) $CHILD2_1_ENGINE $CHILD2_1_CHARSET;
|
|
|
INSERT INTO `tbl_b` VALUES (1,'DEMONGEOT'),(2,'VAROQUI'); |
|
|
eval CREATE TABLE `tbl_c` ( |
`id` int(10) unsigned NOT NULL AUTO_INCREMENT, |
`surname` varchar(255) DEFAULT NULL, |
PRIMARY KEY (`id`) |
) $CHILD2_1_ENGINE $CHILD2_1_CHARSET;
|
|
|
INSERT INTO `tbl_c` VALUES (1,'CON'),(2,'MOYEN'),(3,'MOYEN2'); |
|
|
--connection master_1
|
CREATE DATABASE auto_test_local; |
USE auto_test_local; |
|
|
eval CREATE TABLE tbl_a ( |
`id` int(10) unsigned NOT NULL AUTO_INCREMENT, |
`first_name` varchar(255) DEFAULT NULL, |
PRIMARY KEY (`id`) |
) $MASTER_1_ENGINE $MASTER_1_CHARSET COMMENT='table "tbl_a"' |
PARTITION BY LIST COLUMNS(`id`) ( |
PARTITION `pt1` DEFAULT COMMENT = 'srv "s_2_1"' |
);
|
|
|
eval CREATE TABLE `tbl_b` ( |
`id` int(10) unsigned NOT NULL AUTO_INCREMENT, |
`last_name` varchar(255) DEFAULT NULL, |
PRIMARY KEY (`id`) |
) $MASTER_1_ENGINE $MASTER_1_CHARSET COMMENT='table "tbl_b"' |
PARTITION BY LIST COLUMNS(`id`) ( |
PARTITION `pt1` DEFAULT COMMENT = 'srv "s_2_1"' |
);
|
|
|
eval CREATE TABLE `tbl_c` ( |
`id` int(10) unsigned NOT NULL AUTO_INCREMENT, |
`surname` varchar(255) DEFAULT NULL, |
PRIMARY KEY (`id`) |
) $MASTER_1_ENGINE $MASTER_1_CHARSET COMMENT='table "tbl_c"' |
PARTITION BY LIST COLUMNS(`id`) ( |
PARTITION `pt1` DEFAULT COMMENT = 'srv "s_2_1"' |
);
|
|
|
SELECT * from tbl_b JOIN tbl_c ON tbl_b.id = tbl_c.id LEFT OUTER JOIN tbl_a ON tbl_a.id = tbl_b.id; |
|
|
--connection master_1
|
DROP DATABASE auto_test_local; |
|
|
--connection child2_1
|
DROP DATABASE auto_test_remote; |
|
|
--disable_query_log
|
--disable_result_log
|
--source ../t/test_deinit.inc
|
--enable_result_log
|
--enable_query_log |
I tried git-bisect but it didn't work well between 10.4.6 and 10.4.7 (it required code modification to pass compilation and the test failed because of different reasons).
mariadb-10.4.6 does not push down the join query. So, it is likely that this bug has been latent since when this kind (what kind?) of JOINs were to pushed down.
The following patch fixes the bug but this must affect other queries. So, I need to dig the problem further.
diff --git a/storage/spider/spd_db_mysql.cc b/storage/spider/spd_db_mysql.cc
|
index ee3c25d914c..b0f868110fd 100644
|
--- a/storage/spider/spd_db_mysql.cc
|
+++ b/storage/spider/spd_db_mysql.cc
|
@@ -7070,11 +7070,11 @@ int spider_db_mbase_util::append_table( |
} else { |
if (str) |
{
|
- if (str->reserve(SPIDER_SQL_COMMA_LEN)) |
+ if (str->reserve(SPIDER_SQL_JOIN_LEN)) |
{
|
DBUG_RETURN(HA_ERR_OUT_OF_MEM);
|
}
|
- str->q_append(SPIDER_SQL_COMMA_STR, SPIDER_SQL_COMMA_LEN);
|
+ str->q_append(SPIDER_SQL_JOIN_STR, SPIDER_SQL_JOIN_LEN);
|
}
|
}
|
}
|
It doesn't seem to affect the other queries as much as I had initially expected.
holyfoot Please review https://github.com/MariaDB/server/commit/33a058593f5c76f0a30c79ad7f1d7f7be83f18ea
holyfoot The fix has been proposed months ago. Please review.
Same issue after an upgrade to MariaDB 10.5.11 .