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

AlexisWilke’s picture

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.
AlexisWilke’s picture

Okay, I checked in my fix. See here:

http://drupalcode.org/project/spam.git/commit/4371ba0

killes@www.drop.org’s picture

Actually, I had not missed these but left them as they were with intention: the comment id will always be the cid which is numeric. Would that have caused problems?

AlexisWilke’s picture

Yes. PostgreSQL fails with an error when one column is varchar and the other is integer. It does no auto-cast like MySQL does (not in this case) as shown in my post #1 (very last entry is the test that fails.)

killes@www.drop.org’s picture

Status: Active » Fixed

Ok, thanks, got it now!

Status: Fixed » Closed (fixed)

Automatically closed -- issue fixed for 2 weeks with no activity.

Liam Morland’s picture

Issue tags: +PostgreSQL