Project

General

Profile

Bug #3171

a natural join combined with the :: operator will cause an unexpected result

Added by Greg Shah over 7 years ago. Updated over 7 years ago.

Status:
New
Priority:
Normal
Assignee:
-
Target version:
-
Start date:
Due date:
% Done:

0%

billable:
No
vendor_id:
GCD
case_num:
version:

History

#1 Updated by Greg Shah over 7 years ago

The following testcase can be found in uast/natural_joins_combine_with_colon_colon_with_surprising_result.p:

DEFINE TEMP-TABLE tt
   FIELD af AS INTEGER.
CREATE tt.
tt.af = 11.

FOR EACH book, EACH tt WHERE tt.af <> book.book-id:
   DISPLAY book-id WITH FRAME f0 DOWN TITLE "Natural Join Only".
   DOWN WITH FRAME f0.
END.

FIND FIRST tt.

FOR EACH book WHERE BUFFER tt::af <> book.book-id:
   DISPLAY book-id WITH FRAME f1 DOWN TITLE ":: Reference" COLUMN 20.
   DOWN WITH FRAME f1.
END.

FOR EACH book, EACH tt WHERE BUFFER tt::af <> book.book-id:
   DISPLAY book-id WITH FRAME f2 DOWN TITLE "Natural Join and :: Reference" COLUMN 34.
   DOWN WITH FRAME f2.
END.

FIND FIRST tt.

FOR EACH book WHERE tt.af <> book.book-id:
   DISPLAY book-id WITH FRAME f3 DOWN TITLE "No Join, No :: Reference" COLUMN 65.
   DOWN WITH FRAME f3.
END.

/* results */

/*
┌Natural Join Only┐┌:: Reference┐┌Natural Join and :: Reference┐┌No Join, No :: Reference┐
│ Book ID         ││ Book ID    ││ Book ID                     ││ Book ID                │
│────────         ││────────    ││────────                     ││────────                │
│00000001         ││00000001    ││00000001                     ││00000001                │
│00000002         ││00000002    ││00000002                     ││00000002                │
│00000003         ││00000003    ││00000003                     ││00000003                │
│00000004         ││00000004    ││00000004                     ││00000004                │
│00000005         ││00000005    ││00000005                     ││00000005                │
│00000006         ││00000006    ││00000006                     ││00000006                │
│00000007         ││00000007    ││00000007                     ││00000007                │
│00000008         ││00000008    ││00000008                     ││00000008                │
│00000009         ││00000009    ││00000009                     ││00000009                │
│00000010         ││00000010    ││00000010                     ││00000010                │
│00000012         ││00000012    ││00000011                     ││00000012                │
│00000013         ││00000013    ││00000012                     ││00000013                │
│00099999         ││00099999    ││00000013                     ││00099999                │
│                 ││            ││00099999                     ││                        │
│                 ││            ││                             ││                        │
│                 ││            ││                             ││                        │
│                 ││            ││                             ││                        │
└─────────────────┘└────────────┘└─────────────────────────────┘└────────────────────────┘

Procedure complete. Press space bar to continue.
*/

Logically, all 4 FOR EACH loops should have the same results. But one of these things is not like the other. The combination of the multi-table query and the dynamic field reference (inside a static WHERE clause) somehow combines to include the bookid eq 11 record unexpectedly.

Also available in: Atom PDF