6 Replies Latest reply on Feb 25, 2015 10:09 AM by philmodjunk

    LEFT JOIN Fails when using Access Privileges



      LEFT JOIN Fails when using Access Privileges


      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
      m.timestamp_mod > ? AND (m.timestamp_mod > r.timestamp_mod OR r.timestamp_mod IS NULL ) " ;
      ] ;
      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,

        • 1. Re: LEFT JOIN Fails when using Access Privileges

          I've been double checking the reference doc from FileMaker Inc on SQL. Curiously, "Left Join" is not discussed in the section on Joins. Instead, reference is made to Left Outer Join as the join that inlcudes the left hand records even when there is no related record on the other side of the Join. Do you get any change in your results if you use Left Outer Join instead of Left Join?

          And possible bugs should be posted to Report an Issue where FileMaker personnel monitor those reports and respond when needed to get more info as well as to confirm and log bugs for correction in future releases.

          • 2. Re: LEFT JOIN Fails when using Access Privileges

            Phil, thanks for your response and question.



            A LEFT JOIN and LEFT OUTER JOIN are semantically identical, they would be implemented in the exact same way providing the exact same results because they have exact same meaning. However, to you point, they are different in syntax. Nevertheless, you will see that LEFT JOIN works everywhere else just as expected. 

            The direct answer to your question, is yes, it fails the same with regardless of which syntax is used -- LEFT JOIN or LEFT OUTER JOIN. You can check this for yourself by downloading the sample file and changing the syntax for yourself.

            BTW - as you will notice in the description and sample file, the LEFT JOIN works as expected in every scenario except one -- privileged access. That's the failure.

            thanks again and all the best, M

            • 3. Re: LEFT JOIN Fails when using Access Privileges

              Record Level Access Control can make the data from a record inaccessible which is clearly the cause of the trouble here. I repeat my suggestion that you post this info to Report an Issue. (you can save some typing by posting a link to this thread as part of your report.)

              • 4. Re: LEFT JOIN Fails when using Access Privileges

                Hey Phil thanks for the suggestion. I spoke with Filemaker early today and thought I posted where I was told. Guess that was my mistake ... so thx ... 

                • 5. Re: LEFT JOIN Fails when using Access Privileges

                  Michael Tisdale:

                  Thank you for your post.

                  Our Development and Testing departments are aware of this issue.  In general, FileMaker does not support ternary logic (http://en.wikipedia.org/wiki/Three-valued_logic) and treats NULL values not as unknown, but as False.

                  I have attached your post to the original report.  When more information becomes available, I will post again.

                  On a side note, most customer questions posted to the "Using FileMaker Pro" boards are answered by other forum members, so Technical Support directs most customers to this board.  Since your scenario was determined an issue, posting an alert to he "Report an Issue" board linking back to this posting was perfect.  In the future, if you run into another issue using FileMaker, feel free to post directly to the "Report an Issue" board.

                  FileMaker, Inc.

                  • 6. Re: LEFT JOIN Fails when using Access Privileges

                    Whether or not this is officially classed as a "bug" by FileMaker, this is clearly an Issue and so:

                    An entry in the Known Bugs List has been linked to this Issue Report. Any Comments/Questions/Suggested Corrections should be posted here or in a new thread. Please do not post such comments to the Known Bugs List thread.