-1

I have a MySql view defined basically in this way

CREATE VIEW viewexample AS

SELECT 
CONCAT(a.ID,d.ID) as ID
FROM`TBLA` a
INNER JOIN `TBLD` d ON ...
group by a.ID,d.ID

It is working perfectly in a MySql 5.6.21 server, I can perform select * from viewexample where ID = 123456 without any problem.

Now, I migrated to a MySql 10.4.11-MariaDB Server, with the same view definition. I can perform ok select * from viewexample, but if I execute select * from viewexample where ID= 123456 I'm getting the 1052 error

SQL Error (1052): Column 'ID' in order clause is ambiguous

If I remove the group by clause it works.

If I change my view definition to

CREATE VIEW viewexample AS

SELECT 
CONCAT(a.ID,d.ID) as ID2
FROM`TBLA` a
INNER JOIN `TBLD` d ON ...
group by a.ID,d.ID

select * from viewexample where ID2= 123456 works perfectly fine.

I guess that would be my solution for now, but I'm really very curious about this issue. Am I missing any new rule on the newest MySql version?

Lucas
  • 1
  • 3
  • This seems like a bug. The column names in the underlying table should not be visible to users of the view. – Barmar Aug 17 '23 at 14:25
  • Oh, I didn't notice that this was complaining about `ORDER BY`, not `WHERE`. – Barmar Aug 17 '23 at 14:26
  • Try changing the view to `ORDER BY CONCAT(a.ID, d.ID)` instead of using the alias. – Barmar Aug 17 '23 at 14:27
  • Sorry, I just edited. I missed a group by clause in my view definition. There is no order by involved in any step of the view use – Lucas Aug 17 '23 at 14:29
  • If there's no ORDER BY and the error message says that's where the ambiguity is, that's really a bug. – Barmar Aug 17 '23 at 14:45
  • Study "CREATE VIEW Syntax" in the documentation, pay attention to ALGORITHM clause. – Akina Aug 17 '23 at 15:23
  • Thank you @Akina, following the documentation MERGE algorithm can't be used on my view (because of group by). TEMPTABLE algorithm still throws the 1052 error – Lucas Aug 17 '23 at 18:15
  • I see a different error https://dbfiddle.uk/r4oup-7E please add sample data as text and complete the on clause in the query. – P.Salmon Aug 18 '23 at 06:22
  • You should use in dbfiddle create view... CONCAT(a.ID,d.ID) as ID instead of CONCAT(a.ID,d.ID) as ID2. "ID2" was a workaround. I already updated your fiddle in order to it to show the error – Lucas Aug 18 '23 at 13:16

0 Answers0