List:General Discussion« Previous MessageNext Message »
From:SGreen Date:October 18 2005 1:11pm
Subject:Re: Strange error in query with joins and subquery
View as plain text  
(response interspersed)

Jasper Bryant-Greene <jasper@stripped> wrote on 10/17/2005 07:49:36 PM:

> Just recently (possibly since upgrading to MySQL 5.0.13 RC), I've been 
> getting the following error with queries like the one below.
> 
> Unknown column 'photos.id' in 'on clause' (1054)
> 
> SELECT photos.*
> FROM photos
> LEFT JOIN tags_photos ON tags_photos.photo = photos.id
                                                ^
I suspect that the error isn't here -------------

> WHERE 1
> AND tags_photos.tag IN (6)
> AND NOT EXISTS (
> 
>    SELECT 1
>    FROM tags AS t
>    LEFT OUTER JOIN tags_photos AS t2 ON (
>       t.id = t2.tag AND t2.photo = photos.id
                                      ^
it's here -----------------------------

>    )
>    WHERE t.id IN (6)
>    AND t2.tag IS NULL
> 
> )
> GROUP BY photos.id
> 
> 
> This was working previously, and there is definitely an `id` column in 
> the `photos` table. The table definitions follow:
> 
> 
> CREATE TABLE `photos` (
>    `id` int(10) unsigned NOT NULL auto_increment,
>    `filename` varchar(100) NOT NULL,
>    `caption` varchar(200) NOT NULL,
>    `description` text NOT NULL,
>    `user` int(10) unsigned NOT NULL default '0',
>    `exif` longtext NOT NULL,
>    `uploaded` int(10) unsigned NOT NULL default '0',
>    `modified` int(10) unsigned NOT NULL,
>    `privacy` 
> enum('public','friends-family','friends','family','private') NOT NULL 
> default 'public',
>    `rights` enum('copyright','creative-commons','public-domain') NOT 
> NULL default 'copyright',
>    `notes` text NOT NULL,
>    `offensive` tinyint(1) unsigned NOT NULL,
>    `resizeMode` varchar(5) NOT NULL,
>    `allowOriginal` tinyint(1) NOT NULL default '1',
>    `licenseURL` varchar(200) NOT NULL,
>    `licenseName` varchar(50) NOT NULL,
>    `licenseButton` varchar(250) NOT NULL,
>    PRIMARY KEY  (`id`),
>    KEY `user` (`user`),
>    KEY `uploaded` (`uploaded`),
>    KEY `privacy` (`privacy`),
>    KEY `modified` (`modified`),
>    CONSTRAINT `photos_ibfk_1` FOREIGN KEY (`user`) REFERENCES `users` 
(`id`)
> ) ENGINE=InnoDB DEFAULT CHARSET=utf8
> 
> 
> CREATE TABLE `tags_photos` (
>    `tag` int(10) unsigned NOT NULL default '0',
>    `photo` int(10) unsigned NOT NULL default '0',
>    PRIMARY KEY  (`tag`,`photo`),
>    KEY `photo` (`photo`),
>    CONSTRAINT `tags_photos_ibfk_1` FOREIGN KEY (`tag`) REFERENCES `tags` 

> (`id`),
>    CONSTRAINT `tags_photos_ibfk_2` FOREIGN KEY (`photo`) REFERENCES 
> `photos` (`id`)
> ) ENGINE=InnoDB DEFAULT CHARSET=utf8
> 
> 
> CREATE TABLE `tags` (
>    `id` int(10) unsigned NOT NULL auto_increment,
>    `tagName` varchar(50) NOT NULL,
>    PRIMARY KEY  (`id`)
> ) ENGINE=InnoDB DEFAULT CHARSET=utf8


In this case, I don't think that the query parser is correctly parsing 
your EXISTS subquery within the context of the outer query. It's probably 
a bug and you should submit a test case for resolution. To get your query 
to work in the mean time, don't use subqueries use joins. I *think* you 
are looking for photos that are not tagged so here is how I would re-write 
your query. Let me know if I guessed incorrectly.

SELECT DISTINCT photos.*
FROM photos
LEFT JOIN tags_photos tp
ON tp.photo = photos.id
LEFT JOIN tags t
        on tp.tag = t.id 
WHERE tags_photos.tag IN (6)
AND t.id IS NULL;

I also made a few editorial changes:
1) using a GROUP BY with no aggregate functions (sum(), avg(), min(), 
max(), etc) is illogical as your results will be indeterminate. If you do 
not want duplicate rows, you have two options: deduplicate your data or 
use the DISTINCT modifier.

2) "WHERE 1" is always true so it plays no useful part in your WHERE 
clause. I removed it.

3) Technically, the IN comparator should be used with lists of values and 
an = should be used for comparing single values. However, I understand the 
pattern you are using and single values are legal in an IN comparison is 
allowed on MySQL (not so with other RDBMSs) so it can stay.

As I said above, if I didn't divine the correct question your query was 
designed to answer, just fill me in and I will happily rewrite the query 
to fit.

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Thread
Strange error in query with joins and subqueryJasper Bryant-Greene18 Oct
  • Re: Strange error in query with joins and subquerySGreen18 Oct