AnsweredAssumed Answered

LEFT JOIN Fails when using Access Privileges

Question asked by mptwisdale on Feb 24, 2015
Latest reply on Feb 25, 2015 by philmodjunk

Title

LEFT JOIN Fails when using Access Privileges

Post

There is an issue when using access privileges and SQL. The Left JOIN will fail when there is a Null item in the right table. 
 
For your convenience, I have put together a simple file, three User accounts with two different access privileges and a simple SQL query to help you quickly understand the failure.
 
The file is as follows:
 
The three User accounts are: 
1. Admin, which has full access privileges and the password is left blank.
2. Michael and Mary Beth, which have Special Access, a custom privilege set with records viewable when the field, owner = Get ( AccountName ).
         
  • PW for Michael is michael
  •      
  • PW for Mary Beth is mary Beth
The SQL is as follows:
 
Let ( [
$TestTimeStamp = GetAsTimestamp ( "2/17/2015 8:55:08.937 AM" ) ;
sqlstmt =  
"Select m.id, r.id
FROM ljtest AS m
LEFT JOIN remoteljtest as r
ON m.id = r.id
WHERE
m.timestamp_mod > ? AND (m.timestamp_mod > r.timestamp_mod OR r.timestamp_mod IS NULL ) " ;
 
A=""
] ;
ExecuteSQL ( sqlstmt ; " > RemoteFile:   " ; ¶ ; $TestTimeStamp )
)
 
RUN the Test as follows:
 
Login to LJTest with Admin. Press the button to run the script containing the above SQL and it correctly returns the following:
 
6A3C12B5-8C78-44D1-BB6F-C830DE864412    > RemoteFile:   6A3C12B5-8C78-44D1-BB6F-C830DE864412
0B69718B-83D2-4EFA-B1E3-95D672CC680F    > RemoteFile:   0B69718B-83D2-4EFA-B1E3-95D672CC680F
D796E8BF-F08A-48B0-A879-54BAC6C25746    > RemoteFile:   D796E8BF-F08A-48B0-A879-54BAC6C25746
E6BF71B6-EAC9-4974-B0A6-CD74EC17CF48  > RemoteFile:   
 
Close the file and reopen using the account Michael. Now pressing the button incorrectly returns only
 
D796E8BF-F08A-48B0-A879-54BAC6C25746    > RemoteFile:   D796E8BF-F08A-48B0-A879-54BAC6C25746
 
rather than what is expected
 
D796E8BF-F08A-48B0-A879-54BAC6C25746    > RemoteFile:   D796E8BF-F08A-48B0-A879-54BAC6C25746
E6BF71B6-EAC9-4974-B0A6-CD74EC17CF48  > RemoteFile:   
 
Hope these details help. It is inconceivable that this behavior makes sense. I could imagne a rational that would say, 
"when access privileges are used in this way, there is no way for the system to know if, in fact, the right item is null or not because the user has limited access." 
However, this user gets an incomplete answer regarding items in his own set. This behavior will always leave unforseen wholes that 
will not be addressed except by special exception processing. Not desirable!
 
I hope this has been useful to filemaker. I am very eager to hear back. 
 
until then, all the best,
 

Outcomes