Hello everyone, just a quick rundown of our current situation. We plan to switch from ChartIO to Retool. ChartIO supports SQL SET syntax when building our query. However, while performing our migration, Retool does not support the SET syntax.
Here's the issue: in our system, we have this subscriptions table, and we want to get the user's most recent subscription record by running this query.
select id, user_id from transactions group by user_id
In strict mode, this will result in this error.
Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column transactions.id which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
To fix the above SQL statement, simply tell SQL that we want to retrieve the user's most recent transaction record.
select max(id) as id, user_id from payment_transactions group by user_id
Another alternative would be and what we use very often, is to include the SET statement before the SELECT statement.
set sql_mode=''; # Temporary disable strict mode
select id, user_id from transactions group by user_id
The disadvantage of this query is that it will return a random result from the grouping.
BUT, this is very useful for JOIN clauses that join more than 2-3 tables. That line of code will tell MySql to silent errors thrown by the strict mode by temporarily disabling it.
Why do we need this?
If you have extensive experience with Mysql 5.7, you may be aware of the "strict" mode feature, which prevents you from easily performing group by clauses (ONLY_FULL_GROUP_BY) on multiple properties, particularly when performing SQL JOIN.
Is there a workaround for this?
Yes, there are several alternatives on this. The first solution is to manage SQL configuration directly on the server. This, however, means that it is disabled globally, and what we are attempting to achieve here is to disable it only on demand.
Another workaround is to use MIN and MAX aggregate when doing group by to get the exact record we need. However, every now and then we need to create charts for the marketing team, so we usually just disable it temporarily to quickly create the necessary charts. Here's a more complex query to help you better understand our issue.
Assume we have an app that handles the user's subscriptions and payment transactions. Where a user can only have one active subscription and pay once a month, with a copy of the payment saved in the transaction history table.
SET sql_mode=''; # Disable strict mode temporarily, only for this query.
SELECT
subscriptions.id,
transactions.id,
plans.name,
transactions.amount
FROM
subscriptions
LEFT JOIN transactions ON transactions.subscription_id = subscriptions.id
LEFT JOIN plans ON plans.id = subscriptions.plan_id
GROUP BY
subscriptions.id;
An alternative solution would be:
SELECT
subscriptions.id,
transactions.id,
plans.name,
transactions.amount
FROM
subscriptions
LEFT JOIN (
SELECT
max(id) AS id,
subscription_id
FROM
transactions
GROUP BY
subscription_id) AS t_max ON subscription.id = t_max.subscription_id
LEFT JOIN transactions ON transactions.subscription_id = subscriptions.id
LEFT JOIN plans ON plans.id = subscriptions.plan_id
GROUP BY
subscriptions.id;
We can always use the alternative solution; however, newly onboarded users may also observe this behavior, indicating that it does not work within Retool. This is a combination of a bug and a feature, in my opinion. It's a bug because it says nothing when you run the query.