| Bug #1970 | Second scalar subquery alters results of first | ||
|---|---|---|---|
| Submitted: | 27 Nov 2003 7:09 | Modified: | 3 Dec 2003 12:23 |
| Reporter: | Phil Sladen | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server | Severity: | S2 (Serious) |
| Version: | 4.1.0.0 | OS: | Linux (Linux 9.0) |
| Assigned to: | CPU Architecture: | Any | |
[28 Nov 2003 7:41]
Dean Ellis
There have been so many changes between 4.1.0 and 4.1.1 that if you can download the source for 4.1.1 and test against that, it would be appreciated and it may in fact have already been corrected. Otherwise, I cannot duplicate this in either 4.1.0 or 4.1.1, so for me to test it I would need tables which exhibit the behavior (ie: repeatable test case). Thank you
[2 Dec 2003 2:21]
Phil Sladen
In the process of dumping the tables to give to you for analysis mysqldump complained with: "mysqldump: Can't get CREATE TABLE for table 'RECORDS' (Table 'RECORDS' was not locked with LOCK TABLES)" and didn't dump table RECORDS or any other tables after it in the database. I can only think that this problem is related in some way to my upgrade from 4.0.15 to 4.1.0.0. Anyway, re-creating this database with 4.1.0.0 and re-testing I found that the problem for which this bug report was raised is no longer evident. So, there is possibly still an issue with upgrading MySQL versions, but otherwise please close down this bug.
[2 Dec 2003 6:15]
Dean Ellis
Ok, thank you for letting us know.
[3 Dec 2003 6:17]
Phil Sladen
Ok, this bug is closed now. But the mysqldump failure that I observed when investigating this problem happened again yesterday, but to an entirely different table (in fact one that I'm not even manipulating at the moment): mysqldump: Can't get CREATE TABLE for table 'EDITORS' (Table 'EDITORS' was not locked with LOCK TABLES) So, I don't think this problem really has anything to do with upgrading from 4.0.15 to 4.1.0.0. Please could you tell me what might be the problem or if I should raise another bug on it. Thanks.
[3 Dec 2003 7:44]
Phil Sladen
The lock problem with mysqldump goes away for a while if the server is restarted.
[3 Dec 2003 12:23]
Dean Ellis
If you can come up with something we can use to test for it, add a new bug for that and we will be happy to test for it. If you can test it against 4.1.1 that would be better (for you as well), but if that is not feasible yet, binaries for 4.1.1 should be available soon. Thank you
Description: A single scalar subquery in a select returns non-null values, but when followed by a second scalar subquery the first subquery returns nulls. I've tried to reproduce this problem with simple data that has very similar attributes to the actual data that causes the problem, but have not been able to reproduce the problem under these conditions. So before I send you a dump of my tables, please could you tell me if this bug is familiar and whether it has been fixed in 4.1.1. If it is likely to have been fixed then I will upgrade to 4.1.1. Or, if there is a work-around. Thanks. How to repeat: SINGLE SUBQUERY: SELECT RECORDS.ID, (SELECT VALUE FROM RECORDFIELDS WHERE RECORDFIELDS.RECORDID=RECORDS.ID AND RECORDFIELDS.TYPEFIELDID=12) AS INDEX0 FROM RECORDS WHERE RECORDS.ITEMID = 6; +----+--------+ | ID | INDEX0 | +----+--------+ | 85 | 2 | | 86 | 2 | | 87 | 1 | | 88 | 1 | +----+--------+ 4 rows in set (0.06 sec) Note: INDEX0's returns longtext values: '2', '2', '1', '1' (as expected) BUT, WHEN THIS SUBQUERY IS FOLLOWED BY A SIMILAR ONE: SELECT RECORDS.ID, (SELECT VALUE FROM RECORDFIELDS WHERE RECORDFIELDS.RECORDID=RECORDS.ID AND RECORDFIELDS.TYPEFIELDID=12) AS INDEX0, (SELECT VALUE FROM RECORDFIELDS WHERE RECORDFIELDS.RECORDID=RECORDS.ID AND RECORDFIELDS.TYPEFIELDID=13) AS INDEX1 FROM RECORDS WHERE RECORDS.ITEMID = 6; +----+--------+--------+ | ID | INDEX0 | INDEX1 | +----+--------+--------+ | 85 | NULL | 2 | | 86 | NULL | 1 | | 87 | NULL | 2 | | 88 | NULL | 1 | +----+--------+--------+ 4 rows in set (0.03 sec) Note: INDEXNO's have changed to: NULL's !!