I went back to M8 which I hate doing but I don't have any drama there.
? 8 has way more "drama" than 9 when it comes to SQL. 9 is a far cleaner SQL. Learn 9 and use it and it will be much easier than 8 to get consistent, no surprises results.
But the way to learn 9 is to crank through the documentation for 9 and to work through the many SQL examples in that documentation. Trying to learn 9 by re-cycling 8 knowledge and only diving into the documentation when something doesn't work is a formula for unnecessary frustration.
If something doesn't work for you, launch a thread in the forum that provides full info exactly as you do it. SQL is talking to a computer, so details like typos can be a factor. The only way to find those is to paste the queries you are using into the thread exactly as you are using them.
I've had plenty of queries that don't work simply because I miscounted parentheses, forgot a matching [ or ] bracket, put a typo into a field name, forgot to change field names when copying and pasting snippets of SQL from one project into another, and so on Something as simple as WHERE [MyField] = 1 can be wrong if "MyField" is a text field and not a numeric field.
It also helps when learning to see what Manifold would write in a given situation, using the Edit Query button in the Transform or Select panes. It can also help to debug a query taking it step by step in the Command Window, copying and pasting parts of it to form a simpler query that you can then highlight and execute using ALT-ENTER.