sql - mysql - order by inside subquery -



sql - mysql - order by inside subquery -

i used next query mysql 5.5 (or previous versions) years without problems:

select t2.code (select country.code country order country.code desc ) t2;

the order of result descending needed.

last week, migrated new mysql version (in fact, migrated mariadb 10.0.14) , same query same database not sorted descending anymore. sorted ascending (or sorted using natural order, not sure in fact).

so, can tell me if bug or if alter of behaviour in recent versions of mysql/mariadb?

thank you.

g. plante

after bit of digging, can confirm both scenarios:

mysql 5.1 apply order by within subquery.

mariadb 5.5.39 on linux not apply order by within subquery when no limit supplied. does correctly apply order when corresponding limit given:

select t2.code ( select country.code country order country.code desc limit 2 ) t2;

without limit, there isn't reason apply sort within subquery. can equivalently applied outer query.

documented behavior:

as turns out, mariadb has documented behavior , not regarded bug:

a "table" (and subquery in from clause too) - according sql standard - unordered set of rows. rows in table (or in subquery in from clause) not come in specific order. that's why optimizer can ignore order by clause have specified. in fact, sql standard not allow order by clause appear in subquery (we allow it, because order ... limit ... changes result, set of rows, not order).

you need treat subquery in from clause, set of rows in unspecified , undefined order, , set order by on top-level select.

so mariadb recommends applying order by in outermost query, or limit if necessary.

note: don't have access proper mysql 5.5 or 5.6 confirm if behavior same there (and sqlfiddle.com malfunctioning). comments on original bug report (closed not-a-bug) suggest mysql 5.6 behaves same way mariadb.

mysql sql subquery sql-order-by mariadb

Comments

Post a Comment

Popular posts from this blog

xslt - DocBook 5 to PDF transform failing with error: "fo:flow" is missing child elements. Required content model: marker* -

xslt - DocBook 5 to PDF transform failing with error: "fo:flow" is missing child elements. Required content model: marker* - i've inherited doc publishing process takes docbook , builds html , pdf output, using apache fop both. project had been started, never completed. lots of tweaking, i've able of doc sets build (20+), except handful, , fail severe code 1 of these 2 conditions: "fo:flow" missing kid elements. required content model: marker* "fo:block" not valid kid of "fo:root" the xml output comes out of docbook , docbook-fop checks out well-formed. the fop error in 1 book, "endecagloss", example, points end of string: <fo:flow flow-name="xsl-region-body" start-indent="4pc" end-indent="0pt"/> here’s log output in case: [java] severe: javax.xml.transform.transformerexception: file:/scratch/publishing/hudson/jobs/build-main-endeca-documentation/workspace/serverdoc/w...
Read more

mediawiki - How do I insert tables inside infoboxes on Wikia pages? -

mediawiki - How do I insert tables inside infoboxes on Wikia pages? - i want insert tables in infoboxes. example, have proteinbox i've developed using infobox template (http://health-and-medicine.wikia.com/wiki/template:proteinbox), , fasta field big fit comfortably in proteinbox. hence collapsible table within box. this tried: {{infobox | row 1 = {| class = "mw-collapsible mw-collapsed wikitable" |- | <!-- table content --> |} }} your problem is, putting nested table within template. pipe characters ( | ) in table syntax collides pipe usage in templates. the commonly used hack around create template called template:! , containing pipe character, , utilize when need set tables, parser functions or other stuff using pipe characters, within templates. table (with every | replaced {{!}} ): {{{!}} {{!}}- {{!}} a1 {{!}} b1 {{!}}- {{!}} a2 {{!}} b2 {{!}}} ...the equivalent of {| |- | a1 | b1 |- | a2 | b2 |} furthermore, hav...
Read more

Local Service User Logged into Windows -

Local Service User Logged into Windows - i created windows service tracks app server user connected through load balancer. it retrieves user logged windows on machine, cross references active directory (ad) business relationship homecoming first name, lastly name , e-mail (for notification through website created project) there 1 scheme on network users remote system, vista system, reason returning values "local service" logged machine, it's weird , security guys impossible "local service" business relationship logged windows. here how user logged windows managementscope ms = new managementscope("\\\\.\\root\\cimv2"); objectquery query = new objectquery("select * win32_computersystem"); managementobjectsearcher searcher = new managementobjectsearcher(ms, query); foreach (managementobject mo in searcher.get()) { _username = mo["username"].tostring(); } // remove domain part username string[] usernameparts = _...
Read more