RE: [SQLDownUnder] Sql 2000 bug

[matthew_wills]

Tanvir,

Whoops, I am an idiot. I just realise I ran the test against my SQL Server
2000 box.

Apologies.

Thanks
Matthew Wills | Senior Analyst Programmer | Adviser Tools and Services|
Financial Planning and Third Party | NAB Technology | Wealth Management
Australia





|---------+--------------------------------------->
|         |           "Tanvir Huda"               |
|         |           <THuda@xxxxxxxxxxxxxxxxx>   |
|         |           Sent by:                    |
|         |           SQLDownUnderList@listserver.|
|         |           readify.net                 |
|         |                                       |
|         |                                       |
|         |           12/02/2007 12:01 PM         |
|         |           Please respond to           |
|         |           SQLDownUnder                |
|         |                                       |
|---------+--------------------------------------->
  >--------------------------------------------------------------------------------------------------------------|
  |                                                                                                              |
  |       To:       <SQLDownUnder@xxxxxxxxxxxxxxxxxxxxxx>                                                        |
  |       cc:       (bcc: Matthew Wills/AU1/WealthMgmt)                                                          |
  |       Subject:  RE: [SQLDownUnder] Sql 2000 bug                                                              |
  >--------------------------------------------------------------------------------------------------------------|




Hello

I run the queries.

The first one returns 3 records and second one returns 1 record. That
seems alright to me.

Regards

Tanvir

-----Original Message-----
From: SQLDownUnderList@xxxxxxxxxxxxxxxxxxxxxx
[mailto:SQLDownUnderList@xxxxxxxxxxxxxxxxxxxxxx] On Behalf Of
matthew_wills@xxxxxxxxxx
Sent: Monday, 12 February 2007 11:48 AM
To: SQLDownUnder@xxxxxxxxxxxxxxxxxxxxxx
Subject: Re: [SQLDownUnder] Sql 2000 bug

Jonathon,

Both are still there. Although using an ORDER BY fixes them.

Seeya
Matthew Wills | Senior Analyst Programmer | Adviser Tools and Services|
Financial Planning and Third Party | NAB Technology | Wealth Management
Australia





|---------+--------------------------------------->
|         |           "Jonathan Parker"           |
|         |           <JonathanP@westlinkconsultin|
|         |           g.com.au>                   |
|         |           Sent by:                    |
|         |           SQLDownUnderList@listserver.|
|         |           readify.net                 |
|         |                                       |
|         |                                       |
|         |           12/02/2007 11:31 AM         |
|         |           Please respond to           |
|         |           SQLDownUnder                |
|         |                                       |
|---------+--------------------------------------->

>-----------------------------------------------------------------------
---------------------------------------|
  |
|
  |       To:       <SQLDownUnder@xxxxxxxxxxxxxxxxxxxxxx>
|
  |       cc:       (bcc: Matthew Wills/AU1/WealthMgmt)
|
  |       Subject:  [SQLDownUnder] Sql 2000 bug
|

>-----------------------------------------------------------------------
---------------------------------------|




Does anyone know if the bellow issue has been fixed in SQL 2005?

CREATE TABLE foo (
A INTEGER NULL
)

INSERT INTO foo VALUES (1)

INSERT INTO foo
SELECT DISTINCT A FROM foo
UNION ALL
SELECT A FROM foo

SELECT * FROM foo -- !!!
GO

DROP TABLE foo

The select returns 4 rows!

And

CREATE TABLE foo (
A INTEGER NULL
)

INSERT INTO foo
SELECT 77869
UNION ALL
SELECT A FROM foo

SELECT * FROM foo
GO

DROP TABLE foo

The select returns 2 rows!

See here for an old discussion
http://dbforums.com/showthread.php?t=744556


Regards,

Jonathan Parker
.NET Developer
Westlink Consulting
Mobile :  0419969241 Website : http://www.jonathanparker.com.au   Email
:
jonathanp@xxxxxxxxxxxxxxxxxxxxxxxxx  Skype Name :  jonathanparker






______________________________________________________________________
This email has been scanned by the MessageLabs Email Security System.
For more information please visit http://www.messagelabs.com/email 
______________________________________________________________________


to unsubscribe to this list, please send a message back to the list with 'unsubscribe' as the subject. Powered by mailenable.com - List managed by http://www.readify.net

This e-mail is sent by or on behalf of the named sender identified above.
If:

(a) you do not wish to receive any e-mail marketing material from this
person in the future, please forward the contents of this email to
unsubscribe@xxxxxxxxxx with the word "unsubscribe" in the
subject box.

(b) you wish to unsubscribe from all central e-mail marketing lists
used by our business, please forward the contents of this e-mail to
unsubscribeall@xxxxxxxxxx with the message "unsubscribe
from all central e-mail marketing lists" in the subject box.

If you do not forward the contents of this e-mail with your
unsubscription then it may not be able to be implemented.

The information contained in this e-mail communication may be
confidential. You should only read, disclose, re-transmit, copy,
distribute, act in reliance on or commercialise the information if you
are authorised to do so. If you are not the intended recipient of this
e-mail communication, please immediately notify us by e-mail to
postmaster@xxxxxxxxxx, or reply by e-mail direct to the sender and then
destroy any electronic and paper copy of this message. Any views
expressed in this e-mail communication are those of the individual
sender, except where the sender specifically states them to be the views
of a member of the National Australia Bank Group of companies. Any
advice contained in this e-mail has been prepared without taking into
account your objectives, financial situation or needs. Before acting on
any advice in this e-mail, National Australia Bank Limited recommends
that you consider whether it is appropriate for your circumstances. If
this e-mail contains reference to any financial products, the National
recommends you consider the Product Disclosure Statement (PDS) or other
disclosure document before making any decisions regarding any products.
The National Australia Bank Group of companies does not represent,
warrant or guarantee that the integrity of this communication has been
maintained nor that the communication is free of errors, virus or
interference.