Null values can cause unexpected results in SQL subquery

I was recently using a SQL query to examine data in my PostgreSQL 9.3 database, and the results were not what I expected. It took a while to figure out that this due to using a subquery (also known as an inner query or nested query) that involved NULL values.

I have a table users, like this:

And a table addresses, like this:

In addresses, the user_id column indicates the user -- if any -- to whom the address belongs. I want to find any users who don't have an address. In this example, that would be betty: her user_id, 4, doesn't appear anywhere in addresses. I thought this query would do the job:

select * from users where user_id not in

(select distinct user_id from addresses)

But that query returns no rows! Here's why. Note that the row in addresses with address_id = 5 has a NULL user_id. Therefore, the query is equivalent to:

select * from users where user_id not in (1, 2, 3, NULL)

This, in turn, is equivalent to:

select * from users where user_id <> 1 and user_id <> 2 and user_id <> 3

and user_id <> NULL

In SQL, no comparison to NULL is ever true when using the = or <> operator. So no row matches the query. (The IS operator is proper to use for comparisons to NULL.)

We can avoid this problem by excluding NULL values in our subquery:

select * from users where user_id not in

(select distinct user_id from addresses where user_id is not null)

Or by using COALESCE to convert any NULL values to something we can compare against an integer:

select * from users where user_id not in

(select distinct coalesce(user_id, -1) from addresses)

Either solution will cause the query to output betty's row of the addresses table, as desired.

Installing a Belkin N150 wireless network adapter on Ubuntu 14.04 LTS

I'm a longtime Windows user who also has some experience with Linux. This week, I'm taking the plunge and configuring a desktop system with Ubuntu that I plan to use daily for software development and management tasks. My first challenge was getting connected to the network. I purchased a Belkin N150 wireless network adapter. It came with a driver installation CD for Windows, but my computer has neither Windows nor a CD drive! Luckily, I found some good instructions thanks to theharrylucas. I'll just add a couple of points here:

• The instructions are from 2011, so -- like most of the other info I found on this topic -- they pertain to older versions of Ubuntu. However, they worked fine for 14.04.
• The instructions refer to the device ID 050d:935a. My device ID is slightly different, 050d:945a. Nonetheless, the instructions worked as is.

Handling null values in a SQLAlchemy query - equivalent of isnull, nullif or coalesce

SQLAlchemy allows you to use the equivalent of SQL's COALESCE function to handle NULL values. I didn't find the documentation for this easy to understand, so here's a quick tutorial...


Depending on which database management system you're familiar with, you might have used ISNULL, NULLIF or COALESCE in a query to return a specific value in case the database column contains NULL.

My example concerns a PostgreSQL database with a table named CONFIG. I want to find a record that was least recently processed, according to its LAST_PROCESSED_AT column, or was never processed, indicated by LAST_PROCESSED_AT = NULL.

Here's the result of the query

select name, last_processed_at from config:

Note that the third row has a null value for LAST_PROCESSED_AT. Here's a revised query using SQL's coalesce function to map NULLs to the earliest date recognized by PostgreSQL:

select name, coalesce(last_processed_at, to_timestamp(0)) from config

Note the effect on the third row:

Armed with COALESCE, I can find the record I'm looking for with this query:

select name from config order by coalesce(last_processed_at, to_timestamp(0)) limit 1

The question is how to do the same thing with Python and SQLAlchemy. And the answer is this:

import datetime

from sqlalchemy.sql.functions import coalesce

my_config = session.query(Config).order_by(coalesce(Config.last_processed_at, datetime.date.min)).first()

Python Flask - passing JSON to a template in a variable

I ran into a problem in my Python Flask app. I wanted to store some JSON-formatted data in a Python variable, and use JQuery's parseJSON function to consume that data inside a Jinja template. When I tried to parse the JSON in the template, a JavaScript error resulted. I figured out why, and how to work around it.

By the way, the same problem occurs, and the same workaround applies, whether you pass the variable in the call to render_template, or use a session variable.

Here's a copy of my first attempt at the code...

Python:

def index():
        return render_template('viewer_type.html', myVar='{"abc": "123", "def": "456"}')

JavaScript:

<script src='jquery-1.10.0.min.js></script>
<script>
        var myVar = "{{ myVar }}";
        var myJSON = $.parseJSON(myVar);
        prompt("abc", myJSON.abc);
</script>

This failed. prompt never executed, and Chrome's JavaScript console reported this error.

Uncaught SyntaxError: Unexpected token &

I realized that the contents of the variable were being encoded when Flask passed them to the template. Adding this line of JavaScript...

prompt("myVar", myVar);

... revealed that my JSON had turned into:

{"abc": "123", "def": "456"}

Thanks to this helpful post on stackoverflow, I found a convenient way to decode the encoded text in JavaScript. I encapsulated that in a function:

function decodeJSON(encodedJSON) {
            var decodedJSON = $('<div/>').html(encodedJSON).text();
            return $.parseJSON(decodedJSON);
}

Then I just needed to amend my original JavaScript to call decodeJSON, so the whole thing looks like this:

<script src='jquery-1.10.0.min.js></script>
<script>
 function decodeJSON(encodedJSON) {
  var decodedJSON = $('<div/>').html(encodedJSON).text();
  return $.parseJSON(decodedJSON);
 }

 var myVar = '{{ myVar }}';
 prompt('abc', decodeJSON(myVar).abc);
</script>

Success! The expected output, 123, was displayed, and there were no more errors in the JavaScript console.

Iptables settings can interfere with apt-get update

I ran into a problem on my Ubuntu 12.04.3 box today. I issued the command sudo apt-get update. This usually works without a hitch, but today, it obtained a few of the updates successfully but gave 404 errors for many others. Here's an example:

Err http://security.ubuntu.com precise-security/main Sources
  404  Not Found [IP: 91.189.92.200 80]


After a little thought, I realized how this Ubuntu box differed from others I had worked on: I had used iptables to facilitate running Apache Tomcat on ports 80 and 443. (Because these port numbers are below 1024, Tomcat would have to run as a privileged user to access them, and iptables provides a well-known workaround.)

The particular iptables rule that caused the problem was this one:

sudo iptables -t nat -I OUTPUT -p tcp --dport 80 -j REDIRECT --to-ports 8080


By temporarily deleting that rule (which can be done by replacing -I with -D in the above command), I got sudo apt-get update to work.

Running Tomcat 7 on port 80 on Ubuntu 12.04.3

The goal:

• Commission a new Amazon EC2 Instance running Ubuntu 12.04.3.
• Install Apache Tomcat 7 and everything Tomcat requires to run.
• Make Tomcat respond to requests on port 80.

How this differs from other tutorials you might have encountered:

• It's for Ubuntu, not some other flavor of Linux where you can just set AUTHBIND=yes in an /etc/defaults/tomcat7 file.
• It's for Tomcat 7, not some earlier version.
• It doesn't just tell you to edit Tomcat's server.xml file, ignoring the fact that Ubuntu won't let a non-privileged user bind to ports below 1024.
• It doesn't suggest running Tomcat as root, ignoring any resulting security concerns.

How to do it:

• Create a new AWS EC2 Instance.
• Select the AMI Ubuntu Server 12.04.3 LTS, 64-bit.
• Use or create a security group that enables (at least) ports 22 and 80 for your IP address.
• Use an SSH client to connect to the Instance. Most of the remaining steps will be performed in the SSH client.
• Install Tomcat 7: 

wget http://apache.mirrors.lucidnetworks.net/tomcat/tomcat-7/v7.0.50/bin/apache-tomcat-7.0.50.tar.gz

tar -xzvf apache-tomcat-7.0.50.tar.gz

rm apache-tomcat-7.0.50.tar.gz

export CATALINA_HOME=/home/ubuntu/apache-tomcat-7.0.50

export CATALINA_BASE=$CATALINA_HOME

• Install JRE 7. Unfortunately, Oracle requires you to click to accept a license agreement, which you can't do from a headless server. So use another computer to visit http://www.oracle.com/technetwork/java/javase/downloads/server-jre7-downloads-1931105.html, download server-jre-7u51-linux-x64.tar.gz. Find a way to get this file to your home directory on the EC2 Instance. One possibility is to install an FTP or SFTP server on the Instance. Once the file is in your home directory:

cd ~ tar -xzvf /sftp/stevetest/incoming/server-jre-7u51-linux-x64.gz

export JAVA_HOME=/home/ubuntu/jdk1.7.0_51  

• Use iptables to redirect requests on port 80 to port 8080

sudo iptables -t nat -I PREROUTING -p tcp --dport 80 -j REDIRECT --to-ports 8080

sudo iptables -t nat -I OUTPUT -p tcp --dport 80 -j REDIRECT --to-ports 8080

• Start Tomcat:

cd ~/apache-tomcat-7.0.50/bin ./startup.sh

• Now if you type the EC2 Instance's IP address into your browser's address bar, you should see the Tomcat welcome page. No need to specify port 8080!

A tip of the hat goes to Tomcat: The Definitive Guide by Jason Brittain and Ian F. Darwin for a clear explanation of how to use iptables.

In future installments, I hope to cover setting up the server to automatically export the environment variables and start Tomcat when booted, and to enable SSL on port 443.

Modern technology frees desk space

At home, I use a low-cost computer as a basic file and print server. Family members store files such as photos and schoolwork in a shared folder, which is automatically backed up by Carbonite. And an all-in-one printer/scanner is attached via a USB cable for everyone's use. I realize nearly the same thing could be accomplished by using cloud storage and a wireless printer, but my old-fashioned solution is convenient and inexpensive.

For almost ten years, an obsolete desktop computer filled this role in my house. Its slow CPU and minimal RAM didn't have much of a negative effect, since copying small files and printing small documents aren't very taxing. But it sure took up a lot of desk space!

What finally motivated me to replace this old workhorse was when the hard disk, which had a capacity of only 80 GB, got full. After a few days of searching, I found a suitable used laptop for sale on eBay. It's an Acer Aspire V5 with a 11.6-inch screen, 1.5 GHz Celeron dual-core processor, 4 GB RAM, 500 GB hard drive and Windows 8.

With shipping, the cost was around $165.

I was worried that transferring all my files and software to the new computer would be an onerous chore, but it ended up going smoothly and only took a few hours. The steps were:

• Connect to my home wireless network. Windows 8 automatically detected the network, prompted for the key, and connected without a hitch.
• Create a homegroup and a shared folder with read/write access.
• Download and install the printer driver.
• Copy all the files from the old computer.
• Log in to my Carbonite account and transfer the backup to the new computer.

So far, everything is working well. The screen and keyboard are tiny, but since I'll almost never use them, that's actually an advantage. Look at all the desk space I gained!

Java operator precedence can cause unexpected output

My Java program needed to determine whether a particular variable was null. I wasn't getting the expected results, so I added a System.out.println call to print some diagnostic output to the console. The diagnostic output wasn't what I expected, either. It turned out to have to do with operator precedence in Java. Here's some example code that illustrates the problem:

public class Example {
 public static void main(String[] args) {
  Integer n = 1;
  System.out.println("Is n null? " + n == null);
 }
}

I expected the output to be: Is n null? false. Instead, it was just the single word false. Here's why:

The additive operator + has higher precedence than the equality operator ==. So Java was evaluating the string concatenation

"Is n null? " + n

and then comparing the result to null. The result of that comparison was the boolean value false, which is what was displayed. This was easily fixed with the addition of a couple of parentheses:

System.out.println("Is n null? " + (n == null));

Interestingly, if I'd been comparing n to some integer rather than to null, the compiler would have caught the problem for me. This code

public class Example {
 public static void main(String[] args) {
  Integer n = 1;
  System.out.println("Is n one? " + n == 1);
 }
}

causes a compile-time error, Incompatible operand types String and int.

The moral of the story is, whatever language you're programming in, be aware of operator precedence!