Access 2000 bug?

[Dan]

I hate it when people think that their own misunderstandings are bugs
in the program, but this time I think I've got something.

If I run the following SQL code in Access 2000, I get unexpected
results:

SELECT MY_FIELD
FROM
(
SELECT NULL AS MY_FIELD FROM DUAL
UNION ALL
SELECT 1.1 AS MY_FIELD FROM DUAL
)
WHERE MY_FIELD IS NOT NULL;

(I used a dummy table 'DUAL' to simplify the test, but you can get the
same results from any table)

I expect '1.1' to be the result, but I get nothing. Yet, if you replace
the '1.1' in the query with a string (say, "hello"), you get the
"hello" just as you should. Date values are also lost. So far, strings
seem to be the only values that survive the SELECT. Integers produce
junk characters sometimes.

In case anyone is confused by my example, here's an explanation:

The the inner expression (SELECT NULL AS MY_FIELD FROM DUAL UNION ALL
SELECT 1.1 AS MY_FIELD FROM DUAL) is expected to result in a temporary
relation (table) that consists of this:

MY_FIELD
--------
NULL
1.1

If you just run that much in Access, everything's fine.

Now, when we select the non-null value out of the relation (WHERE
MY_FIELD IS NOT NULL), you should get 1.1, but you don't. You get a
blank row.

Here's just the outer expression:

SELECT MY_FIELD
FROM
()
WHERE MY_FIELD IS NOT NULL;

Not very complex.

The weirdness really comes out when you replace the 1.1 with "hello".
Then everything works fine. Replace it with #1/1/01#, and it breaks
again. I'm pretty sure that the type of the field really shouldn't
matter, but it does.

Oh yea, and if you reverse the order of the SELECTs in the UNION ALL
expression, it also works properly. As far as I know, the order of the
SELECTs in a UNION ALL shouldn't influence the result (except in the
ordering of the resulting rows)

If you don't like my example, you can do something like this to
reproduce the same problem:

Given the tables:

-- MY_FRIENDS --

FULL_NAME BIRTHDATE
--------- ---------
JILL NULL

and

-- YOUR_FRIENDS --

FULL_NAME BIRTHDATE
--------- ---------
BILL 1/1/2001

You can do this:

(
SELECT * FROM MY_FRIENDS
UNION ALL
SELECT * FROM YOUR_FRIENDS
)

And get:

FULL_NAME BIRTHDATE
--------- ---------
JILL NULL
BILL 1/1/2001

Now, try to select the rows that aren't NULL:

SELECT * FROM
(
SELECT * FROM MY_FRIENDS
UNION ALL
SELECT * FROM YOUR_FRIENDS
)
WHERE BIRTHDATE IS NOT NULL;

You'd expect 'BILL 1/1/2001', right? Well, you get a row, but it's
blank. Put another row in YOUR_FRIENDS (with a non-NULL birthdate) and
you get two blank rows! Again, using strings instead or reordering the
SELECT's fixes it. That just doesn't make sense.

So, can someone point out my misunderstanding?

Dan

[Allen Browne]

Hi Dan

JET determines the data type of a calculated/literal query field by looking
at the first few rows. In the case of a UNION query, it is the first few
rows of the SELECT query. If the field is Null in all rows (as in your
example), JET settles on it being a Text field, even though the following
SELECT is a numeric value. This results in the non-text data of the
following SELECTs being incorrectly interpreted.

The usual workaround is to use an IIf() expression to give Access some way
to figure out the data type to use for Null. Try:
SELECT IIf(False, 1, NULL) AS MY_FIELD FROM DUAL
UNION ALL
SELECT 1.1 AS MY_FIELD FROM DUAL

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Dan" <da************@gmail.com> wrote in message
news:11*********************@z14g2000cwz.googlegro ups.com...

I hate it when people think that their own misunderstandings are bugs
in the program, but this time I think I've got something.

If I run the following SQL code in Access 2000, I get unexpected
results:

SELECT MY_FIELD
FROM
(
SELECT NULL AS MY_FIELD FROM DUAL
UNION ALL
SELECT 1.1 AS MY_FIELD FROM DUAL
)
WHERE MY_FIELD IS NOT NULL;

(I used a dummy table 'DUAL' to simplify the test, but you can get the
same results from any table)

I expect '1.1' to be the result, but I get nothing. Yet, if you replace
the '1.1' in the query with a string (say, "hello"), you get the
"hello" just as you should. Date values are also lost. So far, strings
seem to be the only values that survive the SELECT. Integers produce
junk characters sometimes.

In case anyone is confused by my example, here's an explanation:

The the inner expression (SELECT NULL AS MY_FIELD FROM DUAL UNION ALL
SELECT 1.1 AS MY_FIELD FROM DUAL) is expected to result in a temporary
relation (table) that consists of this:

MY_FIELD
--------
NULL
1.1

If you just run that much in Access, everything's fine.

Now, when we select the non-null value out of the relation (WHERE
MY_FIELD IS NOT NULL), you should get 1.1, but you don't. You get a
blank row.

Here's just the outer expression:

SELECT MY_FIELD
FROM
()
WHERE MY_FIELD IS NOT NULL;

Not very complex.

The weirdness really comes out when you replace the 1.1 with "hello".
Then everything works fine. Replace it with #1/1/01#, and it breaks
again. I'm pretty sure that the type of the field really shouldn't
matter, but it does.

Oh yea, and if you reverse the order of the SELECTs in the UNION ALL
expression, it also works properly. As far as I know, the order of the
SELECTs in a UNION ALL shouldn't influence the result (except in the
ordering of the resulting rows)

If you don't like my example, you can do something like this to
reproduce the same problem:

Given the tables:

-- MY_FRIENDS --

FULL_NAME BIRTHDATE
--------- ---------
JILL NULL

and

-- YOUR_FRIENDS --

FULL_NAME BIRTHDATE
--------- ---------
BILL 1/1/2001

You can do this:

(
SELECT * FROM MY_FRIENDS
UNION ALL
SELECT * FROM YOUR_FRIENDS
)

And get:

FULL_NAME BIRTHDATE
--------- ---------
JILL NULL
BILL 1/1/2001

Now, try to select the rows that aren't NULL:

SELECT * FROM
(
SELECT * FROM MY_FRIENDS
UNION ALL
SELECT * FROM YOUR_FRIENDS
)
WHERE BIRTHDATE IS NOT NULL;

You'd expect 'BILL 1/1/2001', right? Well, you get a row, but it's
blank. Put another row in YOUR_FRIENDS (with a non-NULL birthdate) and
you get two blank rows! Again, using strings instead or reordering the
SELECT's fixes it. That just doesn't make sense.

So, can someone point out my misunderstanding?

Dan

[Terry Kreft]

Dan,
To back Allen up, if you invert your UNION ALL

SELECT MY_FIELD
FROM
(
SELECT 1.1 AS MY_FIELD FROM DUAL
UNION ALL
SELECT NULL AS MY_FIELD FROM DUAL
)
WHERE MY_FIELD IS NOT NULL;

Or if you explicitly say the field is numeric e.g.

SELECT csng(MY_FIELD)
FROM
(
SELECT 1.1 AS MY_FIELD FROM DUAL
UNION ALL
SELECT NULL AS MY_FIELD FROM DUAL
)
WHERE MY_FIELD IS NOT NULL;

You will get the result you originally expected

--
Terry Kreft
MVP Microsoft Access
"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
news:42***********************@per-qv1-newsreader-01.iinet.net.au...

Hi Dan

JET determines the data type of a calculated/literal query field by looking at the first few rows. In the case of a UNION query, it is the first few
rows of the SELECT query. If the field is Null in all rows (as in your
example), JET settles on it being a Text field, even though the following
SELECT is a numeric value. This results in the non-text data of the
following SELECTs being incorrectly interpreted.

The usual workaround is to use an IIf() expression to give Access some way
to figure out the data type to use for Null. Try:
SELECT IIf(False, 1, NULL) AS MY_FIELD FROM DUAL
UNION ALL
SELECT 1.1 AS MY_FIELD FROM DUAL

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Dan" <da************@gmail.com> wrote in message
news:11*********************@z14g2000cwz.googlegro ups.com...

I hate it when people think that their own misunderstandings are bugs
in the program, but this time I think I've got something.

If I run the following SQL code in Access 2000, I get unexpected
results:

SELECT MY_FIELD
FROM
(
SELECT NULL AS MY_FIELD FROM DUAL
UNION ALL
SELECT 1.1 AS MY_FIELD FROM DUAL
)
WHERE MY_FIELD IS NOT NULL;

(I used a dummy table 'DUAL' to simplify the test, but you can get the
same results from any table)

I expect '1.1' to be the result, but I get nothing. Yet, if you replace
the '1.1' in the query with a string (say, "hello"), you get the
"hello" just as you should. Date values are also lost. So far, strings
seem to be the only values that survive the SELECT. Integers produce
junk characters sometimes.

In case anyone is confused by my example, here's an explanation:

The the inner expression (SELECT NULL AS MY_FIELD FROM DUAL UNION ALL
SELECT 1.1 AS MY_FIELD FROM DUAL) is expected to result in a temporary
relation (table) that consists of this:

MY_FIELD
--------
NULL
1.1

If you just run that much in Access, everything's fine.

Now, when we select the non-null value out of the relation (WHERE
MY_FIELD IS NOT NULL), you should get 1.1, but you don't. You get a
blank row.

Here's just the outer expression:

SELECT MY_FIELD
FROM
()
WHERE MY_FIELD IS NOT NULL;

Not very complex.

The weirdness really comes out when you replace the 1.1 with "hello".
Then everything works fine. Replace it with #1/1/01#, and it breaks
again. I'm pretty sure that the type of the field really shouldn't
matter, but it does.

Oh yea, and if you reverse the order of the SELECTs in the UNION ALL
expression, it also works properly. As far as I know, the order of the
SELECTs in a UNION ALL shouldn't influence the result (except in the
ordering of the resulting rows)

If you don't like my example, you can do something like this to
reproduce the same problem:

Given the tables:

-- MY_FRIENDS --

FULL_NAME BIRTHDATE
--------- ---------
JILL NULL

and

-- YOUR_FRIENDS --

FULL_NAME BIRTHDATE
--------- ---------
BILL 1/1/2001

You can do this:

(
SELECT * FROM MY_FRIENDS
UNION ALL
SELECT * FROM YOUR_FRIENDS
)

And get:

FULL_NAME BIRTHDATE
--------- ---------
JILL NULL
BILL 1/1/2001

Now, try to select the rows that aren't NULL:

SELECT * FROM
(
SELECT * FROM MY_FRIENDS
UNION ALL
SELECT * FROM YOUR_FRIENDS
)
WHERE BIRTHDATE IS NOT NULL;

You'd expect 'BILL 1/1/2001', right? Well, you get a row, but it's
blank. Put another row in YOUR_FRIENDS (with a non-NULL birthdate) and
you get two blank rows! Again, using strings instead or reordering the
SELECT's fixes it. That just doesn't make sense.

So, can someone point out my misunderstanding?

Dan

[Dan]

I see. I'm coming from a different background (Oracle, originally), so
I'm not very familiar with JET-specific behavior.

I'd *like* to think that ANSI SQL produces the same results regardless
of the DB engine behind it, but I know that that's not very realistic
at the moment.

Thanks for the expertise everyone.