System-only search of chained token parms and global _profile, _tag, and _security parms results in unexpected errors

[lmsurpre]

Describe the bug
I noticed that we have a couple cases commented out in our AbstractSearchTokenTest:

assertSearchReturnsComposition("subject:Basic.Coding", "http://example.org/codesystem|");

and

assertSearchReturnsComposition("subject:Basic.Identifier", "http://example.org/identifiersystem|");

I found that uncommenting the Coding one (and probably the Identifier one too) results in an exception like this:

Jun 24, 2021 4:25:00 PM com.ibm.fhir.persistence.jdbc.dao.impl.FHIRDbDAOImpl severe
SEVERE: 
      SELECT COUNT(*) 
        FROM Composition_LOGICAL_RESOURCES AS LR0
       WHERE LR0.IS_DELETED = 'N'
         AND EXISTS (
      SELECT 1 
        FROM Composition_LOGICAL_RESOURCES AS LR1 
  INNER JOIN Composition_TOKEN_VALUES_V AS P2 ON P2.LOGICAL_RESOURCE_ID = LR1.LOGICAL_RESOURCE_ID 
  INNER JOIN Basic_LOGICAL_RESOURCES AS LR2 ON LR2.LOGICAL_ID = P2.TOKEN_VALUE
         AND P2.PARAMETER_NAME_ID = 1396
         AND P2.CODE_SYSTEM_ID = 20038
         AND LR2.IS_DELETED = 'N' 
  INNER JOIN Basic_RESOURCE_TOKEN_REFS AS P3 ON P3.LOGICAL_RESOURCE_ID = LR2.LOGICAL_RESOURCE_ID
         AND P3.PARAMETER_NAME_ID = 20003
         AND ((P3.CODE_SYSTEM_ID = 20024))
       WHERE LR1.IS_DELETED = 'N'
         AND LR1.LOGICAL_RESOURCE_ID = LR0.LOGICAL_RESOURCE_ID) [probeId=7f-0-0-1-51390d80-6674-4956-82a1-7dd977ee2677]
java.sql.SQLSyntaxErrorException: Column 'P3.CODE_SYSTEM_ID' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE  statement then 'P3.CODE_SYSTEM_ID' is not a column in the target table.
	at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown Source)
	at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown Source)
	at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(Unknown Source)
	at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(Unknown Source)
	at org.apache.derby.impl.jdbc.EmbedConnection.handleException(Unknown Source)
	at org.apache.derby.impl.jdbc.ConnectionChild.handleException(Unknown Source)
	at org.apache.derby.impl.jdbc.EmbedPreparedStatement.<init>(Unknown Source)
	at org.apache.derby.impl.jdbc.EmbedPreparedStatement42.<init>(Unknown Source)
	at org.apache.derby.jdbc.Driver42.newEmbedPreparedStatement(Unknown Source)
	at org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown Source)
	at org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown Source)
	at com.ibm.fhir.database.utils.pool.PooledConnection.prepareStatement(PooledConnection.java:429)
	at com.ibm.fhir.database.utils.query.QueryUtil.prepareSelect(QueryUtil.java:49)
	at com.ibm.fhir.persistence.jdbc.dao.impl.FHIRDbDAOImpl.runCountQuery(FHIRDbDAOImpl.java:320)
	at com.ibm.fhir.persistence.jdbc.dao.impl.ResourceDAOImpl.searchCount(ResourceDAOImpl.java:778)
	at com.ibm.fhir.persistence.jdbc.impl.FHIRPersistenceJDBCImpl.newSearch(FHIRPersistenceJDBCImpl.java:700)
	at com.ibm.fhir.persistence.jdbc.impl.FHIRPersistenceJDBCImpl.search(FHIRPersistenceJDBCImpl.java:661)
	at com.ibm.fhir.persistence.test.common.AbstractPersistenceTest.runQueryTest(AbstractPersistenceTest.java:198)
	at com.ibm.fhir.persistence.test.common.AbstractPersistenceTest.runQueryTest(AbstractPersistenceTest.java:161)
	at com.ibm.fhir.persistence.search.test.AbstractPLSearchTest.searchReturnsResource(AbstractPLSearchTest.java:155)
	at com.ibm.fhir.persistence.search.test.AbstractPLSearchTest.searchReturnsResource(AbstractPLSearchTest.java:147)
	at com.ibm.fhir.persistence.search.test.AbstractPLSearchTest.assertSearchReturnsComposition(AbstractPLSearchTest.java:209)
	at com.ibm.fhir.persistence.search.test.AbstractSearchTokenTest.testSearchToken_Coding_chained(AbstractSearchTokenTest.java:396)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at org.testng.internal.MethodInvocationHelper.invokeMethod(MethodInvocationHelper.java:132)
	at org.testng.internal.TestInvoker.invokeMethod(TestInvoker.java:599)
	at org.testng.internal.TestInvoker.invokeTestMethod(TestInvoker.java:174)
	at org.testng.internal.MethodRunner.runInSequence(MethodRunner.java:46)
	at org.testng.internal.TestInvoker$MethodInvocationAgent.invoke(TestInvoker.java:822)
	at org.testng.internal.TestInvoker.invokeTestMethods(TestInvoker.java:147)
	at org.testng.internal.TestMethodWorker.invokeTestMethods(TestMethodWorker.java:146)
	at org.testng.internal.TestMethodWorker.run(TestMethodWorker.java:128)
	at java.util.ArrayList.forEach(ArrayList.java:1257)
	at org.testng.TestRunner.privateRun(TestRunner.java:764)
	at org.testng.TestRunner.run(TestRunner.java:585)
	at org.testng.SuiteRunner.runTest(SuiteRunner.java:384)
	at org.testng.SuiteRunner.runSequentially(SuiteRunner.java:378)
	at org.testng.SuiteRunner.privateRun(SuiteRunner.java:337)
	at org.testng.SuiteRunner.run(SuiteRunner.java:286)
	at org.testng.SuiteRunnerWorker.runSuite(SuiteRunnerWorker.java:53)
	at org.testng.SuiteRunnerWorker.run(SuiteRunnerWorker.java:96)
	at org.testng.TestNG.runSuitesSequentially(TestNG.java:1218)
	at org.testng.TestNG.runSuitesLocally(TestNG.java:1140)
	at org.testng.TestNG.runSuites(TestNG.java:1069)
	at org.testng.TestNG.run(TestNG.java:1037)
	at org.testng.remote.AbstractRemoteTestNG.run(AbstractRemoteTestNG.java:115)
	at org.testng.remote.RemoteTestNG.initAndRun(RemoteTestNG.java:251)
	at org.testng.remote.RemoteTestNG.main(RemoteTestNG.java:77)
Caused by: ERROR 42X04: Column 'P3.CODE_SYSTEM_ID' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE  statement then 'P3.CODE_SYSTEM_ID' is not a column in the target table.
	at org.apache.derby.iapi.error.StandardException.newException(Unknown Source)
	at org.apache.derby.iapi.error.StandardException.newException(Unknown Source)
	at org.apache.derby.impl.sql.compile.ColumnReference.bindExpression(Unknown Source)
	at org.apache.derby.impl.sql.compile.ColumnReference.bindExpression(Unknown Source)
	at org.apache.derby.impl.sql.compile.BinaryOperatorNode.bindExpression(Unknown Source)
	at org.apache.derby.impl.sql.compile.BinaryComparisonOperatorNode.bindExpression(Unknown Source)
	at org.apache.derby.impl.sql.compile.BinaryOperatorNode.bindExpression(Unknown Source)
	at org.apache.derby.impl.sql.compile.BinaryLogicalOperatorNode.bindExpression(Unknown Source)
	at org.apache.derby.impl.sql.compile.AndNode.bindExpression(Unknown Source)
	at org.apache.derby.impl.sql.compile.JoinNode.bindExpression(Unknown Source)
	at org.apache.derby.impl.sql.compile.JoinNode.deferredBindExpressions(Unknown Source)
	at org.apache.derby.impl.sql.compile.JoinNode.bindResultColumns(Unknown Source)
	at org.apache.derby.impl.sql.compile.FromList.bindResultColumns(Unknown Source)
	at org.apache.derby.impl.sql.compile.SelectNode.bindResultColumns(Unknown Source)
	at org.apache.derby.impl.sql.compile.SubqueryNode.bindExpression(Unknown Source)
	at org.apache.derby.impl.sql.compile.BinaryOperatorNode.bindExpression(Unknown Source)
	at org.apache.derby.impl.sql.compile.BinaryLogicalOperatorNode.bindExpression(Unknown Source)
	at org.apache.derby.impl.sql.compile.AndNode.bindExpression(Unknown Source)
	at org.apache.derby.impl.sql.compile.SelectNode.bindExpressions(Unknown Source)
	at org.apache.derby.impl.sql.compile.DMLStatementNode.bindExpressions(Unknown Source)
	at org.apache.derby.impl.sql.compile.DMLStatementNode.bind(Unknown Source)
	at org.apache.derby.impl.sql.compile.CursorNode.bindStatement(Unknown Source)
	at org.apache.derby.impl.sql.GenericStatement.prepMinion(Unknown Source)
	at org.apache.derby.impl.sql.GenericStatement.prepare(Unknown Source)
	at org.apache.derby.impl.sql.conn.GenericLanguageConnectionContext.prepareInternalStatement(Unknown Source)
	... 45 more

In both cases, there is a comment that refers to #1409 but that issue is already addressed and so I think these should be working now.

Environment
4.8.3

To Reproduce
Steps to reproduce the behavior:

1. Uncomment these lines in AbstractSearchTokenTest
2. Execute JDBCSearchTokenTest

Note the exception.

Expected behavior
The tests are uncommented and they pass.

Additional context
The non-chained version of these searches is working now. Here is the working SQL for assertSearchReturnsSavedResource("Coding", "http://example.org/codesystem%7C");

Jun 24, 2021 4:45:22 PM com.ibm.fhir.database.utils.query.QueryUtil prepareSelect
FINE:      query string: 
      SELECT R.RESOURCE_ID, R.LOGICAL_RESOURCE_ID, R.VERSION_ID, R.LAST_UPDATED, R.IS_DELETED, R.DATA, LR.LOGICAL_ID 
        FROM (
      SELECT LR0.LOGICAL_RESOURCE_ID, LR0.LOGICAL_ID, LR0.CURRENT_RESOURCE_ID 
        FROM Basic_LOGICAL_RESOURCES AS LR0
       WHERE LR0.IS_DELETED = 'N'
         AND EXISTS (
      SELECT 1 
        FROM Basic_LOGICAL_RESOURCES AS LR1 
  INNER JOIN Basic_TOKEN_VALUES_V AS P2 ON P2.LOGICAL_RESOURCE_ID = LR1.LOGICAL_RESOURCE_ID
         AND P2.PARAMETER_NAME_ID = 20003
         AND ((P2.CODE_SYSTEM_ID = 20024))
       WHERE LR1.IS_DELETED = 'N'
         AND LR1.LOGICAL_RESOURCE_ID = LR0.LOGICAL_RESOURCE_ID)) AS LR 
  INNER JOIN Basic_RESOURCES AS R ON LR.CURRENT_RESOURCE_ID = R.RESOURCE_ID
    ORDER BY LR.LOGICAL_RESOURCE_ID
 FETCH FIRST 2147483647 ROWS ONLY

[michaelwschroeder]

In addition to the failing searches documented above, the following also fail:

1. non-chained system-only search with _tag and _security search parameters:

.../Patient?_tag=abc|
.../Patient?_security=abc|

2. chained and reverse chained system-only search with _tag and _security search parameters:

.../Observation?subject:Patient._tag=abc|
.../Observation?subject:Patient._security=abc|
.../Organization?_has:Patient:organization:_tag=abc|
.../Organization?_has:Patient:organization:_security=abc|

3. chained and reverse chained search with _profile search parameter:

.../Observation?subject:Patient._profile=abc
.../Organization?_has:Patient:organization:_profile=abc

[lmsurpre]

I loaded up a patient with a tag, then a flag for that patient with the same tag and confirmed that searches like this are working as expected:
GET [base]/Flag?_tag=test&patient:Patient._tag=|test&patient.active=true