1
In the trigger, change
IF NEW.delivery_date = ""
to
IF NEW.delivery_date < '1971-01-01'
That will catch empty string and '1970-01-01' and any timezone variation on that "zero" date. It will fail if it is already NULL`, but that won't change the action of the Trigger.
(In the future, be sure to include any relevant Triggers in the ...
1
For syntax (and naming) reasons, you would need to change to:
select
users.id,
(select count(distinct c.songId)
from c
inner join s on c.songId = s.id
-- where c.userid=tdu.id
where c.id=tdu.id
and s.userId <> tdu.id -- <------ this is where the main issue was
and c.del IS NULL
limit 1 ) as ...
1
It is definitely possible to change the sql_mode in MySQL 5.7.
I suspect you changed the session setting without changing the global setting. Like this:
mysql> set sql_mode = 'STRICT_TRANS_TABLES';
mysql> select @@sql_mode, @@global.sql_mode;
+---------------------+------------------------+
| @@sql_mode | @@global.sql_mode |
+------------...
1
Tables and Resultset are unordered, so you must give it first an Order in the subquery
SELECT `income`, `total`
FROM `transactions`
INNER JOIN `transaction_types` ON `transactions`.`transaction_type_id` = `transaction_types`.`id`
ORDER BY
`date` desc,
`transactions`.`created_at` desc,
`transactions`.`id` asc
LIMIT ...
1
If every row has the same two attributes in JSON, you should consider making those into table columns (date and value). That will make the subsequent SQL much simpler.
Only top voted, non community-wiki answers of a minimum length are eligible
