Lot of joined sources in SELECT

[agershun]

Some of sqllogictest tests include queries like this (they are located in the selec4.test file):

query ITTTIII rowsort join261
SELECT d6, x4, x7, x8, a1, b9, d5*934
  FROM t8, t7, t1, t6, t5, t9, t4
 WHERE (689=c5 OR 657=c5 OR c5=187)
   AND a1 in (324,241,330,352,32,829)
   AND d6=21
   AND b4=735
   AND e8 in (106,846,859,349)
   AND e7 in (248,356,503)
   AND b9 in (214,122,211,913,900,214,524,688)

Usually they return about 20000 records from huge cube space. Current version of AlaSQL does not like such huge queries. The goal is to resolve this problem.

[mathiasrw]

I ran a detailed test of select5.test

https://github.com/mathiasrw/alasql-logictest/blob/master/Details%20on%20select5.test.md

There is in total 700 rows of data (all data like (1,6,'table t3 row 1')) spred in 64 different tables. Most tables has 10 rows. It looks like we at getting some truly exponential growing run times based on the numbers of elements involved:

7 values from 7 tables with 7 constraints took 6 seconds:

Running 752/1436 
SELECT x15,x53,x55,x62,x43,x20,x8 FROM t43,t62,t20,t8,t55,t15,t53 WHERE b20=a8 AND a53=b62 AND b43=a20 AND a53=2 AND a62=b8 AND a55=b15 AND b55=a43
It took: 6376ms

8 values from 8 tables with 8 constraints took 1 minute:

Running 753/1436 
SELECT x22,x44,x30,x36,x48,x27,x2,x57 FROM t48,t57,t27,t2,t36,t30,t44,t22 WHERE b30=a48 AND b44=a27 AND a22=b27 AND b22=a36 AND b48=a57 AND a57=7 AND a30=b36 AND a44=b2 
It took: 65855ms

10 values from 10 tables with 10 constraints took 2.4 hours:

Running 782/1436 
SELECT x45,x21,x32,x25,x24,x43,x19,x39,x55,x35 FROM t19,t55,t35,t45,t25,t21,t39,t32,t43,t24 WHERE b35=a32 AND b45=a24 AND b39=a35 AND a24=8 AND b21=a45 AND b19=a39 AND a43=b25 AND b32=a25 AND a21=b55 AND a55=b43 
It took: 8705257ms

At some point it would be good to look into if we can process the selections in a way where run times are less than exponential.

[agershun]

Yes, this is a challenge

[mathiasrw]

Fix released in v4.11.0