List:General Discussion« Previous MessageNext Message »
From:Michael Widenius Date:December 20 1999 3:25am
Subject:RE: Serious Error in INSERT...SELECT
View as plain text  
Hi!

>>>>> "Jason" == Jason  <Jason@stripped> writes:

> Description:
>   INSERT...SELECT fails to insert the correct rows.
> 
> >How-To-Repeat:
> >create table band (bandID MEDIUMINT UNSIGNED NOT NULL 
PRIMARY KEY, payoutID SMALLINT UNSIGNED NOT NULL);
> Query OK, 0 rows affected (0.11 sec)
> 
> >insert into band (bandID,payoutID) VALUES 
(1,6),(2,6),(3,4),(4,9),(5,10),(6,1),(7,12),(8,12);
> Query OK, 8 rows affected (0.00 sec)
> Records: 8  Duplicates: 0  Warnings: 0
> 
> >create table tempP (payoutID SMALLINT UNSIGNED NOT NULL 
PRIMARY KEY);
> Query OK, 0 rows affected (0.09 sec)
> 
> >insert into tempP (payoutID) SELECT DISTINCT payoutID FROM band;
> Query OK, 6 rows affected (0.01 sec)
> Records: 6  Duplicates: 0  Warnings: 0
> 
> >select * from tempP;
> Empty set (0.00 sec)


The above happens with tables with fixed length rows that < 5 bytes.

The problem is that we have to pad out very short fixed size rows to be
able to have room for a delete link, and I had missed to correct the
key reference to the row.

Here is a patch for this:

((/my/monty/srclib/myisam)) diff -c /my/monty/master/mysql-3.23.7-alpha/myisam/m
i_search.c .
*** /my/monty/master/mysql-3.23.7-alpha/myisam/mi_search.c      Thu Dec  2 03:59:43 1999
--- ./mi_search.c       Mon Dec 20 05:19:38 1999
***************
*** 406,412 ****
    if (!(info->s->options &
        (HA_OPTION_PACK_RECORD | HA_OPTION_COMPRESS_RECORD)) &&
        pos != HA_OFFSET_ERROR)
!     pos/=info->s->base.reclength;

    switch (info->s->rec_reflength) {
  #if SIZEOF_OFF_T > 4
--- 406,412 ----
    if (!(info->s->options &
        (HA_OPTION_PACK_RECORD | HA_OPTION_COMPRESS_RECORD)) &&
        pos != HA_OFFSET_ERROR)
!     pos/=info->s->base.pack_reclength;

    switch (info->s->rec_reflength) {
  #if SIZEOF_OFF_T > 4

Regards,
Monty
Thread
Serious Error in INSERT...SELECTweb16 Dec
  • Re: Serious Error in INSERT...SELECTsinisa16 Dec
RE: Serious Error in INSERT...SELECTJason16 Dec
  • RE: Serious Error in INSERT...SELECTsinisa16 Dec
RE: Serious Error in INSERT...SELECTMichael Widenius20 Dec