JOIN with mixed data types fails on pgsql 8.3

Oops, it looks like the wrong patch was copied over from the other issue. Here is the correct patch (from http://drupal.org/node/220064#comment-827368). This patch seems to work, and the reasons why it should be OK are discussed at http://drupal.org/node/220064#comment-827364... however, it needs one more reviewer before it's RTBC.

Also, if the JOIN really does turn out to be unnecessary, then it shouldn't be in 7.x either, so let's start there and then backport...

I attached the patch and then it disappeared... so here it is again ;)

Both this and http://drupal.org/node/220064 can go into D7; they don't conflict with each other at all. The reason for the patch here is that it's an unnecessary database JOIN that serves no purpose, that's all (in addition to the critical bug).

However, it's definitely much more time-sensitive for D6, so I suppose we can leave it here and then it can be backported ("forwardported"?) to D7 later...

Tested the:

#3
David_Rothstein - May 20, 2008 - 15:18

I attached the patch and then it disappeared... so here it is again ;)

Attachment Size
block-box-get.patch 1.56 KB

And it worked, everything seems ok now.
(and the sql statement appears to be more "cleaner" and optimized)

Thks

I know that this may not belong to this issue, but is regarding pgsql 8.3

please see this http://drupal.org/node/263737

#263737 has nothing to do with this, but is another critical bug for PostgreSQL (that definitely needs more testers).

Tested #3, which fixes the bug for me. I can now access /admin/build/block/configure/block/[bid] without errors. My platform is:

• Drupal 6.2
• PostgreSQL 8.3.1-1
• Apache 2.2.8-1ubuntu0.1
• Ubuntu 8.04

Per David_Rothstein's remark at http://drupal.org/node/220064#comment-850768 this seems to be RTBC.

Thanks for all the effort in #220064: Ensure block delta is class safe and this issue to improve PostgreSQL support!

Committed to Drupal 6, thanks!

Oops, this one was supposed to go into Drupal 5.x too.

Attached is the patch for 5.x; the functions that use this code are essentially the same in 5.x and 6.x, and I have tested that admin-created blocks still work fine after this patch. So I think it's RTBC.

Committed to 5.x.

I ran into this again today. Running Drupal 5.14 with PostgreSQL 8.3.3. The previous patch in #13 still use delta = %d in block_box_delete_submit function. I've check the schema and it's a varchar and hence the error when deleting the block.

\d blocks
                              Table "public.blocks"
   Column   |          Type          |                 Modifiers                  
------------+------------------------+--------------------------------------------
 module     | character varying(64)  | not null default ''::character varying
 delta      | character varying(32)  | not null default '0'::character varying
 theme      | character varying(255) | not null default ''::character varying

The code suppose to be:-

db_query("DELETE FROM {blocks} WHERE module = 'block' AND delta = '%s'", $form_values['bid']);

I try to run update.php in case there's schema changes but no update available from 5.14 upgrade. To reproduce, I just add a new block through admin/build/block and then delete the block. The block is deleted but an error message displayed:-

warning: pg_query() [function.pg-query]: Query failed: ERROR: operator does not exist: character varying = integer LINE 1: DELETE FROM blocks WHERE module = 'block' AND delta = 24 ^ HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.

Thanks! I verified that the patch makes sense and works (and also verified that this issue is limited to Drupal 5.x only).

As a tip for the future, please set issues to 'patch (code needs review)' when you provide a patch that is ready for review. A lot of people don't pay attention to an issue if it's still marked as 'closed'...

Committed to 5.x.