Support for Drupal 7 is ending on 5 January 2025—it’s time to migrate to Drupal 10! Learn about the many benefits of Drupal 10 and find migration tools in our resource center.
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 CreditAttribution: AlexisWilke commentedSome testing with CAST...
PostgreSQL using just 'char' breaks:
However, in MySQL it works great. You get 123 as expected (obviously, it can be interpreted as wrong since CHAR represents 1 character!).
To fix the problem in PostgreSQL, we can use CHAR(32) or VARCHAR(32). The size is understood although not necessary in VARCHAR:
The CHAR(32) will cause problems as it adds all those spaces...
Testing the VARCHAR in MySQL, it breaks:
The CHAR(32) works, however:
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:
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:
Comment #2
AlexisWilke CreditAttribution: AlexisWilke commentedOkay, I checked in my fix. See here:
http://drupalcode.org/project/spam.git/commit/4371ba0
Comment #3
killes@www.drop.org CreditAttribution: killes@www.drop.org commentedActually, 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?
Comment #4
AlexisWilke CreditAttribution: AlexisWilke commentedYes. 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.)
Comment #5
killes@www.drop.org CreditAttribution: killes@www.drop.org commentedOk, thanks, got it now!
Comment #7
Liam Morland