Description:
It's common knowledge that SQL officially frowns upon null equality as a matter of academic purity and tradition. However, there are plenty of scenarios where having to explicitly specify "IS NULL" instead of comparing a field's value to NULL is a pain.
One particular one involves iterating through a loop with a JDBC PreparedStatement, making repeated selections based upon object values that might be, but probably aren't, null (but should match "null" field values when the object itself has a value of null). Part of JDBC's appeal is the ability to dispense with having to write queries on the fly -- instead, building queries one time with placeholders, setting the placeholder values, and executing it. As such, it would be a tremendous convenience if there were a comparison operator that basically did the same thing as "=", but considered NULL=NULL to be true.
I'd like to suggest "==" as being the functional equivalent of "=", but evaluating comparisons between two null values as "true".
Offering "==" as a null-equality-recognizing alternative would make sense, because it's almost always programmers (as opposed to database analysts) who gripe about null non-equality (mainly, because most programming languages DO consider null to be equal to null). Using "==" would allow SQL-acculturated individuals to have the official behavior from "=" that ANSI compliance demands, but let programmers (who'd likely use "==" for just about everything anyway, even if they DIDN'T have the slightest clue how its behavior differed in a selection query from "=" just because it looked more familiar) have the behavior they intuitively EXPECT from "==".
It would also give MySQL a free competitive advantage over other database platforms. The fact is, if MySQL enabled truthful null-equality via "==", programmers WOULD use it... and would use it a lot. As a result, it would make switching to other database platforms FROM MySQL nearly impossible because it would break too many queries. Management could bitch about wanting to switch to Oracle so they could wave an expensive brand name in front of their shareholders... but faced with the cold reality of having to rewrite just about every app that touched the database, they wouldn't dare ;-)
How to repeat:
Sample usage scenario:
create table testtable (id integer, name text, phone text);
insert into testtable(id, name, phone) values (1, 'foo', '212-555-1212');
insert into testtable(id, name, phone) values (2, 'bar', null);
insert into testtable(id, name, phone) values (3, null, '305-555-1212');
now, in Java:
PreparedStatement sth = conn.prepareStatement("SELECT id, phone FROM testtable WHERE name == ?");
Iterator iterator = nameList.iterator();
while (iterator.hasNext()) {
String name = (String)iterator.next();
sth.setString(1, name);
ResultSet rs = sth.executeQuery();
// ...
// ...
}
with "==" treating null as equal to null, sth.setString(1, null) would cause the query to match record #3.
This is a trivial example, but it's motivated by a real-life scenario where I had to search a table for an exact match of 30 fields, some of which can be null. The lack of null equality turned an otherwise trivial selection exercise capable of executing quickly from a precompiled PreparedStatement into 3 pages of beastially-ugly code that had to rewrite the query for every selection, replacing tests for "foo = ? " with "foo is null " when the object being compared had a null value. I ended up getting some of the performance back by adding a crc field and searching for a checksum match before bothering to do a brute-force selection on every field... but still, it made something that's conceptually straightforward, sensible, and easy to do needlessly difficult and ugly.
Suggested fix:
add new comparison operator "==" as equality test that evaluates "null=null" to true.
Description: It's common knowledge that SQL officially frowns upon null equality as a matter of academic purity and tradition. However, there are plenty of scenarios where having to explicitly specify "IS NULL" instead of comparing a field's value to NULL is a pain. One particular one involves iterating through a loop with a JDBC PreparedStatement, making repeated selections based upon object values that might be, but probably aren't, null (but should match "null" field values when the object itself has a value of null). Part of JDBC's appeal is the ability to dispense with having to write queries on the fly -- instead, building queries one time with placeholders, setting the placeholder values, and executing it. As such, it would be a tremendous convenience if there were a comparison operator that basically did the same thing as "=", but considered NULL=NULL to be true. I'd like to suggest "==" as being the functional equivalent of "=", but evaluating comparisons between two null values as "true". Offering "==" as a null-equality-recognizing alternative would make sense, because it's almost always programmers (as opposed to database analysts) who gripe about null non-equality (mainly, because most programming languages DO consider null to be equal to null). Using "==" would allow SQL-acculturated individuals to have the official behavior from "=" that ANSI compliance demands, but let programmers (who'd likely use "==" for just about everything anyway, even if they DIDN'T have the slightest clue how its behavior differed in a selection query from "=" just because it looked more familiar) have the behavior they intuitively EXPECT from "==". It would also give MySQL a free competitive advantage over other database platforms. The fact is, if MySQL enabled truthful null-equality via "==", programmers WOULD use it... and would use it a lot. As a result, it would make switching to other database platforms FROM MySQL nearly impossible because it would break too many queries. Management could bitch about wanting to switch to Oracle so they could wave an expensive brand name in front of their shareholders... but faced with the cold reality of having to rewrite just about every app that touched the database, they wouldn't dare ;-) How to repeat: Sample usage scenario: create table testtable (id integer, name text, phone text); insert into testtable(id, name, phone) values (1, 'foo', '212-555-1212'); insert into testtable(id, name, phone) values (2, 'bar', null); insert into testtable(id, name, phone) values (3, null, '305-555-1212'); now, in Java: PreparedStatement sth = conn.prepareStatement("SELECT id, phone FROM testtable WHERE name == ?"); Iterator iterator = nameList.iterator(); while (iterator.hasNext()) { String name = (String)iterator.next(); sth.setString(1, name); ResultSet rs = sth.executeQuery(); // ... // ... } with "==" treating null as equal to null, sth.setString(1, null) would cause the query to match record #3. This is a trivial example, but it's motivated by a real-life scenario where I had to search a table for an exact match of 30 fields, some of which can be null. The lack of null equality turned an otherwise trivial selection exercise capable of executing quickly from a precompiled PreparedStatement into 3 pages of beastially-ugly code that had to rewrite the query for every selection, replacing tests for "foo = ? " with "foo is null " when the object being compared had a null value. I ended up getting some of the performance back by adding a crc field and searching for a checksum match before bothering to do a brute-force selection on every field... but still, it made something that's conceptually straightforward, sensible, and easy to do needlessly difficult and ugly. Suggested fix: add new comparison operator "==" as equality test that evaluates "null=null" to true.