Visualization Type big number with trend line - psycopg2.ProgrammingError

bbs2009 commented 7 years ago

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 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

Superset version

{"GIT_SHA": "", "version": "0.17.4"}

Expected results

Trend graph

Actual results

I greet everyone. When you select the type of graph

Visualization Type big number with trend line

the query for the date field(DATEPARSING) is formed without quotes. Then an error occurs.

Dateparsing - type field date not timestamp

default

generated sql

SELECT DATE_TRUNC('day', DATEPARSING) AS __timestamp, sum(mark_2_not_retake) AS "2 не отр." FROM (SELECT id, "IDFACULTY", "FACULTY", "SPEC", "FINANCE", "IDKAFEDRA", "KAFEDRA", "DISCIPLINE", "STUDENTGROUP", "IDSTUDENT", "STUDENTFIO", "TYPEZAN", "NUMZAN", "DATEZAN", "DATEPARSING", "TEMAZAN", "CONTROLZAN", "STUDENTPRESENT", "STUDENTMARK", "STUDENTMARKID", "STUDENTRETAKE", "STUDENTREASONABSENT", "STUDENTNUMDOC", "STUDENTNUMINVOICE", CASE when ("STUDENTMARK"=5) or ("STUDENTRETAKE"=5)then 1 else 0 end as "mark_5_all", CASE when ("STUDENTMARK"=4) or ("STUDENTRETAKE"=4)then 1 else 0 end as "mark_4_all", CASE when "STUDENTMARK"=3 or ("STUDENTRETAKE"=3) then 1 else 0 end as "mark_3_all", CASE when "STUDENTMARK"=2 then 1 else 0 end as "mark_2_all", CASE when "STUDENTMARK"=2 and "STUDENTRETAKE"=0 then 1 else 0 end as "mark_2_not_retake", CASE when "STUDENTMARK"=2 and "STUDENTRETAKE">0 then 1 else 0 end as "mark_2_retake", CASE when "TYPEZAN"='1' and "STUDENTPRESENT"='1' then 1 else 0 end as "nb_pr_all", CASE when "TYPEZAN"=1 and "STUDENTPRESENT"=1 and "STUDENTRETAKE"=0 then 1 else 0 end as "nb_pr_not_retake", CASE when "TYPEZAN"=0 and "STUDENTPRESENT"=1 then 1 else 0 end as "nb_lec_all", CASE when "TYPEZAN"=0 and "STUDENTPRESENT"=1 and "STUDENTRETAKE"=-1 then 1 else 0 end as "nb_lec_not_retake", CASE when "TYPEZAN"=1 and "STUDENTMARK" >0 then "STUDENTMARK" when "TYPEZAN"=1 and "STUDENTRETAKE" >0 then "STUDENTRETAKE" else 0 end as "student_mark_for_avg", "STUDENTYEAR", "SEMESTR", "TEACHERNAME" FROM public.marks) AS expr_qry WHERE "DATEPARSING" >= '2017-04-11 12:11:07' AND "DATEPARSING" <= '2017-04-11 13:11:07' AND ("IDFACULTY"=2 and "STUDENTYEAR"=1) GROUP BY DATE_TRUNC('day', DATEPARSING) LIMIT 10000000

If you have time, please correct. Thank you

Steps to reproduce

xrmx commented 7 years ago

Could you please replicate with latest master?

bbs2009 commented 7 years ago

Unfortunately, it still does not work.

2017-04-13 13:03:06,746:INFO:root:SELECT DATE_TRUNC('day', DATEPARSING) AS __timestamp, sum(mark_2_not_retake) AS "2 не отр." FROM (SELECT id, "IDFACULTY", "FACULTY", "SPEC", "FINANCE", "IDKAFEDRA", "KAFEDRA", "DISCIPLINE", "STUDENTGROUP", "IDSTUDENT", "STUDENTFIO", "TYPEZAN", "NUMZAN", "DATEZAN", "DATEPARSING", "TEMAZAN", "CONTROLZAN", "STUDENTPRESENT", "STUDENTMARK", "STUDENTMARKID", "STUDENTRETAKE", "STUDENTREASONABSENT", "STUDENTNUMDOC", "STUDENTNUMINVOICE", CASE when ("STUDENTMARK"=5) or ("STUDENTRETAKE"=5)then 1 else 0 end as "mark_5_all", CASE when ("STUDENTMARK"=4) or ("STUDENTRETAKE"=4)then 1 else 0 end as "mark_4_all", CASE when "STUDENTMARK"=3 or ("STUDENTRETAKE"=3) then 1 else 0 end as "mark_3_all", CASE when "STUDENTMARK"=2 then 1 else 0 end as "mark_2_all", CASE when "STUDENTMARK"=2 and "STUDENTRETAKE"=0 then 1 else 0 end as "mark_2_not_retake", CASE when "STUDENTMARK"=2 and "STUDENTRETAKE">0 then 1 else 0 end as "mark_2_retake", CASE when "TYPEZAN"='1' and "STUDENTPRESENT"='1' then 1 else 0 end as "nb_pr_all", CASE when "TYPEZAN"=1 and "STUDENTPRESENT"=1 and "STUDENTRETAKE"=0 then 1 else 0 end as "nb_pr_not_retake",
   CASE when "TYPEZAN"=0 and  "STUDENTPRESENT"=1 then 1
   else 0
   end as "nb_lec_all",
   CASE when "TYPEZAN"=0 and  "STUDENTPRESENT"=1 and "STUDENTRETAKE"=-1 then 1
   else 0
   end as "nb_lec_not_retake",

   CASE when "TYPEZAN"=1 and  "STUDENTMARK" >0 then  "STUDENTMARK"
        when "TYPEZAN"=1 and  "STUDENTRETAKE" >0 then "STUDENTRETAKE"
        else 0
   end as "student_mark_for_avg",
"STUDENTYEAR", "SEMESTR", "TEACHERNAME"

FROM public.marks ) AS expr_qry WHERE "DATEPARSING" >= '2017-04-06 13:03:06' AND "DATEPARSING" <= '2017-04-13 13:03:06' AND ("IDFACULTY"=2 and "STUDENTYEAR"=1) GROUP BY DATE_TRUNC('day', DATEPARSING) LIMIT 1000000 2017-04-13 13:03:06,922:ERROR:root:(psycopg2.ProgrammingError) column "dateparsing" does not exist LINE 1: SELECT DATE_TRUNC('day', DATEPARSING) AS timestamp, sum(ma... ^ [SQL: 'SELECT DATE_TRUNC(\'day\', DATEPARSING) AS __timestamp, sum(mark_2_not_retake) AS "2 не отр." \nFROM (SELECT id,\n "IDFACULTY",\n "FACULTY",\n "SPEC",\n "FINANCE",\n "IDKAFEDRA",\n "KAFEDRA",\n "DISCIPLINE",\n "STUDENTGROUP",\n "IDSTUDENT",\n "STUDENTFIO",\n "TYPEZAN",\n "NUMZAN",\n "DATEZAN",\n "DATEPARSING",\n "TEMAZAN",\n "CONTROLZAN",\n "STUDENTPRESENT",\n "STUDENTMARK",\n "STUDENTMARKID",\n "STUDENTRETAKE",\n "STUDENTREASONABSENT",\n "STUDENTNUMDOC",\n "STUDENTNUMINVOICE",\n CASE when ("STUDENTMARK"=5) or ("STUDENTRETAKE"=5)then 1\n else 0\n end as "mark_5_all",\n CASE when ("STUDENTMARK"=4) or ("STUDENTRETAKE"=4)then 1\n else 0\n end as "mark_4_all",\n CASE when "STUDENTMARK"=3 or ("STUDENTRETAKE"=3) then 1\n else 0\n end as "mark_3_all",\n CASE when "STUDENTMARK"=2 then 1\n else 0\n end as "mark_2_all",\n CASE when "STUDENTMARK"=2 and "STUDENTRETAKE"=0 then 1\n else 0\n end as "mark_2_not_retake",\n CASE when "STUDENTMARK"=2 and "STUDENTRETAKE">0 then 1\n else 0\n end as "mark_2_retake",\n CASE when "TYPEZAN"=\'1\' and "STUDENTPRESENT"=\'1\' then 1\n else 0\n end as "nb_pr_all",\n CASE when "TYPEZAN"=1 and "STUDENTPRESENT"=1 and "STUDENTRETAKE"=0 then 1\n else 0\n end as "nb_pr_not_retake",\n \n CASE when "TYPEZAN"=0 and "STUDENTPRESENT"=1 then 1\n else 0\n end as "nb_lec_all",\n CASE when "TYPEZAN"=0 and "STUDENTPRESENT"=1 and "STUDENTRETAKE"=-1 then 1\n else 0\n end as "nb_lec_not_retake",\n \n CASE when "TYPEZAN"=1 and "STUDENTMARK" >0 then "STUDENTMARK"\n when "TYPEZAN"=1 and "STUDENTRETAKE" >0 then "STUDENTRETAKE"\n else 0\n end as "student_mark_for_avg",\n "STUDENTYEAR",\n"SEMESTR",\n"TEACHERNAME"\n \n\nFROM public.marks ) AS expr_qry \nWHERE "DATEPARSING" >= \'2017-04-06 13:03:06\' AND "DATEPARSING" <= \'2017-04-13 13:03:06\' AND ("IDFACULTY"=2 and "STUDENTYEAR"=1) GROUP BY DATE_TRUNC(\'day\', DATEPARSING) \n LIMIT %(param_1)s'] [parameters: {'param_1': 1000000}] Traceback (most recent call last): File "/data/work/virtualenvs/superset3/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1182, in _execute_context context) File "/data/work/virtualenvs/superset3/lib/python3.6/site-packages/sqlalchemy/engine/default.py", line 470, in do_execute cursor.execute(statement, parameters) psycopg2.ProgrammingError: column "dateparsing" does not exist LINE 1: SELECT DATE_TRUNC('day', DATEPARSING) AS timestamp, sum(ma... ^

The above exception was the direct cause of the following exception:

Traceback (most recent call last): File "/data/work/virtualenvs/superset3/lib/python3.6/site-packages/superset/connectors/sqla/models.py", line 544, in query df = pd.read_sql_query(qry, con=engine) File "/data/work/virtualenvs/superset3/lib/python3.6/site-packages/pandas/io/sql.py", line 431, in read_sql_query parse_dates=parse_dates, chunksize=chunksize) File "/data/work/virtualenvs/superset3/lib/python3.6/site-packages/pandas/io/sql.py", line 1190, in read_query result = self.execute(args) File "/data/work/virtualenvs/superset3/lib/python3.6/site-packages/pandas/io/sql.py", line 1081, in execute return self.connectable.execute(args, *kwargs) File "/data/work/virtualenvs/superset3/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 2055, in execute return connection.execute(statement, multiparams, **params) File "/data/work/virtualenvs/superset3/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 945, in execute return meth(self, multiparams, params) File "/data/work/virtualenvs/superset3/lib/python3.6/site-packages/sqlalchemy/sql/elements.py", line 263, in _execute_on_connection return connection._execute_clauseelement(self, multiparams, params) File "/data/work/virtualenvs/superset3/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1053, in _execute_clauseelement compiled_sql, distilled_params File "/data/work/virtualenvs/superset3/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1189, in _execute_context context) File "/data/work/virtualenvs/superset3/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1393, in _handle_dbapi_exception exc_info File "/data/work/virtualenvs/superset3/lib/python3.6/site-packages/sqlalchemy/util/compat.py", line 203, in raise_from_cause reraise(type(exception), exception, tb=exc_tb, cause=cause) File "/data/work/virtualenvs/superset3/lib/python3.6/site-packages/sqlalchemy/util/compat.py", line 186, in reraise raise value.with_traceback(tb) File "/data/work/virtualenvs/superset3/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1182, in _execute_context context) File "/data/work/virtualenvs/superset3/lib/python3.6/site-packages/sqlalchemy/engine/default.py", line 470, in do_execute cursor.execute(statement, parameters) sqlalchemy.exc.ProgrammingError: (psycopg2.ProgrammingError) column "dateparsing" does not exist LINE 1: SELECT DATE_TRUNC('day', DATEPARSING) AS timestamp, sum(ma... ^ [SQL: 'SELECT DATE_TRUNC(\'day\', DATEPARSING) AS __timestamp, sum(mark_2_not_retake) AS "2 не отр." \nFROM (SELECT id,\n "IDFACULTY",\n "FACULTY",\n "SPEC",\n "FINANCE",\n "IDKAFEDRA",\n "KAFEDRA",\n "DISCIPLINE",\n "STUDENTGROUP",\n "IDSTUDENT",\n "STUDENTFIO",\n "TYPEZAN",\n "NUMZAN",\n "DATEZAN",\n "DATEPARSING",\n "TEMAZAN",\n "CONTROLZAN",\n "STUDENTPRESENT",\n "STUDENTMARK",\n "STUDENTMARKID",\n "STUDENTRETAKE",\n "STUDENTREASONABSENT",\n "STUDENTNUMDOC",\n "STUDENTNUMINVOICE",\n CASE when ("STUDENTMARK"=5) or ("STUDENTRETAKE"=5)then 1\n else 0\n end as "mark_5_all",\n CASE when ("STUDENTMARK"=4) or ("STUDENTRETAKE"=4)then 1\n else 0\n end as "mark_4_all",\n CASE when "STUDENTMARK"=3 or ("STUDENTRETAKE"=3) then 1\n else 0\n end as "mark_3_all",\n CASE when "STUDENTMARK"=2 then 1\n else 0\n end as "mark_2_all",\n CASE when "STUDENTMARK"=2 and "STUDENTRETAKE"=0 then 1\n else 0\n end as "mark_2_not_retake",\n CASE when "STUDENTMARK"=2 and "STUDENTRETAKE">0 then 1\n else 0\n end as "mark_2_retake",\n CASE when "TYPEZAN"=\'1\' and "STUDENTPRESENT"=\'1\' then 1\n else 0\n end as "nb_pr_all",\n CASE when "TYPEZAN"=1 and "STUDENTPRESENT"=1 and "STUDENTRETAKE"=0 then 1\n else 0\n end as "nb_pr_not_retake",\n \n CASE when "TYPEZAN"=0 and "STUDENTPRESENT"=1 then 1\n else 0\n end as "nb_lec_all",\n CASE when "TYPEZAN"=0 and "STUDENTPRESENT"=1 and "STUDENTRETAKE"=-1 then 1\n else 0\n end as "nb_lec_not_retake",\n \n CASE when "TYPEZAN"=1 and "STUDENTMARK" >0 then "STUDENTMARK"\n when "TYPEZAN"=1 and "STUDENTRETAKE" >0 then "STUDENTRETAKE"\n else 0\n end as "student_mark_for_avg",\n "STUDENTYEAR",\n"SEMESTR",\n"TEACHERNAME"\n \n\nFROM public.marks ) AS expr_qry \nWHERE "DATEPARSING" >= \'2017-04-06 13:03:06\' AND "DATEPARSING" <= \'2017-04-13 13:03:06\' AND ("IDFACULTY"=2 and "STUDENTYEAR"=1) GROUP BY DATE_TRUNC(\'day\', DATEPARSING) \n LIMIT %(param_1)s'] [parameters: {'param_1': 1000000}] 2017-04-13 13:03:06,929:INFO:root:Caching for the next 86400 seconds 2017-04-13 13:03:06,955:INFO:werkzeug:192.168.2.58 - - [13/Apr/2017 13:03:06] "GET /superset/explore_json/table/27/?form_data=%7B%22datasource%22%3A%2227table%22%2C%22viz_type%22%3A%22big_number%22%2C%22slice_id%22%3A42%2C%22granularity_sqla%22%3A%22DATEPARSING%22%2C%22time_grain_sqla%22%3A%22day%22%2C%22since%22%3A%227+days+ago%22%2C%22until%22%3A%22now%22%2C%22metric%22%3A%222+%D0%BD%D0%B5+%D0%BE%D1%82%D1%80.%22%2C%22compare_lag%22%3A7%2C%22y_axis_format%22%3A%22.3s%22%2C%22where%22%3A%22%5C%22IDFACULTY%5C%22%3D2+and+%5C%22STUDENTYEAR%5C%22%3D1%22%2C%22having%22%3A%22%22%2C%22filters%22%3A%5B%5D%7D HTTP/1.1" 400 -

mistercrunch commented 6 years ago

Notice: this issue has been closed because it has been inactive for 374 days. Feel free to comment and request for this issue to be reopened.

tgomas commented 6 years ago

Same issue here. {"GIT_SHA": "", "version": "0.26.3"}

A workaround is to use only spaceless lowercase column names.

tgomas commented 6 years ago

Since the problem only occurs when a time grain is selected, it could come from that code in incubator-superset/superset/db_engine_specs.py

time_grain_functions = {
        None: '{col}',
        'PT1S': "DATE_TRUNC('second', {col}) AT TIME ZONE 'UTC'",
        'PT1M': "DATE_TRUNC('minute', {col}) AT TIME ZONE 'UTC'",
        'PT1H': "DATE_TRUNC('hour', {col}) AT TIME ZONE 'UTC'",
        'P1D': "DATE_TRUNC('day', {col}) AT TIME ZONE 'UTC'",
        'P1W': "DATE_TRUNC('week', {col}) AT TIME ZONE 'UTC'",
        'P1M': "DATE_TRUNC('month', {col}) AT TIME ZONE 'UTC'",
        'P0.25Y': "DATE_TRUNC('quarter', {col}) AT TIME ZONE 'UTC'",
        'P1Y': "DATE_TRUNC('year', {col}) AT TIME ZONE 'UTC'",
    }

where {col} is between quotes only when there is no time grain.

villebro commented 6 years ago

@tgomas this issue is closed. Please post a new issue with details about the type of db engine you're using.

apache / superset