I'm building an accounting table and I need to show the current balance on each transaction.
Everything is working fine in my local environment (Windows 10 + MySQL 5.7.19), but the same query gives me strange results on my VPS (Linux + MariaDB 10.2.31) and in SQL Fiddle (MySQL 5.6).
First the code, then the examples (SQL Fiddle).
Tables:
CREATE TABLE `transaction_types` (
`id` bigint(20) UNSIGNED NOT NULL,
`name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
`slug` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
`amount` decimal(13,4) NOT NULL DEFAULT 0.0000,
`interests` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
`hourly` tinyint(1) NOT NULL DEFAULT 0,
`salary` tinyint(1) NOT NULL DEFAULT 0,
`income` tinyint(1) NOT NULL DEFAULT 1,
`created_at` timestamp NULL DEFAULT NULL,
`updated_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO `transaction_types` (`id`, `name`, `slug`, `amount`, `interests`, `hourly`, `salary`, `income`, `created_at`, `updated_at`) VALUES
(1, 'Alquiler', 'alquiler', '43000.0000', NULL, 0, 0, 0, '2021-06-07 09:52:49', '2021-06-10 03:26:17'),
(2, 'Campus Nube', 'campus-nube', '13500.0000', NULL, 0, 0, 0, '2021-06-07 09:52:49', '2021-06-10 03:26:33'),
(3, 'Impuestos', 'impuestos', '8400.0000', NULL, 0, 0, 0, '2021-06-07 09:52:49', '2021-06-10 03:26:47'),
(4, 'Marketing', 'marketing', '0.0000', NULL, 0, 0, 0, '2021-06-07 09:52:49', '2021-06-07 09:52:49'),
(5, 'Otros', 'otros', '0.0000', NULL, 0, 0, 0, '2021-06-07 09:52:49', '2021-06-07 09:52:49'),
(6, 'Sueldo', 'sueldo', '750.0000', NULL, 1, 1, 0, '2021-06-07 09:52:49', '2021-06-10 03:28:49'),
(7, 'Videollamadas', 'videollamadas', '0.0000', NULL, 0, 0, 0, '2021-06-07 09:52:49', '2021-06-07 09:52:49'),
(8, 'Cuota', 'cuota', '0.0000', NULL, 0, 0, 1, '2021-06-07 09:52:49', '2021-06-07 09:52:49'),
(9, 'Derecho a examen', 'derecho-a-examen', '0.0000', NULL, 0, 0, 1, '2021-06-07 09:52:49', '2021-06-07 09:52:49'),
(10, 'Materiales', 'materiales', '0.0000', NULL, 0, 0, 1, '2021-06-07 09:52:49', '2021-06-07 09:52:49'),
(11, 'Matrícula', 'matricula', '0.0000', NULL, 0, 0, 1, '2021-06-07 09:52:49', '2021-06-07 09:52:49'),
(12, 'Otros', 'otros-1', '0.0000', NULL, 0, 0, 1, '2021-06-07 09:52:49', '2021-06-07 09:52:49'),
(13, 'Salida didáctica', 'salida-didactica', '0.0000', NULL, 0, 0, 1, '2021-06-07 09:52:49', '2021-06-07 09:52:49'),
(14, 'Sueldo Secretaria', 'sueldo-secretaria', '25780.0000', NULL, 0, 1, 0, '2021-06-10 03:29:21', '2021-06-10 03:29:21'),
(15, 'Gas natural', 'gas-natural', '400.0000', NULL, 0, 0, 0, '2021-06-29 20:40:16', '2021-06-29 20:40:16');
CREATE TABLE transactions (
id bigint(20) UNSIGNED NOT NULL,
transaction_type_id bigint(20) UNSIGNED NOT NULL,
payment_id bigint(20) UNSIGNED DEFAULT NULL,
salary_id bigint(20) UNSIGNED DEFAULT NULL,
division_id bigint(20) UNSIGNED DEFAULT NULL,
user_id bigint(20) UNSIGNED DEFAULT NULL,
amount decimal(13,4) NOT NULL DEFAULT 0.0000,
discount decimal(13,4) NOT NULL DEFAULT 0.0000,
interest decimal(13,4) NOT NULL DEFAULT 0.0000,
total decimal(13,4) NOT NULL DEFAULT 0.0000,
date date NOT NULL,
comment longtext COLLATE utf8mb4_unicode_ci DEFAULT NULL,
created_at timestamp NULL DEFAULT NULL,
updated_at timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO transactions (id, transaction_type_id, payment_id, salary_id, division_id, user_id, amount, discount, interest, total, date, comment, created_at, updated_at) VALUES
(6, 8, NULL, NULL, 96, 81, '3200.0000', '0.0000', '0.0000', '3200.00', '2021-05-31', 'Cuotas Mayo', '2021-06-10 03:23:38', '2021-06-10 03:23:38'),
(7, 8, NULL, NULL, 96, 80, '3200.0000', '0.0000', '0.0000', '3200.00', '2021-05-31', 'Cuotas Mayo', '2021-06-10 03:23:38', '2021-06-10 03:23:38'),
(8, 8, NULL, NULL, 96, 96, '3200.0000', '0.0000', '0.0000', '3200.00', '2021-05-31', 'Cuotas Mayo', '2021-06-10 03:23:38', '2021-06-10 03:23:38'),
(9, 8, NULL, NULL, 96, 120, '3200.0000', '0.0000', '0.0000', '3200.00', '2021-05-31', 'Cuotas Mayo', '2021-06-10 03:23:38', '2021-06-10 03:23:38'),
(10, 8, NULL, NULL, 96, 31, '3200.0000', '0.0000', '0.0000', '3200.00', '2021-05-31', 'Cuotas Mayo', '2021-06-10 03:23:38', '2021-06-10 03:23:38'),
(11, 8, NULL, NULL, 96, 25, '3200.0000', '0.0000', '0.0000', '3200.00', '2021-05-31', 'Cuotas Mayo', '2021-06-10 03:23:38', '2021-06-10 03:23:38'),
(12, 8, NULL, NULL, 96, 35, '3200.0000', '0.0000', '0.0000', '3200.00', '2021-05-31', 'Cuotas Mayo', '2021-06-10 03:23:38', '2021-06-10 03:23:38'),
(13, 8, NULL, NULL, 96, 46, '3200.0000', '0.0000', '0.0000', '3200.00', '2021-05-31', 'Cuotas Mayo', '2021-06-10 03:23:38', '2021-06-10 03:23:38'),
(14, 8, NULL, NULL, 96, 202, '3200.0000', '0.0000', '0.0000', '3200.00', '2021-05-31', 'Cuotas Mayo', '2021-06-10 03:23:38', '2021-06-10 03:23:38'),
(15, 8, NULL, NULL, 96, 40, '3200.0000', '0.0000', '0.0000', '3200.00', '2021-05-31', 'Cuotas Mayo', '2021-06-10 03:23:38', '2021-06-10 03:23:38'),
(19, 8, NULL, NULL, 95, 111, '5000.0000', '700.0000', '900.0000', '5200.00', '2021-06-06', 'pago cuotas', '2021-06-18 20:50:08', '2021-06-18 20:50:08'),
(20, 8, NULL, NULL, 95, 68, '5000.0000', '700.0000', '900.0000', '5200.00', '2021-06-06', 'pago cuotas', '2021-06-18 20:50:08', '2021-06-18 20:50:08'),
(21, 7, NULL, NULL, NULL, NULL, '2000.0000', '0.0000', '0.0000', '2000.00', '2021-06-04', 'zoom', '2021-06-18 20:50:51', '2021-06-18 20:50:51'),
(36, 14, NULL, 4, NULL, 32, '29780.0000', '0.0000', '0.0000', '29780.00', '2021-06-05', 'aaa', '2021-06-24 16:44:34', '2021-06-24 16:44:34'),
(37, 6, NULL, 5, NULL, 105, '30000.0000', '0.0000', '0.0000', '30000.00', '2021-06-01', NULL, '2021-06-24 16:45:25', '2021-06-24 16:45:25'),
(43, 8, 14, NULL, 95, 63, '5000.0000', '0.0000', '0.0000', '5000.00', '2021-06-01', NULL, '2021-06-24 18:45:19', '2021-06-24 18:45:19'),
(81, 4, NULL, NULL, NULL, NULL, '4000.0000', '0.0000', '0.0000', '4000.00', '2021-06-02', 'asasas', '2021-06-29 18:43:49', '2021-06-29 18:43:49'),
(83, 6, NULL, 15, NULL, 89, '18250.0000', '0.0000', '0.0000', '18250.00', '2021-06-01', NULL, '2021-06-29 19:04:20', '2021-06-29 19:04:20');
Query:
SET @balance = 0;
SET @temp = 0;
SELECT SUM(IF(income, +total, -total)) INTO @balance
FROM (
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 9223372036854775807 OFFSET 0
) AS sub;
SELECT
transactions.id,
transactions.date,
transactions.total,
transaction_types.name AS typeName,
transaction_types.income,
@balance := (@balance - @temp) AS balance,
@temp := IF(income, +total, -total) AS temp
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;
This is the expected output (local):
| id | date | income | total | balance | temp |
|---|---|---|---|---|---|
| 19 | 2021-06-06 | 1 | 5200.0000 | -36630.0000 | 5200.0000 |
| 20 | 2021-06-06 | 1 | 5200.0000 | -41830.0000 | 5200.0000 |
| 36 | 2021-06-05 | 0 | 29780.0000 | -47030.0000 | -29780.0000 |
| 21 | 2021-06-04 | 0 | 2000.0000 | -17250.0000 | -2000.0000 |
| 81 | 2021-06-02 | 0 | 4000.0000 | -15250.0000 | -4000.0000 |
| 83 | 2021-06-01 | 0 | 18250.0000 | -11250.0000 | -18250.0000 |
| 43 | 2021-06-01 | 1 | 5000.0000 | 7000.0000 | 5000.0000 |
| 37 | 2021-06-01 | 0 | 30000.0000 | 2000.0000 | -30000.0000 |
| 6 | 2021-05-31 | 1 | 3200.0000 | 32000.0000 | 3200.0000 |
| 7 | 2021-05-31 | 1 | 3200.0000 | 28800.0000 | 3200.0000 |
| 8 | 2021-05-31 | 1 | 3200.0000 | 25600.0000 | 3200.0000 |
| 9 | 2021-05-31 | 1 | 3200.0000 | 22400.0000 | 3200.0000 |
| 10 | 2021-05-31 | 1 | 3200.0000 | 19200.0000 | 3200.0000 |
| 11 | 2021-05-31 | 1 | 3200.0000 | 16000.0000 | 3200.0000 |
| 12 | 2021-05-31 | 1 | 3200.0000 | 12800.0000 | 3200.0000 |
| 13 | 2021-05-31 | 1 | 3200.0000 | 9600.0000 | 3200.0000 |
| 14 | 2021-05-31 | 1 | 3200.0000 | 6400.0000 | 3200.0000 |
| 15 | 2021-05-31 | 1 | 3200.0000 | 3200.0000 | 3200.0000 |
This is what I'm getting (server):
| id | date | total | income | balance | temp |
|---|---|---|---|---|---|
| 19 | 2021-06-06 | 5200.0000 | 1 | -14380.0000 | 5200.0000 |
| 20 | 2021-06-06 | 5200.0000 | 1 | -19580.0000 | 5200.0000 |
| 36 | 2021-06-05 | 29780.0000 | 0 | -29780.0000 | -29780.0000 |
| 21 | 2021-06-04 | 2000.0000 | 0 | 15620.0000 | -2000.0000 |
| 81 | 2021-06-02 | 4000.0000 | 0 | -36630.0000 | -4000.0000 |
| 83 | 2021-06-01 | 18250.0000 | 0 | -2630.0000 | -18250.0000 |
| 43 | 2021-06-01 | 5000.0000 | 1 | -24780.0000 | 5000.0000 |
| 37 | 2021-06-01 | 30000.0000 | 0 | -32630.0000 | -30000.0000 |
| 6 | 2021-05-31 | 3200.0000 | 1 | 17620.0000 | 3200.0000 |
| 7 | 2021-05-31 | 3200.0000 | 1 | 14420.0000 | 3200.0000 |
| 8 | 2021-05-31 | 3200.0000 | 1 | 11220.0000 | 3200.0000 |
| 9 | 2021-05-31 | 3200.0000 | 1 | 8020.0000 | 3200.0000 |
| 10 | 2021-05-31 | 3200.0000 | 1 | 4820.0000 | 3200.0000 |
| 11 | 2021-05-31 | 3200.0000 | 1 | 1620.0000 | 3200.0000 |
| 12 | 2021-05-31 | 3200.0000 | 1 | -1580.0000 | 3200.0000 |
| 13 | 2021-05-31 | 3200.0000 | 1 | -4780.0000 | 3200.0000 |
| 14 | 2021-05-31 | 3200.0000 | 1 | -7980.0000 | 3200.0000 |
| 15 | 2021-05-31 | 3200.0000 | 1 | -11180.0000 | 3200.0000 |
SQL Fiddle gives me the same results if I have the typeName column on the SELECT, if I remove it, the result changes: SQL Fiddle. Maybe has something to do with the JOIN?
If you read the first table from bottom to top, you will see that the temp column (which is the total signed) is being added to the balance.
-36630.0000 is the total balance, and both tables have that number, but that number should be the first one.
If I delete some records, sometimes the query gives me the expected results, sometimes.
I have no idea what's going on.
