<p dir="auto">A clear and concise description of what the bug is.<br>
Sort By on table viz produces undesired results.</p>
<h3 dir="auto">Expected results</h3>
<p dir="auto">It should able to sort the result set.</p>
<h3 dir="auto">Actual results</h3>
<p dir="auto">Adding an extra column in the result set - which is confusing.</p>
<h4 dir="auto">Screenshots</h4>
<p dir="auto"><a target="_blank" rel="noopener noreferrer nofollow" href="https://user-images.githubusercontent.com/11397834/80009779-5f6dd280-847e-11ea-82d7-5bf7fd2d874e.png"><img src="https://user-images.githubusercontent.com/11397834/80009779-5f6dd280-847e-11ea-82d7-5bf7fd2d874e.png" alt="image" style="max-width: 100%;"></a><br>
<a target="_blank" rel="noopener noreferrer nofollow" href="https://user-images.githubusercontent.com/11397834/80009881-84624580-847e-11ea-8660-234d11c0b446.png"><img src="https://user-images.githubusercontent.com/11397834/80009881-84624580-847e-11ea-8660-234d11c0b446.png" alt="image" style="max-width: 100%;"></a><br>
<a target="_blank" rel="noopener noreferrer nofollow" href="https://user-images.githubusercontent.com/11397834/80010006-a3f96e00-847e-11ea-9c18-fb7487daf7fc.png"><img src="https://user-images.githubusercontent.com/11397834/80010006-a3f96e00-847e-11ea-9c18-fb7487daf7fc.png" alt="image" style="max-width: 100%;"></a></p>
<h4 dir="auto">How to reproduce the bug</h4>
<ol dir="auto">
<li>Go to source-&gt;tables-&gt;select bart_lines</li>
<li>Click on bart_lines to explore data set</li>
<li>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.</li>
<li>See error<br>
See attached screen shot (result window)</li>
</ol>
<h3 dir="auto">Environment</h3>
<p dir="auto">(please complete the following information):</p>
<ul dir="auto">
<li>superset version:  0.36.0, 0.35.2 and the master</li>
<li>python version: 3.6.8</li>
<li>node.js version: <code class="notranslate">node -v</code></li>
<li>npm version: <code class="notranslate">npm -v</code></li>
</ul>
<h3 dir="auto">Checklist</h3>
<p dir="auto">Make sure these boxes are checked before submitting your issue - thank you!</p>
<ul class="contains-task-list">
<li class="task-list-item"><input type="checkbox" id="" disabled="" class="task-list-item-checkbox" checked=""> I have checked the superset logs for python stacktraces and included it here as text if there are any.</li>
<li class="task-list-item"><input type="checkbox" id="" disabled="" class="task-list-item-checkbox" checked=""> I have reproduced the issue with at least the latest released version of superset.</li>
<li class="task-list-item"><input type="checkbox" id="" disabled="" class="task-list-item-checkbox" checked=""> I have checked the issue tracker for the same issue and I haven't found one similar.</li>
</ul>
<h3 dir="auto">Additional context</h3>
<p dir="auto">The explore request<br>
<code class="notranslate">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}</code></p>
<p dir="auto">the query:<br>
<code class="notranslate">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;</code></p>
<p dir="auto">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.</p>
<p dir="auto">After running <code class="notranslate">caravel db upgrade</code> the value of <code class="notranslate">alembic_version</code> is the second to last migration revision instead of the last revision.  This causes a subsequent run of <code class="notranslate">caravel db upgrade</code> to attempt to re-apply the final patch.  This fails with no side effects, so it it not that severe.</p>
<p dir="auto">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.</p>
<ol dir="auto">
<li>Create an admin user.</li>
</ol>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="# 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."><pre class="notranslate"><code class="notranslate"># 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.
</code></pre></div>
<ol start="2" dir="auto">
<li>Run <code class="notranslate">caravel db upgrade</code>.</li>
</ol>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="# 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  -&gt; 4e6a06bad7a8, Init
INFO  [alembic.runtime.migration] Running upgrade 4e6a06bad7a8 -&gt; 5a7bad26f2a7, empty message
INFO  [alembic.runtime.migration] Running upgrade 5a7bad26f2a7 -&gt; 1e2841a4128, empty message
...
INFO  [alembic.runtime.migration] Running upgrade 1226819ee0e3 -&gt; d8bc074f7aad, Add new field 'is_restricted' to SqlMetric and DruidMetric
INFO  [alembic.runtime.migration] Running upgrade d8bc074f7aad -&gt; 27ae655e4247, Make creator owners
INFO  [alembic.runtime.migration] Running upgrade 27ae655e4247 -&gt; 960c69cb1f5b, add dttm_format related fields in table_columns"><pre class="notranslate"><code class="notranslate"># 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  -&gt; 4e6a06bad7a8, Init
INFO  [alembic.runtime.migration] Running upgrade 4e6a06bad7a8 -&gt; 5a7bad26f2a7, empty message
INFO  [alembic.runtime.migration] Running upgrade 5a7bad26f2a7 -&gt; 1e2841a4128, empty message
...
INFO  [alembic.runtime.migration] Running upgrade 1226819ee0e3 -&gt; d8bc074f7aad, Add new field 'is_restricted' to SqlMetric and DruidMetric
INFO  [alembic.runtime.migration] Running upgrade d8bc074f7aad -&gt; 27ae655e4247, Make creator owners
INFO  [alembic.runtime.migration] Running upgrade 27ae655e4247 -&gt; 960c69cb1f5b, add dttm_format related fields in table_columns
</code></pre></div>
<p dir="auto">Notice the last upgrade was "27ae655e4247 -&gt; 960c69cb1f5b" so you would expect <code class="notranslate">alembic_version</code> to be "960c69cb1f5b".  But in fact it is the second to last upgrade revision "27ae655e4247".</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="# 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"><pre class="notranslate"><code class="notranslate"># 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
</code></pre></div>
<p dir="auto">And if I run <code class="notranslate">caravel db upgrade</code> again it will attempt to apply that final upgrade.</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="# 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 -&gt; 960c69cb1f5b, add dttm_format related fields in table_columns
Traceback (most recent call last):
  File &quot;/opt/conda/lib/python3.5/site-packages/sqlalchemy/engine/base.py&quot;, line 1139, in _execute_context
    context)
  File &quot;/opt/conda/lib/python3.5/site-packages/sqlalchemy/engine/default.py&quot;, line 450, in do_execute
    cursor.execute(statement, parameters)
sqlite3.OperationalError: duplicate column name: python_date_format
..."><pre class="notranslate"><code class="notranslate"># 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 -&gt; 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
...
</code></pre></div>
<p dir="auto">Querying the sqlite db I see the same result.</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="# 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 &quot;help&quot;, &quot;copyright&quot;, &quot;credits&quot; or &quot;license&quot; for more information.
&gt;&gt;&gt; import sqlite3
&gt;&gt;&gt; conn = sqlite3.connection('/home/caravel/db/caravel.db')
Traceback (most recent call last):
  File &quot;&lt;stdin&gt;&quot;, line 1, in &lt;module&gt;
AttributeError: module 'sqlite3' has no attribute 'connection'
&gt;&gt;&gt; conn = sqlite3.connect('/home/caravel/db/caravel.db')
&gt;&gt;&gt; c = conn.cursor()
&gt;&gt;&gt; c.execute(&quot;SELECT * FROM alembic_version&quot;).fetchall()
[('27ae655e4247',)]"><pre class="notranslate"><code class="notranslate"># 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.
&gt;&gt;&gt; import sqlite3
&gt;&gt;&gt; conn = sqlite3.connection('/home/caravel/db/caravel.db')
Traceback (most recent call last):
  File "&lt;stdin&gt;", line 1, in &lt;module&gt;
AttributeError: module 'sqlite3' has no attribute 'connection'
&gt;&gt;&gt; conn = sqlite3.connect('/home/caravel/db/caravel.db')
&gt;&gt;&gt; c = conn.cursor()
&gt;&gt;&gt; c.execute("SELECT * FROM alembic_version").fetchall()
[('27ae655e4247',)]
</code></pre></div>
<p dir="auto">My environment:</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="# conda list
# packages in environment at /opt/conda:
#
alembic                   0.8.7                     &lt;pip&gt;
Babel                     2.3.4                     &lt;pip&gt;
caravel                   0.10.0                    &lt;pip&gt;
cffi                      1.7.0                    py35_0  
click                     6.6                       &lt;pip&gt;
colorama                  0.3.7                     &lt;pip&gt;
conda                     4.1.11                   py35_0  
conda-env                 2.5.2                    py35_0  
cryptography              1.2.3                    py35_0  
cryptography              1.4                       &lt;pip&gt;
defusedxml                0.4.1                     &lt;pip&gt;
Flask                     0.11.1                    &lt;pip&gt;
Flask-AppBuilder          1.8.1                     &lt;pip&gt;
Flask-Babel               0.11.1                    &lt;pip&gt;
Flask-Cache               0.13.1                    &lt;pip&gt;
Flask-Login               0.2.11                    &lt;pip&gt;
Flask-Migrate             1.5.1                     &lt;pip&gt;
Flask-OpenID              1.2.5                     &lt;pip&gt;
Flask-Script              2.0.5                     &lt;pip&gt;
Flask-SQLAlchemy          2.0                       &lt;pip&gt;
Flask-Testing             0.5.0                     &lt;pip&gt;
Flask-WTF                 0.12                      &lt;pip&gt;
gevent                    1.1.2                    py35_0  
greenlet                  0.4.10                   py35_0  
gunicorn                  19.6.0                    &lt;pip&gt;
humanize                  0.5.1                     &lt;pip&gt;
idna                      2.0                      py35_0  
itsdangerous              0.24                      &lt;pip&gt;
Jinja2                    2.8                       &lt;pip&gt;
libffi                    3.2.1                         0  
Mako                      1.0.4                     &lt;pip&gt;
Markdown                  2.6.6                     &lt;pip&gt;
MarkupSafe                0.23                      &lt;pip&gt;
mkl                       11.3.3                        0  
mysqlclient               1.3.7                     &lt;pip&gt;
numpy                     1.11.0                   py35_2  
openssl                   1.0.2h                        1  
pandas                    0.18.1              np111py35_0  
parsedatetime             2.0                       &lt;pip&gt;
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                     &lt;pip&gt;
python                    3.5.2                         0  
python-dateutil           2.5.3                    py35_0  
python-editor             1.0.1                     &lt;pip&gt;
python3-openid            3.0.10                    &lt;pip&gt;
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                     &lt;pip&gt;
six                       1.10.0                   py35_0  
SQLAlchemy                1.0.13                    &lt;pip&gt;
SQLAlchemy-Utils          0.32.7                    &lt;pip&gt;
sqlite                    3.13.0                        0  
sqlparse                  0.1.19                    &lt;pip&gt;
tk                        8.5.18                        0  
Werkzeug                  0.11.10                   &lt;pip&gt;
wheel                     0.29.0                   py35_0  
WTForms                   2.1                       &lt;pip&gt;
xz                        5.2.2                         0  
yaml                      0.1.6                         0  
zlib                      1.2.8                         3  "><pre class="notranslate"><code class="notranslate"># conda list
# packages in environment at /opt/conda:
#
alembic                   0.8.7                     &lt;pip&gt;
Babel                     2.3.4                     &lt;pip&gt;
caravel                   0.10.0                    &lt;pip&gt;
cffi                      1.7.0                    py35_0  
click                     6.6                       &lt;pip&gt;
colorama                  0.3.7                     &lt;pip&gt;
conda                     4.1.11                   py35_0  
conda-env                 2.5.2                    py35_0  
cryptography              1.2.3                    py35_0  
cryptography              1.4                       &lt;pip&gt;
defusedxml                0.4.1                     &lt;pip&gt;
Flask                     0.11.1                    &lt;pip&gt;
Flask-AppBuilder          1.8.1                     &lt;pip&gt;
Flask-Babel               0.11.1                    &lt;pip&gt;
Flask-Cache               0.13.1                    &lt;pip&gt;
Flask-Login               0.2.11                    &lt;pip&gt;
Flask-Migrate             1.5.1                     &lt;pip&gt;
Flask-OpenID              1.2.5                     &lt;pip&gt;
Flask-Script              2.0.5                     &lt;pip&gt;
Flask-SQLAlchemy          2.0                       &lt;pip&gt;
Flask-Testing             0.5.0                     &lt;pip&gt;
Flask-WTF                 0.12                      &lt;pip&gt;
gevent                    1.1.2                    py35_0  
greenlet                  0.4.10                   py35_0  
gunicorn                  19.6.0                    &lt;pip&gt;
humanize                  0.5.1                     &lt;pip&gt;
idna                      2.0                      py35_0  
itsdangerous              0.24                      &lt;pip&gt;
Jinja2                    2.8                       &lt;pip&gt;
libffi                    3.2.1                         0  
Mako                      1.0.4                     &lt;pip&gt;
Markdown                  2.6.6                     &lt;pip&gt;
MarkupSafe                0.23                      &lt;pip&gt;
mkl                       11.3.3                        0  
mysqlclient               1.3.7                     &lt;pip&gt;
numpy                     1.11.0                   py35_2  
openssl                   1.0.2h                        1  
pandas                    0.18.1              np111py35_0  
parsedatetime             2.0                       &lt;pip&gt;
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                     &lt;pip&gt;
python                    3.5.2                         0  
python-dateutil           2.5.3                    py35_0  
python-editor             1.0.1                     &lt;pip&gt;
python3-openid            3.0.10                    &lt;pip&gt;
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                     &lt;pip&gt;
six                       1.10.0                   py35_0  
SQLAlchemy                1.0.13                    &lt;pip&gt;
SQLAlchemy-Utils          0.32.7                    &lt;pip&gt;
sqlite                    3.13.0                        0  
sqlparse                  0.1.19                    &lt;pip&gt;
tk                        8.5.18                        0  
Werkzeug                  0.11.10                   &lt;pip&gt;
wheel                     0.29.0                   py35_0  
WTForms                   2.1                       &lt;pip&gt;
xz                        5.2.2                         0  
yaml                      0.1.6                         0  
zlib                      1.2.8                         3  
</code></pre></div>
<p dir="auto">I tried with caravel==0.9.3 and saw the same problem.  I have not yet tried on master.</p>
<p dir="auto">This is probably a bug in Flask-Migrate or alembic.  Possibly related?  <a href="https://bitbucket.org/zzzeek/alembic/issues/378" rel="nofollow">https://bitbucket.org/zzzeek/alembic/issues/378</a></p>
<p dir="auto">This doesn't seem to do any harm besides putting stacktraces in the logs.</p>
<p dir="auto">cheers,<br>
Dennis</p>