Sfrgttk

Should I use a zero-interest credit card for a large one-time purchase?

Do wooden building fires get hotter than 600°C?

An adverb for when you're not exaggerating

Would the Life Transference spell be unbalanced if it ignored resistance and immunity?

What was the first language to use conditional keywords?

Illegal assignment from sObject to Id

What is this clumpy 20-30cm high yellow-flowered plant?

Project Euler #1 in C++

Dating a Former Employee

What does it mean that physics no longer uses mechanical models to describe phenomena?

What initially awakened the Balrog?

Do any jurisdictions seriously consider reclassifying social media websites as publishers?

Why wasn't DOSKEY integrated with COMMAND.COM?

What is "gratricide"?

Why do we need to use the builder design pattern when we can do the same thing with setters?

Effects on objects due to a brief relocation of massive amounts of mass

What is the appropriate index architecture when forced to implement IsDeleted (soft deletes)?

Is a ledger board required if the side of my house is wood?

As a beginner, should I get a Squier Strat with a SSS config or a HSS?

Drawing without replacement: why is the order of draw irrelevant?

If Windows 7 doesn't support WSL, then what does Linux subsystem option mean?

AppleTVs create a chatty alternate WiFi network

Do I really need to have a message in a novel to appeal to readers?

Putting class ranking in CV, but against dept guidelines

Tolerating SQL errors (orderby not in distinct, select not in group by) in MySQL 5.7.13 vs 5.7.11

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ margin-bottom:0;
}

4

This question is related to the following 2 SQL errors (line breaks added by me)

MySql.Data.MySqlClient.MySqlException (0x80004005):

Expression #2 of ORDER BY clause is not in SELECT list, references column 'auitool2014.a.prog' which is not in SELECT list;

this is incompatible with DISTINCT





MySql.Data.MySqlClient.MySqlException (0x80004005): 

Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'H.C51' which is not functionally dependent on columns in GROUP BY clause; 

this is incompatible with sql_mode=only_full_group_by

My problem is simple to describe, even without posting the original SQL. I have a Mysql 5.7.11-log server running on Windows in which I can SELECT A,B GROUP BY A and SELECT DISTINCT A,B ORDER BY C without any problem. Yes, this is wrong from the SQL point of view.

But it works and customers are happy. It will never be fixed be fixed in the future.

We just installed a fresh standard Mysql 5.7.13-log on Linux using Yum. Now all queries containing such errors break.

I have looked around and it seems that Mysql team did some work about improper GROUP BYs. I have tried to disable full group bys but then I am left with the distinct issue.

I have also tried to replicate part of the Mysql configuration from the Windows box but returned again to both issues.

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES 

I just want to know how do I tell the new Mysql 5.7.13 to behave exactly like the (bugged?) version 5.7.11. We will try to install the exact 5.7.11 from Yum but it could take a considerable while. So to be more clear: how do I get rid of those two errors, humbly given that the SQL statements need to be eventually fixed?

mysql group-by syntax mysql-5.7 distinct

share|improve this question

edited May 23 '17 at 12:40

Community♦

1

asked Jul 12 '16 at 15:09

usr-local-ΕΨΗΕΛΩΝusr-local-ΕΨΗΕΛΩΝ

1394

bumped to the homepage by Community♦ 9 mins ago

This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.

• 
  
  
  

  
  4
  

  
  
  
  
  
  

  
  
  Why not fix the queries now? Seems like a good time to do that!
  
  – ypercubeᵀᴹ
  Jul 12 '16 at 15:47
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  Do you really want to know why it is impossible? Those queries must be signed-off by a non-IT person, a lawyer, who simply says "they worked on the previous version". Hahah please don't ask me the full story, it's a coding horror story :-)
  
  – usr-local-ΕΨΗΕΛΩΝ
  Jul 15 '16 at 10:19
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Rolled back to Mysql 5.6. The original application worked on Mysql 5.1 by the way. It is a few years old application
  
  – usr-local-ΕΨΗΕΛΩΝ
  Jul 15 '16 at 10:20
  

  
  
  
  

add a comment | 

4

This question is related to the following 2 SQL errors (line breaks added by me)

MySql.Data.MySqlClient.MySqlException (0x80004005):

Expression #2 of ORDER BY clause is not in SELECT list, references column 'auitool2014.a.prog' which is not in SELECT list;

this is incompatible with DISTINCT





MySql.Data.MySqlClient.MySqlException (0x80004005): 

Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'H.C51' which is not functionally dependent on columns in GROUP BY clause; 

this is incompatible with sql_mode=only_full_group_by

My problem is simple to describe, even without posting the original SQL. I have a Mysql 5.7.11-log server running on Windows in which I can SELECT A,B GROUP BY A and SELECT DISTINCT A,B ORDER BY C without any problem. Yes, this is wrong from the SQL point of view.

But it works and customers are happy. It will never be fixed be fixed in the future.

We just installed a fresh standard Mysql 5.7.13-log on Linux using Yum. Now all queries containing such errors break.

I have looked around and it seems that Mysql team did some work about improper GROUP BYs. I have tried to disable full group bys but then I am left with the distinct issue.

I have also tried to replicate part of the Mysql configuration from the Windows box but returned again to both issues.

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES 

I just want to know how do I tell the new Mysql 5.7.13 to behave exactly like the (bugged?) version 5.7.11. We will try to install the exact 5.7.11 from Yum but it could take a considerable while. So to be more clear: how do I get rid of those two errors, humbly given that the SQL statements need to be eventually fixed?

mysql group-by syntax mysql-5.7 distinct

share|improve this question

edited May 23 '17 at 12:40

Community♦

1

asked Jul 12 '16 at 15:09

usr-local-ΕΨΗΕΛΩΝusr-local-ΕΨΗΕΛΩΝ

1394

bumped to the homepage by Community♦ 9 mins ago

This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.

• 
  
  
  

  
  4
  

  
  
  
  
  
  

  
  
  Why not fix the queries now? Seems like a good time to do that!
  
  – ypercubeᵀᴹ
  Jul 12 '16 at 15:47
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  Do you really want to know why it is impossible? Those queries must be signed-off by a non-IT person, a lawyer, who simply says "they worked on the previous version". Hahah please don't ask me the full story, it's a coding horror story :-)
  
  – usr-local-ΕΨΗΕΛΩΝ
  Jul 15 '16 at 10:19
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Rolled back to Mysql 5.6. The original application worked on Mysql 5.1 by the way. It is a few years old application
  
  – usr-local-ΕΨΗΕΛΩΝ
  Jul 15 '16 at 10:20
  

  
  
  
  

add a comment | 

This question is related to the following 2 SQL errors (line breaks added by me)

MySql.Data.MySqlClient.MySqlException (0x80004005):

Expression #2 of ORDER BY clause is not in SELECT list, references column 'auitool2014.a.prog' which is not in SELECT list;

this is incompatible with DISTINCT





MySql.Data.MySqlClient.MySqlException (0x80004005): 

Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'H.C51' which is not functionally dependent on columns in GROUP BY clause; 

this is incompatible with sql_mode=only_full_group_by

My problem is simple to describe, even without posting the original SQL. I have a Mysql 5.7.11-log server running on Windows in which I can SELECT A,B GROUP BY A and SELECT DISTINCT A,B ORDER BY C without any problem. Yes, this is wrong from the SQL point of view.

But it works and customers are happy. It will never be fixed be fixed in the future.

We just installed a fresh standard Mysql 5.7.13-log on Linux using Yum. Now all queries containing such errors break.

I have looked around and it seems that Mysql team did some work about improper GROUP BYs. I have tried to disable full group bys but then I am left with the distinct issue.

I have also tried to replicate part of the Mysql configuration from the Windows box but returned again to both issues.

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES 

I just want to know how do I tell the new Mysql 5.7.13 to behave exactly like the (bugged?) version 5.7.11. We will try to install the exact 5.7.11 from Yum but it could take a considerable while. So to be more clear: how do I get rid of those two errors, humbly given that the SQL statements need to be eventually fixed?

mysql group-by syntax mysql-5.7 distinct

share|improve this question

edited May 23 '17 at 12:40

Community♦

1

asked Jul 12 '16 at 15:09

usr-local-ΕΨΗΕΛΩΝusr-local-ΕΨΗΕΛΩΝ

1394

MySql.Data.MySqlClient.MySqlException (0x80004005):

Expression #2 of ORDER BY clause is not in SELECT list, references column 'auitool2014.a.prog' which is not in SELECT list;

this is incompatible with DISTINCT





MySql.Data.MySqlClient.MySqlException (0x80004005): 

Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'H.C51' which is not functionally dependent on columns in GROUP BY clause; 

this is incompatible with sql_mode=only_full_group_by

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES 

share|improve this question

edited May 23 '17 at 12:40

Community♦

1

asked Jul 12 '16 at 15:09

usr-local-ΕΨΗΕΛΩΝusr-local-ΕΨΗΕΛΩΝ

1394

share|improve this question

edited May 23 '17 at 12:40

Community♦

1

asked Jul 12 '16 at 15:09

usr-local-ΕΨΗΕΛΩΝusr-local-ΕΨΗΕΛΩΝ

1394

edited May 23 '17 at 12:40

Community♦

1

edited May 23 '17 at 12:40

Community♦

1

asked Jul 12 '16 at 15:09

usr-local-ΕΨΗΕΛΩΝusr-local-ΕΨΗΕΛΩΝ

1394

asked Jul 12 '16 at 15:09

usr-local-ΕΨΗΕΛΩΝusr-local-ΕΨΗΕΛΩΝ

1394

1 Answer
1

active

oldest

votes

0

Not quite what you are saying:

5.7.5 changelog says

"Queries of the form SELECT DISTINCT col1 ... ORDER BY col2 qualify as forbidden by SQL2003 (hidden ORDER BY columns combined with DISTINCT), but were not rejected with the ONLY_FULL_GROUP_BY SQL mode enabled. "

Perhaps you should plea to bugs.mysql.com for a 'mode' to violate SQL2003 on the grounds that there is no way to get what you want.

share|improve this answer

edited Jul 15 '16 at 13:30

answered Jul 15 '16 at 3:00

Rick JamesRick James

43.9k22360

add a comment | 

Your Answer

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});


}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f143619%2ftolerating-sql-errors-orderby-not-in-distinct-select-not-in-group-by-in-mysql%23new-answer', 'question_page');
}
);

Post as a guest

Name

Email

Required, but never shown

0

Not quite what you are saying:

5.7.5 changelog says

"Queries of the form SELECT DISTINCT col1 ... ORDER BY col2 qualify as forbidden by SQL2003 (hidden ORDER BY columns combined with DISTINCT), but were not rejected with the ONLY_FULL_GROUP_BY SQL mode enabled. "

Perhaps you should plea to bugs.mysql.com for a 'mode' to violate SQL2003 on the grounds that there is no way to get what you want.

share|improve this answer

edited Jul 15 '16 at 13:30

answered Jul 15 '16 at 3:00

Rick JamesRick James

43.9k22360

add a comment | 

0

Not quite what you are saying:

5.7.5 changelog says

"Queries of the form SELECT DISTINCT col1 ... ORDER BY col2 qualify as forbidden by SQL2003 (hidden ORDER BY columns combined with DISTINCT), but were not rejected with the ONLY_FULL_GROUP_BY SQL mode enabled. "

Perhaps you should plea to bugs.mysql.com for a 'mode' to violate SQL2003 on the grounds that there is no way to get what you want.

share|improve this answer

edited Jul 15 '16 at 13:30

answered Jul 15 '16 at 3:00

Rick JamesRick James

43.9k22360

add a comment | 

Not quite what you are saying:

5.7.5 changelog says

"Queries of the form SELECT DISTINCT col1 ... ORDER BY col2 qualify as forbidden by SQL2003 (hidden ORDER BY columns combined with DISTINCT), but were not rejected with the ONLY_FULL_GROUP_BY SQL mode enabled. "

Perhaps you should plea to bugs.mysql.com for a 'mode' to violate SQL2003 on the grounds that there is no way to get what you want.

share|improve this answer

edited Jul 15 '16 at 13:30

answered Jul 15 '16 at 3:00

Rick JamesRick James

43.9k22360

share|improve this answer

edited Jul 15 '16 at 13:30

answered Jul 15 '16 at 3:00

Rick JamesRick James

43.9k22360

answered Jul 15 '16 at 3:00

Rick JamesRick James

43.9k22360

answered Jul 15 '16 at 3:00

Rick JamesRick James

43.9k22360