Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Too many references to "advised": max 65535 #283

Open
yilinxia opened this issue Aug 9, 2023 · 3 comments
Open

Too many references to "advised": max 65535 #283

yilinxia opened this issue Aug 9, 2023 · 3 comments

Comments

@yilinxia
Copy link
Contributor

yilinxia commented Aug 9, 2023

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
Copy link
Contributor Author

yilinxia commented Aug 9, 2023

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
Copy link
Contributor Author

yilinxia commented Aug 9, 2023

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
Copy link
Owner

EvgSkv commented Aug 9, 2023

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.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

No branches or pull requests

2 participants