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]
