Serendipity Forums

[sasek]

Hi

I've installed Serendipity on my server with apache, mod_rewrite an PostgreSQL. Everything work just fine, but when I click on the title of the entry I get 'No entries to print' message instead of the entry view. I tried switching mod_rewrite off, but it didn't help. How can I fix it?

SQL log entry looks like this:

Code: Select all

ERROR:  operator does not exist: integer ~~ unknown at character 1559
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.
STATEMENT:  SELECT  e.id,
                                    e.title,
                                    e.timestamp,
                                    e.body,
                                    e.comments,
                                    e.trackbacks,
                                    e.extended,
                                    e.exflag,
                                    e.authorid,
                                    e.isdraft,
                                    e.allow_comments,
                                    e.last_modified,
                                    e.moderate_comments,

                                    a.realname AS author,
                                    a.username AS loginname,
                                    a.email
                              FROM
                                    serendipity_entries e
                         LEFT JOIN  serendipity_authors a
                                ON  e.authorid = a.authorid
                                     LEFT JOIN serendipity_entrycat ec
                                                   ON e.id = ec.entryid LEFT JOIN serendipity_category c
                                               ON ec.categoryid = c.categoryid LEFT JOIN serendipity_authorgroups AS acl_a
                                           ON acl_a.authorid = 2
                                    LEFT JOIN serendipity_access AS acl_acc
                                           ON (    acl_acc.artifact_mode = 'read'
                                               AND acl_acc.artifact_type = 'category'
                                               AND acl_acc.artifact_id   = c.categoryid
                                              )
                             WHERE
                                    e.id LIKE '9'
                                     AND e.isdraft = 'false'  AND e.timestamp <= 1204208700 AND     (
                                         c.categoryid IS NULL
                                         OR ( acl_acc.groupid = acl_a.groupid OR acl_acc.groupid = 0)
                                         OR ( acl_acc.artifact_id IS NULL
                                             
                                            )
                                       )
                             LIMIT  1

[garvinhicking]

Hi!

Hm, is this maybe an oddity of your PGSQL setup? It sounds as if it cannot execute one of the WHERE operators. But which one, can't PGSQL tell us that?

does it work if you replace in the query

Code: Select all

e.id LIKE '9'


with

Code: Select all

e.id = 9


if it does, I wonder why other people can use the same query on pgsql, I bet it's a config option of pgsql whether to enforce strict or unstrict type casting?

Regards,
Garvin

[judebert]

That's an unhelpful error message. I don't see "integer", "~~", or "unknown" anywhere in that SQL statement.

When I massage the statement into a single line, character 1559 can be a blank, or the middle of "acl_acc.artifact_type", depending on what I do with newlines. That's the character right after the . on acl_acc.artifact_type.

Does serendipity_access exist in your database? Is it populated?

I'm just guessing until someone more knowledgeable arrives to help.

[judebert]

And of course, by the time I do all that work, somebody more knowledgeable has answered. Stick with Garvin, disregard my post.

[sasek]

Garvin: I think, that's the problem. How can I change type casting i pg? Can't find the option...

[garvinhicking]

Hi!

Garvin: I think, that's the problem. How can I change type casting i pg? Can't find the option...

What do you mean? Did you try the SQL?

I don't know anything about pgsql.

Regards,
Garvin

[sasek]

I did. It works with '=', but I have no idea how to change type casting method. There's nothing about it in pg's docs. Maybe changing the query might help. I'll try to figure it out.

I've found the same problem when searching entries via calendar.

Do you know, where this query is generated?

[garvinhicking]

Hi!

Hm, there are more often parts in the s9y code where "LIKE 'integer'" is used. This works for all past pstgresql users. Maybe something changed in pgsql just recently so that it no longer works?

Using my 7.4 installation at home, the 'LIKE'-query doesn't make a problem, it'S the debian default pgsql installation from some time ago.

Regards
Garvin

[sasek]

I'm using postgres 8.3 and it doesn't work And I can't locate place, where the query is built... I'll stay in toutch.

[sasek]

Done

You have to change 'LIKE' in line 521 of include/functions_entries.inc.php to '=' and it does work. Meanwhile i've done some tests on previous versions of postgres (default ubuntu package). The "LIKE int" have worked until ver 8.1. Thanks for your help

[garvinhicking]

Hi!

Sigh. I bet there are more places in the s9y code where a LIKE syntax is used. Maybe you can find out how to re-enable the LIKE int syntax in PGSQL 8.1+? I wonder what the reasoning was to remove that behaviour, many cross-DB PHP applications will get problems witht that.

Regards,Garvin

[judebert]

I found some online information about pgsql and type conversions.

First off, the error message makes much more sense if you know that "~~" is equivalent to "LIKE". So it's complaining that there is no operator "LIKE" that can be applied to integers.

The manual states that type conversion will occur automatically. It specifically covers numeric-to-string conversion for operators. I don't know why your installation isn't doing it.

At least one site claims LIKE can't be used on integers at all. Here's how to cast the integer to a string in SQL.

[griffinn]

Just upgraded from PostgreSQL 8.2 to 8.3, using Debian defaults. I was burnt by this too.

From the 8.3 release notes:

Non-character data types are no longer automatically cast to TEXT

Arrgh. Anyway, the SQL-compliant way to keep the LIKE operator and also have typecasting happen would be to change:

Code: Select all

e.$key LIKE ...

to:

Code: Select all

CAST(e.$key AS TEXT) LIKE ...

The CAST operator works in MySQL as far back as 3.23 and in PostgreSQL as far back as 7.4.

[garvinhicking]

Hi!

Sadly CAST does not work in SQLite, I believe? Also, there are dozens of places where s9y uses LIKE uniquely, because it can either attach to TEXT or INT columns, so this would meen a massive codebase scan to find all occurences.

I'd rather appreciate that PGSQL would perform like it did before, which is compatible to MySQL and SQLite parsing

Why doesn't every other project pay close attention to backwards compatibility if needed, like we do?

Regards,
Garvin

[griffinn]

Sadly CAST does not work in SQLite, I believe?

Not sure if it does anything useful in SQLite, but thankfully at least it is accepted, according to:
http://www.sqlite.org/lang_expr.html

Combing through the PostgreSQL archives, it seems they have been mulling over whether to remove implicit casting to text for months (presumably out of concerns for backward compatibility), and then decided to "bite the bullet" and do it. They don't seem to have provided any easy means (e.g. a config value) to revert to the old behaviour.

An ugly hack using CREATE CAST might coerce PostgreSQL 8.3 into doing implicit casts to text again, but it just looks too invasive.