What's the consensus on updating old SQL files? Because there are more instances of this.

Maybe you can find the answer in 22754.

That's fine for copyright changes. But if existing code can potentially cause issues, it should be updated.

What's the consensus on updating old SQL files? Because there are more instances of this.

What is the benefit of doing that? As long as the current sql is correct thats all that matters.

It's not. If you have a 3rd component with Article type title, this SQL is going to affect it as well. Although neither type_alias nor type_title column is unique in the database, we can assume that type_alias is used as such. I.e. you should not have multiple row with com_content.article alias in the table. But you can have com_example.article and com_content.article, as well as multiple entries with title Article.

My comment was about updating the old sql only

Old SQLs have the same potential issue.

But they will never be the sql used on a site. Or am I missing something.

if you update form < 3.9 you execute this sql file and it should be changed because it's wrong

As long as the last of the update sql files is correct thats all the matters. There is no point in going back through all the existing update sql and changing them. At least thats my understanding of the question about OLD sql files

if we have sql query that can break a website because our query is wrong it has to be changed.

And thats the problem here, we break websites having an extensions matching the query in the SQL. The reason for this is because the where clause doesn't match a unique core field instead it match a field used by any component.

I am obviously really stupid then as I still don't see the need to change an OLD update sql file.

Site is on Joomla 3.9.1 and updates
update-3.9.2.sql is applied (still with the error)
update-3.9.3.sql is applied (still with the error)
update-3.9.4.sql is applied (still with the error)
update-3.9.5.sql is applied (still with the error)
update-3.9.6.sql is applied (still with the error)
update-3.9.7.sql is applied (still with the error)
update-3.9.8.sql is applied (with the correction)

So why do you go back in time and apply the fix to update-3.9.4.sql etc

Site is on 3.8.0 then it get executed.

@brianteeman I'm not talking about every SQL file, only those that contain similar errors, for example:

@SharkyKZ I stand by my comments. If that file with the incorrect sql is applied it will be later updated by the correct sql file.

@brianteeman I think you don't understand the problem. It's not possible to fix it after we broke it because we have a data loss after this sql statement.

There is no way to create a new update file to "fix" any damage created by the pre-patch version of these files, the WHERE condition is too broad and potentially updates rows it wasn't intended to in the right conditions. This patch adjusts those queries so they are more correctly limited to only the rows the original author actually intended to touch. So it fixes a problem in that someone updating from 3.8 or earlier will not have an overly eager query applied which may introduce issues to their #__content_types tables, but there is not a way to introduce a new UPDATE query that reverses this one.

This is in part why data updates do not belong in an API that should ONLY be dealing with schema changes...

I have tested this item ✅ successfully on 49efcaa

This PR is correct, I agree with the explanation by @mbabker here: #22419 (comment).

I have tested this item ✅ successfully on 49efcaa

RTC

Thanks finally.