MySQL Bugs: #113343: Expression #1 of ORDER BY is not in SELECT / incompatible with DISTINCT (#3065)

Bug #113343	Expression #1 of ORDER BY is not in SELECT / incompatible with DISTINCT (#3065)
Submitted:	5 Dec 2023 18:12	Modified:	15 Dec 2023 14:13
Reporter:	Emmanuel Renauld	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	8.0.32	OS:	Windows (Windows server 2019 standard)
Assigned to:		CPU Architecture:	Any
Tags:	incompatible with DISTINCT (#3065)

Description:
Our Access 2016 VBA application was working fine with MySQL server 5.7.16 and ODBC 5.3.6 Unicode Driver (64-bit) on Windows server 2019 standard.
We migrated to MySQL server 8.0.32 and ODBC 8.2.0 Unicode Driver (64-bit).
We now have quite often and randomly the following error:
Expression #1 of ORDER BY clause is not in SELECT list, references column 'xxxx' which is not in SELECT list; this is incompatible with DISTINCT (#3065)

We have checked all our queries. We couldn't find any query that would do an "ORDER BY Field" where "Field" is not listed in "SELECT DISTINCTROW Field".

The error can't be reproduced when redoing exactly the same thing.
e.g. when navigating through our records through a form that relies on the following 6 queries, we randomly get the error. When we get the error, we move back to the previous record, then again on the following one which gave the error but we don't get the error again.

SELECT DISTINCTROW [T/Video tapes].*
FROM [T/Video tapes]
ORDER BY [T/Video tapes].Location, [T/Video tapes].Status, [T/Video tapes].Format, [T/Video tapes].Standard, [T/Video tapes].Created, [T/Video tapes].[Video tape ID];

SELECT DISTINCTROW [T/Video tapes].Location, [T/Video tapes].Status, [ST/Video tape parents-children].[Child video tape ID], [ST/Video tape parents-children].[Parent video tape ID], [T/Video tapes].Format, [T/Video tapes].Standard, [T/Video tapes].[Date copied], [T/Video tapes].Quantity, [T/Video tapes].Colour, [T/Video tapes].[Channel 1 code], [T/Video tapes].[Channel 2 code], [T/Video tapes].[Channel 3 code], [T/Video tapes].[Channel 4 code]
FROM [T/Video tapes] INNER JOIN [ST/Video tape parents-children] ON [T/Video tapes].[Video tape ID] = [ST/Video tape parents-children].[Child video tape ID]
ORDER BY [T/Video tapes].Location, [T/Video tapes].Status, [ST/Video tape parents-children].[Child video tape ID];

SELECT DISTINCTROW [ST/Videos comments on recording].[Video ID], [ST/Videos comments on recording].Timing, [ST/Videos comments on recording].Sort, [ST/Videos comments on recording].Comment
FROM [ST/Videos comments on recording]
ORDER BY [ST/Videos comments on recording].[Video ID], [ST/Videos comments on recording].Timing, [ST/Videos comments on recording].Comment;

SELECT DISTINCTROW [ST/Events-Video tapes].[Video tape ID], [ST/Events-Video tapes].Segmentation, [ST/Events-Video tapes].[Event ID], [T/Events].[Dated ID], IIf([Place] Is Not Null And [Description 1] Is Not Null,[Place] & " - " & [Description 1] & IIf([Description 2] Is Not Null," - " & [Description 2]) & IIf([Number] Is Not Null," - " & [Number]) & "    [" & [Place and program] & "]","[" & [Place and program] & "]") AS [Place and description], [T/Events].[Title 1], [T/Events].[Duration mn]
FROM ([RT/Event description 2] RIGHT JOIN ([RT/Event description 1] RIGHT JOIN ([RT/Event place] RIGHT JOIN [T/Events] ON [RT/Event place].Code = [T/Events].[Place code]) ON [RT/Event description 1].Code = [T/Events].[Description 1 code]) ON [RT/Event description 2].Code = [T/Events].[Description 2 code]) INNER JOIN [ST/Events-Video tapes] ON [T/Events].[Event ID] = [ST/Events-Video tapes].[Event ID]
ORDER BY [ST/Events-Video tapes].Segmentation, [T/Events].[Dated ID];

SELECT DISTINCTROW [T/Video tapes].Location, [T/Video tapes].Status, [ST/Video tape parents-children].[Parent video tape ID], [ST/Video tape parents-children].[Child video tape ID], [T/Video tapes].Format, [T/Video tapes].Standard, [T/Video tapes].[Date copied], [T/Video tapes].Quantity, [T/Video tapes].Colour, [T/Video tapes].[Channel 1 code], [T/Video tapes].[Channel 2 code], [T/Video tapes].[Channel 3 code], [T/Video tapes].[Channel 4 code]
FROM [T/Video tapes] INNER JOIN [ST/Video tape parents-children] ON [T/Video tapes].[Video tape ID] = [ST/Video tape parents-children].[Parent video tape ID]
ORDER BY [T/Video tapes].Location, [T/Video tapes].Status, [ST/Video tape parents-children].[Parent video tape ID];

SELECT DISTINCTROW [ST/Video tapes-Transcripts].[Video tape ID], [ST/Video tapes-Transcripts].Segmentation, [ST/Video tapes-Transcripts].[Transcript ID], [T/Transcripts].Version, [T/Events].[Dated ID], IIf([Place] Is Not Null And [Description 1] Is Not Null,[Place] & " - " & [Description 1] & IIf([Description 2] Is Not Null," - " & [Description 2]) & IIf([Number] Is Not Null," - " & [Number]) & "    [" & [Place and program] & "]","[" & [Place and program] & "]") AS [Place and description], [T/Transcripts].Pages, [T/Transcripts].Notes
FROM ([T/Transcripts] LEFT JOIN (([RT/Event description 2] RIGHT JOIN ([RT/Event description 1] RIGHT JOIN ([RT/Event place] RIGHT JOIN [T/Events] ON [RT/Event place].Code = [T/Events].[Place code]) ON [RT/Event description 1].Code = [T/Events].[Description 1 code]) ON [RT/Event description 2].Code = [T/Events].[Description 2 code]) RIGHT JOIN [ST/Events-Transcripts] ON [T/Events].[Event ID] = [ST/Events-Transcripts].[Event ID]) ON [T/Transcripts].[Transcript ID] = [ST/Events-Transcripts].[Transcript ID]) INNER JOIN [ST/Video tapes-Transcripts] ON [T/Transcripts].[Transcript ID] = [ST/Video tapes-Transcripts].[Transcript ID]
ORDER BY [T/Transcripts].Version, [ST/Video tapes-Transcripts].Segmentation;

How to repeat:
Random occurrence

Hello Emmanuel Renauld,

Thank you for the bug report.
May I kindly request you to provide the test data along with tables and exact steps to reproduce this issue at our end?

Regards,
Ashwini Patil

Error message

Attachment: Error 3146.jpg (image/jpeg, text), 34.57 KiB.

Hello Ashwini Patil,

Thanks for looking into this.

Unfortunately there are no exact steps to reproduce the error. The error message will pop up once or twice per hour of work on our database. The users report that "even trying the exact same action again does not reproduce the error". Also the data is not easy to transfer...

There are two other queries used by the form that I forgot to mention. I will list them for exhausitivity since they may be the queries triggering the error:

SELECT DISTINCTROW [Q/Video generation tracking].[Child 0] AS [Video tape SN], Max(Len(IIf([Parent 1] Is Not Null,"x") & IIf([Parent 2] Is Not Null,"x") & IIf([Parent 3] Is Not Null,"x") & IIf([Parent 4] Is Not Null,"x") & IIf([Parent 5] Is Not Null,"x") & IIf([Parent 6] Is Not Null,"x") & IIf([Parent 7] Is Not Null,"x") & IIf([Parent 8] Is Not Null,"x") & IIf([Parent 9] Is Not Null,"x") & IIf([Parent 10] Is Not Null,"x") & IIf([Parent 11] Is Not Null,"x") & IIf([Parent 12] Is Not Null,"x") & IIf([Parent 13] Is Not Null,"x") & IIf([Parent 14] Is Not Null,"x") & IIf([Parent 15] Is Not Null,"x"))) AS [Generation No]
FROM [Q/Video generation tracking]
GROUP BY [Q/Video generation tracking].[Child 0];

Query [Q/Video generation tracking] is:
SELECT DISTINCTROW [ST/Video tape parents-children].[Child video tape ID] AS [Child 0], [ST/Video tape parents-children].[Parent video tape ID] AS [Parent 1], [ST/Video tape parents-children_1].[Parent video tape ID] AS [Parent 2], [ST/Video tape parents-children_2].[Parent video tape ID] AS [Parent 3], [ST/Video tape parents-children_3].[Parent video tape ID] AS [Parent 4], [ST/Video tape parents-children_4].[Parent video tape ID] AS [Parent 5], [ST/Video tape parents-children_5].[Parent video tape ID] AS [Parent 6], [ST/Video tape parents-children_6].[Parent video tape ID] AS [Parent 7], [ST/Video tape parents-children_7].[Parent video tape ID] AS [Parent 8], [ST/Video tape parents-children_8].[Parent video tape ID] AS [Parent 9], [ST/Video tape parents-children_9].[Parent video tape ID] AS [Parent 10], [ST/Video tape parents-children_10].[Parent video tape ID] AS [Parent 11], [ST/Video tape parents-children_11].[Parent video tape ID] AS [Parent 12], [ST/Video tape parents-children_12].[Parent video tape ID] AS [Parent 13], [ST/Video tape parents-children_13].[Parent video tape ID] AS [Parent 14], [ST/Video tape parents-children_14].[Parent video tape ID] AS [Parent 15]
FROM ((((((((((((([ST/Video tape parents-children] LEFT JOIN [ST/Video tape parents-children] AS [ST/Video tape parents-children_1] ON [ST/Video tape parents-children].[Parent video tape ID] = [ST/Video tape parents-children_1].[Child video tape ID]) LEFT JOIN [ST/Video tape parents-children] AS [ST/Video tape parents-children_2] ON [ST/Video tape parents-children_1].[Parent video tape ID] = [ST/Video tape parents-children_2].[Child video tape ID]) LEFT JOIN [ST/Video tape parents-children] AS [ST/Video tape parents-children_3] ON [ST/Video tape parents-children_2].[Parent video tape ID] = [ST/Video tape parents-children_3].[Child video tape ID]) LEFT JOIN [ST/Video tape parents-children] AS [ST/Video tape parents-children_4] ON [ST/Video tape parents-children_3].[Parent video tape ID] = [ST/Video tape parents-children_4].[Child video tape ID]) LEFT JOIN [ST/Video tape parents-children] AS [ST/Video tape parents-children_5] ON [ST/Video tape parents-children_4].[Parent video tape ID] = [ST/Video tape parents-children_5].[Child video tape ID]) LEFT JOIN [ST/Video tape parents-children] AS [ST/Video tape parents-children_6] ON [ST/Video tape parents-children_5].[Parent video tape ID] = [ST/Video tape parents-children_6].[Child video tape ID]) LEFT JOIN [ST/Video tape parents-children] AS [ST/Video tape parents-children_7] ON [ST/Video tape parents-children_6].[Parent video tape ID] = [ST/Video tape parents-children_7].[Child video tape ID]) LEFT JOIN [ST/Video tape parents-children] AS [ST/Video tape parents-children_8] ON [ST/Video tape parents-children_7].[Parent video tape ID] = [ST/Video tape parents-children_8].[Child video tape ID]) LEFT JOIN [ST/Video tape parents-children] AS [ST/Video tape parents-children_9] ON [ST/Video tape parents-children_8].[Parent video tape ID] = [ST/Video tape parents-children_9].[Child video tape ID]) LEFT JOIN [ST/Video tape parents-children] AS [ST/Video tape parents-children_10] ON [ST/Video tape parents-children_9].[Parent video tape ID] = [ST/Video tape parents-children_10].[Child video tape ID]) LEFT JOIN [ST/Video tape parents-children] AS [ST/Video tape parents-children_11] ON [ST/Video tape parents-children_10].[Parent video tape ID] = [ST/Video tape parents-children_11].[Child video tape ID]) LEFT JOIN [ST/Video tape parents-children] AS [ST/Video tape parents-children_12] ON [ST/Video tape parents-children_11].[Parent video tape ID] = [ST/Video tape parents-children_12].[Child video tape ID]) LEFT JOIN [ST/Video tape parents-children] AS [ST/Video tape parents-children_13] ON [ST/Video tape parents-children_12].[Parent video tape ID] = [ST/Video tape parents-children_13].[Child video tape ID]) LEFT JOIN [ST/Video tape parents-children] AS [ST/Video tape parents-children_14] ON [ST/Video tape parents-children_13].[Parent video tape ID] = [ST/Video tape parents-children_14].[Child video tape ID]
ORDER BY [ST/Video tape parents-children].[Child video tape ID];

A screen copy of the error message is in the file section.

Hello Emmanuel Renauld,

Thank you for the details.
We cannot reproduce the issue without proper steps and test case. If you can provide more information, feel free to add it to this bug.  

Thank you for your interest in MySQL.

Regards,
Ashwini Patil

I removed all the DISTINCTROW keywords from the queries in the database since some of them seemed to trigger the error and actually none seemed necessary to get the same query result.

After this, the error still appeared once and pointed to the following query:

[Q/Videos parents-children coherence B FINAL] is:
SELECT DISTINCT [T/Video tapes].Location AS [Parent location], [T/Video tapes].[No Event] AS [Parent No Event], [ST/Video tape parents-children].[Parent video tape ID], [T/Video tapes_1].Location, [T/Video tapes_1].[No Event] AS [Child No Event], [ST/Video tape parents-children].[Child video tape ID]
FROM [T/Video tapes] INNER JOIN (([ST/Video tape parents-children] LEFT JOIN [Q/Videos parents-children coherence B] ON ([ST/Video tape parents-children].[Child video tape ID] = [Q/Videos parents-children coherence B].[Child video tape ID]) AND ([ST/Video tape parents-children].[Parent video tape ID] = [Q/Videos parents-children coherence B].[Parent video tape ID])) INNER JOIN [T/Video tapes] AS [T/Video tapes_1] ON [ST/Video tape parents-children].[Child video tape ID] = [T/Video tapes_1].[Video tape ID]) ON [T/Video tapes].[Video tape ID] = [ST/Video tape parents-children].[Parent video tape ID]
WHERE ((([T/Video tapes].[No Event])=False) AND (([T/Video tapes_1].[No Event])=False) AND (([Q/Videos parents-children coherence B].[Parent video tape ID]) Is Null) AND (([Q/Videos parents-children coherence B].[Child video tape ID]) Is Null)) OR ((([T/Video tapes].[No Event])=False) AND (([T/Video tapes_1].[No Event])=True) AND (([Q/Videos parents-children coherence B].[Parent video tape ID]) Is Null) AND (([Q/Videos parents-children coherence B].[Child video tape ID]) Is Null)) OR ((([T/Video tapes].[No Event])=True) AND (([T/Video tapes_1].[No Event])=False) AND (([ST/Video tape parents-children].[Child video tape ID])<>62706) AND (([Q/Videos parents-children coherence B].[Parent video tape ID]) Is Null) AND (([Q/Videos parents-children coherence B].[Child video tape ID]) Is Null))
ORDER BY [T/Video tapes_1].Location, [ST/Video tape parents-children].[Child video tape ID];

[Q/Videos parents-children coherence B] is:
SELECT DISTINCT [ST/Video tape parents-children].[Parent video tape ID], [ST/Video tape parents-children].[Child video tape ID]
FROM ([ST/Events-Video tapes] INNER JOIN [ST/Events-Video tapes] AS [ST/Events-Video tapes_1] ON [ST/Events-Video tapes].[Event ID] = [ST/Events-Video tapes_1].[Event ID]) INNER JOIN [ST/Video tape parents-children] ON ([ST/Events-Video tapes].[Video tape ID] = [ST/Video tape parents-children].[Parent video tape ID]) AND ([ST/Events-Video tapes_1].[Video tape ID] = [ST/Video tape parents-children].[Child video tape ID]);

I ran this [Q/Videos parents-children coherence B FINAL] query manually a few times to test it out of the VBA code and luckily after a while, it did trigger the error. I then ran it again immediately and it worked again without error.

Then I wrote a bit of VBA code to run this query in a loop 10000 times but I didn't get the error. So the error needs a context to occur, that I can't reproduce on purpose so far...