Can one of the devs explain to me how it can give me "wrong" data?

I'm super confused how there can be a bug that exists where I run a query on a table the alters data. The query is reported to have ran successfully and my views in Heidi show that the query ran successfully - even if I refresh the host, database and table individually, Heidi insists the data was changed. This is not the case, however, as my external application that also uses the same database does not see the changes. Upon completely shutting down and restarting Heidi, it shows that the query was in-fact, not ran, and had been just wasting my time lying to me about the data in the table.

So my question is simply, how can this possibly happen? The only thing I can think of is that there is some kind of "cache" on which my query is also ran on. Then, instead of just running the query on the database to get the data when do an action that should result in data being populated, it's returning the data from the "cache" which is somehow desynced from the actual data because somehow the query I ran was only executed on the cached copy and not the actual DB.

If this is the case, my next question is why? Why is this necessary to have a cached copy of the data? This is literally an SQL workbench. Is really necessary? Cant we just query the database each time we need data? Isn't that the whole point of the database? What are we really saving optimization wise by doing that?

I'd love to know, because it's honestly so confusing to me (and frustrating) that this is not only a "thing" but a "thing" that has been around for as long as it has.

There is no such a data cache in HeidiSQL like you describe. There are some caches but these just hold table/column definitions, and some meta data. When you run a query through a query tab, the data is fetched freshly from the server.

There is no real technical info in your post so I can not really help you here. Only one thing wondering - in the first paragraph you're talking about views, and later about tables. Just to be sure, not that you are comparing different things.

Is it possible that the queries ran in a transaction that never got commited?

I'm not talking about "generated views" in the context of an actual generated view, I'm just referring to my personal "view" of the data tab of the table. I guess that wording is confusing.

@roelschroeven: I'm not creating any type of transaction so if that is happening its happening internal to Heidi's execution of the query. Even then, I don't really know how it could be transaction related if what @ansgar says is true, as refreshing the "data" tab on the table is literally showing the wrong data. It's showing me the data as if the query I ran was executed on the table, no matter how many times I refresh, even though in reality, the query I ran was either not executed on the table, or something failed in the process of it attempting to run the query where Heidi's code was under the impression that it completed (or otherwise unaware that it failed).

So to recap, I ran a query that mutated data in a table, heidi reported success, showed data in the data tab for the table that I expected to see had the query been successful (replaced all data in a column for all rows in this instance - so now all rows show the new value in the colum that I set in my query). This is only correct for my Heidi instance in this case. All other external apps that query this table still see the old, non-updated column data instead of the new data. Even if I refresh at the host, database or table level, the data tab insists the data has been changed when it has not been. Closing Heidi at this point and re-opening it shows the data in the table was not changed. Running the same query in the same manner at this point now actually works correctly - the only difference being a fresh instance of the app.

I'm not sure how to reproduce this bug as it does not happen reliably. It seems to happen randomly when the app has been open already for a while as it generally never bugs out after a fresh opening.

I do use a laptop that I move between work and home so there are times where a Heidi instance may be open for several days straight, spanning across multiple hibernations.

I'm not sure if something related to the computer sleeping / hibernating with Heidi open is related.

Note, my other post here (https://www.heidisql.com/forum.php?t=39940#p40431) has the query I ran which I am referring to here.

I suppose it could be that a transaction is started and is never committed or rolled back so its just hanging open and that might explain why "refreshing" the data is showing the uncommitted data from the current transaction that was never committed and closed.

Again - I'm not creating a transaction myself, so if this is the case, then there is some bug causing the the transaction to not be committed "on occasion" that could in theory result in behavior that resembles what I am reporting...

Does your HeidiSQL session have a "Startup script" in the "Advanced" tab? If yes, does the content say something about a transaction, e.g. "BEGIN" or such?