Bug #3171
a natural join combined with the :: operator will cause an unexpected result
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.