3 ms·

That is eye-opening but - if it actually works and I find it hard to believe - is no way ... ah, the insert is a CTE because it produces a value ('returning' I

That is eye-opening but - if it actually works and I find it hard to believe - is no way ... ah, the insert is a CTE because it produces a value ('returning' I guess). Hmm. This is very odd. Doesn't seem to work in mssql. Well thanks for the can of worms...

ltbarcly3 4y ago
mssql CTE support is very very basic, to the point of being not very useful.
- zasdffaa 4y ago
  What the hell are you talking about?
  - ltbarcly3 4y ago
    You can only select, you can't nest cte, the query planner has no understanding of joins that cross cte boundaries so they are totally unoptimized, you can't use distinct or group by or etcetc. Really the CTE implementation in SqlServer is basically a parser level hack.
    - zasdffaa 4y ago
      > You can only select with x as (...) update x set ... > you can't nest cte ok but you can linearise them with x as (...), y as (...) > the query planner has no understanding of joins that cross cte boundaries so they are totally unoptimized utter, reeking garbage. > you can't use distinct or group more garbage. I have. Show me an example of it not working. (Edited for less rudeness)
      - ltbarcly3 4y ago
        so you can do this? WITH t AS ( DELETE FROM foo ) DELETE FROM bar; Or can you only use SELECT for WITH queries? Did you not realize that other databases and the SQL standard allow you to do this? Yes on the final query of the CTE you can do all sorts of things, but that's way less useful if you can't do them in all the component queries.
        zasdffaa 4y ago
        Show me any sql dialect that will allow that CTE you give here (edit; and what on earth is that supposed to actually mean) > Or can you only use SELECT for WITH queries you can only use a select inside a cte (or should be able to) because the 'e' stands for 'expression'. It seems postgres does allow an insert with and output which sort of makes sense but I doubt it's in the standard. Your last sentence makes no sense to me. Give an example. Also you failed to give an example that group by/distiinct weren't allowed in ctes.

ltbarcly3 4y ago

mssql CTE support is very very basic, to the point of being not very useful.

zasdffaa 4y ago
What the hell are you talking about?
- ltbarcly3 4y ago
  You can only select, you can't nest cte, the query planner has no understanding of joins that cross cte boundaries so they are totally unoptimized, you can't use distinct or group by or etcetc. Really the CTE implementation in SqlServer is basically a parser level hack.
  - zasdffaa 4y ago
    > You can only select with x as (...) update x set ... > you can't nest cte ok but you can linearise them with x as (...), y as (...) > the query planner has no understanding of joins that cross cte boundaries so they are totally unoptimized utter, reeking garbage. > you can't use distinct or group more garbage. I have. Show me an example of it not working. (Edited for less rudeness)
    - ltbarcly3 4y ago
      so you can do this? WITH t AS ( DELETE FROM foo ) DELETE FROM bar; Or can you only use SELECT for WITH queries? Did you not realize that other databases and the SQL standard allow you to do this? Yes on the final query of the CTE you can do all sorts of things, but that's way less useful if you can't do them in all the component queries.
      - zasdffaa 4y ago
        Show me any sql dialect that will allow that CTE you give here (edit; and what on earth is that supposed to actually mean) > Or can you only use SELECT for WITH queries you can only use a select inside a cte (or should be able to) because the 'e' stands for 'expression'. It seems postgres does allow an insert with and output which sort of makes sense but I doubt it's in the standard. Your last sentence makes no sense to me. Give an example. Also you failed to give an example that group by/distiinct weren't allowed in ctes.