13

When I run a query in MS Access I can happily use a query like this:

SELECT clients.* FROM clients WHERE active=True;

or

SELECT clients.* FROM clients WHERE active=-1;

but not

SELECT clients.* FROM clients WHERE active=1;

In addition, say I want to query the database using PDO I might use a prepared statement:

$db->prepare('SELECT clients.* FROM clients WHERE active=:isactive;');
$db->bindValue(':isactive', True); //Does not work
$db->bindValue(':isactive', 1); //Does not work
$db->bindValue(':isactive', -1); //Does work

So even though true works when sending a plain query to Access, if binding only -1 or 0 will work for boolean.

Why is this and why is -1 representative of true when 1 usually means true in other languages/databases?

harryg
  • 23,311
  • 45
  • 125
  • 198

3 Answers3

8

I can't seem to find the exact source this is from, but I remember reading about this a while ago on I think MSDN. This answer has a technical description of Visual Basic's boolean true, which applies to Access as well.

If I remember correctly, it's because -1 is represented in binary with every bit set to 1 (1111 1111), while +1 only has the least significant bit set to 1 with all the rest 0 (0000 0001). Because false is represented as 0 (0000 0000), it's very easy to change between true and false using a bitwise NOT, but if true was anything else, a bitwise NOT would result in something that isn't false. Also, using bitwise AND to check for truth on any truthy value would work, while if true was 0000 0001 it would not.

Community
  • 1
  • 1
jonhopkins
  • 3,844
  • 3
  • 27
  • 39
  • That seems very logical. Obviously Access can cope with being fed a `true` in a normal statement but binding queries cannot so creates a problem unless you feed it the proper value. `<>0` seems like a good workaround for `true` as @ChristianSpecht mentioned – harryg Jun 28 '13 at 15:33
  • Yea, Christian is correct about that. I just figured I'd answer the second part of your question. – jonhopkins Jun 28 '13 at 15:35
  • 1
    +1 Another way of looking at it is that a [two's complement](http://en.wikipedia.org/wiki/Two%27s_complement) signed integer that is only one bit long can only be one of two values: 0 or -1. – Gord Thompson Jun 28 '13 at 15:37
4

I don't know a better answer to "Why is this" than Dale Wilson already said in his answer, but it's quite simple to circumvent this problem:

Just get used to use WHERE active <> 0 in your queries.

I'm using MS Access (where True is -1) and MS SQL Server (where True is 1) and checking <> 0 is the easiest way that always works.

Community
  • 1
  • 1
Christian Specht
  • 35,843
  • 15
  • 128
  • 182
  • 1
    Or just `WHERE Active` (I haven't tested this with my SQL Server tables, but `WHERE ACTIVE` and `WHERE NOT ACTIVE` would do the trick I think – Scotch Jun 28 '13 at 17:46
  • @Scotch Yes, having just tested, it seems if you have a boolean field you can query it just by going `SELECT * FROM table WHERE booleanfield` which will give you all rows where `booleanfield = true`. – harryg Jul 01 '13 at 08:14
0

Not trying to be facetious, but the answer is "because that's the way they did it." Your statement that 1 usually means true in other languages is not correct. In C/C++ for example, false is defined as ==0 and true is defined as !=0.

That's why you can say if(pointer) { ...}

Dale Wilson
  • 9,166
  • 3
  • 34
  • 52
  • Fair enough but `1!=0` so should surely return true. This is not the case in my question - no results are given, whether true or false. Basically 1 != true or false – harryg Jun 28 '13 at 15:13
  • Rule of thumb: Test boolean values, don't compare them. For example, don't say if(testFunction() == true), just say if(testFunction()) This gets messy when you are trying to simulate exclusive or (if(function1() != function2()) The safe and correct way to do this is slightly more verbose: if function1() then ! function2() else function2() is guaranteed to call each function once and give you the exclusive or of the results. – Dale Wilson Jun 28 '13 at 19:27