#141 Cannot find table in Access Query (although table exists)

[adam brin]

We're having trouble with a "query" in access (exception below) that states that a table (UpperBurnFloorStrata(JSW)) doesn't exist in a query (burngs(JSW)), though it appears to. I've included the database (deleting all of the content) for context, but it appears that it might be evaluating the wrong set of join objects in the QueryImpl class when stepping through the query.

---

DEBUG 2017-05-23 19:42:41,135 41888 [main []] (AccessDatabaseConverter.java:234) org.tdar.db.conversion.converters.AccessDatabaseConverter - burngs(JSW) SELECT [] 
ERROR 2017-05-23 19:42:41,136 41889 [main []] (AccessDatabaseConverter.java:237) org.tdar.db.conversion.converters.AccessDatabaseConverter - java.lang.IllegalStateException: Cannot find join table UpperBurnFloorStrata(JSW) (Query: burngs(JSW))
java.lang.IllegalStateException: Cannot find join table UpperBurnFloorStrata(JSW) (Query: burngs(JSW))
    at com.healthmarketscience.jackcess.impl.query.QueryImpl.getJoinExpr(QueryImpl.java:279) ~[jackcess-2.1.6.jar:?]
    at com.healthmarketscience.jackcess.impl.query.QueryImpl.getFromTables(QueryImpl.java:246) ~[jackcess-2.1.6.jar:?]
    at com.healthmarketscience.jackcess.impl.query.BaseSelectQueryImpl.getFromTables(BaseSelectQueryImpl.java:125) ~[jackcess-2.1.6.jar:?]
    at com.healthmarketscience.jackcess.impl.query.BaseSelectQueryImpl.toSQLSelectString(BaseSelectQueryImpl.java:53) ~[jackcess-2.1.6.jar:?]
    at com.healthmarketscience.jackcess.impl.query.SelectQueryImpl.toSQLString(SelectQueryImpl.java:40) ~[jackcess-2.1.6.jar:?]
    at com.healthmarketscience.jackcess.impl.query.QueryImpl.toSQLString(QueryImpl.java:356) ~[jackcess-2.1.6.jar:?]
    at org.tdar.db.conversion.converters.AccessDatabaseConverter.lambda$0(AccessDatabaseConverter.java:235) ~[classes/:?]
    at java.util.ArrayList.forEach(ArrayList.java:1249) [?:1.8.0_111]
    at org.tdar.db.conversion.converters.AccessDatabaseConverter.dumpData(AccessDatabaseConverter.java:232) [classes/:?]
    at org.tdar.db.conversion.converters.DatasetConverter$Base.execute(DatasetConverter.java:211) [classes/:?]
    at org.tdar.core.bean.AbstractIntegrationTestCase.convertDatabase(AbstractIntegrationTestCase.java:1018) [test-classes/:?]
    at org.tdar.db.conversion.AccessConverterITCase.testDatabase(AccessConverterITCase.java:47) [test-classes/:?]
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[?:1.8.0_111]
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[?:1.8.0_111]
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[?:1.8.0_111]
    at java.lang.reflect.Method.invoke(Method.java:498) ~[?:1.8.0_111]
    at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:50) [junit-4.12.jar:4.12]
    at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12) [junit-4.12.jar:4.12]
    at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:47) [junit-4.12.jar:4.12]
    at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17) [junit-4.12.jar:4.12]
    at org.junit.internal.runners.statements.RunBefores.evaluate(RunBefores.java:26) [junit-4.12.jar:4.12]
    at org.springframework.test.context.junit4.statements.RunBeforeTestMethodCallbacks.evaluate(RunBeforeTestMethodCallbacks.java:75) [spring-test-4.3.8.RELEASE.jar:4.3.8.RELEASE]
    at org.junit.internal.runners.statements.RunAfters.evaluate(RunAfters.java:27) [junit-4.12.jar:4.12]
    at org.springframework.test.context.junit4.statements.RunAfterTestMethodCallbacks.evaluate(RunAfterTestMethodCallbacks.java:86) [spring-test-4.3.8.RELEASE.jar:4.3.8.RELEASE]
    at org.junit.rules.TestWatcher$1.evaluate(TestWatcher.java:55) [junit-4.12.jar:4.12]
    at org.junit.rules.TestWatcher$1.evaluate(TestWatcher.java:55) [junit-4.12.jar:4.12]
    at org.junit.rules.RunRules.evaluate(RunRules.java:20) [junit-4.12.jar:4.12]
    at org.springframework.test.context.junit4.statements.SpringRepeat.evaluate(SpringRepeat.java:84) [spring-test-4.3.8.RELEASE.jar:4.3.8.RELEASE]
    at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:325) [junit-4.12.jar:4.12]
    at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:252) [spring-test-4.3.8.RELEASE.jar:4.3.8.RELEASE]
    at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:94) [spring-test-4.3.8.RELEASE.jar:4.3.8.RELEASE]
    at org.junit.runners.ParentRunner$3.run(ParentRunner.java:290) [junit-4.12.jar:4.12]
    at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:71) [junit-4.12.jar:4.12]
    at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:288) [junit-4.12.jar:4.12]
    at org.junit.runners.ParentRunner.access$000(ParentRunner.java:58) [junit-4.12.jar:4.12]
    at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:268) [junit-4.12.jar:4.12]
    at org.springframework.test.context.junit4.statements.RunBeforeTestClassCallbacks.evaluate(RunBeforeTestClassCallbacks.java:61) [spring-test-4.3.8.RELEASE.jar:4.3.8.RELEASE]
    at org.springframework.test.context.junit4.statements.RunAfterTestClassCallbacks.evaluate(RunAfterTestClassCallbacks.java:70) [spring-test-4.3.8.RELEASE.jar:4.3.8.RELEASE]
    at org.junit.runners.ParentRunner.run(ParentRunner.java:363) [junit-4.12.jar:4.12]
    at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.run(SpringJUnit4ClassRunner.java:191) [spring-test-4.3.8.RELEASE.jar:4.3.8.RELEASE]
    at org.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.run(JUnit4TestReference.java:86) [.cp/:?]
    at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38) [.cp/:?]
    at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:459) [.cp/:?]
    at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:678) [.cp/:?]
    at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:382) [.cp/:?]
    at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:192) [.cp/:?]

[adam brin]

I had meant to add, the root cause of the exception is calling:

           logger.debug("from:{}",(BaseSelectQuery)query).getFromTables());

or

            logger.debug("\t\t--> {} ", query.toSQLString());

[James Ahlborn]

Ticket moved from /p/openhms/bugs/21/

[James Ahlborn]

looks like there is a bug in how jackcess handles joins.

[James Ahlborn]

Have to admit, this is a tough one. This database has some complex queries in it. i'm struggling to figure out the logic acess uses to generate the query joins.

[James Ahlborn]

Fixed in trunk, will be in the 2.1.9 release.

[adam brin]

cool, thanks! I hope it turned into a fun puzzle (even if it was tough).

[James Ahlborn]

The underlying problem ended up being that ms access was "lying" to me. once i figured that part out, i was able to decipher the correct algorithm. in the end, i'm happy because i think i figured out the general solution. (initially, when i was trying the decipher the "deceptive" data, the algorithm i was coming up with was turning out very hacky, and i wasn't at all happy with it.)