Wednesday, March 18, 2015

Understanding SQL's Null

 

Tony Hoare invented the null reference in 1965, and he considers it his "billion-dollar mistake" for the amount of trouble it has caused. Even today, SQL's null value is the cause of several common mistakes.

Let’s go over the most egregious.

Equals Null

These two queries return the exact same result on a users table with many rows:

select * from users where deleted_at = null;
-- result: 0 rows

select * from users where deleted_at != null;
-- result: 0 rows

How can that be? It's because null represents an "unknown" type. This means it doesn't make sense to compare null to anything else with normal conditional operators. Null isn't even equal to itself:

select null > 0;
-- result: null

select null < 0;
-- result: null

select null = 0;
-- result: null

select null = null;
-- result: null

The right way to compare values with null is with the is, and is not operators:

select * from users 
where deleted_at is null;
-- result: all non-deleted users

select * from users
where deleted_at is not null;
-- result: all deleted users

If you want to check if two columns are different, you can use is distinct from:

select * from users
where has_address is distinct from has_photo
-- result: users with an address or
-- photo, but not both

Not in Null

One handy way to filter rows is with a subselect. For example, if you wanted the userswho did not have any packages, you could write a query like this:

select * from users 
where id not in (select user_id from packages)

But if one of the rows in packages has a null user_id, this query will return no results! To understand why this happens we need to factor the query like the SQL compiler does. Here's a simpler example:

select * from users 
where id not in (1, 2, null)

Which translates to:

select * from users 
where id != 1 and id != 2 and id != null

As we now know, id != null is an unknown value, null. Using and on any value withnull becomes null, so all of the other and conditions fall away. No rows match the resulting query since null is not equal to true.

If the condition is inverted, the query works fine. This time we'll look for users withpackages.

select * from users 
where id in (select user_id from packages)

Which we can simplify for the example:

select * from users 
where id in (1, 2, null)

This query translates to:

select * from users 
where id = 1 or id = 2 or id = null

Since the where clause is a list of or conditions, it doesn't matter that one of them is null. That condition is simply ignored because non-true values do not change the evaluation of the rest of the clause.

Sorting Nulls

When it comes to sorting, nulls are considered the largest possible value. This can lead to some frustrating queries when trying to sort values descending, since all the nulls will be on top.

This query is meant to show the users ranked by their points, but it's putting users without any points first!

select name, points
from users
order by 2 desc;
-- null points sort above
-- any number of points!

There are two ways to deal with this. The easiest way is to get rid of those nulls in the output or the comparison using coalesce:

-- treat nulls as 0 in output
select name, coalesce(points, 0)
from users
order by 2 desc;

-- keep nulls in output, but sort as 0
select name, points
from users
order by coalesce(points, 0) desc;

And if your database supports it, you can instead tell it where to put nulls when sorting with nulls first or nulls last:

select name, coalesce(points, 0)
from users
order by 2 desc nulls last;

Of course, nulls can also be used to prevent errors. One great use of nulls is in dealing with divide by zero errors.

Divide by Zero

Divide by zero errors are especially painful. Queries that ran fine yesterday all of a sudden are failing with divide by zero errors. One common solution is to check if the denominator is 0 before dividing with a case statement:

select case when num_users = 0 then 0 
else total_sales/num_users end;

The case statement approach is verbose and duplicates the denominator. That's OK if the denominator is simple, but if it's an expression, you're likely to get more bugs if you change the query later.

Here we can use null to our advantage. Use nullif on the denominator to make the denominator null instead of zero. Rather than failing, the query will return null on days where num_users = 0.

select total_sales/nullif(num_users, 0);

If you prefer the result to be 0 or anything else instead of null, use coalesce on the previous query:

select coalesce(total_sales/nullif(num_users, 0), 0);
-- nulls results become 0

Conclusion

Tony Hoare may regret his mistake, but at least it’s easy to work around the issues that null presents. Now go forth with your new knowledge and keep null from nullifying your future query results!

Monday, March 2, 2015

TBE: Enabling General Application in Career Web Site

 

Taleo Business Edition (TBE) is Oracle's recruiting application offering for small to mid-sized companies. It does not have the full feature set available in Tale Enterprise Edition, and the system is deliberately watered down to allow quick setup and faster go-live. The system is also not well documented, and sometimes you have to hunt across the application to implement the simplest settings.

Like when we tried to turn on General Application in the Career Web Site.

1: Go to Administration -> Customize Recruit. Click on "Careers Website Pages"

tmpDE81 2:  Edit the Job Search Pages.

tmpE97A

3: In the next page, just unhide the two entries for "General Application" and its header section.

tmp723A

 

This is weird, because we expected some kind of global parameter which can be set to turn on such features. Instead, this configuration change is the only way to turn this on.

Sunday, March 1, 2015

25 Funny Situations Of A Programmer's Life

 

1. When the project manager enters the office

2. When I try to fix a bug at 3 in the morning


3. When I'm told that the module on which I have worked all the week will never be used

4. When the code that I have not tested on dev works perfectly in production

5. When the sales people announce they have sold our product to the customer

6. When sysadmin finally gives us the root access

7. When I launch my script for the first time after several hours of development

8. When I go off for the weekend while everyone else is still trying to fix bugs

9. When the boss is looking for someone to urgently fix a difficult bug

10. When a thing that worked on Friday no longer works on Monday

11. When a bug goes unnoticed during a presentation

12. When a newbie suggests to add a new feature to project

13. When I realize that I have been blocked for two hours because of a forgotten semicolon

14. When the project manager suddenly looks on my screen

15. When customer wants to change specification 2 days before pushing to production

16. When my script finally worked

17. When I'm told that my code is broken in production

18. When I find a solution without searching Google

19. When the intern tells me that "the tests are for those who can not program"

20. When I manage to replace 200 lines of the algorithm by only 10 lines

21. When I return to development of my code that wasn't commented

22. When they tell me the website has to be supported by IE6

23. When a misformed sql query actually returns me the correct results

24. When I start coding without doing analysis first

25. When project manager thinks that I can handle whole project all by myself

Oracle Cloud Support

 

Now that Oracle is a full fledged Cloud company, certain changes can be seen across their sites, letting visitors known that they tend to move to the cloud full on. If you are using an Oracle cloud product, you will see changes in their supportweb system. Logging into Suppotweb with the OPN of your cloud service takes you a new site, with the text "Oracle Cloud Support" on the top.

 

image

This new site has a minimalistic design, even the process of creating a Service Request is easier.

 

image

Clicking on the "Knowledge" tab , the search page is toned down. Users can choose to search in the realm of their cloud product, or search in "All Knowledge" for fill supportweb content.

image

Siebel IP 18 - 2018 SOD

Oracle is going ahead and staying with Siebel. For now. They have just released the statement of direction for IP 2018. At a high level th...

Randomizer

On Stackoverflow

You might also like...