Difficult Query: is this possible in SQL?

[Richard Romley]

Re: Difficult Query: is this possible in SQL?

Erland,
[color=blue][color=green]
>>If the product does absolutely silly things, you don't have no choice<<[/color][/color]

I have in my hand a SQL Server 1.0 manual dated 1989. Across the top of the
front cover it says "Microsoft" (and Ashton-Tate <g>). If the product did
absolutely silly things Microsoft never should have put their name on it -
and then maintained the *faulty* logic for 10 years before deciding, all of
a sudden, that the behavior was suddenly unacceptable.

In any case, in addition to the *absolutely silly things* that it did, it
also did perfectly reasonable things. One small example - the original
PATINDEX function returned a 0 if a match was made beginning with the 1st
character of a string. Then somebody noticed that the CHARINDEX function
returned a 1 on a 1st character match. Thinking that it would be nice for
these 2 similar functions to work the same way, they simply changed the
PATINDEX function. It wasn't even a documented change. That was a long time
ago, but I still remember very well what happened to my code. Just a small
example of many - and has nothing to do with ANSI compliance.

A more recent example was a *feature* introduced in release 7. Somebody
decided that the ARITHABORT command was really only to be taken seriously
for SELECT statements and should be ignored for INSERT and UPDATE
statements. This was also (initially) an undocumented feature. This really
wasn't very nice for those of us who process million row inserts at night
which include arithmetic calculations on some of the columns. Microsoft
refused to fix this until SQL 2000. That one *feature* alone made 7.0 an
unusable product for us.
[color=blue][color=green]
>>the keyword is ANSI. Microsoft wanted their product to adhere more to ANSI[/color][/color]
standards<<

Erland, that is Microsoft's line and the excuse they use every time they
deliberately break something. The bottom line here is they are very
selective about WHICH ANSI rules they choose to implement and which they
conveniently ignore. If you don't believe it, call up your friends on the
development team and remind them that FROM clauses are illegal in UPDATE and
DELETE statements and they really shouldn't allow this non-ANSI behavior in
their product. Be sure to report back what they say.
[color=blue][color=green]
>>...when you port from other products...<<[/color][/color]

This is my favorite. How many products have stored procedures written in
TSQL? OK, SYBASE at least started out the same - but that was a long time
ago. When's the last time you tried to port a SYBASE stored procedure into
SQL Server? Oracle? Anybody? When's the last time you tried to port a SQL
Server stored procedure to any other database? I just love hearing about
portability!

Erland, there's nothing wrong with enhancements. Add all the features you
want but don't break my old code in the process. And don't tell me that
everything you do is to improve ANSI compliance. It's true sometimes, but
ONLY sometimes.

Microsoft simply has a terrible track record when it comes to backward
compatibility. I've been burned so many times you'll never convince me
otherwise.

In any case, what started this whole discussion was a query example that
only worked with defaults configured a particular way. I maintain that if
you code that way you will eventually be punished by Microsoft for trusting
them. It happens with almost every new release.

Richard


"Erland Sommarskog" <esquel@sommars kog.se> wrote in message
news:Xns951FF02 0B5D10Yazorman@ 127.0.0.1...[color=blue]
> Richard Romley (richardromley@ optonline.net) writes:[color=green]
> > Hmmm. Where have I heard that before? There is ALWAYS a good reason to
> > change defaults. There is ALWAYS a good reason to change the behavior of
> > existing code. Well, there is also one very good reason NOT to - doing[/color][/color]
so[color=blue][color=green]
> > breaks all the code that's been written since the beginning of time that
> > depended on the old rules.[/color]
>
> If the product does absolutely silly things, you don't have no choice. For
> instance in SQL Server 4.x you could say:
>
> SELECT a, b, COUNT(*)
> FROM tbl
> GROUP BY a
>
> Both Sybase and Microsoft outlawed this syntax in System 10 and SQL
> Server 6 respectively.
>
> For the issues we discuss, the keyword is ANSI. Microsoft wanted their
> product to adhere more to ANSI standards, and this certainly applies
> to existing code, to wit when you port from other products. However,
> MS did not go all the way. If you say SET ANSI_DEFAULTS ON, there are
> a couple of settings which are set, and I will add ARITHABORT and
> CONCAT_NULL_YIE LDS_NULL to these.
>
> ANSI_NULL_DLFT_ ON - this setting controls what happens if you create
> a column in a table without specify explicit nullability. Since none
> of NOT NULL or NULL is obvious, good practice is always state this
> explicitly.
>
> ANSI_PADDING - this column affects how trailing spaces are saved for
> varchar values. It is not likely to affect that many appliactions.
>
> ANSI_WARNINGS - the most important effect is that you get an error if
> you try to store a value that does not fir into a char/binary column.
> It does affect some application, but there is no dramatic issue. The
> other things caused by ANSI_WARNINGS is likely to have even lower impact.
>
> ARITHABORT - Few would probably complain that their division with zero
> yields an error (this also comes with ANSI_WARNINGS). Most probably
> find it a good thing.
>
> QUOTED_IDENTIFI ER - Now, here is one that with a huge impact, since
> strings quoted by " suddenly became identifiers. Mitigating, though, is
> that this can be fixed mechanically.
>
> ANSI_NULLS - This is an setting that should have absolutely no effect on
> properly written code. @x = NULL was wrong in 4.x days, it has always
> been wrong, it was just that Sybase out of folly handled NULL as equal
> to NULL. But of course, a system where the programmers did not have
> understanding of NULL values takes a serious toll here. But as I said
> the Best Practice Analyzer can help you out here.
>
> CONCAT_NULL_YIE LDS_NULL - Exactly the same thing applies here: on a
> properly implemented system, this is not an issue.
>
> ............... .....
>
> IMPLICIT_TRANSA CTIONS - Now here is one! This option is OFF by default,
> and had MS made this the default, about every application out there would
> have succumbed. Right or wrong, auto-commit has always been the way Sybase
> and SQL Server has gone.
>
> CURSOR_CLOSE_ON _COMMIT - This option is OFF by default as well. Since
> cursors are something you should not use anyway, it should have less
> impact. But the whole idea sounds corny to me. I might have a transaction
> for each iteration in the cursor. Certainly I don't want it close.
>
> So Microsoft acted judiciously when they decided what should be on by
> default and what should not. ANSI_NULLS and CONCAT_NULL_YIE LDS_NULL
> may have a great impact your code, but let me again stress that the
> original defaults were just plain wrong and indefensible to have in
> an engine to aspire to be an enterprise DBMS.
>[color=green]
> > Microsoft has proven time and time again that they simply don't give a
> > damn about the harm they inflict on their customers every time they do
> > that.[/color]
>
> Since I have good contacts with the SQL Server team, I can assure you
> that they are very anxious about compability. Very anxious.
>
> But if you have ever worked with product development, you also know that
> sometimes maintaining old functionality can be a true burden. (If you
> work for a site where you only have one production environment, this is
> not an issue for you. You can rip things out as you please.)
>
>
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se
>
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techinf...2000/books.asp[/color]

[Erland Sommarskog]

Re: Difficult Query: is this possible in SQL?

Richard Romley (rromley@optonl ine.net) writes:[color=blue]
> I have in my hand a SQL Server 1.0 manual dated 1989. Across the top of
> the front cover it says "Microsoft" (and Ashton-Tate <g>). If the
> product did absolutely silly things Microsoft never should have put
> their name on it - and then maintained the *faulty* logic for 10 years
> before deciding, all of a sudden, that the behavior was suddenly
> unacceptable.[/color]

You seem to think that Microsoft is some being in itself. It isn't.
Microsoft consists of people. And Microsoft 1989 was a very different
company from Microsoft 2004.

In the 1980s Microsoft did not have the resources to develop a DB
engine themselves, so they hooked up an available partner. That was
Sybase (and Ashton-Tate). I don't know what alternatives they had, but
I doubt that for instance Oracle was among them. The choice could not
be made technical qualifications only.

Also, you should keep in mind that there was less of standards in SQL
than there is today. SQL itself was not universal as a query language.
In 1989 my DBMS was DEC/Rdb and the query language was RDO.

Let me also remind you that it is extremely unfair to slam the SQL
Server team for events of 1989, as all MS did in those days was to
port to OS/2, and had very little influence of the features. There
wasn't any SQL Server team of today in 1989.
[color=blue]
> In any case, in addition to the *absolutely silly things* that it did, it
> also did perfectly reasonable things. One small example - the original
> PATINDEX function returned a 0 if a match was made beginning with the 1st
> character of a string. Then somebody noticed that the CHARINDEX function
> returned a 1 on a 1st character match. Thinking that it would be nice for
> these 2 similar functions to work the same way, they simply changed the
> PATINDEX function.[/color]

Are you sure that Microsoft did that change? I checked some online manuals
I have of System 10, which says this about patindex:

Returns an integer representing the starting position of the first
occurrence of pattern in the specified character expression, or a
zero if pattern is not found. By default, patindex returns the offset
in characters; to return the offset in bytes (multibyte character
strings), specify using bytes.

The text is a bit confused, since it talks about both starting position
and offset. The text for 12.5 is on

http://manuals.sybase.com:80/onlinebooks/group-as/asg1250e/refman/@Generic__BookView

also mixes offset and starting position, but example 4 makes it clear
that the return value is > 0 for a match in position 1.

So if there was a change, maybe you should blame Sybase for it.
[color=blue]
> A more recent example was a *feature* introduced in release 7. Somebody
> decided that the ARITHABORT command was really only to be taken seriously
> for SELECT statements and should be ignored for INSERT and UPDATE
> statements. This was also (initially) an undocumented feature. This really
> wasn't very nice for those of us who process million row inserts at night
> which include arithmetic calculations on some of the columns. Microsoft
> refused to fix this until SQL 2000. That one *feature* alone made 7.0 an
> unusable product for us.[/color]

I'm sorry, but you are wrong. I ran this repro on SQL7 SP2 and SQL 6.5 SP5:

CREATE TABLE yxa (a int NOT NULL,
b smallint NOT NULL)
go
SET ARITHABORT ON
SET ANSI_WARNINGS OFF -- Or ON
go
INSERT yxa (a, b) VALUES (100000, 234)
INSERT yxa (a, b) SELECT b, a FROM yxa
INSERT yxa (a, b) VALUES (1, 251)
go
INSERT yxa (a, b) VALUES (100000, 0)
INSERT yxa (a, b) SELECT a/b, 9 FROM yxa
INSERT yxa (a, b) VALUES (1, 251)
go
SELECT * FROM yxa
go
DROP TABLE yxa

The output was identical. Maybe it did not work in SQL7 RTM, which I
assum it was a bug. Bugs happens in the best families.
[color=blue][color=green][color=darkred]
>>>the keyword is ANSI. Microsoft wanted their product to adhere more to > > ANSI standards<<[/color][/color]
>
> Erland, that is Microsoft's line and the excuse they use every time they
> deliberately break something. The bottom line here is they are very
> selective about WHICH ANSI rules they choose to implement and which they
> conveniently ignore. If you don't believe it, call up your friends on
> the development team and remind them that FROM clauses are illegal in
> UPDATE and DELETE statements and they really shouldn't allow this
> non-ANSI behavior in their product. Be sure to report back what they
> say.[/color]

Sigh. I posted a long list of ANSI options with a through discussion of
which are now the defaults (if you connect with ODBC or OLE DB), and
pointed out that some of the ANSI settings are ignored. For very good
reasons. You don't have to tell me that they are selective. Of course
they are keeping the good features like FROM for UPDATE.

But what you don't seem to get is that ANSI_NULLS and
CONCAT_NULLS_YI ELDS_NULL are about fundamental things in a relational
database. They *had* to change it.

But, note that the change was made in a way that it did not affect
[color=blue]
> This is my favorite. How many products have stored procedures written in
> TSQL?[/color]

Many applications have SQL sent from the client. Not everything is
stored procedures.
[color=blue]
> When's the last time you tried to port a SQL Server stored procedure to
> any other database? I just love hearing about portability![/color]

For some people, like me, portability is a non-issue. For other people
it's extremely important. If you can mandate which customer can use, you
can ignore being portable. If the customer decides the engine, you
have to be portable.

And if you think I'm just driveling, think SAP and other big apps. If
you can get SAP to run on your engine, you have increased your income
potential significantly.
[color=blue]
> Microsoft simply has a terrible track record when it comes to backward
> compatibility. I've been burned so many times you'll never convince me
> otherwise.[/color]

As I said, I know that the SQL Server team is very anxious about
backward compatibility. Sometimes, yes, they do make changes which
breaks existing code, but often then because this code relied on
behaviour that never was correct. And in such cases, you can get
back the old behaviour by setting the compatibility level for the
database to lower than one of the current product.

Oh, probably not for optimizer changes. A change in the optimizer can
be of benefit to many, but a disaster to some. Then again, that is
not unique to SQL Server, but to about any DBMS product.
[color=blue]
> In any case, what started this whole discussion was a query example that
> only worked with defaults configured a particular way.[/color]

Not any particular random way, but *the* way a proper DBMS should
behave. You seem to be missing this all the time.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Richard Romley]

Re: Difficult Query: is this possible in SQL?

>>You seem to think that Microsoft is some being in itself. It isn't.
Microsoft consists of people. And Microsoft 1989 was a very different
company from Microsoft 2004.<<

I'm sorry Erland. I completely disagree. The way the company treats its
customers is dictated by senior management and, by observation, it has never
changed. It makes no difference who the employees are at any given time. You
would never tolerate an explanation like that from your phone company, auto
manufacturer, or anyone else. We've been conditioned to tolerate grief from
software that we would never accept from anyplace else. I'm sure you've seen
this... http://www.vbrad.com/Misc/hum_ms_cars.htm
[color=blue][color=green]
>>...it is extremely unfair to slam the SQL Server team for events of[/color][/color]
1989...<<
You're right, it is. Because of the time frame it was a bad example. It just
happened to cause me an enormous amount of grief so I remember it well. If
it were an isolated event it wouldn't be worth discussing. Unfortunately it
isn't.
[color=blue][color=green]
>>Are you sure that Microsoft did that change?<<[/color][/color]
I have no way of knowing who was controlling the software when the decision
was made to make that change. I can assure you it happened. And I clearly
remember that the change wasn't even mentioned in the release notes. But
you're right - this particular incident happened a long time ago.
[color=blue][color=green]
>>Somebody decided that the ARITHABORT command was really only to be taken[/color][/color]
seriously for SELECT statements and should be ignored for INSERT and UPDATE
statements.<<
[color=blue][color=green]
>> I'm sorry, but you are wrong.<<[/color][/color]

Sorry for the confusion Erland, but I am NOT wrong. You have the condition
backward in your test. The problem is that "set arithabort OFF" is ignored
for INSERT and UPDATE statements. It's only respected for SELECT statements.

Run this script and you will see. This works fine in all releases of SQL
server EXCEPT 7. To the best of my knowledge Microsoft never publicly
acknowledged this. We had many meetings over it. At first they denied it.
They then acknowledged it and agreed to fix it - then changed their mind and
refused to fix it until SQL 2000 - which is what happened.


CREATE TABLE yxa
(
a int NULL ,
b smallint NULL
)
go
SET ARITHABORT OFF
SET ARITHIGNORE ON
go
INSERT yxa (a, b) VALUES (100000, 234)
INSERT yxa (a, b) SELECT b, a FROM yxa
INSERT yxa (a, b) VALUES (1, 251)
go
INSERT yxa (a, b) VALUES (100000, 0)
INSERT yxa (a, b) SELECT a/b, 9 FROM yxa
INSERT yxa (a, b) VALUES (1, 251)
go
SELECT * FROM yxa
go
DROP TABLE yxa
go

There were many people trying to figure out what they were doing wrong and
trying to work around this. There WAS no practical work-around.

Here are a few newsgroup posts I just found...

http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&selm=36E800FA.A7E04598%40decath.com

http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&selm=381a295d.0%40news.tesser.com

http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&selm=ezc4dYYv%23GA.283%40cppssbbsa03

What always bugged me about this is that I just can't believe that it wasn't
a simple fix to correct this. Somebody, for whatever reason, added this test
and they simply refused to correct it. Given that we are one of Microsoft's
larger SQL Server customers (275,000 employees), it was simply astonishing
to observe Microsoft's arrogance. When the PATINDEX problem occurred I was
working for a company with about 20 employees. At the time I assumed that
was the reason for the lack of cooperation. I have since learned it doesn't
matter who you are.
[color=blue][color=green]
>>Sigh. I posted a long list of ANSI options...<<[/color][/color]
You did - and it wasn't necessary. I can assure you I'm familar with them.
I'm sorry but last night's post was made at around 1:00AM and my alarm goes
off at 5:30. I didn't have the strength - nor did I think it was necessary -
to go through the list.
[color=blue][color=green]
>>...some of the ANSI settings are ignored. For very good reasons...<<[/color][/color]

Ah! We're making progress! All this time I thought the ANSI spec was the
Bible - to work toward at all costs. Now it turns out that parts of the spec
really aren't very good after all - so we'll just ignore the *bad* parts! I
wonder what the folks who wrote the spec would think about that.

Erland, what is this instruction supposed to do if col1 is NOT the primary
key of table b?

update a
set a.col2 = b.col2
from a,b
where a.col1 = b.col1

Do you think this instruction makes sense...

select col1
from tbl
order by col2


How about CASE statements in GROUP BY clauses? in ORDER BY clauses?

This stuff all works in SQL Server. People write code like this every day.
Will it work in the next release of SQL Server? Who knows? None of it is
ANSI compliant. It all depends on whether somebody in development thinks
these are *good* rules or *bad* rules (now that we've established that both
exist!). What if the guy who makes this decision leaves the company and is
replaced with somebody who disagrees with him. Don't you see - you just
can't keep changing the rules and breaking people's code while quoting ANSI
compliance - not when you are deliberately ignoring the parts you don't
like. It is exactly that kind of thinking that causes all the resentment.
Think about it.

Richard



"Erland Sommarskog" <esquel@sommars kog.se> wrote in message
news:Xns9520F41 CEB2FDYazorman@ 127.0.0.1...[color=blue]
> Richard Romley (rromley@optonl ine.net) writes:[color=green]
> > I have in my hand a SQL Server 1.0 manual dated 1989. Across the top of
> > the front cover it says "Microsoft" (and Ashton-Tate <g>). If the
> > product did absolutely silly things Microsoft never should have put
> > their name on it - and then maintained the *faulty* logic for 10 years
> > before deciding, all of a sudden, that the behavior was suddenly
> > unacceptable.[/color]
>
> You seem to think that Microsoft is some being in itself. It isn't.
> Microsoft consists of people. And Microsoft 1989 was a very different
> company from Microsoft 2004.
>
> In the 1980s Microsoft did not have the resources to develop a DB
> engine themselves, so they hooked up an available partner. That was
> Sybase (and Ashton-Tate). I don't know what alternatives they had, but
> I doubt that for instance Oracle was among them. The choice could not
> be made technical qualifications only.
>
> Also, you should keep in mind that there was less of standards in SQL
> than there is today. SQL itself was not universal as a query language.
> In 1989 my DBMS was DEC/Rdb and the query language was RDO.
>
> Let me also remind you that it is extremely unfair to slam the SQL
> Server team for events of 1989, as all MS did in those days was to
> port to OS/2, and had very little influence of the features. There
> wasn't any SQL Server team of today in 1989.
>[color=green]
> > In any case, in addition to the *absolutely silly things* that it did,[/color][/color]
it[color=blue][color=green]
> > also did perfectly reasonable things. One small example - the original
> > PATINDEX function returned a 0 if a match was made beginning with the[/color][/color]
1st[color=blue][color=green]
> > character of a string. Then somebody noticed that the CHARINDEX function
> > returned a 1 on a 1st character match. Thinking that it would be nice[/color][/color]
for[color=blue][color=green]
> > these 2 similar functions to work the same way, they simply changed the
> > PATINDEX function.[/color]
>
> Are you sure that Microsoft did that change? I checked some online manuals
> I have of System 10, which says this about patindex:
>
> Returns an integer representing the starting position of the first
> occurrence of pattern in the specified character expression, or a
> zero if pattern is not found. By default, patindex returns the offset
> in characters; to return the offset in bytes (multibyte character
> strings), specify using bytes.
>
> The text is a bit confused, since it talks about both starting position
> and offset. The text for 12.5 is on
>[/color]
http://manuals.sybase.com:80/onlineb...eric__BookView[color=blue]
> also mixes offset and starting position, but example 4 makes it clear
> that the return value is > 0 for a match in position 1.
>
> So if there was a change, maybe you should blame Sybase for it.
>[color=green]
> > A more recent example was a *feature* introduced in release 7. Somebody
> > decided that the ARITHABORT command was really only to be taken[/color][/color]
seriously[color=blue][color=green]
> > for SELECT statements and should be ignored for INSERT and UPDATE
> > statements. This was also (initially) an undocumented feature. This[/color][/color]
really[color=blue][color=green]
> > wasn't very nice for those of us who process million row inserts at[/color][/color]
night[color=blue][color=green]
> > which include arithmetic calculations on some of the columns. Microsoft
> > refused to fix this until SQL 2000. That one *feature* alone made 7.0 an
> > unusable product for us.[/color]
>
> I'm sorry, but you are wrong. I ran this repro on SQL7 SP2 and SQL 6.5[/color]
SP5:[color=blue]
>
> CREATE TABLE yxa (a int NOT NULL,
> b smallint NOT NULL)
> go
> SET ARITHABORT ON
> SET ANSI_WARNINGS OFF -- Or ON
> go
> INSERT yxa (a, b) VALUES (100000, 234)
> INSERT yxa (a, b) SELECT b, a FROM yxa
> INSERT yxa (a, b) VALUES (1, 251)
> go
> INSERT yxa (a, b) VALUES (100000, 0)
> INSERT yxa (a, b) SELECT a/b, 9 FROM yxa
> INSERT yxa (a, b) VALUES (1, 251)
> go
> SELECT * FROM yxa
> go
> DROP TABLE yxa
>
> The output was identical. Maybe it did not work in SQL7 RTM, which I
> assum it was a bug. Bugs happens in the best families.
>[color=green][color=darkred]
> >>>the keyword is ANSI. Microsoft wanted their product to adhere more to >[/color][/color]
> ANSI standards<<[color=green]
> >
> > Erland, that is Microsoft's line and the excuse they use every time they
> > deliberately break something. The bottom line here is they are very
> > selective about WHICH ANSI rules they choose to implement and which they
> > conveniently ignore. If you don't believe it, call up your friends on
> > the development team and remind them that FROM clauses are illegal in
> > UPDATE and DELETE statements and they really shouldn't allow this
> > non-ANSI behavior in their product. Be sure to report back what they
> > say.[/color]
>
> Sigh. I posted a long list of ANSI options with a through discussion of
> which are now the defaults (if you connect with ODBC or OLE DB), and
> pointed out that some of the ANSI settings are ignored. For very good
> reasons. You don't have to tell me that they are selective. Of course
> they are keeping the good features like FROM for UPDATE.
>
> But what you don't seem to get is that ANSI_NULLS and
> CONCAT_NULLS_YI ELDS_NULL are about fundamental things in a relational
> database. They *had* to change it.
>
> But, note that the change was made in a way that it did not affect
>[color=green]
> > This is my favorite. How many products have stored procedures written in
> > TSQL?[/color]
>
> Many applications have SQL sent from the client. Not everything is
> stored procedures.
>[color=green]
> > When's the last time you tried to port a SQL Server stored procedure to
> > any other database? I just love hearing about portability![/color]
>
> For some people, like me, portability is a non-issue. For other people
> it's extremely important. If you can mandate which customer can use, you
> can ignore being portable. If the customer decides the engine, you
> have to be portable.
>
> And if you think I'm just driveling, think SAP and other big apps. If
> you can get SAP to run on your engine, you have increased your income
> potential significantly.
>[color=green]
> > Microsoft simply has a terrible track record when it comes to backward
> > compatibility. I've been burned so many times you'll never convince me
> > otherwise.[/color]
>
> As I said, I know that the SQL Server team is very anxious about
> backward compatibility. Sometimes, yes, they do make changes which
> breaks existing code, but often then because this code relied on
> behaviour that never was correct. And in such cases, you can get
> back the old behaviour by setting the compatibility level for the
> database to lower than one of the current product.
>
> Oh, probably not for optimizer changes. A change in the optimizer can
> be of benefit to many, but a disaster to some. Then again, that is
> not unique to SQL Server, but to about any DBMS product.
>[color=green]
> > In any case, what started this whole discussion was a query example that
> > only worked with defaults configured a particular way.[/color]
>
> Not any particular random way, but *the* way a proper DBMS should
> behave. You seem to be missing this all the time.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se
>
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techinf...2000/books.asp[/color]

[Richard Romley]

Re: Difficult Query: is this possible in SQL?

>>You seem to think that Microsoft is some being in itself. It isn't.
Microsoft consists of people. And Microsoft 1989 was a very different
company from Microsoft 2004.<<

I'm sorry Erland. I completely disagree. The way the company treats its
customers is dictated by senior management and, by observation, it has never
changed. It makes no difference who the employees are at any given time. You
would never tolerate an explanation like that from your phone company, auto
manufacturer, or anyone else. We've been conditioned to tolerate grief from
software that we would never accept from anyplace else. I'm sure you've seen
this... http://www.vbrad.com/Misc/hum_ms_cars.htm
[color=blue][color=green]
>>...it is extremely unfair to slam the SQL Server team for events of[/color][/color]
1989...<<
You're right, it is. Because of the time frame it was a bad example. It just
happened to cause me an enormous amount of grief so I remember it well. If
it were an isolated event it wouldn't be worth discussing. Unfortunately it
isn't.
[color=blue][color=green]
>>Are you sure that Microsoft did that change?<<[/color][/color]
I have no way of knowing who was controlling the software when the decision
was made to make that change. I can assure you it happened. And I clearly
remember that the change wasn't even mentioned in the release notes. But
you're right - this particular incident happened a long time ago.
[color=blue][color=green]
>>Somebody decided that the ARITHABORT command was really only to be taken[/color][/color]
seriously for SELECT statements and should be ignored for INSERT and UPDATE
statements.<<
[color=blue][color=green]
>> I'm sorry, but you are wrong.<<[/color][/color]

Sorry for the confusion Erland, but I am NOT wrong. You have the condition
backward in your test. The problem is that "set arithabort OFF" is ignored
for INSERT and UPDATE statements. It's only respected for SELECT statements.

Run this script and you will see. This works fine in all releases of SQL
server EXCEPT 7. To the best of my knowledge Microsoft never publicly
acknowledged this. We had many meetings over it. At first they denied it.
They then acknowledged it and agreed to fix it - then changed their mind and
refused to fix it until SQL 2000 - which is what happened.


CREATE TABLE yxa
(
a int NULL ,
b smallint NULL
)
go
SET ARITHABORT OFF
SET ARITHIGNORE ON
go
INSERT yxa (a, b) VALUES (100000, 234)
INSERT yxa (a, b) SELECT b, a FROM yxa
INSERT yxa (a, b) VALUES (1, 251)
go
INSERT yxa (a, b) VALUES (100000, 0)
INSERT yxa (a, b) SELECT a/b, 9 FROM yxa
INSERT yxa (a, b) VALUES (1, 251)
go
SELECT * FROM yxa
go
DROP TABLE yxa
go

There were many people trying to figure out what they were doing wrong and
trying to work around this. There WAS no practical work-around.

Here are a few newsgroup posts I just found...

http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&selm=36E800FA.A7E04598%40decath.com

http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&selm=381a295d.0%40news.tesser.com

http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&selm=ezc4dYYv%23GA.283%40cppssbbsa03

What always bugged me about this is that I just can't believe that it wasn't
a simple fix to correct this. Somebody, for whatever reason, added this test
and they simply refused to correct it. Given that we are one of Microsoft's
larger SQL Server customers (275,000 employees), it was simply astonishing
to observe Microsoft's arrogance. When the PATINDEX problem occurred I was
working for a company with about 20 employees. At the time I assumed that
was the reason for the lack of cooperation. I have since learned it doesn't
matter who you are.
[color=blue][color=green]
>>Sigh. I posted a long list of ANSI options...<<[/color][/color]
You did - and it wasn't necessary. I can assure you I'm familar with them.
I'm sorry but last night's post was made at around 1:00AM and my alarm goes
off at 5:30. I didn't have the strength - nor did I think it was necessary -
to go through the list.
[color=blue][color=green]
>>...some of the ANSI settings are ignored. For very good reasons...<<[/color][/color]

Ah! We're making progress! All this time I thought the ANSI spec was the
Bible - to work toward at all costs. Now it turns out that parts of the spec
really aren't very good after all - so we'll just ignore the *bad* parts! I
wonder what the folks who wrote the spec would think about that.

Erland, what is this instruction supposed to do if col1 is NOT the primary
key of table b?

update a
set a.col2 = b.col2
from a,b
where a.col1 = b.col1

Do you think this instruction makes sense...

select col1
from tbl
order by col2


How about CASE statements in GROUP BY clauses? in ORDER BY clauses?

This stuff all works in SQL Server. People write code like this every day.
Will it work in the next release of SQL Server? Who knows? None of it is
ANSI compliant. It all depends on whether somebody in development thinks
these are *good* rules or *bad* rules (now that we've established that both
exist!). What if the guy who makes this decision leaves the company and is
replaced with somebody who disagrees with him. Don't you see - you just
can't keep changing the rules and breaking people's code while quoting ANSI
compliance - not when you are deliberately ignoring the parts you don't
like. It is exactly that kind of thinking that causes all the resentment.
Think about it.

Richard

[Richard Romley]

Re: Difficult Query: is this possible in SQL?

Oops. Sorry for the double post. I got an error the first time saying it had
failed.

[Erland Sommarskog]

Re: Difficult Query: is this possible in SQL?

Richard Romley (rromley@optonl ine.net) writes:[color=blue]
> I'm sorry Erland. I completely disagree. The way the company treats its
> customers is dictated by senior management and, by observation, it has
> never changed.[/color]

Or it is the observer that reufeses to change. It's is apparent from
the discussion that you have an opinion, and you are not going to change,
even when proven wrong.

Ever heard of sp_dbcmptlevel? If Microsoft did not care about backward
compatibility, why did they put this in? I have even heard it being
considered in one case to keep incorrectly behaviour in compatibility
level 80 for next version "in case some customers would depend on it".

Tell me, have you ever worked with product development yourself, and
ever had to consider compatibility issues? In that case you would
know that maintaining 100% compatibility in a complex system is about
impossible. If nothing else, it is extremely expenseive, and even at
Microsoft there is a limit of resources.
[color=blue]
> I have no way of knowing who was controlling the software when the
> decision was made to make that change.[/color]

Up to 4.x Sybase made all the decisions and Microsoft's influence was
very limited. Kalen Delaneys "Inside SQL Server 2000" includes a chapter
on the history of SQL Server which describes this fairly well. (And even
in 6.0 Sybase shines through. Some constructs that Sybase added in
System 10, made it to 6.0. Presumably they got those with the code
from Sybase.)
[color=blue]
> Run this script and you will see. This works fine in all releases of SQL
> server EXCEPT 7. To the best of my knowledge Microsoft never publicly
> acknowledged this. We had many meetings over it. At first they denied it.
> They then acknowledged it and agreed to fix it - then changed their mind
> and refused to fix it until SQL 2000 - which is what happened.[/color]

I have run your script on SQL 6.5 SP2, SQL7 SP2, SQL 2000 SP3 and
SQL Express 2005, and the behaviour was exactly the same on all
versions. However, I had to add SET ANSI_WARNINGS OFF to prevent
errors from being raised. Again, this applied to all the versions above.

But it is true that it happens that bugs found are deferred to the
next major release. Why? Again, if you have experience of product
development, you would know. There is a trade-off between the impact
of the bug, available workarounds, and the risks involved with the bug.
It's no good fixing a minor bug and introduce a major one.

Personally, for this particular case, I have never understood the point
with ARITHIGNORE - or ARITHABORT OFF for that matter. I would never
accept that a system that was involved with to run with ARITHIGNORE on.
[color=blue]
> http://groups.google.com/groups?hl=e...8%40decath.com[/color]

I tried this repro too. Same result on all versions I tried with. But
the post was from March 1999, so this obviously related to the RTM
version. SQL 7 RTM had some vile bugs, but then it was also a huge
rewrite from SQL 6.5.
[color=blue]
> What always bugged me about this is that I just can't believe that it wasn't
> a simple fix to correct this. Somebody, for whatever reason, added this test
> and they simply refused to correct it.[/color]

It may seem simple to you, but it could well be have been a bug that
was due to a combination of factors, and it was not obvious where to
change. And keep in mind, as I said, that SQL7 was a huge rewrite, so
it is not likley that we are not talking some few lines of code someone
added for fun.
[color=blue][color=green][color=darkred]
>>>Sigh. I posted a long list of ANSI options...<<[/color][/color]
> You did - and it wasn't necessary. I can assure you I'm familar with them.
>
> Ah! We're making progress! All this time I thought the ANSI spec was the
> Bible - to work toward at all costs.[/color]

Maybe you should have gone through my list of ANSI options anyway. It
would have been clear already to you it never was a talk of a bible, and
you could have spared us your sarcasms.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Richard Romley]

Re: Difficult Query: is this possible in SQL?

>>It's is apparent from the discussion that you have an opinion, and you are
not going to change, even when proven wrong.<<

That's pretty presumptuous on your part, don't you think? I can make exactly
the same argument about you. Let me ask you something: When you're involved
in a debate with someone who *refuses* to see your point of view, why is it
the OTHER guy who is being stubborn?

[color=blue][color=green]
>>Tell me, have you ever worked with product development yourself...<[/color][/color]

Since you've asked several times I will answer your question: Yes, I've
worked in product development for almost 40 years. In the old days I was
involved in flight simulation. I designed and built the first digital
simulator for a commercial inertial navigation system. I designed and built
the VAMP (variable anamorphic motion picture) visual system used (at that
time) for flight training on many of the world's airlines. In fact I have
the fondest memories of the time I spend at the Bromma Flygplats during the
SAS installation. (I wonder if SAS flight training is still there?) So the
next time you fly you can take comfort in the fact that your flight crew
might very well have trained on equipment I designed and built. In recent
years I have been working for the largest financial services company in the
world involved in the design, development, and maintenance of the system
that supports all of its retail branches. So, yes, I have worked with
product development. How about you?
[color=blue][color=green]
>>I have run your script on SQL 6.5 SP2, SQL7 SP2, SQL 2000 SP3 and SQL[/color][/color]
Express 2005, and the behaviour was exactly the same on all versions.<<

Erland, I find that absolutely amazing. I wonder when they fixed it.
Microsoft was adamant that it would never be fixed in SQL 7. That cost them
a lot of money. Nobody ever informed us that it had been fixed.

[color=blue][color=green]
>>I would never accept that a system that was involved with to run with[/color][/color]
ARITHIGNORE on.<<

You are certainly entitled to your opinion. It is way beyond the scope of
this discussion to go into the issues involved. But I'd be careful with my
use of the word *never.* It makes you sound inflexible - kind of like you're
accusing me of being.
[color=blue][color=green]
>>...you could have spared us your sarcasms<<[/color][/color]

I'm really sorry you feel that way. I was trying to point out the hypocrisy
of Microsoft's position - and the examples I gave spoke to the heart of the
issue. Erland, why is it that if I choose to ignore an ANSI rule I am guilty
of bad design - but when I give examples of Microsoft doing it I am being
sarcastic? I don't understand. The ENTIRE POINT OF THE DISCUSSION is that
SQL Server is LOADED with ANSI violations that won't be removed because
doing so would break the code of virtually the entire customer base. Don't
you then find it a bit disingenuous of Microsoft when they DO change
something that breaks existing code to quote ANSI compliance as
justification for doing so? I am truly sorry (and disappointed) if you find
that to be sarcastic.

I am afraid we are going to have to agree to disagree.



"Erland Sommarskog" <esquel@sommars kog.se> wrote in message
news:Xns9522326 61C30Yazorman@1 27.0.0.1...[color=blue]
> Richard Romley (rromley@optonl ine.net) writes:[color=green]
> > I'm sorry Erland. I completely disagree. The way the company treats its
> > customers is dictated by senior management and, by observation, it has
> > never changed.[/color]
>
> Or it is the observer that reufeses to change. It's is apparent from
> the discussion that you have an opinion, and you are not going to change,
> even when proven wrong.
>
> Ever heard of sp_dbcmptlevel? If Microsoft did not care about backward
> compatibility, why did they put this in? I have even heard it being
> considered in one case to keep incorrectly behaviour in compatibility
> level 80 for next version "in case some customers would depend on it".
>
> Tell me, have you ever worked with product development yourself, and
> ever had to consider compatibility issues? In that case you would
> know that maintaining 100% compatibility in a complex system is about
> impossible. If nothing else, it is extremely expenseive, and even at
> Microsoft there is a limit of resources.
>[color=green]
> > I have no way of knowing who was controlling the software when the
> > decision was made to make that change.[/color]
>
> Up to 4.x Sybase made all the decisions and Microsoft's influence was
> very limited. Kalen Delaneys "Inside SQL Server 2000" includes a chapter
> on the history of SQL Server which describes this fairly well. (And even
> in 6.0 Sybase shines through. Some constructs that Sybase added in
> System 10, made it to 6.0. Presumably they got those with the code
> from Sybase.)
>[color=green]
> > Run this script and you will see. This works fine in all releases of SQL
> > server EXCEPT 7. To the best of my knowledge Microsoft never publicly
> > acknowledged this. We had many meetings over it. At first they denied[/color][/color]
it.[color=blue][color=green]
> > They then acknowledged it and agreed to fix it - then changed their mind
> > and refused to fix it until SQL 2000 - which is what happened.[/color]
>
> I have run your script on SQL 6.5 SP2, SQL7 SP2, SQL 2000 SP3 and
> SQL Express 2005, and the behaviour was exactly the same on all
> versions. However, I had to add SET ANSI_WARNINGS OFF to prevent
> errors from being raised. Again, this applied to all the versions above.
>
> But it is true that it happens that bugs found are deferred to the
> next major release. Why? Again, if you have experience of product
> development, you would know. There is a trade-off between the impact
> of the bug, available workarounds, and the risks involved with the bug.
> It's no good fixing a minor bug and introduce a major one.
>
> Personally, for this particular case, I have never understood the point
> with ARITHIGNORE - or ARITHABORT OFF for that matter. I would never
> accept that a system that was involved with to run with ARITHIGNORE on.
>[color=green]
> >[/color][/color]
http://groups.google.com/groups?hl=e...8%40decath.com[color=blue]
>
> I tried this repro too. Same result on all versions I tried with. But
> the post was from March 1999, so this obviously related to the RTM
> version. SQL 7 RTM had some vile bugs, but then it was also a huge
> rewrite from SQL 6.5.
>[color=green]
> > What always bugged me about this is that I just can't believe that it[/color][/color]
wasn't[color=blue][color=green]
> > a simple fix to correct this. Somebody, for whatever reason, added this[/color][/color]
test[color=blue][color=green]
> > and they simply refused to correct it.[/color]
>
> It may seem simple to you, but it could well be have been a bug that
> was due to a combination of factors, and it was not obvious where to
> change. And keep in mind, as I said, that SQL7 was a huge rewrite, so
> it is not likley that we are not talking some few lines of code someone
> added for fun.
>[color=green][color=darkred]
> >>>Sigh. I posted a long list of ANSI options...<<[/color]
> > You did - and it wasn't necessary. I can assure you I'm familar with[/color][/color]
them.[color=blue][color=green]
> >
> > Ah! We're making progress! All this time I thought the ANSI spec was the
> > Bible - to work toward at all costs.[/color]
>
> Maybe you should have gone through my list of ANSI options anyway. It
> would have been clear already to you it never was a talk of a bible, and
> you could have spared us your sarcasms.
>
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se
>
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techinf...2000/books.asp[/color]

[Erland Sommarskog]

Re: Difficult Query: is this possible in SQL?

Richard Romley (rromley@optonl ine.net) writes:[color=blue]
> That's pretty presumptuous on your part, don't you think? I can make
> exactly the same argument about you. Let me ask you something: When
> you're involved in a debate with someone who *refuses* to see your point
> of view, why is it the OTHER guy who is being stubborn?[/color]

The main problem I have with you is that you throw a lot of Microsoft
for no good reason. It does give, as they say, a unprofessional impression.
Initially I ignored that in my responses and focused on the technical
details. But you preferred to skip over a lot of that.
[color=blue]
> So, yes, I have worked with product development. How about you?[/color]

I work for a small ISV, so you can guess the rest. Of course, since
we have a very small customer base, our situation is quite different
from Microsoft. But I do recognize the problems. "Do we really want to
keep this obscure feature at any cost." It would surprise me if you
never have run into that dilemma yourself.
[color=blue]
> I'm really sorry you feel that way. I was trying to point out the
> hypocrisy of Microsoft's position - and the examples I gave spoke to the
> heart of the issue. Erland, why is it that if I choose to ignore an ANSI
> rule I am guilty of bad design - but when I give examples of Microsoft
> doing it I am being sarcastic?[/color]

Because handling NULL as an unknown value which is not equal to another
unknown value is a very basic concept in relational databases.
[color=blue]
> I don't understand. The ENTIRE POINT OF THE DISCUSSION is that
> SQL Server is LOADED with ANSI violations that won't be removed because
> doing so would break the code of virtually the entire customer base.[/color]

Many non-ANSI features will remain there forever, and Microsoft will
also add features that are not in ANSI. But really bad ones will go
away. Extraneous columns with GROUP BY went away already in 6.0. *= and
=* will also go away some day, the sooner the better. (It has been
deprecated in Books Online in the last two versions.)

And, mind you, when Microsoft changed the defaults they did that
depending on the client library. If you connect with DB-Library
everything is off. Then again, there will be the day when DB-Library
will no longer be with us.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp