473,326 Members | 2,196 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

Another JET flaw

Access yields the wrong result for this simple query:
SELECT (4 <> Null) AS Expr1;

Details:
http://allenbrowne.com/bug-11.html

--
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.
Nov 13 '05 #1
16 1547
Mr. Browne,
Maybe so. But for the tons of admin assistants out there using Access that
are not schooled in boolean algebra or versed in set theory or give a rats
but about relational theory the statement (4 <> Null) = Null defies logic.
4 does not equal Null they would say, and agree with Access that this is a
true statement. Access is an rdbms pitched as a database for the rest of
us. Many of Jet's flaws are changes to the orthodox way of doing things to
make life easier for the administrative assistants that often get roped into
building database applications without formal training.

"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
news:41***********************@per-qv1-newsreader-01.iinet.net.au...
Access yields the wrong result for this simple query:
SELECT (4 <> Null) AS Expr1;

Details:
http://allenbrowne.com/bug-11.html

--
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.

Nov 13 '05 #2
"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
news:41***********************@per-qv1-newsreader-01.iinet.net.au...
Access yields the wrong result for this simple query:
SELECT (4 <> Null) AS Expr1;

FWIW in ANSI SQL you cannot do this at all. Boolean data types cannot be
specified as the data type of a table column or variable, and cannot be
returned in a result set.
Nov 13 '05 #3
"Allen Browne" <Al*********@SeeSig.Invalid> wrote in news:4173a408$0$25588
$5*******@per-qv1-newsreader-01.iinet.net.au:
Access yields the wrong result for this simple query:
SELECT (4 <> Null) AS Expr1;

Details:
http://allenbrowne.com/bug-11.html


Uh huh!

--
Lyle
--
use iso date format: yyyy-mm-dd
http://www.w3.org/QA/Tips/iso-date
--
The e-mail address isn't, but you could use it to find one.
Nov 13 '05 #4
A97 needs a source table, but still has the error:
SELECT (Employees.EmployeeID <> Null) AS Expr1,
(Null <> Employees.EmployeeID) AS Expr2
FROM Employees;

Output of Expr1 is True, and Expr2 is Null. Same error.

--
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.

"Chuck Grimsby" <c.*******@worldnet.att.net.invalid> wrote in message
news:gb********************************@4ax.com...

This won't even run in Access 97. (Reserved Word error)

Access 2K and 2K3 both return -1 however, which is correct. 4 is not
Null.

On Mon, 18 Oct 2004 19:07:46 +0800, "Allen Browne"
<Al*********@SeeSig.Invalid> wrote:
Access yields the wrong result for this simple query:
SELECT (4 <> Null) AS Expr1;
Details:
http://allenbrowne.com/bug-11.html

Nov 13 '05 #5
"Alan Webb" <kn*****@hotmail.com> wrote in
news:D6********************@comcast.com:
Maybe so. But for the tons of admin assistants out there using
Access that are not schooled in boolean algebra or versed in set
theory or give a rats but about relational theory the statement (4
<> Null) = Null defies logic. 4 does not equal Null they would
say, and agree with Access that this is a true statement. Access
is an rdbms pitched as a database for the rest of us. Many of
Jet's flaws are changes to the orthodox way of doing things to
make life easier for the administrative assistants that often get
roped into building database applications without formal training.


That's the same philosophy behind Internet Explorer's rendering of
completely invalid HTML, which has led us to an Internet littered
with web pages that render legibly *only* in IE.

If it's logically wrong, it should not return a result that is a
guess as to what the user intends.

A SQL parser should not guess at what the user means.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Nov 13 '05 #6
On Oct 18 2004, 07:07 am, "Allen Browne" <Al*********@SeeSig.Invalid> wrote
in news:41***********************@per-qv1-newsreader-01.iinet.net.au:
Access yields the wrong result for this simple query:
SELECT (4 <> Null) AS Expr1;

Details:
http://allenbrowne.com/bug-11.html


Just to split hairs, this looks like a query parser bug as opposed to an
engine bug. The query below, where the NULL is passed through the
Expression Service, produces correct result:

SELECT (4<>IIf(True,Null)) AS Expr1;

--
remove a 9 to reply by email
Nov 13 '05 #7
Yes, it does seem to be a parsing bug, i.e. "<> Null" is parsed as "Is Not
Null", and so your expression is interpreted correctly.

(Not sure that the ES is involved though.)

--
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.

"Dimitri Furman" <df*****@cloud99.net> wrote in message
news:Xn****************************@127.0.0.1...
On Oct 18 2004, 07:07 am, "Allen Browne" <Al*********@SeeSig.Invalid>
wrote
in news:41***********************@per-qv1-newsreader-01.iinet.net.au:
Access yields the wrong result for this simple query:
SELECT (4 <> Null) AS Expr1;

Details:
http://allenbrowne.com/bug-11.html


Just to split hairs, this looks like a query parser bug as opposed to an
engine bug. The query below, where the NULL is passed through the
Expression Service, produces correct result:

SELECT (4<>IIf(True,Null)) AS Expr1;

--
remove a 9 to reply by email

Nov 13 '05 #8
David & the rest,
I am not defending the "bug", just pointing out that Microsoft tries very
hard to listen to its customers and does things to respond to what it
believes best serves the customer, standards be danged. Jet SQL steps on
the ASNI-SQL standards pretty hard in places--especially where doing so
makes life easier for the target audience of a product.

"David W. Fenton" <dX********@bway.net.invalid> wrote in message
news:Xn**********************************@24.168.1 28.86...
"Alan Webb" <kn*****@hotmail.com> wrote in
news:D6********************@comcast.com:
Maybe so. But for the tons of admin assistants out there using
Access that are not schooled in boolean algebra or versed in set
theory or give a rats but about relational theory the statement (4
<> Null) = Null defies logic. 4 does not equal Null they would
say, and agree with Access that this is a true statement. Access
is an rdbms pitched as a database for the rest of us. Many of
Jet's flaws are changes to the orthodox way of doing things to
make life easier for the administrative assistants that often get
roped into building database applications without formal training.


That's the same philosophy behind Internet Explorer's rendering of
completely invalid HTML, which has led us to an Internet littered
with web pages that render legibly *only* in IE.

If it's logically wrong, it should not return a result that is a
guess as to what the user intends.

A SQL parser should not guess at what the user means.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

Nov 13 '05 #9
Alan Webb wrote:
I am not defending the "bug", just pointing out that Microsoft tries very
hard to listen to its customers and does things to respond to what it
believes best serves the customer, standards be danged.


Point taken. But I have to say, if that is in fact Microsoft's
viewpoint, then it is 180 degrees off course in what it believes to be
what best serves the customer. Standards are standards. And that is
what's best for the customer.

Sloppiness is not good even if a majority think that way. And while I
know that you know what you're talking about technically, I heartily
disagree with what is best "philosophically" and real-world-applicable
in this case.

Nov 13 '05 #10
"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message news:<41***********************@per-qv1-newsreader-01.iinet.net.au>...
Access yields the wrong result for this simple query:
SELECT (4 <> Null) AS Expr1;

Details:
http://allenbrowne.com/bug-11.html


Yup. But just to nitpick, you want to use the word "reflexive" rather
than "transitive". Transitive is:

if (A op B) and (B op C) then (A op C) is true

where "op" is replaced with a suitable something.
Nov 13 '05 #11
Fair enough. Altered.

--
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.

"Damien" <Da*******************@hotmail.com> wrote in message
news:ac**************************@posting.google.c om...
Access yields the wrong result for this simple query:
SELECT (4 <> Null) AS Expr1;

Details:
http://allenbrowne.com/bug-11.html


Yup. But just to nitpick, you want to use the word "reflexive" rather
than "transitive". Transitive is:

if (A op B) and (B op C) then (A op C) is true

where "op" is replaced with a suitable something.

Nov 13 '05 #12
Allen Browne wrote:
Access yields the wrong result for this simple query:
SELECT (4 <> Null) AS Expr1;

Details:
http://allenbrowne.com/bug-11.html


Hi Allen. In A97, I can't enter
Expr1:(4 <> Null)
using the query builder.

As soon as I tab to the next field, the code changes to
Expr1:(4 Is Not Null)

So I tried
Sub Test()
Dim s As String
Dim r As Recordset
Dim i As Integer

s = "SELECT (4 <> Null) AS Expr1 From Employees"
Set r = CurrentDb.OpenRecordset(s, dbOpenSnapshot)

r.MoveFirst
Do While i < 4
i = i + 1
MsgBox r!Expr1
r.MoveNext
Loop
r.Close
Set r = Nothing
End Sub

and I get True for the 4 records. Since I can't get the SQL for the
recordset once opened, I'll assume it changed the (4<>Null) to (4 Is Not
Null). I'm sure if I were to assign it to a recordsource, I would see
that it has corrected my code.

I think Access is doing it's best to correct the developer in this case
whether or not you want the correction.
Nov 13 '05 #13
In the query design window, switch to SQL View (View menu).
You can type the statement in there, and it will retain the "<> Null".

I have no argument with the GUI trying to help the user figure out what they
meant. But, when the query parser/engine misinterprets the statement and
gives you a different answer than what you asked for, it is just plain
wrong.

--
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.

"Salad" <oi*@vinegar.com> wrote in message
news:wl*****************@newsread3.news.pas.earthl ink.net...
Allen Browne wrote:
Access yields the wrong result for this simple query:
SELECT (4 <> Null) AS Expr1;

Details:
http://allenbrowne.com/bug-11.html


Hi Allen. In A97, I can't enter
Expr1:(4 <> Null)
using the query builder.

As soon as I tab to the next field, the code changes to
Expr1:(4 Is Not Null)

So I tried
Sub Test()
Dim s As String
Dim r As Recordset
Dim i As Integer

s = "SELECT (4 <> Null) AS Expr1 From Employees"
Set r = CurrentDb.OpenRecordset(s, dbOpenSnapshot)

r.MoveFirst
Do While i < 4
i = i + 1
MsgBox r!Expr1
r.MoveNext
Loop
r.Close
Set r = Nothing
End Sub

and I get True for the 4 records. Since I can't get the SQL for the
recordset once opened, I'll assume it changed the (4<>Null) to (4 Is Not
Null). I'm sure if I were to assign it to a recordsource, I would see
that it has corrected my code.

I think Access is doing it's best to correct the developer in this case
whether or not you want the correction.

Nov 13 '05 #14
Allen Browne wrote:
In the query design window, switch to SQL View (View menu).
You can type the statement in there, and it will retain the "<> Null".
Hmmmm. I'm in A97. It autoconverts it to (4 Is Not Null). In my case
I'd never be able to enter your statement.
I have no argument with the GUI trying to help the user figure out what they
meant. But, when the query parser/engine misinterprets the statement and
gives you a different answer than what you asked for, it is just plain
wrong.


I agree with you. Can't NZ() be used to overcome the problem.

Nov 13 '05 #15
"Salad" <oi*@vinegar.com> wrote in message
news:Hk****************@newsread1.news.pas.earthli nk.net...
In the query design window, switch to SQL View (View menu).
You can type the statement in there, and it will retain the "<> Null".


Hmmmm. I'm in A97. It autoconverts it to (4 Is Not Null). In my case
I'd never be able to enter your statement.


Even in SQL View?
I see that behavior in Design View, but can enter the statement in SQL View.

--
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.
Nov 13 '05 #16
Allen Browne wrote:
"Salad" <oi*@vinegar.com> wrote in message
news:Hk****************@newsread1.news.pas.earthli nk.net...
In the query design window, switch to SQL View (View menu).
You can type the statement in there, and it will retain the "<> Null".
Hmmmm. I'm in A97. It autoconverts it to (4 Is Not Null). In my case
I'd never be able to enter your statement.

Even in SQL View?


My mistake. As long as I never go into Design view it will keep the
syntax as (4 <> Null). IOW, I need to save it while in SQL view. Then
it opens with the command intact. The second I go enter into Design
view it "corrects" me.
I see that behavior in Design View, but can enter the statement in SQL View.


I mentioned earlier I tried this by opening a recordset. The values
returned True. So I created Table1; ID, NumberField, BoolField and
created a form off of this table. I then entered the following.

Dim s As String
s = "SELECT Table1.ID, Table1.NumberField, " & _
"(4 <> Null) AS BoolField FROM Table1 ORDER BY Table1.ID;"
Me.RecordSource = s

MsgBox Me.RecordSource

And it kept the select string as shown above. But the results came back
true.

I guess behind the scenes it "corrects" me.

You've found an interesting "feature". I guess I'd go with NZ().
Nov 13 '05 #17

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

19
by: Christian Engström | last post by:
If you have a function that returns something by value, the gcc compiler (version 3.2.3 on Windows XP with MinGW) converts the returned value from the type you specify in the code, to the const...
10
by: ma740988 | last post by:
I'm hoping my post here doesn't fall into the 'hard to say' category, nonetheless I've been advised that multiple uses of accessor/mutator (get/set) member functions can be viewed as a 'design...
5
by: christophe (dot) poucet (at) gmail (dot) com | last post by:
Hello, I noticed there is a flaw in the vector implementation of g++ (3.4). Basically when you erase an element from a vector, it calls the wrong destructor. In most cases this is not such a...
7
by: Sky | last post by:
What I have currently: I have a user control called mod_container.aspx that is basically two divs -- the top a toolbar, that expands/collapse the second div which can contain other...
23
by: Java script Dude | last post by:
Give this a test in IE (verified in IE 6.0 sp2) Has anybody else seen this and is there a workaround besides escaping with html special chars and not using IE ;] ~ file 1 ~ <html> <head>...
8
by: Matt Kruse | last post by:
http://news.zdnet.com/2100-1009_22-6121608.html Hackers claim zero-day flaw in Firefox 09 / 30 / 06 | By Joris Evers SAN DIEGO--The open-source Firefox Web browser is critically flawed in...
9
by: mps | last post by:
I want to define a class that has a generic parameter that is itself a generic class. For example, if I have a generic IQueue<Tinterface, and class A wants to make use of a generic class that...
15
by: Phlip | last post by:
Javascripters: I have an outer page and an inner iframe. The outer page calculates some javascript, and wants the inner frame to run it. The inner frame should hit a page on the same (private)...
3
by: Windows | last post by:
Microsoft Windows | MSN Flaw I have found a flaw in MSN. For those that use MSN or Live.com with Windows see:...
1
by: Guy Macon | last post by:
Serious Security Flaw in Google Chrome: http://www.readwriteweb.com/archives/security_flaw_in_google_chrome.php -- Guy Macon <http://www.GuyMacon.com/>
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.