Issue 1:
A clear and concise description of what the bug is.

Sort By on table viz produces undesired results.

Expected results

It should able to sort the result set.

Actual results

Adding an extra column in the result set - which is confusing.

Screenshots

How to reproduce the bug

Go to source->tables->select bart_lines

Click on bart_lines to explore data set

Scroll down to "Sort By" (choose a column or aggregate function). Unable to select the Group by columns (see the screen shot). It forces to select aggregate function or custom sql otherwise it throws duplicate columns exception if custom sql column doesn't have alias name.

See error

See attached screen shot (result window)

Environment

(please complete the following information):

superset version: 0.36.0, 0.35.2 and the master

python version: 3.6.8

node.js version: node -v

npm version: npm -v

Checklist

Make sure these boxes are checked before submitting your issue - thank you!

I have checked the superset logs for python stacktraces and included it here as text if there are any.

I have reproduced the issue with at least the latest released version of superset.

I have checked the issue tracker for the same issue and I haven't found one similar.

Additional context

The explore request

http://localhost:8088/superset/explore/?form_data={"datasource":"13__table","viz_type":"table","url_params":{},"time_range_endpoints":["inclusive","exclusive"],"granularity_sqla":null,"time_grain_sqla":"P1D","time_range":"Last+week","groupby":["name"],"metrics":[{"expressionType":"SIMPLE","column":{"id":456,"column_name":"color","verbose_name":null,"description":null,"expression":null,"filterable":true,"groupby":true,"is_dttm":false,"type":"VARCHAR(255)","optionName":"_col_color"},"aggregate":"COUNT","sqlExpression":null,"hasCustomLabel":false,"fromFormData":true,"label":"COUNT(color)","optionName":"metric_qqaak8p1tnh_c7yss63hqv7"}],"percent_metrics":[],"timeseries_limit_metric":{"expressionType":"SIMPLE","column":{"id":455,"column_name":"name","verbose_name":null,"description":null,"expression":null,"filterable":true,"groupby":true,"is_dttm":false,"type":"VARCHAR(255)","optionName":"_col_name"},"aggregate":"COUNT_DISTINCT","sqlExpression":null,"hasCustomLabel":false,"fromFormData":false,"label":"COUNT_DISTINCT(name)","optionName":"metric_r322b0kf66c_hctq4pk133v"},"row_limit":10000,"include_time":false,"order_desc":true,"all_columns":[],"order_by_cols":[],"adhoc_filters":[],"table_timestamp_format":"%Y-%m-%d+%H:%M:%S","page_length":0,"include_search":false,"table_filter":false,"align_pn":false,"color_pn":true,"show_cell_bars":true}

the query:

SELECT name AS name, count(color) AS "COUNT(color)", **count(DISTINCT name) AS "COUNT_DISTINCT(name)"** FROM bart_lines GROUP BY name ORDER BY "COUNT_DISTINCT(name)" DESC LIMIT 10000;

To explore table viz, it should not force to select metrics aggregation instead it should provide columns to be selectable. I noticed the explore request "metrics" key in form_data includes "Sort By" value and it should not add it to the list. The "order_by" key has already the same value. Before, I submit the PR for this issue, need to understand the intend of "sort_by" for Table viz. Please, provide the context.

Issue 2:
After running caravel db upgrade the value of alembic_version is the second to last migration revision instead of the last revision. This causes a subsequent run of caravel db upgrade to attempt to re-apply the final patch. This fails with no side effects, so it it not that severe.

I'm running caravel 0.10.0 in a docker container using miniconda3 as the Python3 distribution. (I see the same thing with Python2.) This example is using SQLite in a clean environment.

Create an admin user.

# fabmanager create-admin --app caravel --username admin --firstname admin --lastname user --email data@gsngames.com --password XXXXXXXXXX
...
2016-08-31 21:19:34,339:INFO:flask_appbuilder.security.sqla.manager:Added user admin
Admin User admin created.

Run caravel db upgrade.

# caravel db upgrade
INFO [alembic.runtime.migration] Context impl SQLiteImpl.
INFO [alembic.runtime.migration] Will assume non-transactional DDL.
INFO [alembic.runtime.migration] Running upgrade -> 4e6a06bad7a8, Init
INFO [alembic.runtime.migration] Running upgrade 4e6a06bad7a8 -> 5a7bad26f2a7, empty message
INFO [alembic.runtime.migration] Running upgrade 5a7bad26f2a7 -> 1e2841a4128, empty message
...
INFO [alembic.runtime.migration] Running upgrade 1226819ee0e3 -> d8bc074f7aad, Add new field 'is_restricted' to SqlMetric and DruidMetric
INFO [alembic.runtime.migration] Running upgrade d8bc074f7aad -> 27ae655e4247, Make creator owners
INFO [alembic.runtime.migration] Running upgrade 27ae655e4247 -> 960c69cb1f5b, add dttm_format related fields in table_columns

Notice the last upgrade was "27ae655e4247 -> 960c69cb1f5b" so you would expect alembic_version to be "960c69cb1f5b". But in fact it is the second to last upgrade revision "27ae655e4247".

# caravel db current --verbose
...
INFO [alembic.runtime.migration] Context impl SQLiteImpl.
INFO [alembic.runtime.migration] Will assume non-transactional DDL.
Current revision(s) for sqlite:////home/caravel/db/caravel.db:
Rev: 27ae655e4247
Parent: d8bc074f7aad
Path: /opt/conda/lib/python3.5/site-packages/caravel/migrations/versions/27ae655e4247_make_creator_owners.py

Make creator owners

Revision ID: 27ae655e4247
Revises: d8bc074f7aad
Create Date: 2016-06-27 08:43:52.592242

And if I run caravel db upgrade again it will attempt to apply that final upgrade.

# caravel db upgrade
...
INFO [alembic.runtime.migration] Context impl SQLiteImpl.
INFO [alembic.runtime.migration] Will assume non-transactional DDL.
INFO [alembic.runtime.migration] Running upgrade 27ae655e4247 -> 960c69cb1f5b, add dttm_format related fields in table_columns
Traceback (most recent call last):
File "/opt/conda/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 1139, in _execute_context
context)
File "/opt/conda/lib/python3.5/site-packages/sqlalchemy/engine/default.py", line 450, in do_execute
cursor.execute(statement, parameters)
sqlite3.OperationalError: duplicate column name: python_date_format
...

Querying the sqlite db I see the same result.

# python
Python 3.5.2 |Continuum Analytics, Inc.| (default, Jul 2 2016, 17:53:06)
[GCC 4.4.7 20120313 (Red Hat 4.4.7-1)] on linux
Type "help", "copyright", "credits" or "license" for more information.
>>> import sqlite3
>>> conn = sqlite3.connection('/home/caravel/db/caravel.db')
Traceback (most recent call last):
File "<stdin>", line 1, in <module>
AttributeError: module 'sqlite3' has no attribute 'connection'
>>> conn = sqlite3.connect('/home/caravel/db/caravel.db')
>>> c = conn.cursor()
>>> c.execute("SELECT * FROM alembic_version").fetchall()
[('27ae655e4247',)]

My environment:

# conda list
# packages in environment at /opt/conda:
#
alembic 0.8.7 <pip>
Babel 2.3.4 <pip>
caravel 0.10.0 <pip>
cffi 1.7.0 py35_0
click 6.6 <pip>
colorama 0.3.7 <pip>
conda 4.1.11 py35_0
conda-env 2.5.2 py35_0
cryptography 1.2.3 py35_0
cryptography 1.4 <pip>
defusedxml 0.4.1 <pip>
Flask 0.11.1 <pip>
Flask-AppBuilder 1.8.1 <pip>
Flask-Babel 0.11.1 <pip>
Flask-Cache 0.13.1 <pip>
Flask-Login 0.2.11 <pip>
Flask-Migrate 1.5.1 <pip>
Flask-OpenID 1.2.5 <pip>
Flask-Script 2.0.5 <pip>
Flask-SQLAlchemy 2.0 <pip>
Flask-Testing 0.5.0 <pip>
Flask-WTF 0.12 <pip>
gevent 1.1.2 py35_0
greenlet 0.4.10 py35_0
gunicorn 19.6.0 <pip>
humanize 0.5.1 <pip>
idna 2.0 py35_0
itsdangerous 0.24 <pip>
Jinja2 2.8 <pip>
libffi 3.2.1 0
Mako 1.0.4 <pip>
Markdown 2.6.6 <pip>
MarkupSafe 0.23 <pip>
mkl 11.3.3 0
mysqlclient 1.3.7 <pip>
numpy 1.11.0 py35_2
openssl 1.0.2h 1
pandas 0.18.1 np111py35_0
parsedatetime 2.0 <pip>
pip 8.1.2 py35_0
psycopg2 2.6.1 py35_1
pyasn1 0.1.9 py35_0
pycosat 0.6.1 py35_1
pycparser 2.14 py35_1
pycrypto 2.6.1 py35_4
pydruid 0.3.0 <pip>
python 3.5.2 0
python-dateutil 2.5.3 py35_0
python-editor 1.0.1 <pip>
python3-openid 3.0.10 <pip>
pytz 2016.6.1 py35_0
pyyaml 3.11 py35_4
readline 6.2 2
requests 2.10.0 py35_0
ruamel_yaml 0.11.14 py35_0
setuptools 23.0.0 py35_0
simplejson 3.8.2 <pip>
six 1.10.0 py35_0
SQLAlchemy 1.0.13 <pip>
SQLAlchemy-Utils 0.32.7 <pip>
sqlite 3.13.0 0
sqlparse 0.1.19 <pip>
tk 8.5.18 0
Werkzeug 0.11.10 <pip>
wheel 0.29.0 py35_0
WTForms 2.1 <pip>
xz 5.2.2 0
yaml 0.1.6 0
zlib 1.2.8 3

I tried with caravel==0.9.3 and saw the same problem. I have not yet tried on master.

This is probably a bug in Flask-Migrate or alembic. Possibly related? https://bitbucket.org/zzzeek/alembic/issues/378

This doesn't seem to do any harm besides putting stacktraces in the logs.

cheers,

Dennis