Do you have a bit more context for us? What is needed to reproduce the issue? Never had a similiar thing on a plain instance myself.

For example how many and what extensions and languages are installed?
Do you or you host have special settings your database?
Just to be sure what version and database typ you are using?
Where exactly does this issue come up? In the backend? In the frontend? While the upgrade is running? After the upgrade within an backend screen?

cc @richard67

And another thing please ask your hoster for the SQL statment that caused this issue. It should be found within the database error log.

Everytime I get this error the helpdesk at www.one.com fixes the issue just so I can get my webside updated.

Sounds a bit weird. Why don't they adapt then their server settings to get rid of this error if they know the reason?

The reason has been discussed already in the past and as far as I remember it won't be fixed in Joomla 4 that relies on the recommended default configuration of the database systems.

The necessary hack is well-known since J!4.0:

Change

// If needed, set the sql modes.
if ($this->options['sqlModes'] !== [])
{
	$this->connection->query('SET @@SESSION.sql_mode = \'' . implode(',', $this->options['sqlModes']) . '\';');
}

to

// If needed, set the sql modes.
if ($this->options['sqlModes'] !== [])
{
	$this->connection->query('SET @@SESSION.sql_mode = \'' . implode(',', $this->options['sqlModes']) . '\';');

	mysqli_query($this->connection,"SET SQL_BIG_SELECTS = 1;" );
}

in libraries\vendor\joomla\database\src\Mysqli\MysqliDriver.php

During update the hack will be overwritten. That's the reason why you see again the error after Joomla update.

But again, the hack is only necessary if the host doesn't adapt the database settings.

Related: joomla-framework/database#266

Thank you ReLater for you answer.
I asked the support over at www.one.com if there was a way I could fix this from my end.
I recived exactly the same solution that you posted. I read about this fix awhile back, but other contributors on that same thread where advicing against using this fix and I was reluctant to try it.

I have now done this and just as you said as soon as my joomla website was updated the error was back.
Still it's easy to do add the lines of config everytime and less time consuming.

Thanks to all who contributed and have a nice holliday

Best regards,
S.

but other contributors on that same thread where advicing against using this fix and I was reluctant to try it.

People are right to discourage this hack! It is an emergency solution that works at the moment but maybe not forever! MysqliDriver.php can change one day and you destroy your Joomla. Who knows...

I asked the support over at www.one.com if there was a way I could fix this from my end.
I recived exactly the same solution that you posted.

All this together is the reason why I recommend you to change the web hoster, because it is not suitable for Joomla 4 and apparently has no interest in it.

The BIG_SELECT solution is a nice workaround, though it needs to be manually re-adjusted after every update since it's not in the Joomla app - for good reasons. But IMHO it should be possible for Joomla to check for updates using a JOIN that requires less than 67 million possibilities to evaluate (default one.com max_join_size=67108864). One might argue that if on a simple Joomla site this limit kicks in, the SQL might be poorly designed an can probably be improved...