Autoincrement value not returned when using join command
I got 3 tables creatred like this:
CREATE TABLE IF NOT EXISTS `t1`
(
`ID` int(11) NOT NULL AUTO_INCREMENT,
`CODE_T2` char(5) COLLATE utf8_slovenian_ci DEFAULT NULL,
`CODE_T3` char(5) COLLATE utf8_slovenian_ci DEFAULT NULL,
`SOMEDATA1` char(20) COLLATE utf8_slovenian_ci DEFAULT NULL,
`SOMEDATA2` char(20) COLLATE utf8_slovenian_ci DEFAULT NULL,
`EDITDATE` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`ID`)
)
ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_slovenian_ci;
CREATE TABLE IF NOT EXISTS `t2`
(
`ID` int(11) NOT NULL AUTO_INCREMENT,
`CODE` char(5) COLLATE utf8_slovenian_ci DEFAULT NULL,
`NAME` char(100) COLLATE utf8_slovenian_ci DEFAULT NULL,
`EDITDATE` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`ID`),
UNIQUE KEY `CODE` (`CODE`),
KEY `NAME` (`NAME`)
)
ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_slovenian_ci;
CREATE TABLE IF NOT EXISTS `t3`
(
`ID` int(11) NOT NULL AUTO_INCREMENT,
`CODE` char(5) COLLATE utf8_slovenian_ci DEFAULT NULL,
`NAME` char(100) COLLATE utf8_slovenian_ci DEFAULT NULL,
`EDITDATE` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`ID`),
UNIQUE KEY `CODE` (`CODE`),
KEY `NAME` (`NAME`)
)
ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_slovenian_ci;
For view and edit data query like this is used:
select `t1`.*, `t2`.NAME NAME_T2, `t3`.NAME NAME_T3
from `t1`
left outer join `t2` on `t2`.CODE = `t1`.CODE_T2
left outer join `t3` on `t3`.CODE = `t1`.CODE_T3;
When there is need to change/edit some data in `t1` and after posting changed data to database table cursor should stay at same place. It behaved properly in MySQL version 5.5.28, but at some MySQL version 5.5.xx database no longer return autoincrement value from `t1`, therefore data access component used in aplication no longer can return to proper position in table after editing or inserting any value.
It happens only when more than one additional table is joined trough query. If only one table is joined it behave properly, so bug might be left unnoticed.
However, even if only one join table is used, but with additional sort from that joined table, autoincrement field is also not properly returned. For example in query like this:
select `t1`.*, `t2`.NAME NAME_T2
from `t1`
left outer join `t2` on `t2`.CODE = `t1`.CODE_T2
order by `t2`.NAME, `t1`.SOMEDATA1 ;
In cases like that, autoincrement value is not returned and table cursor is moved to first position after editing data.