TRUE < FALSE in MS Access?

Question

I wanted to do OR on boolean column in aggregate function of group by and, logically, I chose Max function for this. But I found out that TRUE < FALSE in MS Access! It seems that MS Access is aliasing TRUE to -1 instead of 1.

I tried the same in MySQL, seems working fine:

mysql> select if(TRUE > FALSE, 1, 0);
+------------------------+
| if(TRUE > FALSE, 1, 0) |
+------------------------+
|                      1 |
+------------------------+

Why is that? Why on earth Access broke the SQL norm here?

I don't think there is any SQL norm regarding mapping boolean values to integer values. — ypercubeᵀᴹ, Aug 08 '13 at 12:03
You are talking about Microsoft Access, why do you expect any kind of standards compliant behavior ;) — Mark Rotteveel, Aug 08 '13 at 12:05
possible duplicate of [Why is "Yes" a value of -1 in MS Access database?](http://stackoverflow.com/questions/8827447/why-is-yes-a-value-of-1-in-ms-access-database) — HansUp, Aug 08 '13 at 12:11
Seems to me that TRUE/FALSE should only ever be treated as truth values and as such do not have a magnitude. This renders discussions as to whether `TRUE > FALSE` pointless. After all does it make any sense that `TRUE + 1 = FALSE`? No it doesn't so why should anybody be concerned whether `TRUE > FALSE`? They shouldn't, and that is the answer. — NealB, Aug 08 '13 at 13:39
I don't think so. Aggregate functions like Max and Min should work. There's no other way how to do groupwise AND/OR. — Tomas, Aug 08 '13 at 13:46
@NealB SQL:2011 Foundation section _8.2 Comparison predicate_ states: _"In comparisons of boolean values, True is greater than False"_ — Mark Rotteveel, Aug 08 '13 at 17:42

score 2 · Answer 1 · edited Jun 20 '20 at 09:12

2

From This answer:

The binary representation of False is 0000000000000000. If you perform a NOT operation (in machine code) to it, it will be changed to 1111111111111111, but this is the binary representation of the 16-bit signed integer -1.

Changing the sign of a number happens by inverting all the bits and adding 1. This is called the "two's complement".

Let us change the sign of 1111111111111111. First invert; we get: 0000000000000000

Then add one: 0000000000000001, this is 1.

This is the proof that 1111111111111111 was the binary representation of -1.

edited Jun 20 '20 at 09:12

Community

1
1

answered Aug 08 '13 at 12:06

Mansfield

14,445
18
76
112

Well, I don't think this is an explanation, because e.g. in `C`, `!0 == 1`, not `11111111`. So `NOT` in the Access could have been implemented as `!`, not as bitwise `NOT`. I think this is just a broken design. – Tomas Aug 08 '13 at 12:08
Anyway, why didn't they use `unsigned` types then? Then TRUE could be `11111111` and at the same time `> FALSE`. – Tomas Aug 08 '13 at 12:15
1

"Anyway, why didn't they use unsigned types then"? Because it's built on the VB runtime, and the VB runtime didn't have unsigned types. "But when didn't the VB Runtime . . ." I dunno mate, it was a bad decision that bit me in the arse more than once I can tell you :( – Binary Worrier Aug 08 '13 at 12:21

score 1 · Accepted Answer · answered Aug 08 '13 at 12:07

1

Yes in MSAccess the Value of true is -1.

It may have something to do with it's Visual Basic roots (in VB -1 is true to help it's BITWISE operators do double duty as logical operators), but I wouldn't be so surprised, MSAccess breaks many many SQL Norms.

answered Aug 08 '13 at 12:07

Binary Worrier

50,774
20
136
184

TRUE < FALSE in MS Access?

2 Answers2