mysterious results using NULLS and UNION statements.

Question

Let me start off by saying this is NOT a bug with t-sql or Sql Server (I get the results I expect with t-sql in Sql Server), but it affects a popular frontend for sql server (MS Access), so I thought it might be of interest here.  Is this behavior a different but valid interpretation of SQL UNION (or maybe of an older standard?).  Am I doing something dubious with the SQL, such that I should avoid using nulls with UNION?

Apparently mySql also has produced odd results ( http://bugs.mysql.com/bug.php?id=1833 ), as well as Oracle.   But t-sql behaves the way I would expect.
Predict the result of this query using Access 2010 (btw, you can also replace * with any actual column names from the table and get the same result):
Select NULL AS C, *
FROM [AnyTable]
Union
SELECT 6 AS C, *
FROM [AnyTable]

On my computer the hyphen character ("-") shows as the result for column C for the second select.  I'm not sure what befell the 6, though I'm concerned.  (Hyphens do not appear anywhere in the table data, not that I would feel any better if they did.)
The result is:
NULL,  someColValX
NULL, someColValY
-, someColValX
-, someColValY

I was hit with another variation that did something much worse--completely false but deceptively "valid" results (had seemingly horizontally shifted the third column of the second SELECT to overwrite its first column of the same datatype, when the first SELECT was all nulls).    The common denominator is always that there are nulls in the SELECT preceding the UNION statement.

Answer 1

btw, the mystery deepens if you use 7 rather than 6.  13 gives you NULLs throughout (for C), not sure that is an improvement though.

Answer 2

I think you need to explicitly state the type of the new column as Access can not derive it from the expression. In T-SQL I got NULL and 6 ok.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 3

Naomi, thanks, that sounds plausible but then the query should fail with error (as coalesce does in a similar situation with t-sql).  And why the odd character result?  as if it's performing some kind of bit operation on the data and spitting out a low ascii value for the result.  Unfortunately it means I have to replace all union queries in access that have a possible null result.       

Answer 4

This behavior occurs only when you explicitly mention NULL in your select query. If you have  NULL value in the column, that doesn't change your result.

If you have table like this, and doing a union, you will get the result as expected

Table1

ID	Emp	AGE
1	satheesh

Table2

ID	Emp	AGE
2	Variath	26

Query1

ID	Emp	AGE
1	satheesh
2	Variath	26

Good news is You don't need to change your queries :)

Regards
Satheesh

Answer 5

Unfortunately, in one of my other access union queries, the first SELECT happened to return a null result (no "NULL" literal was passed; a where clause was not met).  The results were strange (that was the horizontal shift of one column's result to another of the same underlying datatype I mentioned).  Anyway, the access results were just plain wrong -- t-sql, with the exact same syntex, gave the right answer; but in any case an entirely different answer, which is not the kind of thing one likes to see.  So I think the UNION queries in Access cannot be trusted if a null is any possibility within the SELECT prior to the union, particularly so if it's in the first column of the first select.  I wouldn't be too confident of the results with a null anywhere else either.  And without knowing the cause of this, I might soon have wider doubts.

Answer 6

I tried even having Null values in the first column, first row combination and in any case i am getting the expected results.

What is the type of the column where you are facing ? May be its a design related issue?

Regards
Satheesh

Answer 7

normal text fields with an intervening tinyint field; however it could be access is also getting confused by the fieldnames, though it shouldn't be.  the basic structure is like this:
select A as B, A, C from tblX
union
select B, D AS A, E AS C from tblY, but the query involves more fields and a join.  when the first select gives no results the results of C for the second select get copied into B (returned as B and not just C, -- B values disappear).  t-sql produces the right answer always.

I'll try to provide a followup later, after I check all my union queries. 

Answer 8

When you select from two tables and expect the data types different in the columns or some not returning the results, it is an always good idea to explicitly provide a placeholder type for the resulting column, e.g.

select cast(myTable.myField as varchar(max)) as FirstColumn, ...

from ...

UNION 

....

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 9

I was hit with another variation that did something much worse--completely false but deceptively "valid" results (had seemingly horizontally shifted the third column of the second SELECT to overwrite its first column of the same datatype, when the first SELECT was all nulls).    The common denominator is always that there are nulls in the SELECT preceding the UNION statement.

The reason SQL Server works as expected is due to data type precedence rules.  SQL Server examines the data types of the columns returned and will implicitly convert values to the highest precedence data type returned in the column.  The order of the UNION selects shouldn't matter in SQL Server.

It looks to me like Access converted the numeric value 6 to the correspinding character code point.

---

Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

Answer 10

@Naomi N, good suggestion, but unfortunately a CAST (or in access I'd use ISNULL test and CSTR etc) would I think change the meaning of the entire query when the whole first select is a null result.  That means no rows, and a cast from null into something on any field creates a row with blank values or zeroes or whatever you've casted to.  I'd have to add a test again that would strip out the row again, and basically at that point I'm testing rewriting the whole thing again.  Or maybe there's another way.

@Dan Guzman,
Good, and nearly complete:
I had to look up the old symbolic representations for the control characters (what you mean by "character code point"?).
For 7 it is producting the symbol for BEL (BELL, alt7 the numeric pad), for 8 it is producing BS (backspace, alt8 on the numeric pad), 11 produces VT symbol (vertical tab, = the symbol for man).  12 doesn't produce a woman (symbol for FF, form feed, and I won't speculate why that's FF and the other VT), but instead a question mark in a box, probably for a missing glyph. I don't why the hyphen for 6 though; the blanks on some probably are the result of some internal filter or error catcher in Access--though of course it shouldn't be outputting most of those below ascii 32 values any of the time.             

But I still don't understand the rotated column--I'll see what the simplest reproducible example I come up with is.  It could be some other cause I'm not seeing there.

Answer 11

Ok, here is an example of incorrect results whenever the first select returns an empty recordset.  Unless someone knows this is not a bug but by design, I'll have to rewrite every union query for peace of mind.  sigh.  (I want to emphasize that the results are ALWAYS correct in T-SQL; the problem is ONLY with Microsoft Access 2010 sql.)  After getting to the barebones query, I see better what happened, but I think it's wrong.

(q#1)  "SELECT y AS x, y AS y FROM tbl WHERE 1=0;" should always return no results,
and so
(q#2)  "SELECT y AS x, y AS y FROM tbl WHERE 1=0
UNION ALL
SELECT x AS x, y AS y FROM tbl;"
should return the same rows as just the second select by itself:
(q#3)  "SELECT x as x, y as y FROM tbl;"
But I'm getting back for q#2 the same results as: "SELECT y, y FROM tbl;"

Here is the actual query (akin to query #3 above):
SELECT g.Thouse AS Thouse, q.AhouseId AS AHouse
FROM Giveup AS g INNER JOIN Acc AS q ON (g.AHouse=q.AHouseId)
WHERE SysId = "TFNG";
This returns a list of rows, ALL with "T" under "Thouse":
Thouse, AHouse
T,   J
T,   N
T,   N
T,   R

However the results are completely different when prefaced by a select returning no rows and a UNION statement.  That gives this instead (notice that the second column, Ahouse, has replaced the first column "THouse"):
Thouse,  AHouse
J,    J
N,   N
N,   N
R,   R

Here is the query that produces that result (akin to query #2 above):
SELECT q.AHouseId AS Thouse, q.AHouseId AS AHouse
FROM Acc AS q
WHERE 1=0
UNION ALL
SELECT g.Thouse AS Thouse, q.AhouseId AS AHouse
FROM Giveup AS g INNER JOIN Acc AS q ON (g.AHouse=q.AHouseId)
WHERE SysId = "TFNG";

It looks like Access has used the first select to reach out and rearrange the results of the second select statement.  T-SQL produces the expected results (the identical query except TFNG is in single quotes)--notice the first column is all "T":
Thouse,  AHouse
T,  J
T,  N
T,  N
T,  R

Answer 12

TechVsLife2 has started a new/additional thread here in "Access for Developers":

http://social.msdn.microsoft.com/Forums/en-GB/accessdev/thread/cf0f9002-60cb-4da3-bd72-03e6771fc539

---

Matthew Slyman M.A. (Camb.)

Answer 13

Thanks--solved over there, by prefacing this to the query:
SELECT "" AS Thouse, "" AS AHouse FROM Acc WHERE 1=0
UNION ALL

But still a bug, as an error message should be thrown if Access cannot figure out what to do (shouldn't return random characters or worse, results that look formally right but are wrong).