[MDEV-24343] Spider Left join failed Unknown column 't0.ID' in 'on clause' - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Major
Resolution: Fixed
Affects Version/s: 10.5.8, 10.4(EOL), 10.6
Fix Version/s: 10.4.26, 10.5.17, 10.6.9, 10.7.5, 10.8.4, 10.9.2
Component/s: Storage Engine - Spider
Labels:
- not-10.2
- not-10.3
Environment:
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

- Sort By Name
- Sort By Date
- Ascending
- Descending
- Thumbnails
- List
- Download All

TEST_RDT_1.sql
2 kB
2020-12-03 15:35
TEST_RDT_2.sql
2 kB
2020-12-03 15:35
TEST_RDT_3.sql
2 kB
2020-12-03 15:35
variables_spider.txt
22 kB
2020-12-03 15:35

Activity

Ascending order - Click to sort in descending order

VAROQUI Stephane created issue - 2020-12-03 15:32

VAROQUI Stephane made changes - 2020-12-03 15:35

Field	Original Value	New Value
Attachment		TEST_RDT_2.sql [ 55005 ]
Attachment		TEST_RDT_3.sql [ 55006 ]
Attachment		TEST_RDT_1.sql [ 55007 ]
Attachment		variables_spider.txt [ 55008 ]

VAROQUI Stephane made changes - 2020-12-03 18:33

Description

Hi using spider as federated tables

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 .

Hi using spider as federated tables

{code:sql}
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'
{code:sql}

{code:sql}
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)
{code:sql}

Depending on the pos of LEFT join in FROM clause it failed or not .

VAROQUI Stephane made changes - 2020-12-03 18:34

Description

Hi using spider as federated tables

{code:sql}
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'
{code}

{code:sql}
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)
{code}

Depending on the pos of LEFT join in FROM clause it failed or not .

VAROQUI Stephane made changes - 2020-12-03 18:35

Description

Hi using spider as federated tables

{code:sql}
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'
{code}

{code:sql}
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)
{code}

Depending on the pos of LEFT join in FROM clause it failed or not .

Hi using spider as federated tables each table on same backend

{code:sql}
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'
{code}

{code:sql}
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)
{code}

Depending on the pos of LEFT join in FROM clause it failed or not .

Elena Stepanova made changes - 2021-01-11 21:40

Fix Version/s		10.5 [ 23123 ]
Assignee		Kentoku Shiba [ kentoku ]

Nayuta Yanagisawa (Inactive) made changes - 2021-08-13 09:09

Status

Open [ 1 ]

Confirmed [ 10101 ]

Nayuta Yanagisawa (Inactive) made changes - 2021-08-13 09:12

Comment

[ I confirmed that the bug is reproducible on 10.5 HEAD.

The wrong query executed on the data node is the following:

{code:sql}
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`)
{code} ]

Nayuta Yanagisawa (Inactive) made changes - 2021-08-13 09:57

Labels

not-10.1

Nayuta Yanagisawa (Inactive) made changes - 2021-08-13 09:57

Labels

not-10.1

not-10.2

Nayuta Yanagisawa (Inactive) made changes - 2021-08-13 10:29

Labels

not-10.2

not-10.2 not-10.3

Nayuta Yanagisawa (Inactive) made changes - 2021-08-13 10:29

Affects Version/s		10.4 [ 22408 ]
Affects Version/s		10.6 [ 24028 ]

Nayuta Yanagisawa (Inactive) made changes - 2021-08-13 10:30

Fix Version/s		10.4 [ 22408 ]
Fix Version/s		10.6 [ 24028 ]

Nayuta Yanagisawa (Inactive) made changes - 2021-08-24 23:54

Assignee

Kentoku Shiba [ kentoku ]

Nayuta Yanagisawa [ JIRAUSER47117 ]

Nayuta Yanagisawa (Inactive) made changes - 2021-10-08 11:35

Status

Confirmed [ 10101 ]

In Progress [ 3 ]

Nayuta Yanagisawa (Inactive) made changes - 2021-10-11 07:10

Assignee	Nayuta Yanagisawa [ JIRAUSER47117 ]	Alexey Botchkov [ holyfoot ]
Status	In Progress [ 3 ]	In Review [ 10002 ]

Sergei Golubchik made changes - 2021-12-06 21:35

Workflow

MariaDB v3 [ 116453 ]

MariaDB v4 [ 143778 ]

Alexey Botchkov made changes - 2022-06-27 13:43

Assignee	Alexey Botchkov [ holyfoot ]	Nayuta Yanagisawa [ JIRAUSER47117 ]
Status	In Review [ 10002 ]	Stalled [ 10000 ]

Nayuta Yanagisawa (Inactive) made changes - 2022-06-28 02:32

Fix Version/s		10.4.26 [ 27511 ]
Fix Version/s		10.5.17 [ 27509 ]
Fix Version/s		10.6.9 [ 27507 ]
Fix Version/s		10.7.5 [ 27505 ]
Fix Version/s		10.8.4 [ 27503 ]
Fix Version/s		10.9.2 [ 27115 ]
Fix Version/s	10.4 [ 22408 ]
Fix Version/s	10.5 [ 23123 ]
Fix Version/s	10.6 [ 24028 ]
Resolution		Fixed [ 1 ]
Status	Stalled [ 10000 ]	Closed [ 6 ]

People

Assignee:: Nayuta Yanagisawa (Inactive)

Reporter:: VAROQUI Stephane

Votes:: 0 Vote for this issue

Watchers:: 4 Start watching this issue

Dates

Created:: 2020-12-03 15:32

Updated:: 2022-06-28 02:32

Resolved:: 2022-06-28 02:32

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB Server

Details

Description

Attachments

Attachments

Activity

People

Dates

Git Integration