MySQL Forums
Forum List  »  Memory Storage Engine

Re: problem with memory table used twice in select
Posted by: Andrej Potocky
Date: December 15, 2006 07:12PM

Hello,

I think there is a bug in MySQL 5.0.27
When I tried to select data from
MEMORY table defined with few indexes, by select * from table where
indexcolumn1 = 'something' or indexcolumn2 = 'something2' or
indexcolumn3 = 'something3' the result will have doubled rows .. rows are physicaly unique in the table..

machine AMD64 3200, 1,5GB RAM, Freebsd 5.5 (9.12.2006) mysql 5.0.27
amd64


To reproduce:

create table:
CREATE TABLE `sklo` (
`uid` int(11) NOT NULL default '0',
`nick` char(12) NOT NULL default '0',
`uidKomu` int(11) NOT NULL default '0',
`komu` char(12) NOT NULL default '0',
`text` char(255) NOT NULL default '0',
`time` int(11) NOT NULL default '0',
`tajne` tinyint(4) default '0',
`miestnost` int(11) NOT NULL default '1',
`visible` tinyint(4) NOT NULL default '0',
KEY `time` (`time`),
KEY `uid` (`uid`),
KEY `uidKomu` (`uidKomu`),
KEY `miestnost` (`miestnost`)
) ENGINE=MEMOTY DEFAULT CHARSET=cp1250;

try put some data in it.. try to put some row with identical value in text field..


then run a select to some existing data.. like this:

SELECT *
FROM sklo
WHERE (
(
uid = '2'
OR uidKomu = '2'
)
AND visible = '1'
)
OR uid = '2'
OR (
miestnost = '1227479158'
AND tajne =0
AND visible = '1'
)
ORDER BY time DESC
LIMIT 30


there will be doubled rows in the result..

I tried this with the same table on MyISAM engine and the result was not doubled..

Best regards, Andrej Potocky

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: problem with memory table used twice in select
4067
December 15, 2006 07:12PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.