When debugging an ORM call, you may find yourself collecting the SQL that will be exectued via the DataList::sql() function - this is great, but when copied and pasted into e.g. PHPMyAdmin, MySQL Workbench, HeidiSQL, DBeaver, Sequel Pro, etc... it doesn't seem to work! Lets look at an example query that might* be used for a template call such as <% if $Menu(1) %>:

SELECT DISTINCT count(DISTINCT "SiteTree_Live"."ID") AS "Count"
FROM "SiteTree_Live"
WHERE (("SiteTree_Live"."ParentID" = 0))

*(simplified, this is not an actual copy/paste)When attempting to run this against my MariaDB database, I get the following error:

Error: ER_PARSE_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '."ID") AS "Count"

The problem here is confusing because there are two things going on to produce this error.

• MySQL does not follow ANSI SQL standards by default
• MySQL is very permissive with what it allows as a string

Those of use who are familiar with MySQL (or MariaDB) may note that they expect backticks (`) everywhere there are double quotes ("), and that for the most part making this change sees the query execute. This is true, but why?

If the quotes were the issue, we might expect the error to begin at "SiteTree_Live"."ID" rather than at ."ID" as reported. This is because MySQL is allowing the first ocurrence of the data identifier SiteTree_Live because it is processed as a string. Strings are valid in select statements like this, so that is OK. But the dot that comes afterward is unexpected for a string value, and an error is produced. OK, so why has SilverstripeCMS seen fit to confuse MySQL with strings where data identifiers should be?

Because Silverstripe CMS formats queries in ANSI compliant SQL (for the most part). This is one place where MySQL diverges from the standard (by default), choosing backticks to delimit data identifiers rather than the standard defined double quote. The mnemonic I learnt for this is "Double for data; Single for strings." When writing a string, always use a single quote. MySQL again is more permissive and diverges from the standard in that it allows either single quote OR double quote to delimit strings. OK, but how to deal with this without a lot of painful copy and paste? The easiest way to make MySQL process a query as supplied by and copied from a Silverstripe CMS site is to set the SQL_MODE to ANSI.

set SQL_MODE = 'ansi';

The above can be prefixed to any query you run, or you can set it globally for your session. See the documentation here: https://dev.mysql.com/doc/refman/8.0/en/sql-mode.html#sql-mode-setting (take note that your MySQL server version may be different, but I think the docs are largely the same in this area). The example given above will run and return results as follows:

set SQL_MODE = 'ansi';
SELECT DISTINCT count(DISTINCT "SiteTree_Live"."ID") AS "Count"
FROM "SiteTree_Live"
WHERE (("SiteTree_Live"."ParentID" <> 0));

Happy querying! :)