search

spine-tools / Spine-Database-API

Database interface to Spine generic data model
https://www.tools-for-energy-system-modelling.org/
GNU Lesser General Public License v3.0
6 stars 5 forks source link

Relationship Object parameter export fails on MySQL #159

Closed soininen closed 2 years ago

soininen commented 2 years ago

Trying to use RelationshipClassObjectHighlightingMapping and related export mappings with MySQL databases results in Traceback with the following message:

(pymysql.err.OperationalError) (1054, "Unknown column 'anon_6.id' in 'on clause'")
[SQL: SELECT %(param_1)s AS param_1, anon_1.id AS relationship_class_id, anon_1.name AS relationship_class_name, anon_1.object_class_id_list AS anon_1_object_class_id_list, anon_1.object_class_name_list AS anon_1_object_class_name_list, anon_2.id AS object_class_id, anon_3.id AS parameter_definition_id, anon_3.name AS parameter_definition_name, anon_4.id AS parameter_value_list_id, anon_4.name AS parameter_value_list_name, anon_5.id AS relationship_id, anon_5.name AS relationship_name, anon_5.object_id_list AS anon_5_object_id_list, anon_5.object_name_list AS anon_5_object_name_list, anon_6.id AS object_id, anon_7.id AS alternative_id, anon_7.name AS alternative_name, anon_7.description AS description, anon_8.value AS anon_8_value, anon_8.type AS anon_8_type 
FROM (SELECT anon_9.id AS id, anon_9.name AS name, anon_9.description AS description, anon_9.display_icon AS display_icon, anon_9.commit_id AS commit_id, group_concat(CAST(anon_9.object_class_id AS CHAR) ORDER BY anon_9.dimension separator %(sep)s) AS object_class_id_list, group_concat(CAST(anon_9.object_class_name AS CHAR) ORDER BY anon_9.dimension separator %(sep)s) AS object_class_name_list 
FROM (SELECT anon_10.id AS id, anon_10.name AS name, anon_10.description AS description, anon_10.dimension AS dimension, anon_10.display_icon AS display_icon, anon_2.id AS object_class_id, anon_2.name AS object_class_name, anon_10.commit_id AS commit_id 
FROM (SELECT anon_11.entity_class_id AS id, anon_11.dimension AS dimension, anon_11.member_class_id AS object_class_id, anon_12.name AS name, anon_12.description AS description, anon_12.display_icon AS display_icon, anon_12.hidden AS hidden, anon_12.commit_id AS commit_id 
FROM (SELECT relationship_entity_class.entity_class_id AS entity_class_id, relationship_entity_class.dimension AS dimension, relationship_entity_class.member_class_id AS member_class_id, relationship_entity_class.member_class_type_id AS member_class_type_id 
FROM relationship_entity_class) AS anon_11, (SELECT entity_class.id AS id, entity_class.type_id AS type_id, entity_class.name AS name, entity_class.description AS description, entity_class.display_order AS display_order, entity_class.display_icon AS display_icon, entity_class.hidden AS hidden, entity_class.commit_id AS commit_id 
FROM entity_class) AS anon_12 
WHERE anon_12.id = anon_11.entity_class_id) AS anon_10, (SELECT anon_12.id AS id, anon_12.name AS name, anon_12.description AS description, anon_12.display_order AS display_order, anon_12.display_icon AS display_icon, anon_12.hidden AS hidden, anon_12.commit_id AS commit_id 
FROM (SELECT entity_class.id AS id, entity_class.type_id AS type_id, entity_class.name AS name, entity_class.description AS description, entity_class.display_order AS display_order, entity_class.display_icon AS display_icon, entity_class.hidden AS hidden, entity_class.commit_id AS commit_id 
FROM entity_class) AS anon_12, (SELECT object_class.entity_class_id AS entity_class_id, object_class.type_id AS type_id 
FROM object_class) AS anon_13 
WHERE anon_12.id = anon_13.entity_class_id) AS anon_2 
WHERE anon_10.object_class_id = anon_2.id ORDER BY anon_10.id, anon_10.dimension) AS anon_9 GROUP BY anon_9.id, anon_9.name, anon_9.description, anon_9.display_icon, anon_9.commit_id) AS anon_1, (SELECT anon_14.id AS id, anon_14.name AS name, anon_14.class_id AS class_id, anon_14.class_name AS class_name, anon_14.commit_id AS commit_id, group_concat(CAST(anon_14.object_id AS CHAR) ORDER BY anon_14.dimension separator %(sep)s) AS object_id_list, group_concat(CAST(anon_14.object_name AS CHAR) ORDER BY anon_14.dimension separator %(sep)s) AS object_name_list, group_concat(CAST(anon_14.object_class_id AS CHAR) ORDER BY anon_14.dimension separator %(sep)s) AS object_class_id_list, group_concat(CAST(anon_14.object_class_name AS CHAR) ORDER BY anon_14.dimension separator %(sep)s) AS object_class_name_list 
FROM (SELECT anon_15.id AS id, anon_15.name AS name, anon_15.class_id AS class_id, anon_15.dimension AS dimension, anon_1.name AS class_name, anon_16.id AS object_id, anon_16.name AS object_name, anon_16.class_id AS object_class_id, anon_16.class_name AS object_class_name, anon_15.commit_id AS commit_id 
FROM (SELECT anon_9.id AS id, anon_9.name AS name, anon_9.description AS description, anon_9.display_icon AS display_icon, anon_9.commit_id AS commit_id, group_concat(CAST(anon_9.object_class_id AS CHAR) ORDER BY anon_9.dimension separator %(sep)s) AS object_class_id_list, group_concat(CAST(anon_9.object_class_name AS CHAR) ORDER BY anon_9.dimension separator %(sep)s) AS object_class_name_list 
FROM (SELECT anon_10.id AS id, anon_10.name AS name, anon_10.description AS description, anon_10.dimension AS dimension, anon_10.display_icon AS display_icon, anon_2.id AS object_class_id, anon_2.name AS object_class_name, anon_10.commit_id AS commit_id 
FROM (SELECT anon_11.entity_class_id AS id, anon_11.dimension AS dimension, anon_11.member_class_id AS object_class_id, anon_12.name AS name, anon_12.description AS description, anon_12.display_icon AS display_icon, anon_12.hidden AS hidden, anon_12.commit_id AS commit_id 
FROM (SELECT relationship_entity_class.entity_class_id AS entity_class_id, relationship_entity_class.dimension AS dimension, relationship_entity_class.member_class_id AS member_class_id, relationship_entity_class.member_class_type_id AS member_class_type_id 
FROM relationship_entity_class) AS anon_11, (SELECT entity_class.id AS id, entity_class.type_id AS type_id, entity_class.name AS name, entity_class.description AS description, entity_class.display_order AS display_order, entity_class.display_icon AS display_icon, entity_class.hidden AS hidden, entity_class.commit_id AS commit_id 
FROM entity_class) AS anon_12 
WHERE anon_12.id = anon_11.entity_class_id) AS anon_10, (SELECT anon_12.id AS id, anon_12.name AS name, anon_12.description AS description, anon_12.display_order AS display_order, anon_12.display_icon AS display_icon, anon_12.hidden AS hidden, anon_12.commit_id AS commit_id 
FROM (SELECT entity_class.id AS id, entity_class.type_id AS type_id, entity_class.name AS name, entity_class.description AS description, entity_class.display_order AS display_order, entity_class.display_icon AS display_icon, entity_class.hidden AS hidden, entity_class.commit_id AS commit_id 
FROM entity_class) AS anon_12, (SELECT object_class.entity_class_id AS entity_class_id, object_class.type_id AS type_id 
FROM object_class) AS anon_13 
WHERE anon_12.id = anon_13.entity_class_id) AS anon_2 
WHERE anon_10.object_class_id = anon_2.id ORDER BY anon_10.id, anon_10.dimension) AS anon_9 GROUP BY anon_9.id, anon_9.name, anon_9.description, anon_9.display_icon, anon_9.commit_id) AS anon_1, (SELECT anon_17.entity_id AS id, anon_17.dimension AS dimension, anon_17.member_id AS object_id, anon_17.entity_class_id AS class_id, anon_18.name AS name, anon_18.commit_id AS commit_id 
FROM (SELECT relationship_entity.entity_id AS entity_id, relationship_entity.entity_class_id AS entity_class_id, relationship_entity.dimension AS dimension, relationship_entity.member_id AS member_id, relationship_entity.member_class_id AS member_class_id 
FROM relationship_entity) AS anon_17, (SELECT entity.id AS id, entity.type_id AS type_id, entity.class_id AS class_id, entity.name AS name, entity.description AS description, entity.commit_id AS commit_id 
FROM entity) AS anon_18 
WHERE anon_18.id = anon_17.entity_id) AS anon_15 LEFT OUTER JOIN (SELECT DISTINCT anon_6.id AS id, anon_6.class_id AS class_id, anon_2.name AS class_name, anon_6.name AS name, anon_6.description AS description, anon_19.entity_id AS group_id, anon_6.commit_id AS commit_id 
FROM (SELECT anon_12.id AS id, anon_12.name AS name, anon_12.description AS description, anon_12.display_order AS display_order, anon_12.display_icon AS display_icon, anon_12.hidden AS hidden, anon_12.commit_id AS commit_id 
FROM (SELECT entity_class.id AS id, entity_class.type_id AS type_id, entity_class.name AS name, entity_class.description AS description, entity_class.display_order AS display_order, entity_class.display_icon AS display_icon, entity_class.hidden AS hidden, entity_class.commit_id AS commit_id 
FROM entity_class) AS anon_12, (SELECT object_class.entity_class_id AS entity_class_id, object_class.type_id AS type_id 
FROM object_class) AS anon_13 
WHERE anon_12.id = anon_13.entity_class_id) AS anon_2, (SELECT anon_18.id AS id, anon_18.class_id AS class_id, anon_18.name AS name, anon_18.description AS description, anon_18.commit_id AS commit_id 
FROM (SELECT entity.id AS id, entity.type_id AS type_id, entity.class_id AS class_id, entity.name AS name, entity.description AS description, entity.commit_id AS commit_id 
FROM entity) AS anon_18, (SELECT object.entity_id AS entity_id, object.type_id AS type_id 
FROM object) AS anon_20 
WHERE anon_18.id = anon_20.entity_id) AS anon_6 LEFT OUTER JOIN (SELECT entity_group.id AS id, entity_group.entity_id AS entity_id, entity_group.entity_class_id AS entity_class_id, entity_group.member_id AS member_id 
FROM entity_group) AS anon_19 ON anon_19.entity_id = anon_6.id 
WHERE anon_6.class_id = anon_2.id) AS anon_16 ON anon_15.object_id = anon_16.id 
WHERE anon_15.class_id = anon_1.id ORDER BY anon_15.id, anon_15.dimension) AS anon_14 GROUP BY anon_14.id, anon_14.name, anon_14.class_id, anon_14.class_name, anon_14.commit_id 
HAVING count(anon_14.dimension) = count(anon_14.object_id)) AS anon_5, (SELECT anon_18.id AS id, anon_18.class_id AS class_id, anon_18.name AS name, anon_18.description AS description, anon_18.commit_id AS commit_id 
FROM (SELECT entity.id AS id, entity.type_id AS type_id, entity.class_id AS class_id, entity.name AS name, entity.description AS description, entity.commit_id AS commit_id 
FROM entity) AS anon_18, (SELECT object.entity_id AS entity_id, object.type_id AS type_id 
FROM object) AS anon_20 
WHERE anon_18.id = anon_20.entity_id) AS anon_6, (SELECT alternative.id AS id, alternative.name AS name, alternative.description AS description, alternative.commit_id AS commit_id 
FROM alternative) AS anon_7, (SELECT anon_12.id AS id, anon_12.name AS name, anon_12.description AS description, anon_12.display_order AS display_order, anon_12.display_icon AS display_icon, anon_12.hidden AS hidden, anon_12.commit_id AS commit_id 
FROM (SELECT entity_class.id AS id, entity_class.type_id AS type_id, entity_class.name AS name, entity_class.description AS description, entity_class.display_order AS display_order, entity_class.display_icon AS display_icon, entity_class.hidden AS hidden, entity_class.commit_id AS commit_id 
FROM entity_class) AS anon_12, (SELECT object_class.entity_class_id AS entity_class_id, object_class.type_id AS type_id 
FROM object_class) AS anon_13 
WHERE anon_12.id = anon_13.entity_class_id) AS anon_2 LEFT OUTER JOIN (SELECT anon_21.id AS id, anon_21.name AS name, anon_21.description AS description, anon_21.entity_class_id AS entity_class_id, CASE WHEN (anon_12.type_id = %(type_id_1)s) THEN anon_12.id END AS object_class_id, CASE WHEN (anon_12.type_id = %(type_id_2)s) THEN anon_12.id END AS relationship_class_id, anon_21.default_value AS default_value, anon_21.default_type AS default_type, anon_21.commit_id AS commit_id, anon_21.parameter_value_list_id AS parameter_value_list_id 
FROM (SELECT parameter_definition.id AS id, parameter_definition.entity_class_id AS entity_class_id, parameter_definition.name AS name, parameter_definition.description AS description, parameter_definition.default_type AS default_type, parameter_definition.default_value AS default_value, parameter_definition.commit_id AS commit_id, parameter_definition.parameter_value_list_id AS parameter_value_list_id 
FROM parameter_definition) AS anon_21 INNER JOIN (SELECT entity_class.id AS id, entity_class.type_id AS type_id, entity_class.name AS name, entity_class.description AS description, entity_class.display_order AS display_order, entity_class.display_icon AS display_icon, entity_class.hidden AS hidden, entity_class.commit_id AS commit_id 
FROM entity_class) AS anon_12 ON anon_12.id = anon_21.entity_class_id) AS anon_3 ON anon_2.id = anon_3.object_class_id LEFT OUTER JOIN (SELECT anon_22.id AS id, anon_22.name AS name, anon_22.commit_id AS commit_id, group_concat(CAST(anon_22.value_index AS CHAR) ORDER BY anon_22.value_index separator %(group_concat_1)s) AS value_index_list, group_concat(CAST(anon_22.value AS CHAR) ORDER BY anon_22.value_index separator %(group_concat_2)s) AS value_list 
FROM (SELECT parameter_value_list.id AS id, parameter_value_list.name AS name, parameter_value_list.value_index AS value_index, parameter_value_list.value AS value, parameter_value_list.commit_id AS commit_id 
FROM parameter_value_list) AS anon_22 GROUP BY anon_22.id, anon_22.name, anon_22.commit_id) AS anon_4 ON anon_4.id = anon_3.parameter_value_list_id LEFT OUTER JOIN (SELECT anon_23.id AS id, anon_23.parameter_definition_id AS parameter_definition_id, anon_23.entity_class_id AS entity_class_id, anon_23.entity_id AS entity_id, CASE WHEN (anon_12.type_id = %(type_id_3)s) THEN anon_12.id END AS object_class_id, CASE WHEN (anon_12.type_id = %(type_id_4)s) THEN anon_12.id END AS relationship_class_id, CASE WHEN (anon_18.type_id = %(type_id_5)s) THEN anon_18.id END AS object_id, CASE WHEN (anon_18.type_id = %(type_id_6)s) THEN anon_18.id END AS relationship_id, anon_23.value AS value, anon_23.type AS type, anon_23.commit_id AS commit_id, anon_23.alternative_id AS alternative_id 
FROM (SELECT parameter_value.id AS id, parameter_value.parameter_definition_id AS parameter_definition_id, parameter_value.entity_id AS entity_id, parameter_value.entity_class_id AS entity_class_id, parameter_value.type AS type, parameter_value.value AS value, parameter_value.commit_id AS commit_id, parameter_value.alternative_id AS alternative_id 
FROM parameter_value) AS anon_23 INNER JOIN (SELECT entity.id AS id, entity.type_id AS type_id, entity.class_id AS class_id, entity.name AS name, entity.description AS description, entity.commit_id AS commit_id 
FROM entity) AS anon_18 ON anon_18.id = anon_23.entity_id INNER JOIN (SELECT entity_class.id AS id, entity_class.type_id AS type_id, entity_class.name AS name, entity_class.description AS description, entity_class.display_order AS display_order, entity_class.display_icon AS display_icon, entity_class.hidden AS hidden, entity_class.commit_id AS commit_id 
FROM entity_class) AS anon_12 ON anon_12.id = anon_23.entity_class_id) AS anon_8 ON anon_6.id = anon_8.object_id AND anon_8.parameter_definition_id = anon_3.id 
WHERE anon_7.id = anon_8.alternative_id]
[parameters: {'param_1': None, 'sep': ',', 'type_id_1': 1, 'type_id_2': 2, 'group_concat_1': ';', 'group_concat_2': ';', 'type_id_3': 1, 'type_id_4': 2, 'type_id_5': 1, 'type_id_6': 2}]
(Background on this error at: http://sqlalche.me/e/13/e3q8)
soininen commented 2 years ago

Fixed.