How can I test function with logic inside sql querys?

My problem is that I have giant SQL queries that have logic inside them and need to be tested someway. I'm using PHP and trying PHPUnit together. I have tons of functions similar to this one, that has different levels of logic inside them and I need to automate tests to every single one. For example, queries where some content returned depends on multiple tables and states. An example in code:

<?php

use \somewhere\controller;

class cidade extends controller {
    // query() comes from controller, and it's a pdo query
    public function listar () {
        return $this->query(
            'SELECT 
                c.id, s.descricao, c.dataInicial, c.dataFinal, c.valor 
            FROM comissao c left join servico s ON (c.servico = s.id)
            WHERE (CURDATE() BETWEEN c.dataInicial AND c.dataFinal)
                OR (CURDATE() > c.dataInicial AND c.dataFinal IS NULL)'
        );
    }
}

That returns this

If I use this setup (I created this only for example, in sqlfiddle.com)

CREATE TABLE IF NOT EXISTS `servico` (
  `id` int(6) unsigned NOT NULL,
  `descricao` varchar(50) NOT NULL
) DEFAULT CHARSET=utf8;
INSERT INTO `servico` (`id`, `descricao`) VALUES
  (1, 'Algo'),
  (2, 'Alguma coisa'),
  (3, 'Outra coisa');
  
CREATE TABLE IF NOT EXISTS `comissao` (
  `id` int(6) unsigned NOT NULL,
  `servico` int(6) unsigned NOT NULL,
  `dataInicial` date NOT NULL,
  `dataFinal` date,
  `valor` decimal(15,2) NOT NULL
) DEFAULT CHARSET=utf8;
INSERT INTO `comissao` (`id`, `servico`, `dataInicial`, `dataFinal`, `valor`) VALUES
  (1, '1', '2021-03-01', '2021-03-10', 12.30),
  (2, '2', '2021-03-01', '2021-03-10', 77.30),
  (3, '3', '2021-03-15', '2021-04-06', 1.30),
  (4, '1', '2021-03-28', NULL, 15.30),
  (5, '2', '2021-03-28', NULL, 6.30);

But in my day to day, I will change this database and it's gonna be complicated to change my tests to new results.

A little bit of what I already read about: I thought in to create a database just to test, but this would be a huge work to set up, so I started to look for a way to "create" a basic fake DB for these queries, but I could not find. So I just read about dbunit for a minute but seems like doesn't work with PHPUnit new versions, so I think that is deprecated. In some other places of PHPUnit documentation, I found things about dependency injection and mock my database results, but actually, I need to test what results I will get with my query, and not set results by myself.

If you want to run tests in your live database without altering it then you need to use named transactions.

At the start you want:

BEGIN TRANSACTON Test123

Then after you've done your test SQL code, run

ROLLBACK TRANSACTION Test123

Now you can make whatever changes you want to your live database and it won't take effect. You can also have sub transactions inside the outer transaction, so you don't have to worry about nesting your tests. Just make sure that you autogenerate a unique name, rather than "Test123" as above.

For testing, I would recommend making a start and an end wrapper method. Start will call begin tran and setup your test data. Then the end wrapper will do a delta comparison of your input data and then clean up with rollback. In between you do your test.

For a super easy way to test expected vs actual results, I can highly recommend Beyond Compare by AirSquirrels. It has some very powerful command line options, which I use daily in my test routines. If you make a simple method to export your data (expected & actual) to text files, it can just compare them and then report back if there are differences.