Обсуждение: Issue with NULL varchars

Hi,

I'm relatively knew to PostgreSQL, but have a fair amount of
experience with JDBC and Oracle/SQL Server.

I'm running PostgreSQL 8.3.0 on Mac OSX 10.5.2, am using the 8.3-603
JDBC drivers, and using Java 1.5.0_13.

I'm a bit confused by the results of the following test case code:

----

import java.sql.*;

public class jdbcLocaleTest
{
  public static void main(String args[])
  {
    try
    {
      Driver driver = (Driver)
Class.forName("org.postgresql.Driver").newInstance();
      DriverManager.registerDriver(driver);
      Connection c =
DriverManager.getConnection("jdbc:postgresql://localhost/testdb",
"testdb", "");

      int count, id = 1;
      PreparedStatement p;
      ResultSet rs;

      String language = "en";
      String country  = "GB";
      String variant  = null;

      // Create a test table:
      p = c.prepareStatement("CREATE TABLE testing (id int, language
varchar(2), country varchar(2), variant varchar(64))");
      p.execute();

      // Create a new Locale in the database:
      p = c.prepareStatement("INSERT INTO testing (id, language,
country, variant) VALUES (?, ?, ?, ?)");
      p.setInt(1, id);
      p.setString(2, language);
      p.setString(3, country);
      p.setNull(4, java.sql.Types.VARCHAR);  // or p.setString(4, variant);
      count = p.executeUpdate();
      System.out.println("INSERT: inserted " + count + " rows.");

      // Retrieve that Locale by its ID:
      p = c.prepareStatement("SELECT language, country, variant FROM
testing WHERE id = ?");
      p.setInt(1, id);
      rs = p.executeQuery();
      while (rs.next())
      {
        System.out.println("SELECT by id: language=" + rs.getString(1)
+ ", country=" + rs.getString(2) + ", variant=" + rs.getString(3));
      }
      rs.close();

      // Retrieve that Locale's ID by its Data:
      p = c.prepareStatement("SELECT id FROM testing WHERE language =
? AND country = ? AND variant = ?");
      p.setString(1, language);
      p.setString(2, country);
      p.setNull(3, java.sql.Types.VARCHAR);  // or p.setString(3, variant);
      rs = p.executeQuery();
      while (rs.next())
      {
        System.out.println("SELECT by data: Got id=" + rs.getInt(1));
      }
      rs.close();

      // Retrieve that Locale's ID by its Data:
      p = c.prepareStatement("SELECT COUNT(*) FROM testing WHERE variant = ?");
      p.setNull(1, java.sql.Types.VARCHAR);    // or p.setString(1, variant);
      rs = p.executeQuery();
      while (rs.next())
      {
        System.out.println("SELECT COUNT: count=" + rs.getInt(1));
      }
      rs.close();

      // Drop the test table:
      p = c.prepareStatement("DROP TABLE testing");
      p.execute();

      p.close();
    }
    catch (Exception e)
    {
      System.out.println("Error: " + e);
    }
  }
}

----

The output, when I run this, is

INSERT: inserted 1 rows.
SELECT by id: language=en, country=GB, variant=null
SELECT COUNT: count=0

which implies that the table is created, the row is inserted, the row
is retrieved when selecting by its Id, but when we try and search for
anything with a NULL varchar value, nothing is returned.

I've also tried swapping the setNull statements with setString(n,
null) - same result.

What am I missing?!

Many thanks,

Ant.

antony baxter wrote:

You should probably enable query tracing, hand-execute the problem
queries, and examine what's going on that way.

However, if I'm not mistaken (which I could very easily be) your issue
is probably here:

>       p = c.prepareStatement("SELECT id FROM testing WHERE language =
> ? AND country = ? AND variant = ?");

If `variant' is NULL, this might be producing a query like:

SELECT id FROM testing WHERE language = 'en' AND country = 'GB' AND
variant = NULL ;

... which, because of the following rule for NULL equality:

craig=# SELECT (NULL = NULL) IS NULL;
 ?column?
----------
 t
(1 row)

won't match anything.

You probably wanted to say:

SELECT id FROM testing WHERE language 'en' AND country = 'GB' AND
variant IS NULL;


Yes, that's a pain.


It's possible to override this behaviour using a variable provided for
MS Access compatibility that treats 'NULL = NULL' as 't' instead of
NULL, but it might be better (more portable, safer, more
standards-compliant) to fix up the query.


> which implies that the table is created, the row is inserted, the row
> is retrieved when selecting by its Id, but when we try and search for
> anything with a NULL varchar value, nothing is returned.

If you:

SELECT * FROM testing;

you should see the inserted row, and if you execute the query your JDBC
layer generates from your parameterised query (which you can discover by
enabling query logging) it'll probably fail to match unless you rewrite
it to use IS NULL.

--
Craig Ringer

antony baxter wrote:

>       // Retrieve that Locale's ID by its Data:
>       p = c.prepareStatement("SELECT COUNT(*) FROM testing WHERE variant = ?");

To avoid conditionally rewriting your query to use IS NULL / IS NOT NULL
instead of equality, you could also use IS DISTINCT FROM. Instead of:

    where variant = ?

try using:

    where not (variant is distinct from ?)


craig=# \pset null '<null>'
Null display is "<null>".

craig=# select null = null;
 ?column?
----------
 <null>
(1 row)

craig=# select not( null is distinct from null );
 ?column?
----------
 t
(1 row)

craig=# select not (1 is distinct from 1);
 ?column?
----------
 t
(1 row)

craig=# select not (1 is distinct from 2);
 ?column?
----------
 f
(1 row)

craig=# select not (null is distinct from 2);
 ?column?
----------
 f
(1 row)


See:

http://www.postgresql.org/docs/8.3/static/functions-comparison.html

--
Craig Ringer

antony baxter wrote:
> I'm relatively knew to PostgreSQL, but have a fair amount of
> experience with JDBC and Oracle/SQL Server.
>
> I'm running PostgreSQL 8.3.0 on Mac OSX 10.5.2, am using the 8.3-603
> JDBC drivers, and using Java 1.5.0_13.
>
> I'm a bit confused by the results of the following test case code:

That is surprising, because the behaviour is the same on Oracle.
I ran your program there.
Can't say about SQL Server.

[...]
>       // Retrieve that Locale's ID by its Data:
>       p = c.prepareStatement("SELECT COUNT(*) FROM testing WHERE variant = ?");
>       p.setNull(1, java.sql.Types.VARCHAR);    // or p.setString(1, variant);
>       rs = p.executeQuery();
>       while (rs.next())
>       {
>         System.out.println("SELECT COUNT: count=" + rs.getInt(1));
>       }
>       rs.close();
[...]

> The output, when I run this, is
[...]
> SELECT COUNT: count=0
>
> which implies that the table is created, the row is inserted, the row
> is retrieved when selecting by its Id, but when we try and search for
> anything with a NULL varchar value, nothing is returned.
>
> I've also tried swapping the setNull statements with setString(n, null) - same result.
>
> What am I missing?!

The fact that "NULL = NULL" evaluates to NULL and not to TRUE.

You can run the statement from the command line, without JDBC, and you will
get the same result.

Maybe you wanted to use:

SELECT COUNT(*) FROM testing WHERE variant IS NULL;

which would return a nonzero result.

Yours,
Laurenz Albe