EXISTS() - BUG or WAD

[luciancotea]

Hello all,

If you run the following script:

TABLE1:
LOAD * INLINE [
    F1, F2
    1, a
    2, b
    3, c
];
LOAD * INLINE [
    F1, F2
    4, d
    4, e
]
WHERE NOT Exists(F1)
;

the last line from second table (4, e) will not be loaded despite the fact that 4 does not exists in TABLE1.

What do you think, is it a BUG or WAD (Work as Designed)?

I say it's a bug.

[swuehl]

WAD, because at the moment you load the last line from second table, the first line is already part of your data model, so F1 value 4 exists.

Rename F1 in your first load and use exists with two parameter:

TABLE1:
LOAD F1 as F11, F2 INLINE [
    F1, F2
    1, a
    2, b
    3, c
];

 
LOAD F1 as F11, F2 INLINE [
    F1, F2
    4, d
    4, e
]
WHERE NOT Exists(F11, F1)
;

[luciancotea]

Thank you for your input. I would like to discuss the behaviour, not the workaround.

I don't think it should behave like this because it's not natural/intuitive.

When I say:

"Load all the rows from TABLE2 into TABLE1 where values from F1 are missing"

...I don't expect to load only the first row for each missing value.

[swuehl]

The where clause is executed per input record. As the last record comes in, the condition is false.

WAD, even / though it may contradict your personal user expectation.

[luciancotea]

You have a point.

Anybody else?

[devans_1]

I think bloe might do the trick:

TABLE1:

LOAD F1, F2

INLINE [

     F1, F2

    1, a

    2, b

    3, c ];

LOAD F1, F2

INLINE [

    F1, F2

     4, d

     4, e

     5, f

     1, g ]

where ISNULL (Lookup ('F2', 'F1', F1, 'TABLE1'));

[#7]

Lucian,

1) swuehl/Stefan always has a point..whatever he says is authority for me....and I'm sure is authority for every community member

2)

As you've said:"the last line from second table (4, e) will not be loaded despite the fact that 4 does not exists in TABLE1"

from QV help:

"exists Determines whether a specific field value exists in a specified field of the data loaded so far. Field is a name or a string expression evaluating to a field name. The field must exist in the data loaded so far by the script"

field value exists in a specified field: so it is not about a previous table but the field.

data loaded so far: 4 has been already been loaded in F1 when the turn for (4,e) comes to be loaded, so it is not loaded

So,it might fit in the category AI(Against Intution) but as per the categories suggested by you, it is WAD hence you can't compare it with intution as then you'll have 3 categories:

BUG,WAD, AI(Against Intuition)

Hope it helps.

Regards