SQLError - too many SQL variables on table with 1000 columns

[leocasarsa]

Ran on casarsa@probcomp2 venv: ~/venv/20160706-july Max sqlite columns on the machine: 2000 with this script Script bayeslite_bug.py:

import bayeslite
import pandas as pd
import numpy as np

from bdbcontrib import bql_utils
from bdbcontrib import Population

data = pd.DataFrame(np.random.rand(180,1000))
temp_bdb_path = 'foo.bdb'
with bayeslite.bayesdb_open(temp_bdb_path)  as bdb:
    bql_utils.query(bdb,'''drop generator if exists temp_cc''')
    bql_utils.query(bdb,'''drop table if exists temp''')

    bayeslite.read_pandas.bayesdb_read_pandas_df(bdb, "temp", data, create=True)
    population = Population(name='temp', bdb_path=temp_bdb_path,
                            session_capture_name=False)

(20160706-july) casarsa@probcomp-2:~/Share/populations$ python bayeslite_bug.py --pdb 
/scratch/home/casarsa/venv/20160706-july/local/lib/python2.7/site-packages/requests/packages/urllib3/util/ssl_.py:318: SNIMissingWarning: An HTTPS request has been made, but the SNI (Subject Name Indication) extension to TLS is not available on this platform. This may cause the server to present an incorrect TLS certificate, which can cause validation failures. You can upgrade to a newer version of Python to solve this. For more information, see https://urllib3.readthedocs.org/en/latest/security.html#snimissingwarning.
  SNIMissingWarning
/scratch/home/casarsa/venv/20160706-july/local/lib/python2.7/site-packages/requests/packages/urllib3/util/ssl_.py:122: InsecurePlatformWarning: A true SSLContext object is not available. This prevents urllib3 from configuring SSL appropriately and may cause certain SSL connections to fail. You can upgrade to a newer version of Python to solve this. For more information, see https://urllib3.readthedocs.org/en/latest/security.html#insecureplatformwarning.
  InsecurePlatformWarning
Traceback (most recent call last):
  File "bayeslite_bug.py", line 14, in <module>
    bayeslite.read_pandas.bayesdb_read_pandas_df(bdb, "temp", data, create=True)
  File "/scratch/home/casarsa/venv/20160706-july/local/lib/python2.7/site-packages/bayeslite/read_pandas.py", line 86, in bayesdb_read_pandas_df
    bdb.sql_execute(sql, (key,) + tuple(df.ix[i]))
  File "/scratch/home/casarsa/venv/20160706-july/local/lib/python2.7/site-packages/bayeslite/bayesdb.py", line 281, in sql_execute
    self.sql_tracer, self._do_sql_execute, string, bindings)
  File "/scratch/home/casarsa/venv/20160706-july/local/lib/python2.7/site-packages/bayeslite/bayesdb.py", line 223, in _maybe_trace
    return meth(string, bindings)
  File "/scratch/home/casarsa/venv/20160706-july/local/lib/python2.7/site-packages/bayeslite/bayesdb.py", line 285, in _do_sql_execute
    cursor.execute(string, bindings)
  File "src/cursor.c", line 1019, in APSWCursor_execute.sqlite3_prepare
  File "src/statementcache.c", line 386, in sqlite3_prepare
apsw.SQLError: SQLError: too many SQL variables

[riastradh-probcomp]

If we recompile sqlite3 ourselves, we can increase this up to 32767 by defining SQLITE_MAX_COLUMN at compile-time. But I'm not sure bayeslite is ready to handle even 2000 variables at the moment!

[Schaechtle]

I have been bitten by this too - having only 1680 columns. I ran the script that Leo attached above and it also indicated max columns to be 2000 (probcomp3). I also searched the bayeslite repo for code that sets the SQLITE_MAX_COLUMN parameter - but I didn't find anything.

[Schaechtle]

same is true 1500 columns.

[riastradh-probcomp]

I'm a little surprised you see a problem with 2000 variables -- though perhaps there is some intermediate table that has 2n columns or something. Can you get a SQL trace to find the query that caused sqlite3 to choke? Something like:

import sys
def trace(q, p): print >>sys.stderr, q, p
bdb.sql_trace(trace)
...whatever you were doing before...
bdb.sql_untrace(trace)

[arpit1997]

hey i got the same error as @leocasarsa

  File "/usr/local/lib/python2.7/site-packages/bayeslite/bayesdb.py", line 281, in sql_execute
    self.sql_tracer, self._do_sql_execute, string, bindings)
  File "/usr/local/lib/python2.7/site-packages/bayeslite/bayesdb.py", line 223, in _maybe_trace
    return meth(string, bindings)
  File "/usr/local/lib/python2.7/site-packages/bayeslite/bayesdb.py", line 285, in _do_sql_execute
    cursor.execute(string, bindings)
  File "src/cursor.c", line 1019, in APSWCursor_execute.sqlite3_prepare
  File "src/statementcache.c", line 386, in sqlite3_prepare
apsw.SQLError: SQLError: too many SQL variables

Actually i tried to reproduce it and it is there so if anyone can suggest the changes that would be nice. :smile_cat: