Mailing List Archive

On 3/12/22 06:20, Roland Ernst wrote:
> It looks like one of the latest commits for MythWeb causes errors
> raised by MySQl server:
> See https://github.com/MythTV/mythweb/commit/d2aa1925 and the forum entry
> https://forum.mythtv.org/viewtopic.php?f=36&t=4797
> <https://forum.mythtv.org/viewtopic.php?f=36&t=4797>
>
> Neither
> |SELECT UNIQUE(people.name <http://people.name>) |
> |nor |SELECT DISTINCT people.name <http://people.name> ... |ORDER BY
> credits.priority; |||
> |||works with MysQL 8.0 and probably earlier versions.|||
> |||Since I do not have the 'people' and 'credits' table populated, it
> is hard for me to test. |||
> |||Roland |||
>
> _______________________________________________

SELECT DISTINCT people.name FROM credits, people WHERE credits.person =
people.person AND credits.role = 'actor' ORDER BY credits.priority;

Error Code: 3065. Expression #1 of ORDER BY clause is not in SELECT
list, references column 'mythdblmst.credits.priority' which is not in
SELECT list; this is incompatible with DISTINCT

This is illogical since one person may have multiple priorities in the
credits table, so the sort may not make sense.

You can solve it with running this in MySQL on that connection before
the query:

set sql_mode = '';

That relaxes various restrictions and lets the DISTINCT work in this
case. It selects thousands of rows so it does not seem like a good idea.

IIRC I added set sql_mode = ''; in Mythweb somewhere a couple of years
ago. I don't recall if that applied to all queries or just a particular
one that was failing.

UNIQUE still does not work with set sql_mode = '';.

Peter

On Sat Mar 12, 2022 at 12:20:03PM +0100, Roland Ernst wrote:

> It looks like one of the latest commits for MythWeb causes errors raised by
> MySQl server:
> See https://github.com/MythTV/mythweb/commit/d2aa1925 and the forum entry
> https://forum.mythtv.org/viewtopic.php?f=36&t=4797
>
> Neither
>
> SELECT UNIQUE(people.name)
>
> nor
> SELECT DISTINCT people.name ... ORDER BY credits.priority;
>
> works with MysQL 8.0 and probably earlier versions.
>
> Since I do not have the 'people' and 'credits' table populated, it is
> hard for me to test.
>
> Roland

The query doesn't make a lot of sense as is - you can have the same
person appearing with multiple credits and priorities, so the
distinct/unique and order by conflict, which is presumably what's
causing the error. I'd suggest something like below instead:

SELECT a.name from (
SELECT people.name name, max(credits.priority) prio
FROM credits, people
WHERE credits.person = people.person
AND credits.role = 'actor'
GROUP BY people.name
) a ORDER BY a.prio;

That will return a unique set of names in order of their maximum
priority (if there's case issues, you may want to group by
upper(people.name) instead).

Cheers,
Robin
--
___
( ' } | Robin Hill <myth@robinhill.me.uk> |
/ / ) | Little Jim says .... |
// !! | "He fallen in de water !!" |
_______________________________________________
mythtv-dev mailing list
mythtv-dev@mythtv.org
http://lists.mythtv.org/mailman/listinfo/mythtv-dev
http://wiki.mythtv.org/Mailing_List_etiquette
MythTV Forums: https://forum.mythtv.org

On Sat, Mar 12, 2022 at 3:21 PM Peter Bennett <pb.mythtv@gmail.com> wrote:

>
> On 3/12/22 06:20, Roland Ernst wrote:
>
> It looks like one of the latest commits for MythWeb causes errors raised
> by MySQl server:
> See https://github.com/MythTV/mythweb/commit/d2aa1925 and the forum entry
> https://forum.mythtv.org/viewtopic.php?f=36&t=4797
>
> Neither
>
> SELECT UNIQUE(people.name)
>
> norSELECT DISTINCT people.name ... ORDER BY credits.priority;
>
>
> works with MysQL 8.0 and probably earlier versions.
>
> Since I do not have the 'people' and 'credits' table populated, it is hard for me to test.
>
> Roland
>
>
> _______________________________________________
>
> SELECT DISTINCT people.name FROM credits, people WHERE credits.person =
> people.person AND credits.role = 'actor' ORDER BY credits.priority;
> Error Code: 3065. Expression #1 of ORDER BY clause is not in SELECT list,
> references column 'mythdblmst.credits.priority' which is not in SELECT
> list; this is incompatible with DISTINCT
>
> This is illogical since one person may have multiple priorities in the
> credits table, so the sort may not make sense.
>
> You can solve it with running this in MySQL on that connection before the
> query:
>
> set sql_mode = '';
>
> That relaxes various restrictions and lets the DISTINCT work in this case.
> It selects thousands of rows so it does not seem like a good idea.
>
> IIRC I added set sql_mode = ''; in Mythweb somewhere a couple of years
> ago. I don't recall if that applied to all queries or just a particular one
> that was failing.
>
> UNIQUE still does not work with set sql_mode = '';.
>
> Peter
>


Peter,
thank you, that was exactly the information I was missing.
The 'sql_mode' is set per session in MythWeb. That's good.
So, "select DISTINCT ... ORDER BY" should work without Error Code: 3065,
see https://github.com/MythTV/mythweb/commit/d2aa1925 .
And the provided sql query was only for checking correctness of the syntax
for MySQL/MariaDB server, I do not intend to change MythWeb in that way.

Roland

On Sat, Mar 12, 2022 at 11:20 AM Roland Ernst <rcrernst@gmail.com> wrote:
>
> It looks like one of the latest commits for MythWeb causes errors raised by MySQl server:
> See https://github.com/MythTV/mythweb/commit/d2aa1925 and the forum entry
> https://forum.mythtv.org/viewtopic.php?f=36&t=4797
>
> Neither
>
> SELECT UNIQUE(people.name)
>
> nor
> SELECT DISTINCT people.name ... ORDER BY credits.priority;
>
> works with MysQL 8.0 and probably earlier versions.
>
> Since I do not have the 'people' and 'credits' table populated, it is hard for me to test.
>
> Roland

Sorry for not fully testing.

I have no idea why I used UNIQUE() rather
than DISTINCT. It was probably very late
and I was not fully thinking when I first wrote
the quick and dirty patch for my use case.

However, a MySQL change in later versions
of 5.7 (after 5.7.5) to include the
"ONLY_FULL_GROUP_BY" in the modes
(either directly, or indirectly) is the cause of
the ORDER BY error message. I will note
that SQL-1999 Feature T301 allows this to
work as expected. I would guess Oracle
is still planning to join the 21st century at
some point, we just don't know when.

SInce I am not paid millions of dollars (or
for that matter even a single dollar) to
promote (and ship) MySQL, and with
the one well known exception distros
now typically only ship MariaDB, and
every project that I care about has
moved to, or fully supports MariaDB as
the primary choice, I have not reliably
tested on MySQL for probably a decade
or so.

That said, this project continues to state
they believe in Oracle's vision first, and
other implementations are considered
to only be tolerated. So a fix is needed.

I'll create a PR that supports both database
servers in another few days after I have
had some time to spin up a system where
I can test with MySQL 5.7 to make sure
there is not some other abnormal case
to be handled.

Thanks for the report.
_______________________________________________
mythtv-dev mailing list
mythtv-dev@mythtv.org
http://lists.mythtv.org/mailman/listinfo/mythtv-dev
http://wiki.mythtv.org/Mailing_List_etiquette
MythTV Forums: https://forum.mythtv.org