| Bug #13013 | Select broken in version 5.0.7 engine = InnoDB | ||
|---|---|---|---|
| Submitted: | 6 Sep 2005 13:08 | Modified: | 22 Sep 2005 9:51 |
| Reporter: | Vasudevan Seshadri | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server | Severity: | S1 (Critical) |
| Version: | 5.0.7 beta | OS: | Linux (Linux,solaris) |
| Assigned to: | CPU Architecture: | Any | |
[6 Sep 2005 13:08]
Vasudevan Seshadri
[6 Sep 2005 13:11]
Vasudevan Seshadri
please ignore the index created on a different table name. Use the specific table names for on index for your analysis
[6 Sep 2005 14:12]
Valeriy Kravchuk
I tried to repeat your bug report on 5.0.12:
mysql> create table ViewToOp(
-> VIEWNAME varchar(50) NOT NULL,
-> OPERATIONSNAME varchar(50) NOT NULL,
-> OPERATIONSTYPE INTEGER,
-> PRIMARY KEY (VIEWNAME,OPERATIONSNAME));
Query OK, 0 rows affected (0.10 sec)
mysql> create table OperationsTT(
-> PARENTOPERATION varchar(50) NOT NULL,
-> CHILDOPERATION varchar(50) NOT NULL,
-> LEAFNODE INTEGER,
-> PRIMARY KEY (PARENTOPERATION,CHILDOPERATION),
-> index OperationsTreeTable0_ndx (PARENTOPERATION),
-> index OperationsTreeTable1_ndx (CHILDOPERATION));
Query OK, 0 rows affected (0.10 sec)
mysql> insert into ViewToOp values('xxx', 'op1', 1);
Query OK, 1 row affected (0.04 sec)
mysql> insert into ViewToOp values('xxx', 'op2', 1);
Query OK, 1 row affected (0.04 sec)
mysql> insert into ViewToOp values('yyy', 'op1', 1);
Query OK, 1 row affected (0.04 sec)
mysql> insert into OperationsTT values('op1', 'nop', 1);
Query OK, 1 row affected (0.03 sec)
mysql> insert into OperationsTT values('nop', 'op2', 1);
Query OK, 1 row affected (0.03 sec)
mysql> insert into OperationsTT values('op1', 'nop2', 2);
Query OK, 1 row affected (0.04 sec)
mysql> select distinct ViewToOp.OPERATIONSTYPE, OperationsTT.CHILDOPERATION
-> from ViewToOp, OperationsTT
-> where ViewToOp.VIEWNAME = 'xxx'
-> AND (ViewToOp.OPERATIONSNAME = OperationsTT.PARENTOPERATION OR ViewToOp.O
PERATIONSNAME = OperationsTT.CHILDOPERATION);
+----------------+----------------+
| OPERATIONSTYPE | CHILDOPERATION |
+----------------+----------------+
| 1 | nop |
| 1 | nop2 |
| 1 | op2 |
+----------------+----------------+
3 rows in set (0.00 sec)
mysql> show create table ViewToOp;
+----------+--------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
+
| Table | Create Table
|
+----------+--------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
+
| ViewToOp | CREATE TABLE `viewtoop` (
`VIEWNAME` varchar(50) NOT NULL,
`OPERATIONSNAME` varchar(50) NOT NULL,
`OPERATIONSTYPE` int(11) default NULL,
PRIMARY KEY (`VIEWNAME`,`OPERATIONSNAME`)
) ENGINE=InnoDB DEFAULT CHARSET=cp1251 |
+----------+--------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
+
1 row in set (0.06 sec)
mysql> select version();
+----------------+
| version() |
+----------------+
| 5.0.12-beta-nt |
+----------------+
1 row in set (0.00 sec)
So, these are InnoDB tables, with "some rows in both the tables" (3 really), and I tried to perform the select as mentioned in the bug report in the forum. I see correct result on 5.0.12.
May be, some more specific rows needed?
What you expected to see on 4.0.20 and 5.0.7 and what exactly you got?
By the way, you were asked to provide table dumps in the forum you refer to. You may upload them using File tab.
[8 Sep 2005 8:55]
Vasudevan Seshadri
Hi Valeriy Kravchuk, Thanks for the update, I tested the same query in 5.0.12-beta and things worked well. Not sure if the bug was introduced in early releases of 5.x series and fixed in the later version of releases. Many thanks for all your efforts. Vasudevan.S
[8 Sep 2005 9:21]
Valeriy Kravchuk
Thank you for taking the time to report a problem. Unfortunately you are not using a current version of the product your reported a problem with -- the problem might already be fixed. Please download a new version from http://www.mysql.com/downloads/ If you are able to reproduce the bug with one of the latest versions, please change the version on this bug report to the version you tested and change the status back to "Open". Again, thank you for your continued support of MySQL. Additional info: So, may I mark it as "Not a bug" in the current versions of 5.0.x?
