UNIQUE + CDX + set delete on

[Otto]

I have a question concerning: UNIQUE

Records:
natural order
------------------------------
101 DELETED
101
102
103
104


SET delete on
index on field->test TAG TEMPADR UNIQUE

then 101 is missing in the index file.

If the natural order is like this:
------------------------------
101
101 DELETED
102
103
104

101 is in the index scope.

Is this behavior correct?

Thanks in advance
Otto

[Enrico Maria Giordano]

I see no problem using this sample (xHarbour + BCC55):

Code: Select all  Expand view

REQUEST DBFCDX


FUNCTION MAIN()

    RDDSETDEFAULT( "DBFCDX" )

    DBCREATE( "BUGTEST", { { "CODICE", "N", 3, 0 } } )

    USE BUGTEST

    APPEND BLANK

    REPLACE FIELD -> codice WITH 101

    DELETE

    APPEND BLANK

    REPLACE FIELD -> codice WITH 101

    APPEND BLANK

    REPLACE FIELD -> codice WITH 102

    INDEX ON FIELD -> codice TO BUGTEST UNIQUE

    GO TOP

    WHILE !EOF()
        ? FIELD -> codice
        SKIP
    ENDDO

    CLOSE

    RETURN NIL

It correctly prints

101
102

EMG

[Otto]

Hello Enrico,

thank you for your answer.
Please try with:

SET DELETE ON

I only get 102 back.
Best regards,
Otto

[Enrico Maria Giordano]

Same result with Clipper.

EMG

[Otto]

Enrico, thank you.
I never noticed this behavior. I have to look after where I use unique in my index expression.
I don’t use it much.
Best regards,
Otto

[Enrico Maria Giordano]

I just report the problem to the Harbour and xHarbour mailing-lists. I will report back here the answers.

EMG

[Otto]

Enrico, thank you very much.

[Enrico Maria Giordano]

This is the answer from Przemyslaw:

The behavior is correct. Index contain record 1 with value 101 but
is filter by your SET DELETE ON filter. If you want to filter such
records when index is created then use USEFILTER clause in INDEX ON
command, i.e.:
INDEX ON FIELD -> codice TO BUGTEST UNIQUE USEFILTER
In such case record 1 (101) will not be added to filter but
record 2 which has the same key.

EMG

[James Bott]

Otto,

Instead of this:

SET delete on
index on field->test TAG TEMPADR UNIQUE

Try this:

index on field->test TAG TEMPADR UNIQUE FOR ! DELETED

I don't know if this will work, but it may skip deleted records when building the UNIQUE index. If so, then this should work regardless of the natural order of the records.

Regards,
James

[Enrico Maria Giordano]

Yes, FOR !Deleted() works fine.

EMG