1

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.

1
  • 1
    My guess would be it's because of the row processing order. The order by clause is applied after the columns in the select list are calculated; before that rows are processed in an undefined order, so your assumptions about that may be incorrect.
    – mustaccio
    Jul 1 at 19:41
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 9223372036854775807 OFFSET 0
income |      total
-----: | ---------:
     1 |  5200.0000
     1 |  5200.0000
     0 | 29780.0000
     0 |  2000.0000
     0 |  4000.0000
     0 | 18250.0000
     1 |  5000.0000
     0 | 30000.0000
     1 |  3200.0000
     1 |  3200.0000
     1 |  3200.0000
     1 |  3200.0000
     1 |  3200.0000
     1 |  3200.0000
     1 |  3200.0000
     1 |  3200.0000
     1 |  3200.0000
     1 |  3200.0000
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 @balance
|    @balance |
| ----------: |
| -36630.0000 |
SELECT @temp
| @temp |
| ----: |
|     0 |
SELECT
    `id`,
    `date`,
    `total`,
    `name` AS `typeName`,
    `income`,
    @balance := (@balance - @temp) AS `balance`,
    @temp := IF(`income`, +`total`, -`total`) AS `temp`
FROM
(SELECT     `transactions`.`id`,
    `transactions`.`date`,
    `transactions`.`total`,    `transaction_types`.`name` ,
    `transaction_types`.`income`
 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) t3;
id | date       |      total | typeName          | income |     balance |        temp
-: | :--------- | ---------: | :---------------- | -----: | ----------: | ----------:
19 | 2021-06-06 |  5200.0000 | Cuota             |      1 | -36630.0000 |   5200.0000
20 | 2021-06-06 |  5200.0000 | Cuota             |      1 | -41830.0000 |   5200.0000
36 | 2021-06-05 | 29780.0000 | Sueldo Secretaria |      0 | -47030.0000 | -29780.0000
21 | 2021-06-04 |  2000.0000 | Videollamadas     |      0 | -17250.0000 |  -2000.0000
81 | 2021-06-02 |  4000.0000 | Marketing         |      0 | -15250.0000 |  -4000.0000
83 | 2021-06-01 | 18250.0000 | Sueldo            |      0 | -11250.0000 | -18250.0000
43 | 2021-06-01 |  5000.0000 | Cuota             |      1 |   7000.0000 |   5000.0000
37 | 2021-06-01 | 30000.0000 | Sueldo            |      0 |   2000.0000 | -30000.0000
 6 | 2021-05-31 |  3200.0000 | Cuota             |      1 |  32000.0000 |   3200.0000
 7 | 2021-05-31 |  3200.0000 | Cuota             |      1 |  28800.0000 |   3200.0000
 8 | 2021-05-31 |  3200.0000 | Cuota             |      1 |  25600.0000 |   3200.0000
 9 | 2021-05-31 |  3200.0000 | Cuota             |      1 |  22400.0000 |   3200.0000
10 | 2021-05-31 |  3200.0000 | Cuota             |      1 |  19200.0000 |   3200.0000
11 | 2021-05-31 |  3200.0000 | Cuota             |      1 |  16000.0000 |   3200.0000
12 | 2021-05-31 |  3200.0000 | Cuota             |      1 |  12800.0000 |   3200.0000
13 | 2021-05-31 |  3200.0000 | Cuota             |      1 |   9600.0000 |   3200.0000
14 | 2021-05-31 |  3200.0000 | Cuota             |      1 |   6400.0000 |   3200.0000
15 | 2021-05-31 |  3200.0000 | Cuota             |      1 |   3200.0000 |   3200.0000

db<>fiddle here

4
  • Thanks a lot for your answer. Just two questions: 1. If I need to paginate the results, it's OK to do it outside the subquery? LIMIT 9223372036854775807 OFFSET 0) t3 LIMIT 10 OFFSET 0;. It's the simplest way to make it work with the pagination system of Laravel. 2. Why I don't need to do this subquery in my local environment? Thanks again.
    – azeós
    Jul 1 at 23:23
  • 1
    That depends on the version you got, later updates as in the fiddle implemented that "feature". it was anoying, as i had to rewrite my queiries new, it is now mor estandard compatible
    – nbk
    Jul 1 at 23:28
  • Don't want to be annoying, but what about the pagination? It makes a difference if I LIMIT the subquery to?
    – azeós
    Jul 2 at 0:02
  • That is no problem , the high number guarantees that you get all rows even high bigibt numbers , Burt as you can use where clauses you can also use limit and offset, you have to be carefull that you got all
    – nbk
    Jul 2 at 0:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.