That said, I'm not sure what the benefit is of this "trust no one" mindset.
Classical currencies are based on trust, and their trust argument is _far_ stronger than what the bitcoin community can offer: regulated instutions, the enforcement of laws backed up by firepower, millions of people who can absorb and correct problems, experts with mile long lists of credentials. Etc. And yet many hold the view that the trusted guardians of the popular currencies are violating this trust and will continue to do so. When you look at the Bitcoin ecosystem— it's hard to find arguments for well placed trust even a fraction as convincing as the trust thats failing in the wider world.
Complete trustlessness probably can't be achieved— with a wide enough definition, e.g. I haven't personally proven all of mathematics to myself or inspected my cpu with a home built electron microscope, but it can be approximated apparently arbitrarily close.
But first, there is a big distinction between trusting in the a world wide currency and trusting in a single trade of some small value. However trust dependent Bitcoin itself is no set of transactions denominated in BTC can be less trust dependent than the underlying Bitcoin. If you make ten small trades what is the probability that _all_ ten screw you? It is something like the probability of an individual failure raised to the tenth power (e.g. probably negligible)
plus the probability that Bitcoin screws you— The currency is a single point of failure, encompassing all the money in its economy, so it must be strong if anything is to be strong.
Ultimately you have to trust someone.
Secondly, this is in fact not the case: In situations where people are buying and selling digital data or services which occur within the context of machine verifiable processes it's sometimes possible to trade with absolutely no risk at all. E.g.
CoinSwap can let you trade Bitcoins and foocoins in a cheat proof way (so long as Bitcoin and Foocoin are secure), or e.g.
this protocol can allow selling knoweldge in a bidirectionally cheat proof way, in both cases without any third parties at all.
Even when a cheat-proof protocol isn't possible, as is the case for physical goods, you're not stuck with "a" trusted third party. In Bitcoin you can construct blockchain escrows with release rules like "(A and B) or ((A or B) and any-3-of(C, D, E, F, G))". How likely is a single arbitrator to screw you? Fine, add more until the potential for dishonesty is negligible, if what you're doing justifies it. But this only works if the system itself isn't imposing its own trusted parties on you.
These are all why Bitcoin has script to begin with— simply making transactions isn't enough to achieve trustlessness. Powerful transactions are required to achieve Bitcoin's goals.
Then there is the issue of betrayal by 1000 cuts, by expedience, and with all honest intentions. In classically human mediated systems there is a constant pressure to bend the rules a little bit: "Read this guys email, he's no good", "block this transaction, just this one, the funds are stolen. I swear!", "just print a bit more money, we need to fund a war, it's important.". We find it hard to say no to small compromises, even when the logical outcome, shown by history to be inevitable, is no mystery to us. Improved trustlessness is improved robustness to small betrayals adding up to big ones.
WRT SPV, it's only secure so long as there is an {adequate} supply of fully verifying nodes to keep miners honest. If there isn't then miners as a group (consider: even without an explicit conspiracy, they have some highly correlated interests…) can freely deceive SPV nodes. If there is, then for many use-cases SPV security is quite close to a full node (e.g. accepting confirmed payments with values small relative to the generated coins confirming them). SPV is what makes compact mobile devices viable, and it's also necessary for efficiently binding Bitcoin into other system. But its security absolutely depends on validation being very well distributed.