Error while executing a SQL query using MySQL db and Python client

Question

I have a complex SQL query as below which I am using to access MySQL db from a Python script.

 sql_query_vav = """SELECT t1.deviceId, t1.date, t1.vavId, t1.timestamp, t1.nvo_airflow as airflow, t1.nvo_air_damper_position as damper_position , t1.nvo_temperature_sensor_pps as vavTemperature , d.MILO as miloId ,m1.timestamp as miloTimestamp, m1.temperature as miloTemperature
                   FROM
                       (SELECT deviceId, date, nvo_airflow, nvo_air_damper_position, nvo_temperature_sensor_pps, vavId, timestamp, counter from vavData where date=%s and floor=%s) t1
                   INNER JOIN
                        (SELECT date,max(timestamp) as timestamp,vavId from vavData where date=%s and floor=%s group by vavId) t2
                   ON (t1.timestamp = t2.timestamp)     
                   INNER JOIN 
                       (SELECT VAV,MILO,floor from VavMiloMapping where floor = %s) d
                   ON (t1.vavId = d.VAV )
                   INNER JOIN
                         (SELECT t1.deviceId,t1.date,t1.timestamp,t1.humidity,t1.temperature,t1.block,t1.floor,t1.location
                         FROM
                             (SELECT deviceId,date,timestamp,humidity,temperature,block,floor,location from miloData WHERE date=%s and floor=%s) t1
                         INNER JOIN
                             (SELECT deviceId,max(timestamp) as timestamp,location from miloData where date=%s and floor=%s GROUP BY deviceId) t2
                         ON (t1.timestamp = t2.timestamp)) m1
                   ON (d.MILO = m1.location)  order by t1.vavId"""

I get an error with the above query which says

mysql.connector.errors.ProgrammingError: 1055 (42000): Expression #3 of SELECT list is not in GROUP BY 
clause and contains nonaggregated column 'minniedb.miloData.location' which is not functionally dependent
on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

I have tried to change the SQL mode by executing

SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

and tried to restart the MySQL service using

sudo service mysql restart

I think I have done everything required. Why am I still getting the same error?

perhaps you can try to disable that mode by SET GLOBAL sql_mode = 'NO_ENGINE_SUBSTITUTION'; if you have privilege, or you can edit mysql.cnf file and restart mysql — Barbaros Özhan, Feb 02 '20 at 13:52
`SET GLOBAL sql_mode = 'NO_ENGINE_SUBSTITUTION` has worked like a gem. Thanks @BarbarosÖzhan — chink, Feb 03 '20 at 04:04

score 0 · Answer 1 · answered Feb 02 '20 at 14:56

If you want to find the place of uncorrectness you must format the code carefully at least.

SELECT t1.deviceId, 
       t1.date, 
       t1.vavId, 
       t1.timestamp, 
       t1.nvo_airflow as airflow, 
       t1.nvo_air_damper_position as damper_position , 
       t1.nvo_temperature_sensor_pps as vavTemperature , 
       d.MILO as miloId ,
       m1.timestamp as miloTimestamp, 
       m1.temperature as miloTemperature
FROM ( SELECT deviceId, 
              date, 
              nvo_airflow, 
              nvo_air_damper_position, 
              nvo_temperature_sensor_pps, 
              vavId, 
              timestamp, 
              counter 
       from vavData 
       where date=%s 
         and floor=%s
     ) t1
INNER JOIN ( SELECT date,
                    max(timestamp) as timestamp,
                    vavId 
             from vavData 
             where date=%s 
               and floor=%s 
             group by vavId
           ) t2 ON (t1.timestamp = t2.timestamp)     
INNER JOIN ( SELECT VAV,
                    MILO,
                    floor 
             from VavMiloMapping 
             where floor = %s 
           ) d ON (t1.vavId = d.VAV )
INNER JOIN ( SELECT t1.deviceId,
                    t1.date,
                    t1.timestamp,
                    t1.humidity,
                    t1.temperature,
                    t1.block,
                    t1.floor,
                    t1.location
             FROM ( SELECT deviceId,
                           date,
                           timestamp,
                           humidity,
                           temperature,
                           block,
                           floor,
                           location 
                    from miloData 
                    WHERE date=%s 
                      and floor=%s
                  ) t1
             INNER JOIN ( SELECT deviceId,
                                 max(timestamp) as timestamp,
                                 location 
                          from miloData 
                          where date=%s 
                            and floor=%s 
                          GROUP BY deviceId
                        ) t2 ON (t1.timestamp = t2.timestamp)
           ) m1 ON (d.MILO = m1.location)  
order by t1.vavId

Now it is visible that there are 2 points of uncorrectness. Both problematic subqueries have an alias t2 and looks like

SELECT some_Id,
       max(timestamp) as timestamp,
       some_another_field
from some_table
where some_conditions
GROUP BY some_Id

The fiels marked as some_another_field is included into neither GROUP BY expression not aggregate function.

Correct these subqueries.

Error while executing a SQL query using MySQL db and Python client

1 Answers1