core: SQL sensor adds "LIMIT 1" and breaks MS SQL query.

SQL sensor adds “LIMIT 1” and breaks MS SQL query.

MariaDB constantly failed with DB over 3GB. I have switched to MS SQL. I’m trying to create a sensor to calculate the DB size. It seems the SQL sensor is not compatible with MS SQL at all. Just don’t add anything to the query string creatively, at least in case of db_url: "mssql+pyodbc://

hassio 0.117.1

https://www.home-assistant.io/integrations/sql/

Sensor configuration sample:

sensor:
  - platform: sql
    db_url: "mssql+pyodbc://user:pass@ms_sql_server/homeassistant?charset=utf8;DRIVER={FreeTDS};Port=1433;"
    queries:
      - name: DB size
        query: "SELECT CAST(CAST(SUM(m.size) AS DEC(18,0)) * 8/1024 AS DEC(18,2)) as size FROM sys.master_files m INNER JOIN sys.databases d ON d.database_id=m.database_id WHERE d.name='homeassistant';"
        column: 'size'
        unit_of_measurement: MB

Error message from logs: Error executing query SELECT CAST(CAST(SUM(m.size) AS DEC(18,0)) * 8/1024 AS DEC(18,2)) as size FROM sys.master_files m INNER JOIN sys.databases d ON d.database_id=m.database_id WHERE d.name='homeassistant' LIMIT 1;: (pyodbc.ProgrammingError) ('42000', "[42000] [FreeTDS][SQL Server]Incorrect syntax near 'LIMIT'. (102) (SQLExecDirectW)") [SQL: SELECT CAST(CAST(SUM(m.size) AS DEC(18,0)) * 8/1024 AS DEC(18,2)) as size FROM sys.master_files m INNER JOIN sys.databases d ON d.database_id=m.database_id WHERE d.name='homeassistant' LIMIT 1;] (Background on this error at: http://sqlalche.me/e/13/f405)