[Info-Ingres] When a modify chops a table...

#1

Hi All.

* create table this_sucks as select * from appointments with
nojournaling\g

Executing . . .

(167531 rows)

continue

* modify this_sucks to hash on centre_id\g

Executing . . .

(167531 rows) <--- Which is nice! So the copy of the original table will
modify OK.

continue

* modify appointments to hash on centre_id\g

Executing . . .

(627 rows) <--- What!

That last modify managed to take the original table and reduce it down
to the 627 unique values in the entire table.

Anyone got any ideas about this!

I've seen this on II2.6 and on Ingres2006R2.

Marty

#2

Hi Marty,

This sounds like a technical support issue, so I have opened one for
you.

I can't say yet whether it is a known bug, but I will talk with you
tomorrow.

Michael

________________________________

From: info-ingres-bounces (AT) kettleriver...ting (DOT) com
[mailto:info-ingres-bounces (AT) kettleriverconsulting (DOT) com] On Behalf Of
Martin Bowes
Sent: 10 December 2008 16:01
To: Ingres and related product discussion forum
Subject: [Info-Ingres] When a modify chops a table...

Hi All.

* create table this_sucks as select * from appointments with
nojournaling\g

Executing . . .

(167531 rows)

continue

* modify this_sucks to hash on centre_id\g

Executing . . .

(167531 rows) <--- Which is nice! So the copy of the original table will
modify OK.

continue

* modify appointments to hash on centre_id\g

Executing . . .

(627 rows) <--- What!

That last modify managed to take the original table and reduce it down
to the 627 unique values in the entire table.

Anyone got any ideas about this!

I've seen this on II2.6 and on Ingres2006R2.

Marty

#3

Mad Marty wrote:

Quote:

* modify this_sucks to hash on centre_id\g

Executing . . .

(167531 rows) <--- Which is nice! So the copy of the original table will
modify OK.

continue

* modify appointments to hash on centre_id\g

Executing . . .

(627 rows) <--- What!

I've never seen anything like that one before, and I can't really think
of any obvious way that it could happen. What was the storage
structure of centre_id before the bad modify?

#4

The really funny part...

Is that I only found this when building a test case for a problem with the rand() function.

Marty

________________________________

From: info-ingres-bounces (AT) kettleriver...ting (DOT) com on behalf of Michael Dyer
Sent: Wed 10/12/2008 17:11
To: Ingres and related product discussion forum
Subject: Re: [Info-Ingres] When a modify chops a table...

Hi Marty,

This sounds like a technical support issue, so I have opened one for you.

I can't say yet whether it is a known bug, but I will talk with you tomorrow.

Michael

________________________________

From: info-ingres-bounces (AT) kettleriver...ting (DOT) com [mailto:info-ingres-bounces (AT) kettleriverconsulting (DOT) com] On Behalf Of Martin Bowes
Sent: 10 December 2008 16:01
To: Ingres and related product discussion forum
Subject: [Info-Ingres] When a modify chops a table...

Hi All.

* create table this_sucks as select * from appointments with nojournaling\g

Executing . . .

(167531 rows)

continue

* modify this_sucks to hash on centre_id\g

Executing . . .

(167531 rows) <--- Which is nice! So the copy of the original table will modify OK.

continue

* modify appointments to hash on centre_id\g

Executing . . .

(627 rows) <--- What!

That last modify managed to take the original table and reduce it down to the 627 unique values in the entire table.

Anyone got any ideas about this!

I've seen this on II2.6 and on Ingres2006R2.

Marty

#5

centre_id was integer not null not default.

When I complete a testcase tomorrow I'll send it to the group.

Marty

________________________________

From: info-ingres-bounces (AT) kettleriver...ting (DOT) com on behalf of kbcomput (AT) kbcomputer (DOT) com
Sent: Wed 10/12/2008 18:26
To: Ingres and related product discussion forum
Subject: Re: [Info-Ingres] When a modify chops a table...

Mad Marty wrote:

Quote:

* modify this_sucks to hash on centre_id\g

Executing . . .

(167531 rows) <--- Which is nice! So the copy of the original table will
modify OK.

continue

* modify appointments to hash on centre_id\g

Executing . . .

(627 rows) <--- What!

I've never seen anything like that one before, and I can't really think
of any obvious way that it could happen. What was the storage
structure of centre_id before the bad modify?

_______________________________________________
Info-Ingres mailing list
Info-Ingres (AT) kettleriverconsulting (DOT) com
http://www.kettleriverconsulting.com...fo/info-ingres

#6

Quote:

I've never seen anything like that one before, and I can't really think
of any obvious way that it could happen. What was the storage
structure of centre_id before the bad modify?

In general there is one possibility where modify removes rows
silently. This is when the table is a heap and defined with
"noduplicates". In a heap table "noduplicates" isn't checked, but when
modifying to another structure duplicate rows are silently removed -
this is the expected and documented behaviour.
This is probably not the case here, I would expect the same behaviour
for the first modify too, but just to let you know. When I saw this
the first time I was really surprised .......

Cheers
Kristoff

#7

Hi Kristoff,

Well spotted!

The table appointments is created as heap with noduplicates.

The table this_sucks was created as select * from appointments...which
creates a table in which duplicates are allowed.

Ergo the modify to hash silently removed duplicates from appointments
but not from this_sucks.

Where exactly is that documented behaviour?

Marty

-----Original Message-----
From: info-ingres-bounces (AT) kettleriver...ting (DOT) com
[mailto:info-ingres-bounces (AT) kettleriverconsulting (DOT) com] On Behalf Of
Kristoff
Sent: 11 December 2008 08:26
To: info-ingres (AT) kettleriverconsulting (DOT) com
Subject: Re: [Info-Ingres] When a modify chops a table...

Quote:

I've never seen anything like that one before, and I can't really
think
of any obvious way that it could happen. What was the storage
structure of centre_id before the bad modify?

In general there is one possibility where modify removes rows
silently. This is when the table is a heap and defined with
"noduplicates". In a heap table "noduplicates" isn't checked, but when
modifying to another structure duplicate rows are silently removed -
this is the expected and documented behaviour.
This is probably not the case here, I would expect the same behaviour
for the first modify too, but just to let you know. When I saw this
the first time I was really surprised .......

Cheers
Kristoff
_______________________________________________
Info-Ingres mailing list
Info-Ingres (AT) kettleriverconsulting (DOT) com
http://www.kettleriverconsulting.com...fo/info-ingres

#8

Just found the documentation on this...

[NO]DUPLICATES
Allows or disallows duplicate rows in the table. This option does not
affect
a table created as heap. Heap tables always accept duplicate rows
regardless of the setting of this option. If a heap table is created and
specified with NODUPLICATES, the heap table modified to a different
table
structure, the NODUPLICATES option is enforced. (By default, all
structures accept duplicate rows.)
The DUPLICATES setting can be overridden by specifying a unique key for
a table in the MODIFY statement.

I suppose I sorta expected it to say 'by silently removing the offending
rows'.

But I'd have to say that it should generate an error along the lines of
trying to impose a unique key on non-unique data.
E_US1591 MODIFY: table could not be modified because rows contain
duplicate keys.

Whats the general feeling on this?

Marty

#9

Ah ... I overlooked that you were modifying two different tables. So
we have an explanatiion ...
I agree, that this is quite confusing (which is the reason why I had
created KB 415248 in servicedesk)

May be an enhancement request should be opened.

Cheers
Kristoff

#10

Hi All,

Here's a simple test case....
sql bowtest << SQL_END
set result_structure heap\g
create table x(a integer) with noduplicates\g
insert into x values(1)\g
insert into x values(2)\g\g
insert into x values(3)\g\g\g
commit\g

select * from x\p\g
modify x to hash on a\p\g
select * from x\p\g
commit;
\p\g
drop table x\g
\q
SQL_END

Marty

-----Original Message-----
From: info-ingres-bounces (AT) kettleriver...ting (DOT) com
[mailto:info-ingres-bounces (AT) kettleriverconsulting (DOT) com] On Behalf Of
Martin Bowes
Sent: 11 December 2008 09:13
To: Ingres and related product discussion forum
Cc: Kristoff
Subject: Re: [Info-Ingres] When a modify chops a table...

Just found the documentation on this...

[NO]DUPLICATES
Allows or disallows duplicate rows in the table. This option does not
affect
a table created as heap. Heap tables always accept duplicate rows
regardless of the setting of this option. If a heap table is created and
specified with NODUPLICATES, the heap table modified to a different
table
structure, the NODUPLICATES option is enforced. (By default, all
structures accept duplicate rows.)
The DUPLICATES setting can be overridden by specifying a unique key for
a table in the MODIFY statement.

I suppose I sorta expected it to say 'by silently removing the offending
rows'.

But I'd have to say that it should generate an error along the lines of
trying to impose a unique key on non-unique data.
E_US1591 MODIFY: table could not be modified because rows contain
duplicate keys.

Whats the general feeling on this?

Marty

_______________________________________________
Info-Ingres mailing list
Info-Ingres (AT) kettleriverconsulting (DOT) com
http://www.kettleriverconsulting.com...fo/info-ingres