[RESOLVED] LIMIT question

[Shawazi]

I have a very complicated query but the bottom line is it should return 5 rows.

If I add LIMIT 0, 10 - it will.
If I put NO limit whatsoever, it will only return 4 rows.

Any ideas?

[alimadzi]

Just use 'LIMIT 5' if you want five rows or less.

[Sxooter]

What's the query?

[Shawazi]

This returns one result: (has limit)

SELECT proj.id AS proj_id FROM projects proj, members memb WHERE ( proj.published=1 AND proj.memb_id=memb.id AND ((proj.privacy="public" OR proj.memb_id=50000) OR (SELECT COUNT(*) FROM project_owners po WHERE po.proj_id=proj.id AND po.memb_id=50000) > 0 ) AND proj.memb_id IN (SELECT memb.id FROM members memb, circle_members cm WHERE cm.memb_id=memb.id AND cm.circle_id=30008 UNION SELECT memb.id FROM members memb, circles circ WHERE circ.memb_id=memb.id AND circ.id=30008) ) OR ( proj.published=1 AND proj.memb_id=memb.id AND proj.privacy="my_circles" AND (SELECT COUNT(circ.id) FROM circle_members cm, circles circ WHERE cm.memb_id='50000' AND cm.circle_id=circ.id AND circ.memb_id=proj.memb_id) > 0 AND proj.memb_id IN (SELECT memb.id FROM members memb, circle_members cm WHERE cm.memb_id=memb.id AND cm.circle_id=30008 UNION SELECT memb.id FROM members memb, circles circ WHERE circ.memb_id=memb.id AND circ.id=30008) ) OR ( proj.published=1 AND proj.memb_id=memb.id AND proj.privacy="custom" AND (SELECT COUNT(circ.id) FROM circle_members cm, circles circ, project_viewable pv WHERE cm.memb_id='50000' AND cm.circle_id=circ.id AND circ.memb_id=proj.memb_id AND proj.id=pv.proj_id AND pv.type="circle" AND pv.view_id=circ.id AND pv.view_id=cm.circle_id ) > 0 AND proj.memb_id IN (SELECT memb.id FROM members memb, circle_members cm WHERE cm.memb_id=memb.id AND cm.circle_id=30008 UNION SELECT memb.id FROM members memb, circles circ WHERE circ.memb_id=memb.id AND circ.id=30008) ) OR ( proj.published=1 AND proj.memb_id=memb.id AND proj.privacy="custom" AND (SELECT COUNT(membi.id) FROM members membi, project_viewable pv WHERE pv.type="member" AND pv.view_id=membi.id AND pv.view_id='50000' AND pv.proj_id=proj.id ) > 0 AND proj.memb_id IN (SELECT memb.id FROM members memb, circle_members cm WHERE cm.memb_id=memb.id AND cm.circle_id=30008 UNION SELECT memb.id FROM members memb, circles circ WHERE circ.memb_id=memb.id AND circ.id=30008) ) GROUP BY proj.id ORDER BY proj.id DESC LIMIT 0, 4

This returns zero results (no limit specified)

SELECT proj.id AS proj_id FROM projects proj, members memb WHERE ( proj.published=1 AND proj.memb_id=memb.id AND ((proj.privacy="public" OR proj.memb_id=50000) OR (SELECT COUNT(*) FROM project_owners po WHERE po.proj_id=proj.id AND po.memb_id=50000) > 0 ) AND proj.memb_id IN (SELECT memb.id FROM members memb, circle_members cm WHERE cm.memb_id=memb.id AND cm.circle_id=30008 UNION SELECT memb.id FROM members memb, circles circ WHERE circ.memb_id=memb.id AND circ.id=30008) ) OR ( proj.published=1 AND proj.memb_id=memb.id AND proj.privacy="my_circles" AND (SELECT COUNT(circ.id) FROM circle_members cm, circles circ WHERE cm.memb_id='50000' AND cm.circle_id=circ.id AND circ.memb_id=proj.memb_id) > 0 AND proj.memb_id IN (SELECT memb.id FROM members memb, circle_members cm WHERE cm.memb_id=memb.id AND cm.circle_id=30008 UNION SELECT memb.id FROM members memb, circles circ WHERE circ.memb_id=memb.id AND circ.id=30008) ) OR ( proj.published=1 AND proj.memb_id=memb.id AND proj.privacy="custom" AND (SELECT COUNT(circ.id) FROM circle_members cm, circles circ, project_viewable pv WHERE cm.memb_id='50000' AND cm.circle_id=circ.id AND circ.memb_id=proj.memb_id AND proj.id=pv.proj_id AND pv.type="circle" AND pv.view_id=circ.id AND pv.view_id=cm.circle_id ) > 0 AND proj.memb_id IN (SELECT memb.id FROM members memb, circle_members cm WHERE cm.memb_id=memb.id AND cm.circle_id=30008 UNION SELECT memb.id FROM members memb, circles circ WHERE circ.memb_id=memb.id AND circ.id=30008) ) OR ( proj.published=1 AND proj.memb_id=memb.id AND proj.privacy="custom" AND (SELECT COUNT(membi.id) FROM members membi, project_viewable pv WHERE pv.type="member" AND pv.view_id=membi.id AND pv.view_id='50000' AND pv.proj_id=proj.id ) > 0 AND proj.memb_id IN (SELECT memb.id FROM members memb, circle_members cm WHERE cm.memb_id=memb.id AND cm.circle_id=30008 UNION SELECT memb.id FROM members memb, circles circ WHERE circ.memb_id=memb.id AND circ.id=30008) ) GROUP BY proj.id ORDER BY proj.id DESC

I am doing something like Displaying X of Y projects. X is 1-4 where Y can be anything. When I take away the limit I should be getting the total number.

[cgraz]

That query is nearly impossible to read w/out us having to copy and paste then format. It would be much more helpful if you put your query on multiple lines and indented.

[Shawazi]

Here is format with LIMIT - the other is just without it

 SELECT proj.id AS proj_id
FROM projects proj, members memb
WHERE (
	proj.published =1
	AND proj.memb_id = memb.id
	AND (
			(
				proj.privacy = "public"
				OR proj.memb_id =50000
			)
			OR (

			SELECT COUNT( * )
			FROM project_owners po
			WHERE po.proj_id = proj.id
			AND po.memb_id =50000
		) >0
)
AND proj.memb_id
IN (

	SELECT memb.id
	FROM members memb, circle_members cm
	WHERE cm.memb_id = memb.id
	AND cm.circle_id =30008
	UNION
	SELECT memb.id
	FROM members memb, circles circ
	WHERE circ.memb_id = memb.id
	AND circ.id =30008
)
)
OR (
	proj.published =1
	AND proj.memb_id = memb.id
	AND proj.privacy = "my_circles"
	AND (

	SELECT COUNT( circ.id )
	FROM circle_members cm, circles circ
	WHERE cm.memb_id = '50000'
	AND cm.circle_id = circ.id
	AND circ.memb_id = proj.memb_id
) >0
AND proj.memb_id
IN (

	SELECT memb.id
	FROM members memb, circle_members cm
	WHERE cm.memb_id = memb.id
	AND cm.circle_id =30008
	UNION SELECT memb.id
	FROM members memb, circles circ
	WHERE circ.memb_id = memb.id
	AND circ.id =30008
)
)
OR (
	proj.published =1
	AND proj.memb_id = memb.id
	AND proj.privacy = "custom"
	AND (

	SELECT COUNT( circ.id )
	FROM circle_members cm, circles circ, project_viewable pv
	WHERE cm.memb_id = '50000'
	AND cm.circle_id = circ.id
	AND circ.memb_id = proj.memb_id
	AND proj.id = pv.proj_id
	AND pv.type = "circle"
	AND pv.view_id = circ.id
	AND pv.view_id = cm.circle_id
) >0
AND proj.memb_id
IN (

	SELECT memb.id
	FROM members memb, circle_members cm
	WHERE cm.memb_id = memb.id
	AND cm.circle_id =30008
	UNION SELECT memb.id
	FROM members memb, circles circ
	WHERE circ.memb_id = memb.id
	AND circ.id =30008
)
)
OR (
	proj.published =1
	AND proj.memb_id = memb.id
	AND proj.privacy = "custom"
	AND (

	SELECT COUNT( membi.id )
	FROM members membi, project_viewable pv
	WHERE pv.type = "member"
	AND pv.view_id = membi.id
	AND pv.view_id = '50000'
	AND pv.proj_id = proj.id
) >0
AND proj.memb_id
IN (

	SELECT memb.id
	FROM members memb, circle_members cm
	WHERE cm.memb_id = memb.id
	AND cm.circle_id =30008
	UNION SELECT memb.id
	FROM members memb, circles circ
	WHERE circ.memb_id = memb.id
	AND circ.id =30008
)
)
GROUP BY proj.id
ORDER BY proj.id DESC
LIMIT 0 , 4 

[Sxooter]

OK, that query makes my head hurt. 🙂 Seriously though, move the join syntax out of the the ( ) OR ( ) OR ( ) stuff and make it exist as a separate bit and AND it to everything else.

I.e. this bit:

AND proj.memb_id = memb.id

should definitely be in ONE place, and ANDED with all the other parts of your giant ORED where clause.

After that I'm not so sure. I think what's happening is that the limit causes the query to short circuit some of those evaluations, so you get different behaviour as a side effect.

[Shawazi]

I changed

AND proj.privacy = "custom"
	AND (

	SELECT COUNT( circ.id )
	FROM circle_members cm, circles circ, project_viewable pv
	WHERE cm.memb_id = '50000'
	AND cm.circle_id = circ.id
	AND circ.memb_id = proj.memb_id
	AND proj.id = pv.proj_id
	AND pv.type = "circle"
	AND pv.view_id = circ.id
	AND pv.view_id = cm.circle_id
) >0

to

AND proj.privacy="custom" 
	AND (
			SELECT COUNT(*) FROM circles cc WHERE
			cc.id IN (
				SELECT pvc.view_id
				FROM project_viewable pvc, circle_members cmc
				WHERE pvc.proj_id = proj.id
				AND pvc.type = 'circle'
				AND cmc.memb_id =$userCID
				AND pvc.view_id = cmc.circle_id
			)
	 ) > 0

It had something to do with how it was selecting incorrect projects. The weird thing i still don't understand is how the LIMIT was affecting it. In any case, it is working now.

[Sxooter]

oh man, I see one mistake, you're reusing aliases in you subselects.

when you have a subselect, you can select from the outer query:

select * from bubba b where id in (select id from b ....)

and the b in that subselect is referencing the b in the outer select and runs the subselect for each row returned in the outer select.

I'd look into changing the inner and outer selects to use different aliases.