can you give me a sql case and analysis the logic of the writeAllChanges func internally?

I find it interesting that you have been asking a ton of questions that imply you are looking at the source code, but the answers to your questions are literally a few lines further in the code. If you have gone that far, why not take the time to read the following code and understand for yourself what it is doing? Every assertion you have made about how it should work differently has been wrong. It seems you are trying to do a comparison between technology like snowflake and databricks. This community has been very generous answering your questions, which is awesome. But now you should probably take the time to read and understand the code, not just the comments.

yes, thanks.

i think you might be interested in running delta locally with a debugger and stepping through line by line - you can run all the SQL cases you've proposed and watch it happen live... likely be more illuminating than a static read

how to debug delta_lake? is there a doc?