s9y v0.8 - sqlite query error (crashes) in fetchEntry

[h3]

I ran into a fatal glitch. After creating a category, the front page and, it appeared, any entry related lookups would cause Apache to die.

Code: Select all

[Thu Apr 28 16:07:15 2005] [notice] child pid 25185 exit signal Segmentation fault (11)
[25075] MMCACHE: PHP crashed on opline 40 of sqlite_query() at /home/httpd/us/include/db/sqlite.inc.php:116

I traced it to this query, from serendipity_fetchEntry in include/functions_entries.inc.php, which results in the following sqlite error when using the command-line sqlite client:

Code: Select all

sqlite> SELECT ec.entryid, c.categoryid, c.category_name, c.category_description, c.category_icon, c.parentid FROM serendipity_category AS c LEFT JOIN serendipity_entrycat AS ec ON ec.categoryid = c.categoryid WHERE ec.entryid IN (2);
|1|Dreams|Dreams||0
sqlite: src/vdbe.c:4670: sqliteVdbeExec: Assertion `pSet->prev' failed.
Aborted

Apache 1.3.33/PHP 4.3.10. sqlite for PHP is a lightly patched verions of 1.0.3 from PECL.

I changed the code in functions_entries.inc.php as follows, and it now appears to be working:

Code: Select all

280,281c280,281
<                       ON ec.categoryid = c.categoryid
<                    WHERE ec.entryid IN (" . implode(', ', $search_ids) . ")";
---
>                    WHERE ec.categoryid = c.categoryid
>                      AND ec.entryid IN (" . implode(', ', $search_ids) . ")";

As far as I know the changed query should give identical results, according to the SQL language.

I'm not sure where the bug lies or if this change has consequences beyond what I can think of.

[garvinhicking]

The consequences of your change are that every other SQL system will take up more performance when calculating the query conditions.

I advise you to report the Bug to the SQLite guys; it is a SQLite bug and should be fixed at their engine level, not at our application level.

Thanks for looking into this! I also experienced several SQLite core crashes when debugging, and it just seems to me that certain parts of the extension are not yet stable enough.

Regards,
Garvin

[h3]

Hmm, interesting. Just for kicks, I installed using Postgres and took a look at the query. I was of the impression that the two alternate syntaxes were functionally equivalent once parsed, but it looks like that's not true:

Code: Select all

s9y=> SELECT ec.entryid, c.categoryid, c.category_name, c.category_description, c.category_icon, c.parentid FROM serendipity_category AS c LEFT JOIN serendipity_entrycat AS ec ON ec.categoryid = c.categoryid WHERE ec.entryid IN (1,2,3); 
 entryid | categoryid | category_name | category_description | category_icon | parentid 
---------+------------+---------------+----------------------+---------------+----------
       1 |          1 | cat1          | cat1                 |               |        0
       2 |          2 | cat2          | cat2                 |               |        0
(2 rows)

s9y=> explain SELECT ec.entryid, c.categoryid, c.category_name, c.category_description, c.category_icon, c.parentid FROM serendipity_category AS c LEFT JOIN serendipity_entrycat AS ec ON ec.categoryid = c.categoryid WHERE ec.entryid IN (1,2,3); 
                                                     QUERY PLAN                                                     
--------------------------------------------------------------------------------------------------------------------
 Merge Left Join  (cost=69.83..146.85 rows=1000 width=334)
   Merge Cond: ("outer".categoryid = "inner".categoryid)
   Filter: (("inner".entryid = 1) OR ("inner".entryid = 2) OR ("inner".entryid = 3))
   ->  Index Scan using serendipity_category_pkey on serendipity_category c  (cost=0.00..52.00 rows=1000 width=330)
   ->  Sort  (cost=69.83..72.33 rows=1000 width=8)
         Sort Key: ec.categoryid
         ->  Seq Scan on serendipity_entrycat ec  (cost=0.00..20.00 rows=1000 width=8)
(7 rows)

s9y=> SELECT ec.entryid, c.categoryid, c.category_name, c.category_description, c.category_icon, c.parentid FROM serendipity_category AS c, serendipity_entrycat AS ec where ec.categoryid = c.categoryid and ec.entryid IN (1,2,3); 
 entryid | categoryid | category_name | category_description | category_icon | parentid 
---------+------------+---------------+----------------------+---------------+----------
       1 |          1 | cat1          | cat1                 |               |        0
       2 |          2 | cat2          | cat2                 |               |        0
(2 rows)

s9y=> explain SELECT ec.entryid, c.categoryid, c.category_name, c.category_description, c.category_icon, c.parentid FROM serendipity_category AS c, serendipity_entrycat AS ec where ec.categoryid = c.categoryid and ec.entryid IN (1,2,3); 
                                     QUERY PLAN                                      
-------------------------------------------------------------------------------------
 Hash Join  (cost=27.54..55.20 rows=16 width=334)
   Hash Cond: ("outer".categoryid = "inner".categoryid)
   ->  Seq Scan on serendipity_category c  (cost=0.00..20.00 rows=1000 width=330)
   ->  Hash  (cost=27.50..27.50 rows=15 width=8)
         ->  Seq Scan on serendipity_entrycat ec  (cost=0.00..27.50 rows=15 width=8)
               Filter: ((entryid = 1) OR (entryid = 2) OR (entryid = 3))
(6 rows)

Of course, the "LEFT" part is sort of glossed over here, but for this particular query, it doesn't seem to result in any difference. I'm not much of an SQL guy, but it seems like the latter has a better performance query plan?

Anyway, I agree though that the problem lies with sqlite. I'm going to play around with it more there and see what I can turn up.