dynamobi/dynamobi — Issue #184: [LDB-137] basic merge with star join as ref table test fails
Author: dynamobi-build
Created: 2011-12-30 02:19:45+00:00
State: open
Labels: LDB

[reporter="schoi", created="Mon, 11 Sep 2006 15:49:48 -0500 (GMT-05:00)"]
This is the nightly failure for test-suite: dml-merge, test-case: joinRef.SqlTest.testSql

Either run "ant test -Dbh.test-names=dml-merge.joinRef" in open/luciddb/test/sql/dml/merge, or, copy repro below.

repro:

----------

-- setup:

create schema mergetest;

set schema 'mergetest';

create table EMP (

EMPNO integer not null,

FNAME varchar(15),

LNAME varchar(15),

SEX char(1),

DEPTNO integer,

MANAGER integer,

LOCID char(2) not null,

SAL decimal(10, 2),

COMMISION decimal(10, 2),

HOBBY varchar(25)

);

create table DEPT (

DEPTNO integer not null,

DNAME varchar(20),

LOCID char(2)

);

create table LOCATION (

LOCID char(2) not null,

STREET varchar(30),

CITY varchar(20),

STATE char(2),

ZIP decimal(5,0)

);

insert into EMP values (100,'Bill','Bush','M',30,null,'HQ',50000,null,'Piano');

insert into EMP values(101,'Bob','Brown','M',50,100,'SJ',50000,null,'Skiing');

insert into EMP values(102,'Julie','Andrews','F',30,104,'MP',50000,null,'Rugby');

insert into EMP values(103,'Frank','Webber','M',20,102,'MP',50000,null,'Piano');

insert into EMP values(104,'Fritz','Fratz','M',10,100,'HQ',50000,null,'Rugby');

insert into EMP values(105,'Rich','Guy','M',30,102,'HQ',50000,null,'Gambling');

insert into EMP values(106,'Rob','Roy','M',40,102,'SF',50000,7,'Karate');

insert into EMP values(107,'Robin','Williams','M',40,103,'HQ',50000,10,'Telling Jokes');

insert into EMP values(108,'Mary','Reynolds','F',50,104,'SF',50000,null,null);

insert into EMP values(109,'Jack','Nife','M',40,100,'SF',50000,5,null);

insert into EMP values(110,'Jill','Jones','F',30,101,'MP',50000,null,null);

insert into DEPT values(10,'Accounting','SF');

insert into DEPT values(20,'Marketing','SF');

insert into DEPT values(30,'Development','HQ');

insert into DEPT values(40,'Sales','HQ');

insert into DEPT values(50,'Support','MP');

insert into LOCATION values('HQ','1730 South Amphlett Blvd','San Mateo','CA',94042);

insert into LOCATION values('SF','100 Market','San Francisco','CA',94987);

insert into LOCATION values('MP','770 Coleman','Menlo Park','CA',94025);

insert into LOCATION values('SJ','Main Street','Los Angeles','CA',92123);

-- Create table

create table EMPTEMP (

EMPNO integer not null,

FNAME varchar(15),

LNAME varchar(15),

SEX char(1),

DEPTNO integer,

DNAME varchar(15),

MANAGER integer,

MFNAME varchar(15),

MLNAME varchar(15),

LOCID char(2),

CITY varchar(8),

SAL decimal(10, 2),

COMMISION decimal(10, 2),

HOBBY varchar(25)

);

--

-- basic merge with star join as ref table

--

insert into emptemp(empno, deptno, manager, locid)

select empno, deptno, manager, locid from emp

where empno<=105;

select empno, deptno, dname, manager, locid, city from emptemp;

merge into emptemp et

using

(select * from emp e, dept d, emp m, location l

where e.manager = m.empno and

e.locid = l.locid and

e.deptno = d.deptno) as temp

on et.empno = temp.empno

when matched then

update set dname = temp.dname,

city = temp.city

when not matched then

insert (empno, deptno, dname, manager, locid, city)

values (temp.empno, temp.deptno, temp.dname, temp.manager, temp.locid, temp.city);

select empno, deptno, dname, manager, locid, city from emptemp;

+--------+---------+--------------+----------+--------+-----------+

| EMPNO | DEPTNO | DNAME | MANAGER | LOCID | CITY |

+--------+---------+--------------+----------+--------+-----------+

| 100 | 30 | | | HQ | |

| 101 | 50 | | 100 | SJ | |

| 102 | 30 | | 104 | MP | |

| 103 | 20 | | 102 | MP | |

| 104 | 10 | | 100 | HQ | |

| 105 | 30 | | 102 | HQ | |

| 106 | 40 | Sales| 102 | SF | San Fran |

| 107 | 40 | Sales| 103 | HQ | San Mate |

| 108 | 50 | Support | 104 | SF | San Fran |

| 109 | 40 | Sales| 100 | SF | San Fran |

| 110 | 30 | Development | 101 | MP | Menlo Pa |

+--------+---------+--------------+----------+--------+-----------+

-- EXPECTING:

-----------------------

+--------+---------+--------------+----------+--------+-----------+

| EMPNO | DEPTNO | DNAME | MANAGER | LOCID | CITY |

+--------+---------+--------------+----------+--------+-----------+

| 100 | 30 | | | HQ | |

| 101 | 50 | Support | 100 | SJ | Los Ange |

| 102 | 30 | Development | 104 | MP | Menlo Pa |

| 103 | 20 | Marketing | 102 | MP | Menlo Pa |

| 104 | 10 | Accounting | 100 | HQ | San Mate |

| 105 | 30 | Development | 102 | HQ | San Mate |

| 106 | 40 | Sales| 102 | SF | San Fran |

| 107 | 40 | Sales| 103 | HQ | San Mate |

| 108 | 50 | Support | 104 | SF | San Fran |

| 109 | 40 | Sales| 100 | SF | San Fran |

| 110 | 30 | Development | 101 | MP | Menlo Pa |

+--------+---------+--------------+----------+--------+-----------+