Archives - SQLITE SQL error

[sonichouse]

If I enable the option "Show number of entries per category?" with 1.4b1 I get the following error with SQLite

Code: Select all

Warning: sqlite_query() [function.sqlite-query]: near "DISTINCT": syntax error in /opt/share/www/cherokee/devblog/serendipity/include/db/sqlite.inc.php on line 233
bool(false) string(1376) "SELECT count(DISTINCT e.id) AS orderkey FROM serendipity_entries AS e 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 = 1 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.timestamp >= 1228089600 AND e.timestamp <= 1230767999 AND isdraft = 'false' AND e.timestamp <= 1228738200 AND ( c.categoryid IS NULL OR ( acl_acc.groupid = acl_a.groupid OR acl_acc.groupid = 0) OR ( acl_acc.artifact_id IS NULL ) ) " Query failed: problem with query

[sonichouse]

The solution is to move the count() to above and have the distinct as a sub-select

Code: Select all

select count(orderkey) AS orderkey from 
(
SELECT distinct e.id AS orderkey 
FROM serendipity_entries AS e 
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 = 1 
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.timestamp >= 1228089600 AND e.timestamp <= 1230767999 
AND isdraft = 'false' AND e.timestamp <= 1228738200 
AND ( c.categoryid IS NULL OR ( acl_acc.groupid = acl_a.groupid OR acl_acc.groupid = 0) OR ( acl_acc.artifact_id IS NULL ) )
);

I remembered writing details on SQLite and count distinct on my blog a while back when doing some reporting in SQLite.

The call is made around line 555 in plugin_internal.inc.php

Code: Select all

if ($show_count) {
.....
	$ec = serendipity_fetchEntries(
		array($current_ts, $end_ts),
		false,
		'',
		false,
		false,
		null,
		'',
		false,
		true,
		'count(DISTINCT e.id) AS orderkey',
		'',
		'single',
		false, $category_set // the joins used
	);
.....
}

Not sure how I can resolve this one easily

[sonichouse]

Not sure how I can resolve this one easily

Well, the obvious answer is to remove the DISTINCT from the param.

This gives me the correct results, but I assume this was added in v1.4 for a good reason

[garvinhicking]

Hi!

Thanks. I committed it to remove the DISTINCT for sqlite. It was actually added for postgresql, I think, to only count unique entries and not count a single entry multiple times if it's assigned to multiple categories.

Patching the query to use a subselect is extremely hard and would require rewriting a lot of function codes, and probably have a lot of side-effects

Regards,
Garvin

[sonichouse]

Patching the query to use a subselect is extremely hard and would require rewriting a lot of function codes, and probably have a lot of side-effects

No worries, I had a brief look and rapidly came to the same conclusion.
Better to take the low risk approach every time .

The SQLite issue would be solved if the database/engine used SQLite 3.

SQLite 3 is quicker in my experience, is that an option ?