dolt
dolt copied to clipboard
`sql_mode` is always strict
I have a query failing with ERROR 1105 (HY000): expression 'Policy.data' doesn't appear in the group by expressions
. With legacy systems normally I relax sql_mode
to TRADITIONAL or "" to get round this, which works perfectly on MySQL. With Dolt I can change the value of sql_mode
but it doesn't have any effect on the way the queries run.
The erroring query:
SELECT Acl.target, Acl.principal, Acl.authority, Acl.policy, Policy.data
FROM `access_resource_groups` Acl
LEFT JOIN `access_policies` Policy ON Policy.id = Acl.policy
JOIN `document_groups` ResourceGroup ON Acl.principal_class = 'modUserGroup'
AND (Acl.context_key = 'web' OR Acl.context_key IS NULL OR Acl.context_key = '')
AND ResourceGroup.document = '26714'
AND ResourceGroup.document_group = Acl.target
GROUP BY Acl.target, Acl.principal, Acl.authority, Acl.policy
Sample session:
mysql> set sql_mode='';
Query OK, 1 row affected (0.01 sec)
mysql> select @@sql_mode;
+--------------------+
| @@SESSION.sql_mode |
+--------------------+
| |
+--------------------+
1 row in set (0.01 sec)
mysql> SELECT Acl.target, Acl.principal, Acl.authority, Acl.policy, Policy.data FROM `access_resource_groups` Acl LEFT JOIN `access_policies` Policy ON Policy.id = Acl.policy JOIN `document_groups` ResourceGroup ON Acl.principal_class = 'modUserGroup' AND (Acl.context_key = 'web' OR Acl.context_key IS NULL OR Acl.context_key = '') AND ResourceGroup.document = '26714' AND ResourceGroup.document_group = Acl.target GROUP BY Acl.target, Acl.principal, Acl.authority, Acl.policy;
ERROR 1105 (HY000): expression 'Policy.data' doesn't appear in the group by expressions
mysql> set sql_mode='TRADITIONAL';
Query OK, 1 row affected (0.01 sec)
mysql> select @@sql_mode;
+--------------------+
| @@SESSION.sql_mode |
+--------------------+
| TRADITIONAL |
+--------------------+
1 row in set (0.01 sec)
mysql> SELECT Acl.target, Acl.principal, Acl.authority, Acl.policy, Policy.data FROM `access_resource_groups` Acl LEFT JOIN `access_policies` Policy ON Policy.id = Acl.policy JOIN `document_groups` ResourceGroup ON Acl.principal_class = 'modUserGroup' AND (Acl.context_key = 'web' OR Acl.context_key IS NULL OR Acl.context_key = '') AND ResourceGroup.document = '26714' AND ResourceGroup.document_group = Acl.target GROUP BY Acl.target, Acl.principal, Acl.authority, Acl.policy;
ERROR 1105 (HY000): expression 'Policy.data' doesn't appear in the group by expressions
Related: #2172
Closing in favor of #2172