List:Cluster« Previous MessageNext Message »
From:Tomas Ulin Date:November 4 2004 6:06pm
Subject:Re: Strange behaviour of LEFT JOIN
View as plain text  
I've filed a bug-report at bugs.mysql.com   Bug #6435

T

Dmitry Rojkov wrote:

> I reproduced this situation on my notebook where mysql-4.1.4 was 
> configured as
> a cluster with one MGM, one DB and one API nodes and everything was fine
> with left join.
> I have installed the new code (with the same minimalistic config.ini) 
> and 'left join'
> has broken again on my notebook too.
>
> Configuration options are as follows
> ./configure \
> --prefix=/usr/local/stow/mysql-4.1.8-14 \
> --with-mysqld-user=mysql \
> --localstatedir=/data/var \
> --sysconfdir=/data/var \
> --with-extra-charsets=complex \
> --enable-thread-safe-client \
> --enable-local-infile \
> --with-openssl \
> --with-ndbcluster \
> --with-ndb-shm
>
> So I'm confused. It looks like this bug doesn't correlate with 
> config.ini.
>
> Dmitry
>
> Joseph E. Sacco, Ph.D. wrote:
>
>> Hmmm... That's odd...
>>
>> I doubt that it has anything to do with lack of RAM. NDB is very good at
>> complaining when it runs out of resources.  I guess I would simplify the
>> environment to make sure this failure is not masking some other issue.
>>
>> Suggestion:
>> * start out with a simple two DB node cluster using system defaults in
>> the config.ini file.
>> * create the two tables
>> * add the data
>> * run the select.
>> * see what happens.
>>
>> NOTE:
>> I ran your experiment on a PowerMac running Yellow Dog Linux 3.0.1 using
>> code built from BK in the past couple of days. The ordering of the
>> result set from a "SELECT" is endian dependent, which is why ordering of
>> the output on my machine differs from yours.
>>
>>
>> -Joseph
>>
>> ======================================================================
>>
>> On Wed, 2004-11-03 at 08:47, Dmitry Rojkov wrote:
>>  
>>
>>> Joseph,
>>>
>>> I've upgraded the code to the latest snapshot 
>>> (4.1.8-nightly-20041103), but with no success :(
>>> INNER JOIN works perfectly...
>>> May be the reason is a lack of RAM, but I see no warnings.
>>>
>>> Dmitry
>>>
>>> Joseph E. Sacco, Ph.D. wrote:
>>>
>>>   
>>>
>>>> Dmitry,
>>>>
>>>> When I run your example using a later version of the code all seems 
>>>> well
>>>> [see below].
>>>>
>>>>
>>>> -Joseph
>>>>
>>>> =====================================================================
>>>>
>>>> # mysql
>>>> Welcome to the MySQL monitor.  Commands end with ; or \g.
>>>> Your MySQL connection id is 1 to server version: 4.1.8
>>>>
>>>> Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
>>>>
>>>> mysql> use test;
>>>> Database changed
>>>> mysql> CREATE TABLE `test1` (
>>>>   ->   `SubscrID` int(11) NOT NULL auto_increment,
>>>>   ->   `UsrID` int(11) NOT NULL default '0',
>>>>   ->   PRIMARY KEY  (`SubscrID`),
>>>>   ->   KEY `idx_usrid` (`UsrID`)
>>>>   -> ) ENGINE=ndbcluster DEFAULT CHARSET=latin1;
>>>> Query OK, 0 rows affected (1.34 sec)
>>>>
>>>> mysql>
>>>> mysql> INSERT INTO `test1` VALUES (2,224),(3,224),(1,224);
>>>> Query OK, 3 rows affected (0.07 sec)
>>>> Records: 3  Duplicates: 0  Warnings: 0
>>>>
>>>> mysql>
>>>> mysql> CREATE TABLE `test2` (
>>>>   ->   `SbclID` int(11) NOT NULL auto_increment,
>>>>   ->   `SbcrID` int(11) NOT NULL default '0',
>>>>   ->   PRIMARY KEY  (`SbclID`),
>>>>   ->   KEY `idx_sbcrid` (`SbcrID`)
>>>>   -> ) ENGINE=ndbcluster DEFAULT CHARSET=latin1;
>>>> Query OK, 0 rows affected (1.26 sec)
>>>>
>>>> mysql>
>>>> mysql> INSERT INTO `test2` VALUES (3,2),(1,1),(2,1),(4,2);
>>>> Query OK, 4 rows affected (0.05 sec)
>>>> Records: 4  Duplicates: 0  Warnings: 0
>>>>
>>>> mysql> select * from test1;
>>>> +----------+-------+
>>>> | SubscrID | UsrID |
>>>> +----------+-------+
>>>> |        3 |   224 |
>>>> |        2 |   224 |
>>>> |        1 |   224 |
>>>> +----------+-------+
>>>> 3 rows in set (0.02 sec)
>>>>
>>>> mysql> select * from test2;
>>>> +--------+--------+
>>>> | SbclID | SbcrID |
>>>> +--------+--------+
>>>> |      4 |      2 |
>>>> |      3 |      2 |
>>>> |      2 |      1 |
>>>> |      1 |      1 |
>>>> +--------+--------+
>>>> 4 rows in set (0.01 sec)
>>>>
>>>> mysql> SELECT s.SubscrID,l.SbclID FROM test1 s left JOIN test2 l ON
>>>>   -> l.SbcrID=s.SubscrID WHERE s.UsrID=224;
>>>> +----------+--------+
>>>> | SubscrID | SbclID |
>>>> +----------+--------+
>>>> |        3 |   NULL |
>>>> |        2 |      4 |
>>>> |        2 |      3 |
>>>> |        1 |      1 |
>>>> |        1 |      2 |
>>>> +----------+--------+
>>>> 5 rows in set (0.03 sec)
>>>>
>>>> =========================================================================
> 
>>>>
>>>>
>>>> On Tue, 2004-11-02 at 10:32, Dmitry Rojkov wrote:
>>>>
>>>>
>>>>     
>>>>
>>>>> Hi,
>>>>>
>>>>> I'm testing MySQL cluster (4.1.6-gamma-nightly-20041013).
>>>>>
>>>>> I see that LEFT JOIN doesn't always work as I expect.
>>>>> For example...
>>>>> There are 2 tables: test1 and test2.
>>>>>
>>>>> CREATE TABLE `test1` (
>>>>> `SubscrID` int(11) NOT NULL auto_increment,
>>>>> `UsrID` int(11) NOT NULL default '0',
>>>>> PRIMARY KEY  (`SubscrID`),
>>>>> KEY `idx_usrid` (`UsrID`)
>>>>> ) ENGINE=ndbcluster DEFAULT CHARSET=latin1;
>>>>>
>>>>> INSERT INTO `test1` VALUES (2,224),(3,224),(1,224);
>>>>>
>>>>> CREATE TABLE `test2` (
>>>>> `SbclID` int(11) NOT NULL auto_increment,
>>>>> `SbcrID` int(11) NOT NULL default '0',
>>>>> PRIMARY KEY  (`SbclID`),
>>>>> KEY `idx_sbcrid` (`SbcrID`)
>>>>> ) ENGINE=ndbcluster DEFAULT CHARSET=latin1;
>>>>>
>>>>> INSERT INTO `test2` VALUES (3,2),(1,1),(2,1),(4,2);
>>>>>
>>>>> The query
>>>>> SELECT s.SubscrID,l.SbclID FROM test1 s left JOIN test2 l ON 
>>>>> l.SbcrID=s.SubscrID WHERE s.UsrID=224;
>>>>>
>>>>> shows the result
>>>>> +----------+--------+
>>>>> | SubscrID | SbclID |
>>>>> +----------+--------+
>>>>> |        3 |   NULL |
>>>>> |        1 |   NULL |
>>>>> |        2 |   NULL |
>>>>> +----------+--------+
>>>>> Looks like there are no corresponding rows in the table test2
>>>>>
>>>>> But if I use the MyISAM engine the result is
>>>>> +----------+--------+
>>>>> | SubscrID | SbclID |
>>>>> +----------+--------+
>>>>> |        1 |      1 |
>>>>> |        1 |      2 |
>>>>> |        2 |      3 |
>>>>> |        2 |      4 |
>>>>> |        3 |   NULL |
>>>>> +----------+--------+
>>>>>
>>>>> I can't find notes in the changelog that this issue was solved in 
>>>>> the current release.
>>>>>  
>>>>>       
>>>>
>
>

Thread
Strange behaviour of LEFT JOINDmitry Rojkov2 Nov
  • Re: Strange behaviour of LEFT JOINPh.D. Joseph E. Sacco2 Nov
    • Re: Strange behaviour of LEFT JOINDmitry Rojkov3 Nov
      • Re: Strange behaviour of LEFT JOINPh.D. Joseph E. Sacco3 Nov
        • Re: Strange behaviour of LEFT JOINDmitry Rojkov4 Nov
          • Re: Strange behaviour of LEFT JOINTomas Ulin4 Nov
            • Re: Strange behaviour of LEFT JOINJonas Oreland8 Nov