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>

asked 11 Jan '13, 12:45

Pierotti's gravatar image

Pierotti
66227
accept rate: 0%

edited 11 Jan '13, 14:14

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.

(11 Jan '13, 13:22) Andy Quick
Replies hidden

Ok, I edited the question to add the information you asked.

(11 Jan '13, 13:47) Pierotti

Thank you. I have reproduced the crash and I'm investigating.

(14 Jan '13, 16:37) Andy Quick

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.

(15 Jan '13, 16:06) Andy Quick
Replies hidden

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.

(16 Jan '13, 07:34) Pierotti

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.

(16 Jan '13, 13:55) Andy Quick

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.

(16 Jan '13, 14:06) Pierotti
showing 3 of 7 show all flat view

This has been fixed. The fix will be available in 12.0.1.3845 and up. Thanks for the report.

permanent link

answered 16 Jan '13, 13:57

Andy%20Quick's gravatar image

Andy Quick
2.2k2737
accept rate: 45%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×131
×75
×60

question asked: 11 Jan '13, 12:45

question was seen: 878 times

last updated: 16 Jan '13, 14:06