Too many references to "advised": max 65535

[yilinxia]

Hi,

When running the following script

%%logica Lowest_Common_Ancestors_with_Name
@Engine("sqlite");

@AttachDatabase("mgdb","mgdb.db");
@Dataset("advised");
@Dataset("person");
@Dataset("dissertation");

Adv_Stu(advisor:, student:author) :- Advised(did:x, advisor:),Dissertation(did:y, author:), x=y;

@Recursive(Anc,33);
Anc(ancestor:advisor, student:m) distinct :- Adv_Stu(advisor:, student:m), m=63244;
Anc(ancestor:x, student:) distinct:- Adv_Stu(advisor:x, student:y),Anc(ancestor:y, student:);

Anc(ancestor:advisor, student:l) distinct :- Adv_Stu(advisor:, student:l), l=119280;
Anc(ancestor:x, student:) distinct:- Adv_Stu(advisor:x, student:y),Anc(ancestor:y, student:);

Common_Ancestors(pid_1:m, pid_2:l, anc_id: x) :- Anc(ancestor:x, student:m), 
Anc(ancestor:x, student:l), m=63244,l=119280 ;

Not_Lowest_Common_Ancestors(pid_1:m, pid_2:l, anc_id: x) :- Common_Ancestors(pid_1:m, pid_2:l, anc_id: x), 
Common_Ancestors(pid_1:m, pid_2:l, anc_id: y), Adv_Stu(advisor:x, student:y),m=63244,l=119280;

Lowest_Common_Ancestors_with_Name(pid:x, name:name) :- Common_Ancestors(pid_1:m, pid_2:l, anc_id: x),
~Not_Lowest_Common_Ancestors(pid_1:m, pid_2:l, anc_id: x), Person(pid:x,name:name),m=63244,l=119280;

I will encounter an error below

---------------------------------------------------------------------------
OperationalError                          Traceback (most recent call last)
File ~/opt/miniconda3/envs/logica/lib/python3.11/site-packages/pandas/io/sql.py:2200, in SQLiteDatabase.execute(self, sql, params)
   2199 try:
-> 2200     cur.execute(sql, *args)
   2201     return cur

OperationalError: too many references to "advised": max 65535

The above exception was the direct cause of the following exception:

DatabaseError                             Traceback (most recent call last)
Cell In[13], line 1
----> 1 get_ipython().run_cell_magic('logica', 'Lowest_Common_Ancestors_with_Name', '\n@Engine("sqlite");\n\n@AttachDatabase("mgdb","mgdb.db");\n@Dataset("advised");\n@Dataset("person");\n@Dataset("dissertation");\n\nAdv_Stu(advisor:, student:author) :- Advised(did:x, advisor:),Dissertation(did:y, author:), x=y;\n\n@Recursive(Anc,33);\nAnc(ancestor:advisor, student:m) distinct :- Adv_Stu(advisor:, student:m), m=63244;\nAnc(ancestor:x, student:) distinct:- Adv_Stu(advisor:x, student:y),Anc(ancestor:y, student:);\n\nAnc(ancestor:advisor, student:l) distinct :- Adv_Stu(advisor:, student:l), l=119280;\nAnc(ancestor:x, student:) distinct:- Adv_Stu(advisor:x, student:y),Anc(ancestor:y, student:);\n\nCommon_Ancestors(pid_1:m, pid_2:l, anc_id: x) :- Anc(ancestor:x, student:m), \nAnc(ancestor:x, student:l), m=63244,l=119280 ;\n\nNot_Lowest_Common_Ancestors(pid_1:m, pid_2:l, anc_id: x) :- Common_Ancestors(pid_1:m, pid_2:l, anc_id: x), \nCommon_Ancestors(pid_1:m, pid_2:l, anc_id: y), Adv_Stu(advisor:x, student:y),m=63244,l=119280;\n\nLowest_Common_Ancestors_with_Name(pid:x, name:name) :- Common_Ancestors(pid_1:m, pid_2:l, anc_id: x),\n~Not_Lowest_Common_Ancestors(pid_1:m, pid_2:l, anc_id: x), Person(pid:x,name:name),m=63244,l=119280;\n')

File ~/opt/miniconda3/envs/logica/lib/python3.11/site-packages/IPython/core/interactiveshell.py:2478, in InteractiveShell.run_cell_magic(self, magic_name, line, cell)
   2476 with self.builtin_trap:
   2477     args = (magic_arg_s, cell)
-> 2478     result = fn(*args, **kwargs)
   2480 # The code below prevents the output from being displayed
   2481 # when using magics with decodator @output_can_be_silenced
   2482 # when the last Python token in the expression is a ';'.
   2483 if getattr(fn, magic.MAGIC_OUTPUT_CAN_BE_SILENCED, False):

File ~/opt/miniconda3/envs/logica/lib/python3.11/site-packages/logica/colab_logica.py:128, in logica(line, cell)
    126 @register_cell_magic
    127 def logica(line, cell):
--> 128   Logica(line, cell, run_query=True)

File ~/opt/miniconda3/envs/logica/lib/python3.11/site-packages/logica/colab_logica.py:273, in Logica(line, cell, run_query)
    269   else:
    270     raise Exception('Logica only supports BigQuery, PostgreSQL and SQLite '
    271                     'for now.')   
--> 273   result_map = concertina_lib.ExecuteLogicaProgram(
    274     executions, sql_runner=sql_runner, sql_engine=engine)
    276 for idx, predicate in enumerate(predicates):
    277   t = result_map[predicate]

File ~/opt/miniconda3/envs/logica/lib/python3.11/site-packages/logica/common/concertina_lib.py:270, in ExecuteLogicaProgram(logica_executions, sql_runner, sql_engine, display_mode)
    267   sql_runner(preamble, sql_engine, is_final=False)
    269 concertina = Concertina(config, engine, display_mode=display_mode)
--> 270 concertina.Run()
    271 return engine.final_result

File ~/opt/miniconda3/envs/logica/lib/python3.11/site-packages/logica/common/concertina_lib.py:97, in Concertina.Run(self)
     95 def Run(self):
     96   while self.actions_to_run:
---> 97     self.RunOneAction()

File ~/opt/miniconda3/envs/logica/lib/python3.11/site-packages/logica/common/concertina_lib.py:90, in Concertina.RunOneAction(self)
     88 self.running_actions |= {one_action}
     89 self.UpdateDisplay()
---> 90 self.engine.Run(self.action[one_action].get('action', {}))
     91 self.running_actions -= {one_action}
     92 self.complete_actions |= {one_action}

File ~/opt/miniconda3/envs/logica/lib/python3.11/site-packages/logica/common/concertina_lib.py:32, in ConcertinaQueryEngine.Run(self, action)
     30   print('Running predicate:', predicate, end='')
     31 start = datetime.datetime.now()
---> 32 result = self.sql_runner(action['sql'], action['engine'],
     33                          is_final=(predicate in self.final_predicates))
     34 end = datetime.datetime.now()
     35 if self.print_running_predicate:

File ~/opt/miniconda3/envs/logica/lib/python3.11/site-packages/logica/colab_logica.py:173, in SqliteRunner.__call__(self, sql, engine, is_final)
    172 def __call__(self, sql, engine, is_final):
--> 173   return RunSQL(sql, engine, self.connection, is_final)

File ~/opt/miniconda3/envs/logica/lib/python3.11/site-packages/logica/colab_logica.py:160, in RunSQL(sql, engine, connection, is_final)
    158     print(sql)
    159     ShowError("Error while executing SQL:\n%s" % e)
--> 160     raise e
    161   return None
    162 else:

File ~/opt/miniconda3/envs/logica/lib/python3.11/site-packages/logica/colab_logica.py:153, in RunSQL(sql, engine, connection, is_final)
    150 try:
    151   if is_final:
    152     # For final predicates this SQL is always a single statement.
--> 153     return pandas.read_sql(sql, connection)
    154   else:
    155     connection.executescript(sql)

File ~/opt/miniconda3/envs/logica/lib/python3.11/site-packages/pandas/io/sql.py:633, in read_sql(sql, con, index_col, coerce_float, params, parse_dates, columns, chunksize, dtype_backend, dtype)
    631 with pandasSQL_builder(con) as pandas_sql:
    632     if isinstance(pandas_sql, SQLiteDatabase):
--> 633         return pandas_sql.read_query(
    634             sql,
    635             index_col=index_col,
    636             params=params,
    637             coerce_float=coerce_float,
    638             parse_dates=parse_dates,
    639             chunksize=chunksize,
    640             dtype_backend=dtype_backend,  # type: ignore[arg-type]
    641             dtype=dtype,
    642         )
    644     try:
    645         _is_table_name = pandas_sql.has_table(sql)

File ~/opt/miniconda3/envs/logica/lib/python3.11/site-packages/pandas/io/sql.py:2264, in SQLiteDatabase.read_query(self, sql, index_col, coerce_float, parse_dates, params, chunksize, dtype, dtype_backend)
   2253 def read_query(
   2254     self,
   2255     sql,
   (...)
   2262     dtype_backend: DtypeBackend | Literal["numpy"] = "numpy",
   2263 ) -> DataFrame | Iterator[DataFrame]:
-> 2264     cursor = self.execute(sql, params)
   2265     columns = [col_desc[0] for col_desc in cursor.description]
   2267     if chunksize is not None:

File ~/opt/miniconda3/envs/logica/lib/python3.11/site-packages/pandas/io/sql.py:2212, in SQLiteDatabase.execute(self, sql, params)
   2209     raise ex from inner_exc
   2211 ex = DatabaseError(f"Execution failed on sql '{sql}': {exc}")
-> 2212 raise ex from exc

DatabaseError: Execution failed on sql ....: too many references to "advised": max 65535

[yilinxia]

Split the recursion Anc into two will solve the problem

%%logica Lowest_Common_Ancestors_with_Name

@Engine("sqlite");

@AttachDatabase("mgdb","mgdb.db");
@Dataset("advised");
@Dataset("person");
@Dataset("dissertation");

Adv_Stu(advisor:, student:author) :- Advised(did:x, advisor:),Dissertation(did:y, author:), x=y;

@Recursive(Anc_1,33);
Anc_1(ancestor:advisor, student:m) distinct :- Adv_Stu(advisor:, student:m), m=63244;
Anc_1(ancestor:x, student:) distinct:- Adv_Stu(advisor:x, student:y),Anc_1(ancestor:y, student:);

@Recursive(Anc_2,10);
Anc_2(ancestor:advisor, student:l) distinct :- Adv_Stu(advisor:, student:l), l=119280;
Anc_2(ancestor:x, student:) distinct:- Adv_Stu(advisor:x, student:y),Anc_2(ancestor:y, student:);

Common_Ancestors(pid_1:m, pid_2:l, anc_id: x) :- Anc_1(ancestor:x, student:m), 
Anc_2(ancestor:x, student:l), m=63244,l=119280 ;

Not_Lowest_Common_Ancestors(pid_1:m, pid_2:l, anc_id: x) :- Common_Ancestors(pid_1:m, pid_2:l, anc_id: x), 
Common_Ancestors(pid_1:m, pid_2:l, anc_id: y), Adv_Stu(advisor:x, student:y),m=63244,l=119280;

Lowest_Common_Ancestors_with_Name(pid:x, name:name) :- Common_Ancestors(pid_1:m, pid_2:l, anc_id: x),
~Not_Lowest_Common_Ancestors(pid_1:m, pid_2:l, anc_id: x), Person(pid:x,name:name),m=63244,l=119280;

[yilinxia]

Using Ground will also resolve the error

%%logica Lowest_Common_Ancestors_with_Name

@Engine("sqlite");

@AttachDatabase("mgdb","mgdb.db");

@Ground(Adv_Stu);
Adv_Stu(advisor:, student:author) :- Advised(did:x, advisor:),Dissertation(did:y, author:), x=y;

@Recursive(Anc,33);
@Ground(Anc);
Anc(ancestor:advisor, student:m) distinct :- Adv_Stu(advisor:, student:m), m=63244;
Anc(ancestor:x, student:) distinct:- Adv_Stu(advisor:x, student:y),Anc(ancestor:y, student:);

Anc(ancestor:advisor, student:l) distinct :- Adv_Stu(advisor:, student:l), l=119280;
Anc(ancestor:x, student:) distinct:- Adv_Stu(advisor:x, student:y),Anc(ancestor:y, student:);

Common_Ancestors(pid_1:m, pid_2:l, anc_id: x) :- Anc(ancestor:x, student:m), 
Anc(ancestor:x, student:l), m=63244,l=119280 ;

Not_Lowest_Common_Ancestors(pid_1:m, pid_2:l, anc_id: x) :- Common_Ancestors(pid_1:m, pid_2:l, anc_id: x), 
Common_Ancestors(pid_1:m, pid_2:l, anc_id: y), Adv_Stu(advisor:x, student:y),m=63244,l=119280;

@Ground(Lowest_Common_Ancestors_with_Name);
Lowest_Common_Ancestors_with_Name(pid:x, name:name) :- Common_Ancestors(pid_1:m, pid_2:l, anc_id: x),
~Not_Lowest_Common_Ancestors(pid_1:m, pid_2:l, anc_id: x), Person(pid:x,name:name),m=63244,l=119280;

[EvgSkv]

Hi @yilinxia ,
I wish we can have a better user experience with this, but generally I'd say that this is "working as intended".

When having deep recursion we should be grounding the recursive predicate, to help SQL. Otherwise the query is just too large for it to handle, like here where we hit the reference count maximum. BigQuery will hit issues with this even sooner than SQLite.

Let me know if you have further questions, or ideas on this.