Non qualified column name in GROUP BY clause

[riccardocagnasso]

Using the SQL backend, when cubes creates query containing a GROUP BY clause, the name of the column is not fully qualified (it lacks the name of the table. before).

This creates problem when two tables with columns of same name are joined.

e.g.

sqlalchemy.exc.ProgrammingError: (psycopg2.ProgrammingError) column reference "status" is ambiguous

LINE 3: ...RACT(year FROM activity_date.date) < '2017') GROUP BY status
                                                                 ^
 [SQL: 'SELECT activity_subscription.status AS status \nFROM activity_subscription JOIN activity_date ON activity_subscription.activity_date_id = activity_date.id \nWHERE (EXTRACT(year FROM activity_date.date) = %(param_1)s AND EXTRACT(month FROM activity_date.date) = %(param_2)s AND EXTRACT(day FROM activity_date.date) >= %(param_3)s OR EXTRACT(year FROM activity_date.date) = %(param_4)s AND EXTRACT(month FROM activity_date.date) > %(param_5)s OR EXTRACT(year FROM activity_date.date) > %(param_6)s) AND (EXTRACT(year FROM activity_date.date) = %(param_7)s AND EXTRACT(month FROM activity_date.date) = %(param_8)s AND EXTRACT(day FROM activity_date.date) <= %(param_9)s OR EXTRACT(year FROM activity_date.date) = %(param_10)s AND EXTRACT(month FROM activity_date.date) < %(param_11)s OR EXTRACT(year FROM activity_date.date) < %(param_12)s) GROUP BY status'] [parameters: {'param_6': '2016', 'param_10': '2017', 'param_4': '2016', 'param_8': '8', 'param_3': '1', 'param_2': '9', 'param_12': '2017', 'param_5': '9', 'param_11': '8', 'param_7': '2017', 'param_1': '2016', 'param_9': '31'}]

This is my cube configuration

{
    "info": {
        "cv-datefilter": true,
        "cv-datefilter-hierarchy": "weekly"
    },
    "dimensions": [
        { "name": "public", "attributes": ["public"] },
        { "name": "year", "attributes": ["year"] },
        { "name": "month", "attributes": ["month"] },
        { "name": "title", "attributes": ["title"] },
        { "name": "type", "attributes": ["type"] },
        { "name": "product", "label": "Product"},
        { "name": "target", "label": "Target" },
        { "name": "status", "attributes": ["status"] },
        { "name": "school_type", "attributes": ["school_type"], "label": "school type" },
        { "name": "strategic_project_name", "attributes": ["strategic_project_name"], "label":"strategic project" },

        { "name": "source", "attributes": ["source"], "label": "source" },
        { "name": "already_prepared", "attributes": ["already_prepared"],
          "label": "Ha mai preparato studenti ad un esame Trinity?"},
        { "name": "is_centre", "attributes": ["is_centre"],
          "label": "La sua scuola/istituto/ente è sede di esami Trinity registrata?"},
        { "name": "had_trinity_exam", "attributes": ["had_trinity_exam"],
          "label": "Ha mai sostenuto un esame Trinity?"},
        { "name": "mailing_list", "attributes": ["mailing_list"],
          "label": "Desidera ricevere la Newsletter del team italiano di supporto?" },

        { "name": "themes_evaluation", "attributes": ["themes_evaluation"],
          "label": "Interesse dei contenuti/temi trattati" },
        { "name": "teaching_material_evaluation", "attributes": ["teaching_material_evaluation"],
          "label": "Materiali distribuiti" },
        { "name": "speeches_evaluation", "attributes": ["speeches_evaluation"],
          "label": "Interventi dei formatori" },
        { "name": "is_useful", "attributes": ["is_useful"],
          "label": "Ritiene che queste attività di formazione contribuiscano al suo sviluppo professionale?" },
        { "name": "will_use", "attributes": ["will_use"],
          "label": "Ritiene di poter applicare in classe alcune delle idee o dei contenuti trattati?" },
        { "name": "better_understanding", "attributes": ["better_understanding"],
          "label": "Ritiene di aver ricevuto nuovi stimoli per la valutazione dei suoi studenti?" },
        { "name": "will_reccomend", "attributes": ["will_reccomend"],
          "label": "In una scala di valori da 1 a 10, in che misura consiglierebbe la partecipazione a queste attività ad un/una collega?" },
        { "name": "will_prepare_students", "attributes": ["will_prepare_students"],
          "label": "Pensa di preparare alcuni dei suoi studenti agli esami Trinity quest’anno?" },

        { "name": "date",
            "levels": [
                {
                    "name":"year",
                    "label":"Year",
                    "info": { "cv-datefilter-field": "year" }
                },
                {
                    "name":"month",
                    "label":"Month",
                    "info": { "cv-datefilter-field": "month" }
                },
                {
                    "name":"day",
                    "label":"Day",
                    "info": { "cv-datefilter-field": "day" }
                }
            ],
            "role": "time",
            "hierarchies": [{
                "label": "Date",
                "levels": [
                    "year",
                    "month",
                    "day"
                ],
                "name": "date"
        }]},

        { "name": "datetime",
            "levels": [
                {
                    "name":"year",
                    "label":"Year",
                    "info": { "cv-datefilter-field": "year" }
                },
                {
                    "name":"month",
                    "label":"Month",
                    "info": { "cv-datefilter-field": "month" }
                },
                {
                    "name":"day",
                    "label":"Day",
                    "info": { "cv-datefilter-field": "day" }
                },
                {
                    "name":"hour",
                    "label":"Hour",
                    "info": { "cv-datefilter-field": "hour" }
                },
                {
                    "name":"minute",
                    "label":"Minute",
                    "info": { "cv-datefilter-field": "minute" }
                },
                {
                    "name":"second",
                    "label":"Second",
                    "info": { "cv-datefilter-field": "second" }
                }
            ],
            "role": "time",
            "hierarchies": [{
                "label": "Time",
                "levels": [
                    "year",
                    "month",
                    "day",
                    "hour",
                    "minute",
                    "second"
                ],
                "name": "datetime"
        }]},

        {
            "name": "registration_time",
            "template": "datetime",
            "label": "registration time"
        },

        {
            "name": "location",
            "levels": [
                {
                    "name":"region",
                    "label":"Region"
                },
                {
                    "name":"province",
                    "label":"Province"
                }
            ],
            "hierarchies": [{
                "name": "location",
                "label": "Location",
                "levels": ["region", "province"]
            }]
        },
        {
            "name": "applicant_location",
            "template": "location",
            "label": "applicant location"
        },
        {
            "name": "company_location",
            "template": "location",
            "label": "company location"
        }
    ],

    "cubes": [
        {
            "name": "activity_date",
            "label": "Activity dates",
            "dimensions": [
                "public",
                "title",
                "type",
                "product",
                "date",
                "target",
                "location",
                "school_type",
                "strategic_project_name"
            ],
            "joins": [
                { "master": "activity_id", "detail": "activity.id" },
                { "master": "location_id", "detail": "company.id", "alias": "location" },
                { "master": "location_id", "detail": "identity.id", "alias": "location_identity" },
                { "master": "activity_id", "detail": "target_activity.activity_id" },
                { "master": "activity_id", "detail": "activity_product.activity_id" },
                { "master": "location_identity.province_name", "detail": "province.name", "alias": "location_province" }
            ],
            "mappings": {
                "date.year": {"column":"date", "extract":"year"},
                "date.month": {"column":"date", "extract":"month"},
                "date.day": {"column":"date", "extract":"day"},
                "location.region": "location_province.region_name",
                "location.province": "location_identity.province_name",
                "title": "activity.title",
                "type": "activity.type",
                "product": "activity_product.product_name",
                "target": "target_activity.target",
                "school_type": "location.school_type",
                "strategic_project_name": "activity.strategic_project_name"
            },
            "aggregates": [{
                "name": "activity_dates",
                "label": "Activity dates count",
                "function": "count"
            }]
        },

        {
            "name": "activity_subscription",
            "label": "Applicants",
            "dimensions": [
                "public",
                "title",
                "type",
                "product",
                "date",
                "target",
                "status",
                "location",
                "school_type",
                "themes_evaluation",
                "teaching_material_evaluation",
                "speeches_evaluation",
                "is_useful",
                "will_use",
                "better_understanding",
                "will_reccomend",
                "will_prepare_students",
                "applicant_location",
                "company_location",
                "source",
                "already_prepared",
                "is_centre",
                "had_trinity_exam",
                "mailing_list",
                "strategic_project_name",
                "registration_time"
            ],
            "joins": [
                { "master": "activity_date_id", "detail": "activity_date.id" },
                { "master": "activity_date.activity_id", "detail": "target_activity.activity_id" },
                { "master": "activity_date.activity_id", "detail": "activity_product.activity_id" },
                { "master": "activity_date.activity_id", "detail": "activity.id" },
                { "master": "activity_date.location_id", "detail": "company.id", "alias": "location" },
                { "master": "activity_date.location_id", "detail": "identity.id", "alias": "location_identity" },
                { "master": "location_identity.province_name", "detail": "province.name" , "alias": "location_province" },
                { "master": "person_id", "detail": "person.id", "alias": "person" },
                { "master": "person_id", "detail": "identity.id", "alias": "person_identity" },
                { "master": "person_identity.province_name", "detail": "province.name" , "alias": "person_province" },
                { "master": "id", "detail": "subscription_feedback.subscription_id", "alias": "feedback" },
                { "master": "company_id", "detail": "company.id", "alias": "company" },
                { "master": "company_id", "detail": "identity.id", "alias": "company_identity" },
                { "master": "company_identity.province_name", "detail": "province.name" , "alias": "company_province" }
            ],
            "mappings": {
                "date.year": {"table": "activity_date", "column":"date", "extract":"year"},
                "date.month": {"table": "activity_date", "column":"date", "extract":"month"},
                "date.day": {"table": "activity_date", "column":"date", "extract":"day"},
                "location.region": "location_province.region_name",
                "location.province": "location_identity.province_name",
                "applicant_location.region": "person_province.region_name",
                "applicant_location.province": "person_identity.province_name",
                "company_location.region": "person_province.region_name",
                "company_location.province": "person_identity.province_name",
                "email": "person_identity.email",
                "title": "activity.title",
                "type": "activity.type",
                "product": "activity_product.product_name",
                "target": "target_activity.target",
                "public": "activity_date.public",
                "school_type": "company.school_type",
                "themes_evaluation": "feedback.themes_evaluation",
                "teaching_material_evaluation": "feedback.teaching_material_evaluation",
                "speeches_evaluation": "feedback.speeches_evaluation",
                "is_useful": "feedback.is_useful",
                "will_use": "feedback.will_use",
                "better_understanding": "feedback.better_understanding",
                "will_reccomend": "feedback.will_reccomend",
                "will_prepare_students": "feedback.will_prepare_students",
                "source": "source",
                "already_prepared": "already_prepared",
                "is_centre": "is_centre",
                "had_trinity_exam": "had_trinity_exam",
                "mailing_list": "person_identity.mailing_list",
                "strategic_project_name": "activity.strategic_project_name",
                "registration_time.year": {"column":"time", "extract":"year"},
                "registration_time.month": {"column":"time", "extract":"month"},
                "registration_time.day": {"column":"time", "extract":"day"},
                "registration_time.hour": {"column":"time", "extract":"hour"},
                "registration_time.minute": {"column":"time", "extract":"minute"},
                "registration_time.second": {"column":"time", "extract":"second"},
                "status": {"table": "activity_subscription", "column": "status"}
            },
            "measures": [
                { "name": "email", "label": "email" }
            ],
            "aggregates": [{
                "name": "applicants_count",
                "label": "applicants count",
                "function": "count"
            },{
                "name": "applicants_distinct_count",
                "label": "distinct applicants count (email)",
                "function": "count_distinct",
                "measure": "email"
            }]
        }
    ]
}