1

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
Jan Pech
  • 75
  • 1
  • 8
  • 2
    Instead of single quotes around your aliases (which signifies the values as string literals) use backticks to signify them properly as identifiers. Not sure why that would work on an older version as it should have errored out back then as well. [popular Q&A about this for mysql, but its virtually the same in mariadb](https://stackoverflow.com/questions/11321491/when-to-use-single-quotes-double-quotes-and-back-ticks-in-mysql) – JNevill Jun 08 '18 at 18:56
  • Tried to change that and it still throws same error. – Jan Pech Jun 08 '18 at 19:01
  • You may need to check the SQL mode. If either `ANSI` or `ANSI_QUOTES` are set, use double-quotes. https://mariadb.com/kb/en/library/sql-mode/ – Allan Jun 08 '18 at 19:29
  • 2
    Remove all those back ticks. By using them you are just asking for problems. Please let me know once you had removed them, to look at this question again. – The Impaler Jun 08 '18 at 19:49
  • File a bug with MariaDB. – Rick James Jun 08 '18 at 22:35

0 Answers0