6 Replies Latest reply: May 16, 2013 4:53 AM by Lav Jain RSS

    EXISTS() - BUG or WAD

    Lucian Cotea

      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.

        • Re: EXISTS() - BUG or WAD
          Stefan Wühl

          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)
          ;

            • Re: EXISTS() - BUG or WAD
              Lucian Cotea

              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.

                • Re: EXISTS() - BUG or WAD
                  Stefan Wühl

                  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.

                    • Re: EXISTS() - BUG or WAD
                      Lucian Cotea

                      You have a point.

                       

                      Anybody else?

                        • Re: EXISTS() - BUG or WAD
                          David Evans

                          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'));

                          • Re: EXISTS() - BUG or WAD
                            Lav Jain

                            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