content_id changed to varchar causing problems with PostgreSQL... [#1213246]

Hi Killes,

In the latest changes you made, you transformed the content_id to a varchar type.

This is fine in most cases, although you missed a few SQL statements in the content folder in that regard...

The problem here is that some of these statements look like this:

return 'INNER JOIN {comments} c ON t.content_id = c.cid';
return 'INNER JOIN {node} n ON t.content_id = n.nid';
return 'INNER JOIN {users} u ON t.content_id = u.uid';

The other statements are easy to fix since they use the %d instead of the expected %s now that's a string.

How do you propose we fix those incompatible types problems? Would a CAST() work across RDBMs?

Thank you.
Alexis Wilke

Comments

Comment #1

AlexisWilke commented 8 July 2011 at 19:43

Some testing with CAST...

PostgreSQL using just 'char' breaks:

pgsql> select cast(123 as char);
 bpchar 
--------
 1
(1 row)

However, in MySQL it works great. You get 123 as expected (obviously, it can be interpreted as wrong since CHAR represents 1 character!).

mysql> select cast(123 as char);
+-------------------+
| cast(123 as char) |
+-------------------+
| 123               |
+-------------------+
1 row in set (0.00 sec)

To fix the problem in PostgreSQL, we can use CHAR(32) or VARCHAR(32). The size is understood although not necessary in VARCHAR:

pgsql> select cast(123 as char(32));
              bpchar              
----------------------------------
 123                             
(1 row)

pgsql> select cast(123 as varchar(32));
 varchar 
---------
 123
(1 row)

The CHAR(32) will cause problems as it adds all those spaces...

Testing the VARCHAR in MySQL, it breaks:

mysql> select cast(123 as varchar);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'varchar)' at line 1
mysql> select cast(123 as varchar(32));
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'varchar(32))' at line 1

The CHAR(32) works, however:

mysql> select cast(123 as char(32));
+-----------------------+
| cast(123 as char(32)) |
+-----------------------+
| 123                   |
+-----------------------+
1 row in set (0.00 sec)

And like the PostgreSQL database it generates spaces after the 123.

So, using CHAR(32) would be the solution... I thus compared with the string '123' to see whether it would match in PostgreSQL and MySQL:

pgsql# select cast(123 as char(32)) = '123';
 ?column? 
----------
 t
(1 row)

mysql> select cast(123 as char(32)) = '123';
+-------------------------------+
| cast(123 as char(32)) = '123' |
+-------------------------------+
|                             1 |
+-------------------------------+
1 row in set (0.00 sec)

And both returned true ('t' is true in PostgreSQL.)

Thus, I suggest we fix the 3 problematic INNER JOIN tests using a CAST with VAR(32). This may not work well in Drupal 7.x+ which supports many other database systems.

Thank you.
Alexis Wilke

P.S. Strange behavior of direct compare versus column compare:

-- Working?!
pgsql> select '123' = 123;
 ?column? 
----------
 t
(1 row)

-- Not working!
pgsql> select sid, content_id from spam_tracker s, comments c where sid > 10 and content_id = c.cid;
ERROR:  operator does not exist: character varying = integer
LINE 1: ...tracker s, comments c where sid > 10 and content_id = c.cid;
                                                               ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.