CMS mantainers: maybe a RED

I just have a yellow label here :P

so, just the NO_ZERO_DATE will give error almost in all db 0000-00-00 00:00:00 date values, maybe we need a less stricter mode ...

open to suggestions

How do you intend on dealing with Null dates then if you can't set 0 dates?

ok, so i removed 'NO_ZERO_IN_DATE', and 'NO_ZERO_DATE' from sql modes.
i think we need to be realistic here.

I agree now. Seems good :)

Hi, I would say, "ONLY_FULL_GROUP_BY" should be not applied too.

From my understanding that option makes the use of GROUP BY in MySQL consistent with what PostgreSQL and SQL Server seem to use by default, based on that it should remain.

I am just looking at it from perspective of Joomla!. This will break running of many extensions in Joomla! 4.

It will break all the common SQL queries:

SELECT * FROM table x INNER JOIN ... WHERE ... GROUP BY x.id;

Yes, on the one side you can get unpredictable results in some cases (and in some cases this is not a problem), but on the other side you get better performance (unnecessary columns are not "managed") and the query gets simple and clearer entry.

I can confirm that ONLY_FULL_GROUP_BY breaks most of our extensions.

I have updated one of my extension to meet the "ONLY_FULL_GROUP_BY" rule. I changed cca 10% of queries completely (because of getting right outcomes) and the memory requirement to get the same outcomes with this rule increased :-(

Not nice, but:
$db->setQuery("SET sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));");
$db->execute();

The reason why we include that in the strict mode is because it is needed for other databases like PostgreSQL. So if you want to write queries that work in all databases Joomla supports, then you already need to write queries like this and don't have to change anything.
If you only care about MySQL users, then yes, it doesn't make much sense.

That's not quite true. The fact it is the same as Postgres is a relatively happy bonus. But strict mode is also more compliant with raw sql

There's a relatively good explanation of that here: https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html

I fully understand that because of the accuracy of queries, strict mode is important. In fact as an accountant I like strict rules. But these rules:

ONLY_FULL_GROUP_BY
NO_ZERO_DATE, NO_ZERO_IN_DATE

make the building of SQL queries or PHP code harder.

• ONLY_FULL_GROUP_BY makes the query more robust and complicated. The query needs sometimes more memory resources.

• NO_ZERO_DATE, NO_ZERO_IN_DATE need additional treatment of the column value. Hopefully, if I have right information, NO_ZERO_DATE, NO_ZERO_IN_DATE were removed from strict mode.

But the main reason, Joomla! CMS should care about strict mode, is not if it is right or not. But the backward compatibility.

I really don't know the number but let's say, a lot of all Joomla! extensions (which use SQL queries) never applied MySQL strict mode, so this can be a backward compatibility problem.

And as displayed on the image:

(Joomla! is the blue one, comparing to Wordpress and Drupal)

Joomla! cannot afford to have another backward compatibility problem again :-( Because there is nowhere to fall.

I personally rewrite the SQL queries to strict rules, but I think many developers still don't care about this rules.

IMPO, forcing the sql mode ONLY_FULL_GROUP_BY for the Mysqli Driver is a very harsh and imprudent decision that may compromise hundreds, if not thousands, of extensions.
Okay that PostgreSQL requires it, and that strict mode is more compliant with raw sql, but some developers may want to use queries just for MySQL, not in strict mode. This is going to cause a lot of troubles to the extensions, and I think in these days Joomla really needs third party extensions to work.
Our extensions are working fine with the strict mode, but just to let you understand how easy it is to fall into such issues, please try to call the static method users() of the class JHtmlList:
echo JHtmlList::users('juid', 96);
You will get a nice SQL error 1055 because the query executed in the core file /libraries/cms/html/list.php is missing the field u.name in the GROUP BY.
If there was a configuration setting to turn this sql mode on or off, maybe things would be easier also for hosting companies that could spend hours trying to understand why ONLY_FULL_GROUP_BY is still enabled when it's actually disabled in the db configuration.
It's a big change, and will Joomla really benefit from it? It seems that even the Joomla framework itself is not ready for this change yet.

Actually, a lot of the SELECT id, foo, bar FROM #__table GROUP_BY id, foo, bar could be replaced by just SELECT DISTINCT id, foo, bar FROM #__table. As long as you don't use any aggregate functions, I don't see a reason to use GROUP_BY to begin with. Not sure why we have developed a habit to not use DISTINCT.

@Bakual that is so true, but I guess you were not the only ones who have developed a habit to prefer GROUP BY over DISTINCT. Anyway, the future is strict mode, we all agree with that. Some db versions even ship with ONLY_FULL_GROUP_BY enabled by default from what I've heard, but I just wanted to say that this is/was mainly a setting defined at server level, not in the application. At least for backward compatibility or legacy, I believe a configuration setting or a plugin would not have hurt, and most of all, it would have helped many extensions getting on board of J4 faster. It is just not beautiful, nor handy, to group by 10 fields when you need to select them all, maybe when using also an aggregate function in a sub-query like MIN or MAX.

If you use aggregate functions or joins, group by is mostly the only chance to get right outcomes. Full group by (even used indexes) can be 2 - 6x slower than not "full group by" :-(

that's the SQL standards way, and after all for semver when MAJOR.....

If you use aggregate functions or joins, group by is mostly the only chance to get right outcomes.

Joins aren't a problem with DISTINCT. You only need GROUP_BY for aggregations.

Joins aren't a problem with DISTINCT. You only need GROUP_BY for aggregations.

Yes, sorry for confusing, not joins.

that's the SQL standards way

NO_ZERO_IN_DATE case tell us that something set as standard can be very problematic and even "removed from standards" after that.

Ignoring of "full group by" rule is not used to just break strict standards, it is used to get the results much more faster. Using group by and e.g. group_contact you can get the results faster without having additional queries or subqueries in e.g. loops.

I am not a fan of parameters everywhere and parameters for everything but maybe a parameter to set rules for handling database can be a good idea in Joomla!.

"Breaking" strict rules like "NO_ZERO_IN_DATE" (in past) or "ONLY_FULL_GROUP_BY", etc. is something what is done intentionally - e.g. to run your site much more faster.

And as written above, the problematic part will come when most of developers will find out that they need to overwrite their SQL queries (If you ask me, I have overwritten SQL queries in my extensions to strict rules but what I see is reducing the speed of getting the results).

Hi folks. When i did this a long time ago it was clear Joomla Core had a lot of issues with other Database systems because the Core didn't respect the other databases syntax. Which frequently broken Joomla in other databases system in updates. Also it was becoming clear the mysql Database system where becoming more strict every version.

Meanwhile this strict mode is in the joomla framework, no more in Joomla Core, but since 4.0 uses the Database package 2.0 of the framework, Joomla inherits the default strict mode of the framework.

I don't recall properly because unfortanly don't have time no more to contribute to Joomla as i used to.
But, to change this behaviour, i think all you need to do is to pass sqlModes Array parameter in the create db method with the strict modes to the ones you think should be used in Joomla core. 4.0.

Here https://github.com/joomla/joomla-cms/blob/staging/libraries/src/Factory.php#L651

This will be processed by Joomla framework here https://github.com/joomla-framework/database/blob/2.0-dev/src/Mysqli/MysqliDriver.php#L128

So, please submit a PR to change Joomla 4.0 MySQL strict more and let the cms mantainers and/or the community decide.

Thanks

If you use aggregate functions or joins, group by is mostly the only chance to get right outcomes. Full group by (even used indexes) can be 2 - 6x slower than not "full group by" :-(

indeed i have seen this

• but performance should be fixable in all cases, and not only fixable but also they may even get faster than before because of rewritting them

(yes i think always fixable) by rewritting the queries to remove columns not needed in the SELECT / GROUP BY (i mean columns not needed during deciding the rows to get), and also eliminate Joins needed only for final data retrieval

• thus making the query to steps 1. decide rows, 2. and only then get data for the specific rows

i mention this here:
#19284 (comment)

The problem i see with this change is the need to update many queries in many extensions !

• and this is not trivial work at all, having risk to break them on first tries

[EDIT]
forgot to mention
that of course also queries of Joomla CORE itself will need to be rewritten to fix the performance degrade

To make it clear to everybody
you have no performance degrade if you make your queries be like
this:

SELECT i.id, ordering_column (and nothing more !)
... + REMOVE data-related JOINS (this will be done later)
... + KEEP only filtering-related, ordering-related JOINS
GROUP BY i.id, ordering_column

Instead you will get a performance boost, compared to old queries regardless of strict mode

@ggppdk Okay, but the main point was how many extensions will Joomla 4 support. I don't really think all the current extensions for Joomla 3 will ever support Joomla 4 too. Beside that, there could still be the possibility of developing extensions that don't support strict mode, if this was not forced directly onto the Mysqli Driver.
Also, selecting "nothing more" does not really come handy all the times, unless you build other queries later (slowing down mathematically the execution). The clause GROUP BY is just faster than the DISTINCT optimization or the HAVING condition. So, in some queries, we do have a big performance degrade even though I think this would be the last of the problems.
I do agree that building compliant queries with the strict mode is good practice, but I do not think that Joomla 4 should force it in the Mysqli Driver. In the end, allowing or denying the ONLY_FULL_GROUP_BY mode has always been made at server level, so in the my.cnf for example, not in the PHP Application/Framework. As andrepereiradasilva suggested, the sqlModes Array parameters could just be passed to the DB Drivers that really need it.

I don't really think all the current extensions for Joomla 3 will ever support Joomla 4 too

Why? We're adding additional query restrictions - but all those queries should run in Joomla 3 without any issues no?

The clause GROUP BY is just faster than the DISTINCT optimization or the HAVING condition

That's just not true - it is not faster all the time - it might be in some cases - but definitely not all cases

and nothing more

this will be done later

Moving a part of sql query to another later query (including the need to add it later to loops) does not really help and makes the situation even worse.

I don't really think all the current extensions for Joomla 3 will ever support Joomla 4 too.

Unfortunately, in past happened something similar. Breaking backward compatibility caused stop working many Joomla! extensions including some popular extensions which caused that e.g. web designers moved from Joomla! to another CMS. From charts describing CMS using/popularity/trends you can see the relatioship between breaking the backward compatibility and using/popularity/trends of the CMS.

So if possible (and sometimes it is really not possible), there must be the plan to support and run as much as many current extensions as possible in Joomla! 4.

Why? We're adding additional query restrictions - but all those queries should run in Joomla 3 without any issues no?

I am a part of web designers community even I by myself am not a web designer, the scenario is following:

1. Web designer uses Joomla! for his/her projects including specific extensions
2. Something changed in Joomla! which breaks the backward compatibility
3. Developer of the current extension lost the motivation to overwrite the extension again and again
4. So there is no support of this extension for latest Joomla!
5. Web designer needs this extension and he/she looks for another extension or another CMS

This happened in past to Joomla! (and of course this mostly happened to all software)

I am not the one who says: never break backward compatibility. There must be some progress to go forward and this is not possible without breaking backward compatibility. But if there can be an option to save some more extensions with adding a parameter, maybe this should be deeply discussed. Because Joomla! needs as many current extensions es possible.

Comparing DISTINCT and GROUP BY is really hard but even if we ignore the speed, GROUP BY give us more features - for example how to get result with one query instead of more queries (which sometimes must be set in loops)

@wilsonge

Why? We're adding additional query restrictions - but all those queries should run in Joomla 3 without any issues no?

Of course the adjusted queries will run on J3 without any issues, but I was referring to all the third party extensions that exist today for J3. I was only saying that J4 will have a lot less third party extensions compatible because I'm sure a lot of developers don't even know what's the strict mode, or even worse, that it will be forced by default in J4.
I believe a big number of extensions available for J3 today are not compliant with the strict mode or raw sql. I would dare to say that no extensions are actually programmed to be compliant with raw sql or strict mode (not yet). Therefore, who is going to suffer this change the most, is third party extensions, and so Joomla 4 too, as a consequence of all this.

i agree on most of what was said by @alessio-gaggii and @PhocaCz

It is hard requirement for most extensions

• either spend small / medium amount of time and update for a full-group-by but suffer in performance
• or spend big time trying to re-write queries to support full-group-by with proper performance

This is for from being as easy as replacing a few method calls with others

And the more hard it becomes, it can cause abandoned extensions, because of developers focusing efforts and time only on other CMS,
Also i agree that several websites may choose not update because of non supported extensions and eventually move to other CMS

Does anyone has to get text fields in SELECT clause then have to add these fields to GROUP BY clause due to ONLY_FULL_GROUP_BY requirement and see performance issue?

In my tests, I run two simple SQL queries via PHPMyadmin and see big performance difference;

Without FULL GROUP BY

SELECT a.id, a.title, a.short_description, a.description, COUNT(b.id) AS number_registrants
FROM jos_eb_events AS a INNER JOIN jos_eb_registrants AS b ON a.id = b.event_id
GROUP BY a.id

Showing rows 0 - 24 (43 total, Query took 0.0973 seconds.)

With FULL GROUP BY

SELECT a.id, a.title, a.short_description, a.description, COUNT(b.id) AS number_registrants
FROM jos_eb_events AS a INNER JOIN jos_eb_registrants AS b ON a.id = b.event_id
GROUP BY a.id, a.title, a.short_description, a.description

Showing rows 0 - 24 (43 total, Query took 0.1887 seconds.)

So in my test, FULL GROUP BY is much slower than without FULL GROUP BY. Does anyone knows a way to handle this kind of query better?

Every query like

SELECT a.id, a.title, a.short_description, a.description, COUNT(b.id) AS number_registrants
FROM jos_eb_events AS a INNER JOIN jos_eb_registrants AS b ON a.id = b.event_id
GROUP BY a.id, a.title, a.short_description, a.description

should be rewritten for something like that

SELECT x.id, a2.title, a2.short_description, a2.description, x.number_registrants 
FROM (
    SELECT a.id, COUNT(b.id) AS number_registrants
    FROM jos_eb_events AS a 
    INNER JOIN jos_eb_registrants AS b ON a.id = b.event_id
    GROUP BY a.id) x 
LEFT JOIN jos_eb_events a2 ON a2.id = x.id

joomdonation

I get similar results like you. Full Group By is too slow and the query becomes larger. Even updating/refactoring/maintaining of such query is a pain.

csthomas
From my tests sub queries are really great regarding performance. But the query becomes more complicated than using the full group by :-(

So if you want to get data from database without the use of unnecessary queries and php, sub queries or Full Group By limit you.

Full Group By makes this query hard to maintain, but using sub queries in such query can make the query much more complicated.

I would like to jump back quickly into this conversation to ask whether this PR has been Merged for good, and if J4 will force the Strict Mode for any DB Driver.

Some months ago there were also some Contributors against all this, beside some developers. When/how will we know if Strict Mode will be adopted or dismissed in J4? I believe this is the biggest change of J4 for third party extensions, and I don't understand if anything has been decided yet or not. Thanks to all.

@csthomas

Look like the modified query is incorrect? I got error while trying to run it via PHPMyadmin to compare performance. I tried to modify the query like to

SELECT a2.id, a2.title, a2.short_description, a2.description, x.number_registrants 
FROM (
    SELECT a.id, COUNT(b.id) AS number_registrants
    FROM jos_eb_events AS a 
    INNER JOIN jos_eb_registrants AS b ON a.id = b.event_id
    GROUP BY a.id) AS x 
LEFT JOIN jos_eb_events a2 ON a2.id = x.id

It runs now. However, it still took 0.1587 seconds for the query to run, so it's still much slower than query without FULL GROUP BY.

@joomdonation I updated my above comment. Now should be OK.

You can try to replace INNER JOIN by LEFT JOIN and check again.
If you use LIMIT, OFFSET then more complications will occur. The LIMIT and OFFSET has to be in a subquery.

Now I have to ask, how many folks are actually supporting non-MySQL database engines? If it's consistent with our stats, I'm going to assume that of everyone commenting on this thread I may be the only one who has updated a released extension for multi-database support. The reason I say that is because if you had updated your code to support at least PostgreSQL, odds are you would have already re-written your queries to deal with these GROUP BY issues because it is enforced (and AFAIK not configurable) on that platform since PostgreSQL more strictly adheres to SQL standards, whereas MySQL gives you a lot of leeway to do things that wouldn't necessarily be allowed by standards (and on other platforms).

@csthomas Tried new query, it's still much slower compare to the one without FULL GROUP BY

@mbabker Honestly, none of my extensions support or tested with non-MySQL database engines. And since I started developing extensions, if I remember correctly, only several customers asked for SQL server support (haven't heard anyone request for PostgreSQL)

On a long therm it would be good to have an architecture where we can support multiple database engines.

I don't necessarily make it want to sound like a "I've done this so I know better than you" type thing. But for anyone who has done work either on PostgreSQL or SQL Server specific things or multi-database support, you realize quickly that there are things you can do with MySQL that other platforms won't like, such as the GROUP BY stuff. Yes, having this enabled by default in our database API (which is consistent with some other PHP packages, or the MySQL 5.7 default configuration) does create some pain points for existing extensions and existing queries. I don't think the fix is to turn off that configuration value though and take advantage of MySQL's leniency as it relates to query structure.

On a long therm it would be good to have an architecture where we can support multiple database engines.

We do. The problem is writing cross-platform queries in an optimal and performant manner.

@mbabker in over 8 years our company has never had one single user that required PostgreSQL for his hosting. Thousands of cases. None of our extensions fully supports FULL GROUP BY. That's why I asked how/when we will know whether Strict Mode will be forced for any DB Driver.

@laoneo IMO, supporting multiple database engines means not to force MySQL to use Strict Mode when it does not require it.

I think most 3rd parties don't support non-MySQL. Even core had (and probably still has) issues to solve on PostgreSQL.
BUT that is actually not an argument. Because having no customers that asked for it may not mean there are none. The thing is that since almost no 3rd party extensions run on PostgreSQL, it's not a viable database. Even if Joomla would support it, it's still unusable due to the lacking support done by 3rd parties.
So it's the question about the chicken and the egg, which was first ?

Enforcing strict queries will automatically make your queries run on all database engines, And then there may even be customers running your extension on SQL Server and PostgreSQL just fine. ?

From a marketing perspective we need multidatabase support (time for doctrine now?). @alessio-gaggii I'm not so the DB guy, so can't respond here about the "Strict Mode" issue.

1. we should support the default settings
2. you dont need to use an extension to build a joomla web site. i have many sites that either use no 3pd extension or have completely custom extensions

From a marketing perspective we need multidatabase support (time for doctrine now?).

We have multi-database support. Changing to Doctrine does not change how you write a query.

What Doctrine does offer that we don't:

• A proper PHP API for change deltas (i.e. migrations)
• A proper PHP API for managing table schemas

What using Doctrine won't change:

• Errors in queries because a MySQL server requires FULL GROUP BY

My main concern here is performance issue with FULL GROUP BY. In my simple test, looks like it is much slower than without FULL GROUP BY requirement. Anyone else sees this performance issue?

And I'm afraid of even our core code doesn't handle it properly. Look at this line for example, https://github.com/joomla/joomla-cms/blob/4.0-dev/administrator/components/com_content/Model/ArticlesModel.php#L191, we allow passing custom list of fields via list.select model state, if someone pass a field which is not included in $associationsGroupBy array, I guess error will happen.

I'm not denying there is a performance issue with it. So how is this performance issue dealt with in a way that is acceptable for all of Joomla core's supported database platforms (in 4.0 that's just MySQL and PostgreSQL)?

If the end result is wrapping every database query in a switch statement to build different queries based on engine, IMO that's the wrong move to make and defeats the point of maintaining a cross-database compatible platform (there are some cases in core where we do have this type of behavior, but that was in part because there was a need for very specialized behavior based on the platform, i.e. the Smart Search indexer; FULL GROUP BY (which forces MySQL to behave more closely to PostgreSQL) isn't one of those cases).

Since we have performance issue (and the query is also more difficult to write), I think Joomla can still support multiple database engines like how it is but should not force third party extensions developers to use FULL GROUP BY mode when we only support Mysql:

• Less broken extensions.
• Less work for migrating code to support Joomla 4.
• Run faster.

For me, I have a single code base to support both Joomla 3 and Joomla 4 and don't support none-Mysql database engines, I just use the code below as suggested by someone on this thread to have it works with Joomla 4 for now

if (version_compare(JVERSION, '4.0.0-dev', 'ge'))
{
	$db = JFactory::getDbo();
	$db->setQuery("SET sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));");
	$db->execute();
}

Why don't we just drop PostgreSQL support then since it seems the community seems to favor not following SQL standards as is the case with the MySQL ecosystem then? That solves all problems, nobody has to worry about fielding support requests for SQL standard compliant platforms because Joomla explicitly won't support them. :eyeroll:

@joomdonation I looked closer to your query and this should be correct.

SELECT a.id, a.title, a.short_description, a.description, b.number_registrants 
FROM jos_eb_events AS a INNER JOIN (
    SELECT event_id, COUNT(id) AS number_registrants
    FROM jos_eb_registrants
    GROUP BY event_id) x ON b.event_id = a.id

soon or later even Mysql will come out only with "more strict standard" (ie. SQL standard) by default
so soon or later you should write only SQL standard statements,
do you still write PHP 5 code ?? or are you writing PHP 7 code ?

@alikon PHP 7 code ist faster and more elegant than PHP 5 code

We write code that works on both PHP 5 and PHP 7 because billions of websites will be powered by PHP 5 for at least 2 more years. I don't really think there is any third party extension that uses a spaceship operator or anything that would break PHP 5. Legacy is pretty important, and the use of PHP 7 is welcome and encouraged. Forcing the Strict Mode for MySQL at application level means dropping any kind of Legacy mode with third party extensions. It has never existed, and it can always be turned off from the configuration file of the db.

did you know that joomla 4 will work with php > 6.99 only ?

because billions of websites will be powered by PHP 5 for at least 2 more years

Are those Joomla sites?

Joomla 3 is being supported for that time. So it's not like there won't be
a decent enough timeframe to plan migration efforts, both for code and
clients. Presumably there be work needed for MySQL 8.0 compat (we're still
dealing with 5.7 issues), and PostgreSQL 10.

On Mon, Apr 9, 2018 at 12:45 PM AlessioG notifications@github.com wrote:

We write code that works on both PHP 5 and PHP 7 because billions of
websites will be powered by PHP 5 for at least 2 more years. I don't really
think there is any third party extension that uses a spaceship operator or
anything that would break PHP 5. Legacy is pretty important, and the use of
PHP 7 is welcome and encouraged. Forcing the Strict Mode for MySQL at
application level means dropping any kind of Legacy mode with third party
extensions. It has never existed, and it can always be turned off from the
configuration file of the db.

—
You are receiving this because you were mentioned.
Reply to this email directly, view it on GitHub
#12494 (comment),
or mute the thread
https://github.com/notifications/unsubscribe-auth/AAWfocNyf3YCIkVeAC5U9iBvfqdH0VaNks5tm54bgaJpZM4KcIV8
.

--

• Michael Please pardon any errors, this message was sent from my iPhone.

same as it happens for php 7.2

Of course I know Joomla 4 will work only with PHP 7 and that's a good thing. However, don't think the day J4 will be released every single Joomla 3.x will be updated to the 4, same for the PHP env. As many other developers, we will have to support PHP 5 as well for some time, at least until it's officially supported with security fix. Also, code standard to at least PHP 5.4 has no problems running with PHP 7.

Anyway, the topic is Strict Mode and I just wanted to know if a final decision was already taken for forcing it or not in J4 for MySQL. I believe this is what most developers in this conversation wanted to know after sharing their considerations.

yes a final decision was made - that is why the code was merged

Off topic: PHP5.x or PHP7.x - Of course: PHP 5.x - Once I made a mistake, I wrote one function in PHP7.1 style and released the extension. In minutes, there was a feedback storm on support forum regarding broken extension on PHP5.x servers because of PHP7.x code. Forget the PHP7.2 for at least two or three years.

Back to FULL GROUP BY topic, we need to ask, what is better for Joomla! project:

a) many extensions broken, many developers leaving Joomla! platform, many "customers" lost or
b) Mysql queries will not work on e.g. PostgreSQL and etc. servers. In 11 years of my programming for Joomla! only one user asked for MSSQL support (the solution was easy, user moved to MySQL).

I like the rules, so I have overwritten my queries to fit FULL GROUP BY rule, I even have overwritten some queries completely because of performance issues but what I'm worried about, is Joomla! project and its extensions. This limit will kick out the next batch of extensions from Joomla! project. :-(

This discussion appears to have fizzled out. But it is incomplete. There is important information that is not included. We should take some account of SQL standards. In SQL92 it is true that a SELECT .. GROUP BY that selects fields (non-aggregated) that were not in the group by is illegal. But SQL99 changes this. The constraint is relaxed to the extent that the SELECT can include fields that are "functionally dependent" on the grouping. The obvious example of this is where the group by is on the primary key of one table, and other fields are selected from the same table.

MySQL has, until recently, been relaxed about the rule. In version 5.7 the default becomes ONLY_FULL_GROUP_BY - but the SQL99 relaxation is applied. It follows that there are queries using GROUP BY that are legal in MySQL 5.7 that would be illegal in 5.6 or earlier if ONLY_FULL_GROUP_BY is forced. Hence, in the MySQL world it makes little sense for an application to force ONLY_FULL_GROUP_BY.

So far as I can see, all versions of MariaDB behave like MySQL 5.6 in this respect.

The assumption that it is required for compatibility with PostgreSQL is mistaken. Because PostgreSQL has adopted the SQL99 standard (at least in this respect) it is in the same situation as MySQL 5.7.

It seems likely that the vast majority of queries that can fall foul of ONLY_FULL_GROUP_BY will, in fact, work correctly if ONLY_FULL_GROUP_BY is implemented in the SQL99, MySQL 5.7 or PostgreSQL fashion. But many people will be running an earlier MySQL or MariaDB.

It isn't at all clear to me that enforcing ONLY_FULL_GROUP_BY is justified or significantly advantageous.

One more point - DISTINCT is considered to be a special case of GROUP BY and can be vulnerable to the effects of ONLY_FULL_GROUP_BY.

If some one would like to know there is a task at https://jira.mariadb.org/browse/MDEV-11588

How can this question be re-opened? The justification for the decision does not hold water.

The PR was merged, the decision was made already. If you really feel like it is such an issue, honestly open a PR dropping support for PostgreSQL in the CMS environment then the Joomla CMS application doesn't need to worry about FULL_GROUP_BY because the only platform it will support will be MySQL which as has been stated about a thousand times doesn't give a damn about SQL standards.

As long as core supports running on multiple database platforms, all core queries must follow a common standard. If that means enforcing full group by so we can do better to guarantee we don't break queries on PostgreSQL, then so be it. Otherwise, pretending that MySQL is the only platform that matters for query structure and performance is quite frankly a joke (then again so is Joomla's claim of multi-database support but ¯\_(ツ)_/¯).

I appreciate your concern for PostgreSQL support. It is a misunderstanding to suggest that I am attacking it or suggesting anything that would undermine it. On the contrary. All applications should follow the SQL99 standard in this respect. In order to do this effectively, one has to rely on developments in MySQL and MariaDB, which it is generally agreed have been lax.

As I said above, many queries will fall foul of the imposition of ONLY_FULL_GROUP_BY that are totally legal in both PostgreSQL and every version of MySQL, and moreover comply with SQL99.

It is entirely reasonable to use queries that follow SQL99 and SELECT a bunch of fields from a table with a GROUP BY on the primary key alone. There is nothing ambiguous or in any way suspect about the result. The implementation of ONLY_FULL_GROUP_BY in PostgreSQL accepts such statements. The same is true of MySQL 5.7 where the default is now ONLY_FULL_GROUP_BY.

Problems arise where, for example, MariaDB 10.1 is in use. In that case that whole bunch of perfectly sensible queries will be invalidated by Joomla insisting on imposing ONLY_FULL_GROUP_BY. (And for my own servers, the seemingly reasonable and conservative policy is to run the latest release of Debian, for which the preferred database is MariaDB 10.1).

In fact, the move is actually likely to do harm to compatibility. The simplest solution for an extension writer is not to rewrite dozens of perfectly good SQL statements into a worse form. It is to make a database call before doing anything else to run the query "SET sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));");".

It's not worth the trouble of figuring out which database and version is in use before doing this, and so the checking that might actually be useful in PostgreSQL or MySQL 5.7 will be defeated. This runs counter to your reasonable aims.

I can't be bothered to keep having this fight so joomla-framework/database@25508b8

It's done, will be merged to CMS on next composer update. Heaven forbid Joomla tries to follow a standard.

dont be brow beaten into breaking standards

mariadb is not an officially supported database!!

The Joomla ecosystem only cares about MySQL support so why bother enforcing compatibility with a platform that apparently I am the only person who cares enough to support (and the 3 or 4 SQL Server users who come around at every mention of deprecating its support). #frustrated

even not only cares about old&bad MYSQL

mariadb is not an officially supported database!!

Just to clarify here. At the PHP engine level you cannot reliably determine if you are connecting to one of the MySQL drop-in replacements/forks (MariaDB, Percona, etc.) as it all uses the same PHP extensions and MySQL client libraries in the compiled C code. So at the PHP level we can't implement a Joomla\Database\Mariadb\MariadbDriver class. At least in the case of MariaDB, you can parse the MySQL version number and change driver internal behaviors based on that version data (see here as an example) so if we ever needed to introduce a MariaDB specific code path it is theoretically possible), but that still doesn't change the fact that as far as the PHP classes and the Joomla UI for the database connector form field go, we can't explicitly list MariaDB as an option and therefore it's best to not try to say we support any of the drop-in replacements/forks.

as you know, i am fully aware of that. thats why I am suggesting that if mariadb is doing something that is not 100% compatible with mysql then we cannot support it.

Well, you CAN, it's just bloody difficult, only possible after creating the connection (which is the big key here, if there are connection related difference then all bets are off but after a connection is made then you can make the effort), and last I knew only MariaDB really made that possible since it uses a custom version string (last time I worked with Percona it was using unaltered MySQL version strings so you couldn't programmatically make any kind of determination).

What is all this about fights and brow beating and breaking standards? Please DO support the SQL99 standard! I have nowhere advocated not following standards.

Are you REALLY saying that the ONLY databases Joomla 4 will support are MySQL 5.7.5 and above or PostgreSQL? Are you even listening to what is being said? Why not address the points actually being made? Isn't a constructive discussion possible?

(For the system backed by "Open Source Matters" to prefer MySQL over MariaDB is somewhat bizarre!)

@counterpoint mySQL remains open source AFAIK: https://github.com/mysql/mysql-server so not so sure why OSM and joomla should lean towards MariaDB...

@dgrammatiko The answer is simple. MySQL is owned by Oracle, and has been following policies that close down many aspects of the "openness" of the product. There is nothing to stop Oracle from turning MySQL into closed source. MariaDB is owned by a foundation whose constitution commits it to open source.

Write a C level library that can be integrated into a PHP extension and we can consider an "official" MariaDB driver. Until that happens, there is no practical way for a PHP application to EXPLICITLY offer support for MariaDB as a standalone option. Implicit support for MariaDB capabilities (same for any other MySQL drop-in replacement/fork) can be made on a best effort basis within the confines of the MySQL C language libraries and PHP extensions if a way to detect one of those engines is in use is made available after a connection is made (with MariaDB this is done through the version string, though if MySQL ever has a set of 10.x release that's going to cause problems).

Are you REALLY saying that the ONLY databases Joomla 4 will support are MySQL 5.7.5 and above or PostgreSQL?

Well, if it would let us use the native JSON capabilities of the SQL engines... :snicker:

That's a red herring, there is no need for a MariaDB driver. Please address the actual issues.

I answered your statement about why Joomla "prefers" MySQL over MariaDB (which is not a preference to be perfectly clear, it is an imposed requirement as long as there do not exist dedicated libraries for any of the MySQL drop-in replacements/forks), and I took the forced group by out, what more are you looking for here? If you're saying we should stop supporting MySQL because Oracle, that also means dropping MariaDB support (by that account we should've left GitHub after the Microsoft acquisition, right?).

... mySQL remains open source AFAIK: https://github.com/mysql/mysql-server so not so sure why OSM and joomla should lean towards MariaDB...

right, no need to lean towards it , just support it, unless some serious reason no to do so

That's a side issue of little importance. The primary concern is that it is unreasonable for Joomla to impose ONLY_FULL_GROUP_BY. Especially if you're concerned about standards. It is a database configuration option, not a standard. In different databases, it imposes different standards. And in the case where the standard imposed is SQL92, it is a bad standard, and recognised as such.

We don't explicitly support MariaDB for technical reasons already explained more times than necessary. We don't explicitly not support MariaDB because as long as it functions using the same MySQL oriented code already available in the PHP ecosystem it will just work, and the same goes for any MySQL drop-in replacement/fork. There is no preference being made here, it is a statement of fact and practicality.

Look, ONLY_FULL_GROUP_BY is gone. Seriously, can we just be done with this crap already?

I'm really not interested in pursuing the MariaDB issue, it's an irrelevance.

Why will you not address reasoned objections to the enforcement of ONLY_FULL_GROUP_BY? I've given rational objections to its imposition, none of which seem to have been taken into account in making the decision.

Why do you not stand by the principle of uniform standards, which is broken by its enforcement?

And please try to be polite and avoid talk of "crap".

Did you review the commit and comments from #12494 (comment) because that very clearly removes the ONLY_FULL_GROUP_BY default mode parameter and makes it clear that it will be merged to the CMS on the next composer update of the relevant package?

I think anything has been said now. The point about ONLY_FULL_GROUP_BY is adressed in the framework package and will be included in the next composer update. On any new questions please open a new issue and if required link back to this one. I'm going to lock this issues here for now. Thanks!

Documented here https://docs.joomla.org/Potential_backward_compatibility_issues_in_Joomla_4#PHP_MySQL_Extension