A lot of SQL SELECT statements are broken in the latest (31/07/2004) cvs tree.

SQL error:
ERROR: Function concat(character varying, integer) does not exist
Unable to identify a function that satisfies the given argument types
You may need to add explicit typecasts
In statement:
SELECT COUNT(*) FROM node n INNER JOIN node_access na ON (na.nid = 0 OR na.nid = n.nid) WHERE n.promote = 1 AND n.status = 1 AND na.grant_view = 1 AND CONCAT(na.realm, na.gid) IN ('all0')

SQL error:
ERROR: Argument of AND must be type boolean, not type integer
In statement:
SELECT COUNT(*) FROM node n WHERE n.type = 'blog' AND n.status = 1 AND 1

Also if GROUP is used in the SQL statement there are some errors if the nummer of rows in the SELECT are nog equal to GROUP.

Will this be changed in next updates?

Erwin

Comments

jonbob’s picture

jonbob commented
Component: other » postgresql database

This SQL is formed by the new node access system. We'll need Adrian's help to come up with a compatibility layer for this.

adrian’s picture

adrian commented
Assigned: Unassigned » vertice@www.drop.org

assigning to myself.

erwin’s picture

erwin commented

More and more SQL statements getting into the code !

In aggregator.module

SQL error:
ERROR: Attribute f.block must be GROUPed or used in an aggregate function

In statement:
SELECT f.*, COUNT(i.iid) AS items FROM aggregator_feed f LEFT JOIN aggregator_item i ON f.fid = i.fid GROUP BY f.fid, f.title, f.url, f.refresh, f.checked, f.link, f.description, f.etag, f.modified, f.image ORDER BY f.title

one solution could be adding f.block after the GROUP BY !

SELECT f.*, COUNT(i.iid) AS items FROM aggregator_feed f LEFT JOIN aggregator_item i ON f.fid = i.fid GROUP BY f.fid, f.title, f.url, f.refresh, f.checked, f.link, f.description, f.etag, f.modified, f.image, f.block ORDER BY f.title

-------------------

Another ERROR in aggregator.module !

SELECT c.cid, c.title, count(ci.iid) as items FROM aggregator_category c LEFT JOIN aggregator_category_item ci ON c.cid = ci.cid GROUP BY c.cid, c.title ORDER BY title

add c.title after the GROUP BY statement. Don't know if this is correct SQL code but it seems to work !

adrian’s picture

adrian commented

This is now fixed here : http://drupal.org/node/10945