The following bug has been logged online:
Bug reference: 1268
Logged by: Kent Tong
Email address: kent@cpttm.org.mo
PostgreSQL version: 7.4.5
Operating system: RedHat 9
Description: Two different Unicode chars are treated as equal in a
query
Details:
Steps:
1. Create a test database: "createdb -E Unicode -U postgres testdb".
2. Create a test table: "create table testtable (id varchar(100) primary
key);".
3. With JDBC, insert a record whose id contains unicode: "insert into
testtable values(<a unicode char whose code is 0x4e8c>);".
4. With JDBC, try to retrieve a record whose id contains a different unicde:
"select from testtable where id=<a unicode char whose code is 0x4e94>;". It
should not find any record but it finds the record created in step 3.
Here is the JUnit test case:
public class PgSQLTest extends TestCase {
private Connection conn;
protected void setUp() throws Exception {
conn = makeConnection();
}
protected void tearDown() throws Exception {
conn.close();
}
public void testChinese() throws Exception {
deleteAll();
insertRow();
PreparedStatement st =
conn.prepareStatement("select * from testtable where id=?");
try {
st.setString(1, "\u4e94");
ResultSet rs = st.executeQuery();
assertFalse(rs.next());
} finally {
st.close();
}
}
private void insertRow() throws SQLException {
PreparedStatement st =
conn.prepareStatement("insert into testtable values(?)");
st.setString(1, "\u4e8c");
st.executeUpdate();
st.close();
}
private void deleteAll() throws SQLException {
PreparedStatement st = conn.prepareStatement("delete from testtable");
st.executeUpdate();
st.close();
}
private Connection makeConnection()
throws ClassNotFoundException, SQLException {
Class.forName("org.postgresql.Driver");
Properties properties = new Properties();
properties.put("user", "postgres");
properties.put("password", "");
return DriverManager.getConnection(
"jdbc:postgresql://localhost/testdb",
properties);
}
}