RE: select...where...in...union bug?

Here is the sql script:

---cut here---

-- MySQL dump 10.4
--
-- Host: localhost    Database: foo
-- ------------------------------------------------------
-- Server version 5.0.0-alpha-max-debug

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT, CHARACTER_SET_CLIENT=utf8 */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE=NO_AUTO_VALUE_ON_ZERO */;
-- MySQL dump 10.4
--
-- Host: localhost    Database: foo
-- ------------------------------------------------------
-- Server version 5.0.0-alpha-max-debug

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT, CHARACTER_SET_CLIENT=utf8 */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE=NO_AUTO_VALUE_ON_ZERO */;

--
-- Table structure for table `geom_class`
--

DROP TABLE IF EXISTS geom_class;
CREATE TABLE geom_class (
  GID decimal(20,0) NOT NULL default '0',
  DESCRIPTION varchar(16),
  PRIMARY KEY  (GID)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Dumping data for table `geom_class`
--


/*!40000 ALTER TABLE geom_class DISABLE KEYS */;
LOCK TABLES geom_class WRITE;
INSERT INTO geom_class VALUES ("1", "apples"),("2", "oranges");
UNLOCK TABLES;
/*!40000 ALTER TABLE geom_class ENABLE KEYS */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE <mailto:SQL_MODE=@OLD_SQL_MODE>  */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS <mailto:FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS>  */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS <mailto:UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS>  */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT <mailto:CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT>  */;

SELECT GEOM_CLASS.* FROM GEOM_CLASS;
SELECT GEOM_CLASS.GID FROM GEOM_CLASS WHERE (GEOM_CLASS.GID = 2)
UNION
SELECT GEOM_CLASS.GID FROM GEOM_CLASS WHERE (GEOM_CLASS.GID = 1);
SELECT GEOM_CLASS.* FROM GEOM_CLASS WHERE (GEOM_CLASS.GID IN (
SELECT GEOM_CLASS.GID FROM GEOM_CLASS WHERE (GEOM_CLASS.GID = 2)
UNION
SELECT GEOM_CLASS.GID FROM GEOM_CLASS WHERE (GEOM_CLASS.GID = 1)
));
SELECT GEOM_CLASS.* FROM GEOM_CLASS WHERE (GEOM_CLASS.GID IN (
SELECT 2 UNION SELECT 1
));

---cut here---

Note that I added a "DESCRIPTION" field to the table so that the result I think I should get is:

+-----+-------------+
| GID | DESCRIPTION |
+-----+-------------+
|   1 | apples      |
|   2 | oranges     |
+-----+-------------+

-----Original Message----- 
From: Chuck Han 
Sent: Fri 1/7/2005 11:34 PM 
To: [email protected] 
Cc: 
Subject: select...where...in...union bug?



Hi,

(1) If I issue the following query:

 SELECT GEOM_CLASS.* FROM GEOM_CLASS

I get the following result:

 +-----+
 | GID |
 +-----+
 |   1 |
 |   2 |
 +-----+

(2) If I issue the following query:

 SELECT GEOM_CLASS.GID FROM GEOM_CLASS WHERE (GEOM_CLASS.GID = 2)
 UNION
 SELECT GEOM_CLASS.GID FROM GEOM_CLASS WHERE (GEOM_CLASS.GID = 1)

I get the following result:

 +-----+
 | GID |
 +-----+
 |   2 |
 |   1 |
 +-----+

(3) Now, if I combine (1) and (2) in the following way:

 SELECT GEOM_CLASS.* FROM GEOM_CLASS WHERE (GEOM_CLASS.GID IN (
  SELECT GEOM_CLASS.GID FROM GEOM_CLASS WHERE (GEOM_CLASS.GID = 2)
  UNION
  SELECT GEOM_CLASS.GID FROM GEOM_CLASS WHERE (GEOM_CLASS.GID = 1)
 ))

I get the following result:

 +-----+
 | GID |
 +-----+
 |   1 |
 +-----+

Shouldn't I get:

 +-----+
 | GID |
 +-----+
 |   1 |
 |   2 |
 +-----+

(4) I do get the above result if I run the following query:

 SELECT GEOM_CLASS.* FROM GEOM_CLASS WHERE (GEOM_CLASS.GID IN (
  SELECT 2 UNION SELECT 1
 ))

thanks in advance, Chuck