[RESOLVED] Subquery returns NULL, yet returns proper value on localhost

Hendricus

Hi,

Couldn't get into my old account so my first post on my new account 🙂
Edit: I guess I got into the old account again automatically by email address, funny!

If got a query which works like a charm on my localhost but on the live server it won't work as nice 🙁

SELECT
				series.sID AS sID,
				series.description AS seriename,
				(SELECT sum(images.price) FROM images WHERE images.sID = 1) AS seriestotal, 
				GROUP_CONCAT(DISTINCT themes.description ORDER BY themes.description ASC SEPARATOR ', ') AS themes,
				images.iID,
				images.description,
				images.filename,
				images.price,
				GROUP_CONCAT(DISTINCT tags.description ORDER BY tags.description ASC SEPARATOR ', ') AS tags
			FROM 
				series
			LEFT JOIN
				images ON series.sID = images.sID
			LEFT JOIN
				tags2img ON images.iID = tags2img.iID
			LEFT JOIN
				tags ON tags2img.tID = tags.tID
			LEFT JOIN
				tags2serie ON series.sID = tags2serie.sID
			LEFT JOIN
				tags AS themes ON tags2serie.tID = themes.tID
			WHERE 
				(series.sID = 1)
			GROUP BY
				images.iID
			ORDER BY
				images.iID ASC

The problem is with the subquery

(SELECT sum(images.price) FROM images WHERE images.sID = 1) AS seriestotal

Now on my localhost it returns the sum of all price fields, on the live server it returns NULL as seriestotal but no further errors or anyting. The site and database on my live server are an exact copy of my local site and database.

My local MYSQL version is 5.0.67
On the live server it's 4.1.10

Yet I thought subqueries are support from 4.1 onwards... I've tested another simple query with a subquery on the live server and that works... am I doing some other 4.1 unsupported stuff with the subquery?

Thanx for your help!
Cheers

jay6390

does mysql error bring anything up?

cybernet2u

My local MYSQL version is 5.0.67
On the live server it's 4.1.10

isn't that obvious that is the problem ...

jay6390

If he has another subquery that works on the remote host then why is it not working for that one? Yes it's simple to say that the version is the reason, but what is causing the problem between the two versions?

Hendricus

Thanx for the replies!

MySQL doesn't return any errors... I've also tested the query via phpMyAdmin and here it does the dame, all values returned ok but seriestotal is returned as NULL.

I thougt the version was the problem. But MySQL is supposed to support subqueries from 4.10 onwards. That's why I tested a simple query with a subquery which works.

I've been testing some more and when I take out the GROUP_CONCATs from the SELECT the query works just fine and seriesotal returns the correct value... the plot thickens!

Any clues?
Thanx again!

bradgrafelman

It's very possible you're experiencing a bug in the MySQL server.

At any rate, you really should be moving onto at least some version of MySQL 5 rather than fiddling around with 4 at this point. If that's honestly the most recent version your host has to offer, you should ditch them and find a more competent host.

Hendricus

Yes, I'm starting to think it's not me causing errors this time but MySQL 🙂

The server I'm using is not being updated for various reasons, I'll migrate the account to a updated server and the problem will be gone 🙂

Thanx all for your time!

Cheers