sqlalchemy.exc.DataError - invalid input syntax for integer: ""

[woel0007]

It appears that commit f70d301 may have introduced an error in the 'explore' view working with time series data.

Steps to Reproduce:

1. Click on a datasource to open the explore view to generate a new slice.
2. Ensure that that initial viz returns some data.
3. Choose any other viz type besides the default.

Error message that pops-up:

DataError: (psycopg2.DataError) invalid input syntax for integer: "" LINE 3: WHERE slices.id = '' ^ [SQL: 'SELECT slices.created_on AS slices_created_on, slices.changed_on AS slices_changed_on, slices.id AS slices_id, slices.slice_name AS slices_slice_name, slices.datasource_id AS slices_datasource_id, slices.datasource_type AS slices_datasource_type, slices.datasource_name AS slices_datasource_name, slices.viz_type AS slices_viz_type, slices.params AS slices_params, slices.description AS slices_description, slices.cache_timeout AS slices_cache_timeout, slices.perm AS slices_perm, slices.changed_by_fk AS slices_changed_by_fk, slices.created_by_fk AS slices_created_by_fk \nFROM slices \nWHERE slices.id = %(id_1)s \n LIMIT %(param_1)s'] [parameters: {'id_1': u'', 'param_1': 1}]

The stack trace:

Traceback (most recent call last):
  File "/Users/opjxw0/.envs/caravel/lib/python2.7/site-packages/flask/app.py", line 2000, in __call__
    return self.wsgi_app(environ, start_response)
  File "/Users/opjxw0/.envs/caravel/lib/python2.7/site-packages/flask/app.py", line 1991, in wsgi_app
    response = self.make_response(self.handle_exception(e))
  File "/Users/opjxw0/.envs/caravel/lib/python2.7/site-packages/flask/app.py", line 1567, in handle_exception
    reraise(exc_type, exc_value, tb)
  File "/Users/opjxw0/.envs/caravel/lib/python2.7/site-packages/flask/app.py", line 1988, in wsgi_app
    response = self.full_dispatch_request()
  File "/Users/opjxw0/.envs/caravel/lib/python2.7/site-packages/flask/app.py", line 1641, in full_dispatch_request
    rv = self.handle_user_exception(e)
  File "/Users/opjxw0/.envs/caravel/lib/python2.7/site-packages/flask/app.py", line 1544, in handle_user_exception
    reraise(exc_type, exc_value, tb)
  File "/Users/opjxw0/.envs/caravel/lib/python2.7/site-packages/flask/app.py", line 1639, in full_dispatch_request
    rv = self.dispatch_request()
  File "/Users/opjxw0/.envs/caravel/lib/python2.7/site-packages/flask/app.py", line 1625, in dispatch_request
    return self.view_functions[rule.endpoint](**req.view_args)
  File "/Users/opjxw0/Documents/sourceCode/caravel/caravel/models.py", line 2077, in wrapper
    return f(*args, **kwargs)
  File "/Users/opjxw0/.envs/caravel/lib/python2.7/site-packages/flask_appbuilder/security/decorators.py", line 26, in wraps
    return f(self, *args, **kwargs)
  File "/Users/opjxw0/Documents/sourceCode/caravel/caravel/views.py", line 1296, in explore
    slc = db.session.query(models.Slice).filter_by(id=slice_id).first()
  File "/Users/opjxw0/.envs/caravel/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 2659, in first
    ret = list(self[0:1])
  File "/Users/opjxw0/.envs/caravel/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 2457, in __getitem__
    return list(res)
  File "/Users/opjxw0/.envs/caravel/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 2761, in __iter__
    return self._execute_and_instances(context)
  File "/Users/opjxw0/.envs/caravel/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 2776, in _execute_and_instances
    result = conn.execute(querycontext.statement, self._params)
  File "/Users/opjxw0/.envs/caravel/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 914, in execute
    return meth(self, multiparams, params)
  File "/Users/opjxw0/.envs/caravel/lib/python2.7/site-packages/sqlalchemy/sql/elements.py", line 323, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File "/Users/opjxw0/.envs/caravel/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1010, in _execute_clauseelement
    compiled_sql, distilled_params
  File "/Users/opjxw0/.envs/caravel/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1146, in _execute_context
    context)
  File "/Users/opjxw0/.envs/caravel/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1341, in _handle_dbapi_exception
    exc_info
  File "/Users/opjxw0/.envs/caravel/lib/python2.7/site-packages/sqlalchemy/util/compat.py", line 202, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb, cause=cause)
  File "/Users/opjxw0/.envs/caravel/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1139, in _execute_context
    context)
  File "/Users/opjxw0/.envs/caravel/lib/python2.7/site-packages/sqlalchemy/engine/default.py", line 450, in do_execute
    cursor.execute(statement, parameters)
DataError: (psycopg2.DataError) invalid input syntax for integer: ""
LINE 3: WHERE slices.id = '' 
                          ^
 [SQL: 'SELECT slices.created_on AS slices_created_on, slices.changed_on AS slices_changed_on, slices.id AS slices_id, slices.slice_name AS slices_slice_name, slices.datasource_id AS slices_datasource_id, slices.datasource_type AS slices_datasource_type, slices.datasource_name AS slices_datasource_name, slices.viz_type AS slices_viz_type, slices.params AS slices_params, slices.description AS slices_description, slices.cache_timeout AS slices_cache_timeout, slices.perm AS slices_perm, slices.changed_by_fk AS slices_changed_by_fk, slices.created_by_fk AS slices_created_by_fk \nFROM slices \nWHERE slices.id = %(id_1)s \n LIMIT %(param_1)s'] [parameters: {'id_1': u'', 'param_1': 1}]

This issue doesn't occur with commits immediately prior to commit f70d301, such as b7d1f78...

I suspect there's something that I've misconfigured as it seems like this issue would be easily discoverable... I don't think I've missed any database migrations; I'm not sure what else I would have misconfigured. Does this issue happen for anyone else?

[xrmx]

@woel0007 what version are you trying? There have been some fixes around get_viz recently iirc

[woel0007]

I pulled/tested master yesterday - I think the commit that I pulled was 63161b1

[xrmx]

@woel0007 please don't quote hashes, better have them rendered as links

[cbaakel]

@woel0007 You get this resolved? Seeing the same error, using the 0.12.0 version on pypi

[philippepouliot]

I have the same error

[phreatom]

same error here. Appears also after a fresh install of 0.12.0

[mistercrunch]

I just pushed a hotfix here:
d33874b

Please confirm that this fixes this issue.

[cscotta]

Ran into this as well – thank you very much for looking into it!

Do you have a sense of when the next release will be cut?

[mistercrunch]

Probably this week. I'm trying to push every 2 weeks or so.

[cscotta]

I've verified this issue is fixed in 0.13.2.

Steps:

1. Deploy 0.13.2.
2. Visit the "Explore" page and define a slice.
3. Switch from the table view to a time series visualization.
4. Works great now.

I think this issue can be closed, unless you prefer its originator to verify. Thanks again!

[xrmx]

Closing, thanks @cscotta for reporting it fixed!