Bug editing joined columns

dverspui posted 7 years ago in Running SQL scripts
I just found and installed HeidiSQL and must say I am very impressed. I am a SQLyog user for years but I am now considering using HeidiSQL instead. So thanks for this great piece of software!

I however noticed one bug that could potentially corrupt data. If one executes an SQL statement with joined tables HeidiSQL enables you to edit the resulting grid, but it always executes the UPDATE statements on the base table of the SELECT statement regardless of what table the field belongs to!

For example if you run:

A.id, A.foo, B.id, B.a_id,
B.bar, B.foo
INNER JOIN B ON B.a_id = A.id

and edit a B.bar value it would execute something like:

UPDATE A SET bar='xxx' WHERE id=1;

This would fail and issue a notice to the user because no bar column exists in A.
But when you edit a B.foo value it would update the A.foo with it!

Therefore I propose HeidiSQL should execute the UPDATE query like SQLyog does, it is very handy and intuitive:
1 show a dropdown near the result grid with the names of tables whose columns appear in the results. The user has to explicitely select a table from this list to enable editing of its values in the grid. Without selecting one editing is disabled (for security);
2 the UPDATE statement issued when editing a column value must of course be targeted at the selected table;
3 but additionally its WHERE clause should contain all column values of the edited row equaling the original values. This way one can edit in a grid that lacks the primary key for the selected table. At first it should calculate the number of rows that would be updated and if >1 it should request confirmation from the user first (e.g. "This would update 123 rows, Are you sure?")

I surely hope you can consider implementing this, or at least 2. for fixing the bug.
dverspui posted 7 years ago
Any followup on this? Much appreciated.
ansgar posted 7 years ago
Ok, this is indeed not as it is supposed to be. As soon as you go and start editing such a result, you should get this error popup: More than one table involved.. And editing should be blocked then. At least that is what I implemented. But the method where this error (TMySQLQuery.TableName) is triggered is probably not called for some reason. Will check that.

I will postpone editing multiple table queries for now - that's dangerous and needs some deeper analysis.
ansgar posted 7 years ago
Sorry for the delay, btw. I was in Cologne at that time and just overseen this posting between too many mails.
dverspui posted 7 years ago
No problem at all, thanks for investigating anyway. Can you also please consider my proposal, it should not be too hard to implement and it is very handy, using it all the time.

As a developer I really would be willing to help but I just do not have the skills nor tools to edit Delphi source, sorry.

Please login to leave a reply, or register at first.