API Subpath count throws 500 for dummy DB view

[sgratzl]

@sluger commented on Mon Nov 06 2017

• Release number or git hash: 0cb8aef08e79c8207d796f963bef83fbd324fae3
• Web browser version and OS: Chrome Version 62.0.3202.75 (Official Build) (64-bit) and Ubuntu 17.10
• Environment (local or deployed): local

Steps to reproduce

1. Setup any workspace, e.g. a simple tutorial workspace
2. Add caleydo/tdp_dummy to the workspace
3. Test API subpath count: http://localhost:8080/api/tdp/db/dummy/a/count

Observed behavior

• Any unexpected output or action (or lack of expected output or action)
• Web browser console errors (including tracebacks)
• Server errors (relevant messages and tracebacks)

api_1          | 14:50:57 INFO tdp_core.db: SELECT count(d.*) as count FROM b d  
api_1          | 14:50:57 INFO tdp_core.db: removing session again
api_1          | Traceback (most recent call last):
api_1          |   File "/usr/local/lib/python2.7/site-packages/gevent/pywsgi.py", line 884, in handle_one_response
api_1          |     self.run_application()
api_1          |   File "/usr/local/lib/python2.7/site-packages/geventwebsocket/handler.py", line 88, in run_application
api_1          |     return super(WebSocketHandler, self).run_application()
api_1          |   File "/usr/local/lib/python2.7/site-packages/gevent/pywsgi.py", line 870, in run_application
api_1          |     self.result = self.application(self.environ, self.start_response)
api_1          |   File "/usr/local/lib/python2.7/site-packages/werkzeug/contrib/fixers.py", line 152, in __call__
api_1          |     return self.app(environ, start_response)
api_1          |   File "phovea_server/phovea_server/dispatcher.py", line 62, in __call__
api_1          |     return app(environ, start_response)
api_1          |   File "/usr/local/lib/python2.7/site-packages/flask/app.py", line 2000, in __call__
api_1          |     return self.wsgi_app(environ, start_response)
api_1          |   File "/usr/local/lib/python2.7/site-packages/flask/app.py", line 1991, in wsgi_app
api_1          |     response = self.make_response(self.handle_exception(e))
api_1          |   File "/usr/local/lib/python2.7/site-packages/flask/app.py", line 1567, in handle_exception
api_1          |     reraise(exc_type, exc_value, tb)
api_1          |   File "/usr/local/lib/python2.7/site-packages/flask/app.py", line 1988, in wsgi_app
api_1          |     response = self.full_dispatch_request()
api_1          |   File "/usr/local/lib/python2.7/site-packages/flask/app.py", line 1641, in full_dispatch_request
api_1          |     rv = self.handle_user_exception(e)
api_1          |   File "/usr/local/lib/python2.7/site-packages/flask/app.py", line 1544, in handle_user_exception
api_1          |     reraise(exc_type, exc_value, tb)
api_1          |   File "/usr/local/lib/python2.7/site-packages/flask/app.py", line 1639, in full_dispatch_request
api_1          |     rv = self.dispatch_request()
api_1          |   File "/usr/local/lib/python2.7/site-packages/flask/app.py", line 1625, in dispatch_request
api_1          |     return self.view_functions[rule.endpoint](**req.view_args)
api_1          |   File "/usr/local/lib/python2.7/site-packages/flask_login/utils.py", line 228, in decorated_view
api_1          |     return func(*args, **kwargs)
api_1          |   File "/phovea/tdp_core/tdp_core/sql.py", line 114, in get_count_data
api_1          |     r = db.get_count(database, view_name, request.args)
api_1          |   File "/phovea/tdp_core/tdp_core/db.py", line 362, in get_count
api_1          |     r = sess.run(count_query.format(**replace), **kwargs)
api_1          |   File "/phovea/tdp_core/tdp_core/db.py", line 133, in run
api_1          |     result = self._session.execute(parsed, kwargs)
api_1          |   File "/usr/local/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 1034, in execute
api_1          |     bind, close_with_result=True).execute(clause, params or {})
api_1          |   File "/usr/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 914, in execute
api_1          |     return meth(self, multiparams, params)
api_1          |   File "/usr/local/lib/python2.7/site-packages/sqlalchemy/sql/elements.py", line 323, in _execute_on_connection
api_1          |     return connection._execute_clauseelement(self, multiparams, params)
api_1          |   File "/usr/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1010, in _execute_clauseelement
api_1          |     compiled_sql, distilled_params
api_1          |   File "/usr/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1146, in _execute_context
api_1          |     context)
api_1          |   File "/usr/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1341, in _handle_dbapi_exception
api_1          |     exc_info
api_1          |   File "/usr/local/lib/python2.7/site-packages/sqlalchemy/util/compat.py", line 200, in raise_from_cause
api_1          |     reraise(type(exception), exception, tb=exc_tb, cause=cause)
api_1          |   File "/usr/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1139, in _execute_context
api_1          |     context)
api_1          |   File "/usr/local/lib/python2.7/site-packages/sqlalchemy/engine/default.py", line 450, in do_execute
api_1          |     cursor.execute(statement, parameters)
api_1          | OperationalError: (sqlite3.OperationalError) near "*": syntax error [SQL: u'SELECT count(d.*) as count FROM b d  ']
api_1          | {'GATEWAY_INTERFACE': 'CGI/1.1',
api_1          |  'HTTP_ACCEPT': 'text/html,application/xhtml+xml,application/xml;q=0.9,image/webp,image/apng,*/*;q=0.8',
api_1          |  'HTTP_ACCEPT_ENCODING': 'gzip, deflate, br',
api_1          |  'HTTP_ACCEPT_LANGUAGE': 'en-US,en;q=0.9',
api_1          |  'HTTP_CACHE_CONTROL': 'no-cache',
api_1          |  'HTTP_CONNECTION': 'close',
api_1          |  'HTTP_COOKIE': 'randomCredentials=cranky_golick@b37hyd; session=.eJwdzr1qwzAUQOFXKXfOEP8thgwGFWPDvUZBsrhaQus4jaWog5PQSiHv3tDpfON5wOG0ztcz1Lf1Pm_gsByhfsDbJ9TArnfY6mgD_6BoIpu9I-cLVl85mfGC5j2iGR2GfmE3ZeyawoouQ9N7fJmS3g6iq0hgiUFGq7jgsPfWyF9SumCjIyWO5OR2UBdvXVOhmEp054WMrijJSGrKqeWEqctR4asyQzX6QchyaGXkJEsbcAfPDdyv8_r_Dx_HsHzD8w-VlEkK.DOHSQA.nLIPefFgcGHpFS_g3_yy1PM_S9E',
api_1          |  'HTTP_HOST': 'localhost:8080',
api_1          |  'HTTP_PRAGMA': 'no-cache',
api_1          |  'HTTP_UPGRADE_INSECURE_REQUESTS': '1',
api_1          |  'HTTP_USER_AGENT': 'Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/62.0.3202.75 Safari/537.36',
api_1          |  'PATH_INFO': '/dummy/b/count',
api_1          |  'QUERY_STRING': '',
api_1          |  'REMOTE_ADDR': '172.20.0.1',
api_1          |  'REMOTE_PORT': '46802',
api_1          |  'REQUEST_METHOD': 'GET',
api_1          |  'SCRIPT_NAME': '/api/tdp/db',
api_1          |  'SERVER_NAME': '00b34b55b068',
api_1          |  'SERVER_PORT': '80',
api_1          |  'SERVER_PROTOCOL': 'HTTP/1.1',
api_1          |  'SERVER_SOFTWARE': 'gevent/1.1 Python/2.7',
api_1          |  'werkzeug.proxy_fix.orig_http_host': 'localhost:8080',
api_1          |  'werkzeug.proxy_fix.orig_remote_addr': '172.20.0.1',
api_1          |  'werkzeug.proxy_fix.orig_wsgi_url_scheme': 'http',
api_1          |  'werkzeug.request': <Request 'http://localhost:8080/api/tdp/db/dummy/b/count' [GET]>,
api_1          |  'wsgi.errors': <open file '<stderr>', mode 'w' at 0x7f0f99d511e0>,
api_1          |  'wsgi.input': <gevent.pywsgi.Input object at 0x7f0f74e458d8>,
api_1          |  'wsgi.multiprocess': False,
api_1          |  'wsgi.multithread': False,
api_1          |  'wsgi.run_once': False,
api_1          |  'wsgi.url_scheme': 'http',
api_1          |  'wsgi.version': (1, 0)} failed with OperationalError
api_1          | 

• Static or animated images showing the UI behavior

Expected behavior

• Display the correct amount of the items.

---

@sgratzl commented on Tue Nov 07 2017

issue is that sqlite cannot handle count(alias.*) clauses