Selecting History from the list generates a mysql error

[brianw777]

Describe the bug When I select the 3 bar menu up in the top right then select History from the list I get a 500 Internal Server error.

I also get the same error when going to a plant and clicking on history to move the plant to history. The plant does get moved to the history and is no longer in the list of plants but the error is displayed and I use the browser back button to go back. I have confirmed that the history and history_date fields in the plants table show data. I have also confirmed that this error is produced from all servers (i.e. nginx, apache2 and internal php asatru serve)

500 Internal Server Error Exception at /var/www/html/hortusfox-web-3.1/vendor/danielbrendel/asatru-php-framework/src/database.php:643 Reported error: SQLSTATE[HY000]: General error: 3065 Expression #1 of ORDER BY clause is not in SELECT list, references column 'hortusfox.plants.history_date' which is not in SELECT list; this is incompatible with DISTINCT Refresh Stack trace:

6 /var/www/html/hortusfox-web-3.1/vendor/danielbrendel/asatru-php-framework/src/database.php(643): PDOStatement::execute

5 /var/www/html/hortusfox-web-3.1/app/models/PlantsModel.php(246): Asatru\Database\Model::raw

4 /var/www/html/hortusfox-web-3.1/app/controller/plants.php(519): PlantsModel::getHistoryYears

3 internal function: PlantsController::view_history

2 /var/www/html/hortusfox-web-3.1/vendor/danielbrendel/asatru-php-framework/src/controller.php(591): call_user_func

1 /var/www/html/hortusfox-web-3.1/vendor/danielbrendel/asatru-php-framework/src/bootstrap.php(123): Asatru\Controller\ControllerHandler::parse

0 /var/www/html/hortusfox-web-3.1/public/index.php(25): require_once ( '/var/www/html/hortusfox-web-3.1/vendor/danielbrendel/asatru-php-framework/src/bootstrap.php' )

To Reproduce Steps to reproduce the behavior:

1. With the app open in the browser select the 3 bar menu from the top right
2. Select History from the list
3. See the above error for details
4. This happens with all servers nginx, apache2 and the internal php asatru serve

Expected behavior I am not exactly sure what is supposed to happen I have just been playing around with Hortusfox and haven't seen the history before. From the documentation it looks like a history of all the plants moved to history should show up.

Screenshots If applicable, add screenshots to help explain your problem.

Software:

• HortusFox version 3.1
• PHP Version 8.3
• MySQL version 8.0.37-0ubuntu0.24.04.1

Additional context I wanted to run the query manually so found the query string in the file app/models/PlantsModel.php in the function getHistoryYears()

return static::raw('SELECT DISTINCT YEAR(history_date) AS history_year FROM' . self::tableName() . 'WHERE history = 1 AND history_date IS NOT NULL ORDER BY history_date DESC');

From within mysql I ran the query substituting plants for the tablename and got the error:

mysql> SELECT DISTINCT YEAR(history_date) AS history_year FROM plants WHERE history = 1 AND history_date IS NOT NULL ORDER BY history_date DESC; ERROR 3065 (HY000): Expression #1 of ORDER BY clause is not in SELECT list, references column 'hortusfox.plants.history_date' which is not in SELECT list; this is incompatible with DISTINCT

By removing the DISTINCT I can get the list of years:

mysql> SELECT YEAR(history_date) AS history_year FROM plants WHERE history = 1 AND history_date IS NOT NULL ORDER BY history_date DESC; +--------------+ | history_year | +--------------+ | 2024 | | 2024 | +--------------+ 2 rows in set (0.00 sec)

After doing some research I found that the ORDER BY must also be in the SELECT so, after testing other things and failing, I tested it by replacing the ORDER BY history_date with ORDER BY YEAR(history_date) and the error went away and the distinct years was shown.

mysql> SELECT DISTINCT YEAR(history_date) AS history_year FROM plants WHERE history = 1 AND history_date IS NOT NULL ORDER BY YEAR(history_date) DESC; +--------------+ | history_year | +--------------+ | 2024 | +--------------+ 1 row in set (0.00 sec)

By changing the line of code in the file app/models/PlantsModel.php in the function getHistoryYears() I was able to run it without the error:

return static::raw('SELECT DISTINCT YEAR(history_date) AS history_year FROM' . self::tableName() . 'WHERE history = 1 AND history_date IS NOT NULL ORDER BY YEAR(history_date) DESC');

ORDER BY history_date DESC changed to: ORDER BY YEAR(history_date) DESC

[danielbrendel]

Wow, that is a beautiful issue! <3 I will review and evaluate this as soon as I find a minute! Thank you for your contribution.

[danielbrendel]

I can reproduce this and this issue only appears with MySQL. A fix comes with the current milestone.