MySQL Bugs: #415: subselect, too few rows returned

Bug #415	subselect, too few rows returned
Submitted:	11 May 2003 10:25	Modified:	13 May 2003 6:18
Reporter:	Peter Speck	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	4.1.0	OS:	MacOS (Mac OS X 10.2.6)
Assigned to:		CPU Architecture:	Any

Description:
The last select in the tests below should return 3 rows, but returns only 1 row. 

Tested using the pre-built version of 4.1.0 on MySQL.  Unfortunately I don't have access to any 
other platform, so I have not been able to check if this is a platform specific bug. 

How to repeat:
DROP TABLE /*! IF EXISTS */ aa;
DROP TABLE /*! IF EXISTS */ bb;
CREATE TABLE aa (
	aa_id INT NOT NULL PRIMARY KEY
);
CREATE TABLE bb (
	bb_id INT NOT NULL PRIMARY KEY,
	aa_fk INT NOT NULL,
	version INT NOT NULL
);
/* Oracle version:
	CREATE UNIQUE INDEX bb_version on bb (aa_fk,version);
*/
ALTER TABLE bb ADD
      UNIQUE bb_version (aa_fk,version);
ALTER TABLE bb ADD
      CONSTRAINT bb_aa_fk
      FOREIGN KEY (aa_fk)
      REFERENCES aa (aa_id);
INSERT INTO aa (aa_id) VALUES (1);
INSERT INTO aa (aa_id) VALUES (2);
INSERT INTO aa (aa_id) VALUES (3);
INSERT INTO bb (bb_id, aa_fk, version) VALUES (101, 1, 1);
INSERT INTO bb (bb_id, aa_fk, version) VALUES (102, 1, 2);
INSERT INTO bb (bb_id, aa_fk, version) VALUES (103, 1, 3);
INSERT INTO bb (bb_id, aa_fk, version) VALUES (201, 2, 1);
INSERT INTO bb (bb_id, aa_fk, version) VALUES (202, 2, 2);
INSERT INTO bb (bb_id, aa_fk, version) VALUES (203, 2, 3);
INSERT INTO bb (bb_id, aa_fk, version) VALUES (301, 3, 1);
INSERT INTO bb (bb_id, aa_fk, version) VALUES (302, 3, 2);
INSERT INTO bb (bb_id, aa_fk, version) VALUES (303, 3, 3);
COMMIT;

/* ok, 9 rows */
SELECT  main_aa.aa_id, main_bb.bb_id, main_bb.version
FROM   aa main_aa, bb main_bb
WHERE  main_bb.aa_fk = main_aa.aa_id;

/* ok, 9 rows */
SELECT  main_aa.aa_id, main_bb.bb_id, main_bb.version, MAX(max_bb.version)
FROM   aa main_aa, bb main_bb, bb max_bb
WHERE  main_bb.aa_fk = main_aa.aa_id
GROUP BY main_aa.aa_id, main_bb.bb_id, main_bb.version;

/* ok, the 3 rows */
SELECT  main_aa.aa_id, main_bb.bb_id, main_bb.version
FROM   aa main_aa, bb main_bb
WHERE  main_bb.aa_fk = main_aa.aa_id
AND    main_bb.version = (
       SELECT MAX(sub_bb.version)
       FROM   bb sub_bb
       WHERE  sub_bb.aa_fk = main_bb.aa_fk);

/* fails, returns only 1 row. Should return the same 3 rows as above. */
SELECT  main_aa.aa_id, main_bb.bb_id, main_bb.version
FROM   aa main_aa, bb main_bb
WHERE  main_bb.aa_fk = main_aa.aa_id
AND    main_bb.version = (
       SELECT MAX(sub_bb.version)
       FROM   bb sub_bb
       WHERE  sub_bb.aa_fk = main_bb.aa_fk
       AND    sub_bb.aa_fk = main_aa.aa_id);

/* I can't see any difference in EXPLAIN SELECT output */

Better set of INSERT statements:

INSERT INTO aa (aa_id) VALUES (1);
INSERT INTO aa (aa_id) VALUES (2);
INSERT INTO aa (aa_id) VALUES (3);
INSERT INTO bb (bb_id, aa_fk, version) VALUES (101, 1, 11);
INSERT INTO bb (bb_id, aa_fk, version) VALUES (102, 1, 12);
INSERT INTO bb (bb_id, aa_fk, version) VALUES (103, 1, 13);
INSERT INTO bb (bb_id, aa_fk, version) VALUES (201, 2, 21);
INSERT INTO bb (bb_id, aa_fk, version) VALUES (202, 2, 22);
INSERT INTO bb (bb_id, aa_fk, version) VALUES (203, 2, 23);
INSERT INTO bb (bb_id, aa_fk, version) VALUES (301, 3, 31);
INSERT INTO bb (bb_id, aa_fk, version) VALUES (302, 3, 32);
INSERT INTO bb (bb_id, aa_fk, version) VALUES (303, 3, 33);

With the "improved INSERTs" above, SQL #3 gives only 1 row, and not 3:

SELECT  main_aa.aa_id, main_bb.bb_id, main_bb.version
FROM   aa main_aa, bb main_bb
WHERE  main_bb.aa_fk = main_aa.aa_id
AND    main_bb.version = (
       SELECT MAX(sub_bb.version)
       FROM   bb sub_bb
       WHERE  sub_bb.aa_fk = main_bb.aa_fk);

The 2nd SQL should really be:  (but it is still ok)
SELECT  main_aa.aa_id, main_bb.bb_id, main_bb.version,
MAX(max_bb.version)
FROM   aa main_aa, bb main_bb, bb max_bb
WHERE  main_bb.aa_fk = main_aa.aa_id
AND    max_bb.aa_fk = main_aa.aa_id
GROUP BY main_aa.aa_id, main_bb.bb_id, main_bb.version;

I have too tried using LIMIT, but I still get only 1 row:
SELECT  main_aa.aa_id, main_bb.bb_id, main_bb.version
FROM   aa main_aa, bb main_bb
WHERE  main_bb.aa_fk = main_aa.aa_id
AND    main_bb.version = (
	SELECT sub_bb.version
	FROM bb sub_bb
	WHERE sub_bb. aa_fk = main_aa.aa_id
	ORDER BY sub_bb.version DESC
	LIMIT 1
);

The only solution I can find for "using latest version" is:
SELECT  main_aa.aa_id, main_bb.bb_id, main_bb.version
FROM   aa main_aa, bb main_bb
WHERE  main_bb.aa_fk = main_aa.aa_id
AND    (main_bb.aa_fk, main_bb.version) IN (
	SELECT sub_bb.aa_fk, MAX(sub_bb.version)
	FROM bb sub_bb, aa sub_aa
	WHERE sub_bb.aa_fk = sub_aa.aa_id
	/* my other criterias for aa/bb here, e.g. */
	AND sub_aa.aa_id != 2
	GROUP BY sub_bb.aa_fk
);
but it is slow with my +90000 rows aa/bb tables.

Peter,

I've checked it with recent 4.1 version (compiled from the BK Tree) and it works just fine on Linux.  I suspect this issue was fixes in the meanwhile.

If you have a chance to check it with MySQL compiled from our BK tree we would appreciate if you confirm this bug is fixed now.

Thank you for good bug report.

I can't test - compiling fails for me.

export CC=gcc
export CFLAGS="-O3 -fno-omit-frame-pointer"
export CXX=gcc
export CXXFLAGS="-O3 -fno-omit-frame-pointer -felide-constructors -fno-exceptions -fno-
rtti"
./configure --prefix=$PREFIX \
        --sysconfdir=$DOC/etc-conf \
        --with-extra-charsets=complex \
        --enable-thread-safe-client \
        --enable-local-infile \
        --disable-shared \
        --with-tcp-port=3100

mysqld.cc: In function `my_bool get_one_option(int, const my_option*, char*)':
mysqld.cc:5056: `isam_skip' undeclared (first use this function)
mysqld.cc:5056: (Each undeclared identifier is reported only once for each 
   function it appears in.)
mysqld.cc:5057: `isam_innodb' undeclared (first use this function)
make[4]: *** [mysqld.o] Error 1
make[3]: *** [all-recursive] Error 1
make[2]: *** [all] Error 2
make[1]: *** [all-recursive] Error 1
make: *** [all] Error 2