|
I am facing a problem with big queries using inner joins. Whenever I run the following query with 15 inner joins, the application crashes with Fatal Signal 11 (SIGSEGV). I tried to run the query on Interactive-SQL, but the application closes the connection with the database when I run it. Oddly enough, if I run the same query with one more inner join, or if I change it to left outer joins, the query runs correctly. The query is as follows: SELECT CAST(COUNT(*) AS VARCHAR) as linesCount FROM table01 INNER JOIN table02 ON table01.a = table02.a INNER JOIN table03 ON table02.a = table03.a INNER JOIN table04 ON table03.b = table04.a INNER JOIN table05 ON table01.a = table02.a INNER JOIN table06 ON table05.b = table06.a INNER JOIN table07 ON table05.c = table07.a INNER JOIN table08 ON table07.b = table08.a INNER JOIN table09 ON table01.b = table09.a INNER JOIN table10 ON table01.c = table10.a INNER JOIN table11 ON table01.a = table11.a INNER JOIN table12 ON table11.b = table12.a INNER JOIN table13 ON table01.a = table13.a INNER JOIN table14 ON table13.b = table14.a INNER JOIN table15 ON table13.c = table15.a INNER JOIN table16 ON table01.a = table16.a --INNER JOIN table17 ON table16.b = table17.a Shouldn't the application receive an exception instead of crashing? EDIT we're running on version 12.0.1.3769. The schema.xml is: <?xml version="1.0" encoding="utf-8" standalone="no"?> <ul:ulschema xmlns:ul="urn:ultralite"> <collation name="1252LATIN1" case_sensitive="no" utf8="yes"/> <options> <option name="checksum_level" value="0"/> <option name="dateformat" value="YYYY-MM-DD"/> <option name="dateorder" value="YMD"/> <option name="max_hash_size" value="4"/> <option name="nearestcentury" value="50"/> <option name="precision" value="30"/> <option name="scale" value="6"/> <option name="timeformat" value="HH:NN:SS.SSS"/> <option name="timestampwithtimezoneformat" value="YYYY-MM-DD HH:NN:SS.SSS+HH:NN"/> <option name="timestampformat" value="YYYY-MM-DD HH:NN:SS.SSS"/> <option name="timestampincrement" value="1"/> </options> <tables> <table name="table01" sync="changes"> <columns> <column name="a" type="integer" null="no"/> <column name="b" type="integer" null="yes"/> <column name="c" type="integer" null="yes"/> </columns> <primarykey> <primarycolumn name="a" direction="asc"/> </primarykey> <indexes/> </table> <table name="table02" sync="changes"> <columns> <column name="a" type="integer" null="no"/> </columns> <primarykey> <primarycolumn name="a" direction="asc"/> </primarykey> <indexes/> </table> <table name="table03" sync="changes"> <columns> <column name="a" type="integer" null="no"/> <column name="b" type="integer" null="no"/> </columns> <primarykey> <primarycolumn name="a" direction="asc"/> <primarycolumn name="b" direction="asc"/> </primarykey> <indexes/> </table> <table name="table04" sync="changes"> <columns> <column name="a" type="integer" null="no"/> </columns> <primarykey> <primarycolumn name="a" direction="asc"/> </primarykey> <indexes/> </table> <table name="table05" sync="changes"> <columns> <column name="a" type="integer" null="no"/> <column name="b" type="integer" null="no"/> <column name="c" type="integer" null="yes"/> </columns> <primarykey> <primarycolumn name="a" direction="asc"/> <primarycolumn name="b" direction="asc"/> </primarykey> <indexes/> </table> <table name="table06" sync="changes"> <columns> <column name="a" type="integer" null="no"/> </columns> <primarykey> <primarycolumn name="a" direction="asc"/> </primarykey> <indexes/> </table> <table name="table07" sync="changes"> <columns> <column name="a" type="integer" null="no"/> <column name="b" type="integer" null="yes"/> </columns> <primarykey> <primarycolumn name="a" direction="asc"/> </primarykey> <indexes/> </table> <table name="table08" sync="changes"> <columns> <column name="a" type="integer" null="no"/> </columns> <primarykey> <primarycolumn name="a" direction="asc"/> </primarykey> <indexes/> </table> <table name="table09" sync="changes"> <columns> <column name="a" type="integer" null="no"/> </columns> <primarykey> <primarycolumn name="a" direction="asc"/> </primarykey> <indexes/> </table> <table name="table10" sync="changes"> <columns> <column name="a" type="integer" null="no"/> </columns> <primarykey> <primarycolumn name="a" direction="asc"/> </primarykey> <indexes/> </table> <table name="table11" sync="changes"> <columns> <column name="a" type="integer" null="no"/> <column name="b" type="integer" null="no"/> </columns> <primarykey> <primarycolumn name="a" direction="asc"/> <primarycolumn name="b" direction="asc"/> </primarykey> <indexes/> </table> <table name="table12" sync="changes"> <columns> <column name="a" type="integer" null="no"/> </columns> <primarykey> <primarycolumn name="a" direction="asc"/> </primarykey> <indexes/> </table> <table name="table13" sync="changes"> <columns> <column name="a" type="integer" null="yes"/> <column name="b" type="integer" null="yes"/> <column name="c" type="integer" null="yes"/> <column name="d" type="integer" null="no"/> </columns> <primarykey> <primarycolumn name="d" direction="asc"/> </primarykey> <indexes/> </table> <table name="table14" sync="changes"> <columns> <column name="a" type="integer" null="no"/> </columns> <primarykey> <primarycolumn name="a" direction="asc"/> </primarykey> <indexes/> </table> <table name="table15" sync="changes"> <columns> <column name="a" type="integer" null="no"/> </columns> <primarykey> <primarycolumn name="a" direction="asc"/> </primarykey> <indexes/> </table> <table name="table16" sync="changes"> <columns> <column name="a" type="integer" null="no"/> <column name="b" type="integer" null="no"/> </columns> <primarykey> <primarycolumn name="a" direction="asc"/> <primarycolumn name="b" direction="asc"/> </primarykey> <indexes/> </table> <table name="table17" sync="changes"> <columns> <column name="a" type="integer" null="no"/> </columns> <primarykey> <primarycolumn name="a" direction="asc"/> </primarykey> <indexes/> </table> </tables> </ul:ulschema> |
|
This has been fixed. The fix will be available in 12.0.1.3845 and up. Thanks for the report. |
In order to save us some guesswork in reproducing this, could you please post the schema of your database? (Or something close enough that we could use). Also, the version number of your SQL Anywhere install would helpful.
Thanks.
Ok, I edited the question to add the information you asked.
Thank you. I have reproduced the crash and I'm investigating.
I have found that you are exceeding some limits in the UltraLite query optimizer, and a check was missed. If the case of 16 joins works, that was only by luck since out-of-bounds memory is being accessed.
We need to decide whether to increase the limits in the query optimizer, or to stop optimization at the present limits. In the latter case, your query will execute correctly, but inefficiently. To help with this decision, can you tell us if this is a query from a real application? Or, is it a test of how UltraLite handles queries that push software limits? If it is a query from a real application, what is an upper bound of the number of tables to be joined in a query?
Thanks for your input.
This query is from a real application, only the table names and field names were hidden. As this query is configurable, it may grow indefinitely, depending on the needs of the client, so it's hard to define an upper bound.
FYI there will always be practical limits in database systems, especially ones that run on mobile devices. SQLite has limits, as does UltraLite. I have fixed the crash and made our query optimizer capable of fully optimizing this query. However, UltraLite will not fully optimize queries with an arbitrary number of JOINs, which means that such queries may be very time consuming.
Thanks Andy, we will keep that in mind. As long as the application executes the query (albeit slower than usual), or throws an exception because of the limitation, we will be able to implement a decision-taking code in this scenario.