Inconsistent results between MySQL and MariaDB when using variables and order

Question

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.

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, Commented Jul 1, 2021 at 19:41

nbk · Accepted Answer · 2021-07-01 20:17:31Z

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

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, Commented Jul 1, 2021 at 23:23
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, Commented Jul 1, 2021 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, Commented Jul 2, 2021 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, Commented Jul 2, 2021 at 0:13

Stack Exchange Network

Inconsistent results between MySQL and MariaDB when using variables and order

1 Answer 1

Your Answer

Not the answer you're looking for? Browse other questions tagged
mysql-5.6
mysql-5.7
mariadb-10.2
table-variable
or ask your own question.

Linked

Hot Network Questions

Inconsistent results between MySQL and MariaDB when using variables and order

1 Answer 1

Your Answer

Sign up or log in

Post as a guest

Not the answer you're looking for? Browse other questions tagged mysql-5.6mysql-5.7mariadb-10.2table-variable or ask your own question.

Linked

Related

Hot Network Questions

Not the answer you're looking for? Browse other questions tagged
mysql-5.6
mysql-5.7
mariadb-10.2
table-variable
or ask your own question.