2

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?

Tomas
  • 57,621
  • 49
  • 238
  • 373
  • 3
    http://stackoverflow.com/a/8827633/1324019 – Mansfield Aug 08 '13 at 12:03
  • I don't think there is any SQL norm regarding mapping boolean values to integer values. – ypercubeᵀᴹ Aug 08 '13 at 12:03
  • 3
    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
  • 2
    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
  • 1
    @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

2 Answers2

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.

Community
  • 1
  • 1
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
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.

Binary Worrier
  • 50,774
  • 20
  • 136
  • 184