I have query, which used to work on MariaDB 10.2.15. After upgrading to MariaDB 10.3.7 this query doesn't work anymore and I don't know why.
The query looks something like this:
WITH
table_1 AS (SELECT id AS 'first_id' FROM some_table),
table_2 AS (select id AS 'second_id' FROM another_table)
SELECT *
FROM table_1
LEFT OUTER JOIN table_2 ON table_1.first_id = table_2.second_id
UNION
SELECT *
FROM table_1
RIGHT OUTER JOIN table_2 ON table_1.first_id = table_2.second_id
If i try to run this query, I get error Unknown column 'table_2.second_id' in 'on clause'
. Now it gets weird, because when I modify query (removing aliases from join columns) to something like this (see below), everything works fine.
WITH
table_1 AS (SELECT id FROM some_table),
table_2 AS (select id FROM another_table)
SELECT *
FROM table_1
LEFT OUTER JOIN table_2 ON table_1.id = table_2.id
UNION
SELECT *
FROM table_1
RIGHT OUTER JOIN table_2 ON table_1.id = table_2.id
EDIT 1: I tried to change the query to this and it still doesn't work.
WITH
table_1 AS (SELECT id AS `first_id` FROM some_table),
table_2 AS (select id AS `second_id` FROM another_table)
SELECT *
FROM table_1
LEFT OUTER JOIN table_2 ON table_1.first_id = table_2.second_id
UNION
SELECT *
FROM table_1
RIGHT OUTER JOIN table_2 ON table_1.first_id = table_2.second_id
EDIT 2:
I am pretty sure, that first_id
column exists becuase, when I run query bellow, I can see desired column in retrieved results. I guess my aliased columns are lost during LEFT OUTER JOIN process.
WITH
table_1 AS (SELECT id AS `first_id` FROM some_table),
table_2 AS (select id AS `second_id` FROM another_table)
SELECT *
FROM table_1