dynamobi/dynamobi — Issue #209: [LDB-112] merge seems to insert new records where it should not
Author: dynamobi-build
Created: 2011-12-30 02:22:04+00:00
State: open
Labels: LDB

[reporter="kvu", created="Fri, 30 Jun 2006 18:20:35 -0500 (GMT-05:00)"]
Given tables

bench10k having 10,000 rows with primary key kseq=1~10,000 and

bench100k having 100,000 rows with primary key kseq=1~100,000

Following adding of rows can happen (while we only have when matched update clause)

0: jdbc:luciddb:> merge into bench100k tr

. . . . . . . . > using bench10k as rf

. . . . . . . . > on tr."kseq" = rf."kseq"

. . . . . . . . > when matched then

. . . . . . . . > update set "k2" = 3;

10,000 rows affected (2.131 seconds)

0: jdbc:luciddb:>

0: jdbc:luciddb:> select count(*) from bench100k where "kseq" between 1 and 10000;

+---------+

| EXPR$0 |

+---------+

| 20000 |

+---------+

1 row selected (0.293 seconds)

0: jdbc:luciddb:>

0: jdbc:luciddb:> merge into bench100k tr

. . . . . . . . > using bench10k as rf

. . . . . . . . > on tr."kseq" = rf."kseq"

. . . . . . . . > when matched then

. . . . . . . . > update set "k2" = 3;

20,000 rows affected (2.125 seconds)

0: jdbc:luciddb:> select count(*) from bench100k;

+---------+

| EXPR$0 |

+---------+

| 110000 |

+---------+

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

To reproduce:

within directory ~/open/luciddb/test/sql/bench run ant gen-all-sql and ant copy-csv

at prompt of sqllineEngine run following sql statements:

create foreign data wrapper ff_jdbc

library 'class com.lucidera.farrago.namespace.flatfile.FlatFileDataWrapper'

language java;

create server ff_server

foreign data wrapper ff_jdbc

options(

directory '/home/kvu/open/luciddb/test/sql/bench/csv-copy',

file_extension '.csv',

ctrl_file_extension '.bcp',

field_delimiter ',',

line_delimiter '\n',

quote_char '"',

escape_char '',

with_header 'yes',

num_rows_scan '1'

);

create schema ff_schema;

create foreign table ff_schema."bench10K" (

"kseq" bigint,

"k2" bigint,

"k4" bigint,

"k5" bigint,

"k10" bigint,

"k25" bigint,

"k100" bigint,

"k1k" bigint,

"k10k" bigint,

"k40k" bigint,

"k100k" bigint,

"k250k" bigint,

"k500k" bigint

)

server ff_server

options (

SCHEMA_NAME 'BCP',

filename 'bench10K'

);

create foreign table ff_schema."bench100K" (

"kseq" bigint,

"k2" bigint,

"k4" bigint,

"k5" bigint,

"k10" bigint,

"k25" bigint,

"k100" bigint,

"k1k" bigint,

"k10k" bigint,

"k40k" bigint,

"k100k" bigint,

"k250k" bigint,

"k500k" bigint

)

server ff_server

options (

SCHEMA_NAME 'BCP',

filename 'bench100K'

);

create schema s;

set schema 's';

create table BENCH10K (

"kseq" bigint primary key,

"k2" bigint,

"k4" bigint,

"k5" bigint,

"k10" bigint,

"k25" bigint,

"k100" bigint,

"k1k" bigint,

"k10k" bigint,

"k40k" bigint,

"k100k" bigint,

"k250k" bigint,

"k500k" bigint)

server sys_column_store_data_server;

-- create clustered index BENCH10K_ALL on BENCH10K("kseq","k2","k4","k5","k10","k25","k100","k1k","k10k","k40k","k100k","k250k","k500k");

create table BENCH100K (

"kseq" bigint primary key,

"k2" bigint,

"k4" bigint,

"k5" bigint,

"k10" bigint,

"k25" bigint,

"k100" bigint,

"k1k" bigint,

"k10k" bigint,

"k40k" bigint,

"k100k" bigint,

"k250k" bigint,

"k500k" bigint)

server sys_column_store_data_server;

-- create clustered index BENCH100K_ALL on BENCH100K("kseq","k2","k4","k5","k10","k25","k100","k1k","k10k","k40k","k100k","k250k","k500k");

INSERT INTO s.BENCH10K

SELECT "kseq","k2","k4","k5","k10","k25","k100","k1k","k10k","k40k","k100k","k250k","k500k"

FROM ff_schema."bench10K";

INSERT INTO s.BENCH100K

SELECT "kseq","k2","k4","k5","k10","k25","k100","k1k","k10k","k40k","k100k","k250k","k500k"

FROM ff_schema."bench100K";

analyze table bench10k compute statistics for all columns;

analyze table bench100k compute statistics for all columns;

delete from bench100k;

INSERT INTO s.BENCH100K

SELECT "kseq","k2","k4","k5","k10","k25","k100","k1k","k10k","k40k","k100k","k250k","k500k"

FROM ff_schema."bench100K";

--analyze table bench100k compute statistics for all columns;

merge into bench100k tr

using bench10k as rf

on tr."kseq" = rf."kseq"

when matched then

update set "k2" = 3;

select count(*) from bench100k where "kseq" between 1 and 10000;

merge into bench100k tr

using bench10k as rf

on tr."kseq" = rf."kseq"

when matched then

update set "k2" = 3;

select count(*) from bench100k where "kseq" between 1 and 10000;