logica
logica copied to clipboard
EvgSkv
Too many references to "advised": max 65535
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
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;
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;
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.
