Hi Folks!
I have a problem with a subquery using MySQL 4.1.12 (Fedora Core 4). I
will include the SHOW CREATE TABLE for the two tables involved. Please,
forgive me if I include this information in my original language (Spanish)
but I wish to keep the field names as is to avoid any mistake.
I don't know if this problem is a MySQL subquery bug or I'm doing
something wrong. Anyway, what I'm doing here is pretty straighforward.
First, the table information:
-- SHOW CREATE TABLE FOR TABLE dominios_propios_completos
CREATE TABLE `dominios_propios_completos` (
`criterio` char(100) NOT NULL default '',
`idDominio` int(10) unsigned NOT NULL default '0',
`idSite` int(10) unsigned default NULL,
`fechaCreacion` datetime default NULL,
`idIdioma` tinyint(3) unsigned default NULL,
`estado` tinyint(3) unsigned NOT NULL default '0',
PRIMARY KEY (`criterio`,`idDominio`),
KEY `idSite` (`idSite`),
KEY `fechaCreacion` (`fechaCreacion`),
CONSTRAINT `FK_dominios_propios_completos_1`
FOREIGN KEY (`idSite`)
REFERENCES `sites` (`id`)
ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=FIXED
-- SHOW CREATE TABLE FOR TABLE sites_criterios
CREATE TABLE `sites_criterios` (
`idSite` int(10) unsigned NOT NULL default '0',
`criterio` varchar(100) NOT NULL default '',
`criterio_pub` varchar(100) default NULL,
`idIdioma` int(10) unsigned NOT NULL default '1',
KEY `idSite` (`idSite`),
KEY `idIdioma` (`idIdioma`),
KEY `criterio` (`criterio`),
KEY `criterio_pub` (`criterio_pub`),
CONSTRAINT `sites_criterios_ibfk_1`
FOREIGN KEY (`idSite`)
REFERENCES `sites` (`id`)
ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1
OK, now for the problem. If you look at the above tables, they are
somewhat related via dominios_propios_completos.criterio with
sites_criterios.criterio_pub. In fact, what I am doing is trying to make
dominios_propios_completos.criterio a constraint for criterio_pub on table
sites_criterios.
Now, look carefully:
mysql SELECT criterio, idSite
- FROM dominios_propios_completos
- WHERE criterio = su-turno;
+--++
| criterio | idSite |
+--++
| su-turno | 4305 |
+--++
1 row in set (0.00 sec)
mysql SELECT criterio_pub, idSite
- FROM sites_criterios
- WHERE criterio_pub = su-turno;
Empty set (0.00 sec)
In the above queries it's pretty clear the word su-turno exists in
table dominios-propios-completos but NOT EXISTS in the table
sites_criterios as a value of criterio_pub.
The problem is that the following subquery return no results... Why???
mysql SELECT criterio, idSite
- FROM dominios_propios_completos
- WHERE criterio NOT IN (
- SELECT criterio_pub
- FROM sites_criterios
- );
Empty set (0.05 sec)
Why is this? It's obvious (at least to me) that this query must return
su-turno at least. It interesting to note that if I further restrict the
WHERE clauses, I get the correct output!! Take a look at this, for instance:
mysql SELECT criterio, idSite
- FROM dominios_propios_completos
- WHERE idSite = 4305 AND
- criterio NOT IN (
- SELECT criterio_pub
- FROM sites_criterios
- WHERE idSite = 4305
- );
+++
| criterio | idSite |
+++
... cut ...
| su-turno | 4305 |
+++
245 rows in set (0.00 sec)
I don't know why this last query is working and the former, more general
one, isn't. I have tried different combinations, like using DISTINCT at the
subquery, etc.
I begun thinking this is a bug because I get different results if I
modify the WHERE clause in the inner subquery, like this:
mysql SELECT criterio, idSite
- FROM dominios_propios_completos
- WHERE idSite = 4305 AND
- criterio NOT IN (
- SELECT criterio_pub
- FROM sites_criterios
- );
Empty set (0.01 sec)
Note that this query is THE SAME as the previous one, but with a wider
result set inside the subquery. This makes no difference in my queries as we
confirmed at least the word su-turno does not exists.
Thanks for your patience. If you think I'm doing something wrong please
reply.
Cheers,
Jose Miguel.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
