Why Are Dot-Separated Prefixes Ignored in the Column List for Insert Statements

Why are dot-separated prefixes ignored in the column list for INSERT statements?

This was reported as a bug on Connect and despite initially encouraging comments the current status of the item is closed as "won't fix".

The Order by clause used to behave in a similar fashion but that one was fixed in SQL Server 2005.

INSERT INTO ... SELECT without detailing all columns

Either you list all of the fields you want in the insert...select, or you use something else externally to build the list for you.

SQL does not have something like SELECT * except somefield FROM, so you'll have to bite the bullet and write out the field names.

Why does COALESCE execute the subquery twice?

ISNULL() is associated with SQL Server, so this question appears to be about SQL Server. As pointed out in the comments, SQL Server runs the subquery twice.

By the way, this can be deterimental -- and not just with subqueries. Consider the following expression:

select coalesce(case when rand(checksum(newid())) < 0.5 then 'a' end, 'b')

It can return a NULL value -- despite the COALESCE(), because the first expression is evaluated twice. For fun, you can run this query:

select v.n, coalesce(case when rand(checksum(newid())) < 0.5 then 'a' end, 'b')
from (values (1), (2), (3), (4), (5), (6), (7), (8)) v(n);

I can speculate on several reasons why SQL Server would behave this way.

(1) Someone at Microsoft or Sybase (once upon a time) actually thought this was the right approach.

(2) Someone thought "we already have a function that does this, so COALESCE() should be a little different". Even if that "little difference" makes it look like it is broken.

(3) SQL Server does not optimize subqueries by running them only once (as far as I can tell). So for subqueries in particular the idea might have been: "we'll fix this in a later round of optimization".

This is all speculation (and hence opinion). I wanted to answer because this does not only affect subqueries.

SQL select join: is it possible to prefix all columns as 'prefix.*'?

I see two possible situations here. First, you want to know if there is a SQL standard for this, that you can use in general regardless of the database. No, there is not. Second, you want to know with regard to a specific dbms product. Then you need to identify it. But I imagine the most likely answer is that you'll get back something like "a.id, b.id" since that's how you'd need to identify the columns in your SQL expression. And the easiest way to find out what the default is, is just to submit such a query and see what you get back. If you want to specify what prefix comes before the dot, you can use "SELECT * FROM a AS my_alias", for instance.

SML Error: syntax error: inserting DOT

"syntax error: inserting DOT" means that there's an unexpected token at the position it's complaining about and that a dot would be legal at that position. However that does not mean that a dot would be the only thing allowed in that position or that replacing the token with a dot would fix your error.

For the most part the error message is useless beyond telling you that there's a syntax error at that position. You should probably just ignore the "inserting DOT" part as it generally doesn't lead you in the right direction. Just look at the line and column it's complaining about and try to find what the syntax error is there.

In your case the problem is that dir1=dir2 is not a legal pattern. A legal pattern would be the constructor of a datatype with patterns for each of its argument, a variable name, _ or a constant or a tuple of patterns. There is no pattern to say "a tuple that contains two elements that are equal". For that you need the pattern (dir1, dir2) and then the condition dir1=dir2 in an if.

In your case you don't even need an if though. You can just write:

(dir1, dir2) => dir1 = dir2 andalso same (f2 :: fs)

---

Related Topics