f



1--1

I don't understand why this works as expected:
  select *
  from games
  order by 2
but this doesn't:
  select *
  from games
  order by 1--1
The "order by" is completely ignored.
0
Kiuhnm
1/21/2015 4:44:06 PM
comp.databases.mysql 6944 articles. 1 followers. laredotornado (869) is leader. Post Follow

11 Replies
1024 Views

Similar Articles

[PageSpeed] 14

On Wednesday January 21 2015 11:44, in comp.databases.mysql, "Kiuhnm Mnhuik"
<gandalf23@mail.com> wrote:

> I don't understand why this works as expected:
>   select *
>   from games
>   order by 2
> but this doesn't:
>   select *
>   from games
>   order by 1--1
> The "order by" is completely ignored.

No, it isn't.

In the first example
>   select *
>   from games
>   order by 2
all rows are placed in order of "2". That is to say, each row is treated
(for the purposes of ordering) as if the ordering key were the value
of "2". Since /all/ rows have the same ordering key, there is nothing
special about the resulting ordering; the rows come out in an order that is
effectively random (possibly, the same order that they would come out with
without the "order by" clause).

In the second example
>   select *
>   from games
>   order by 1--1
(which is treated as
>   select *
>   from games
>   order by 1
because a sequence of two dashes ("--") signifies the start of a comment),
all rows are placed in order of "1". That is to say, each row is treated
(for the purposes of ordering) as if the ordering key were the value
of "1". Since, again, /all/ rows have the same ordering key, there is
nothing special about the resulting ordering; the rows come out in an order
that is effectively random (possibly, the same order that they would come
out with without the "order by" clause).

What did you expect would result from each of the two queries?
-- 
Lew Pitcher
"In Skills, We Trust"
PGP public key available upon request

0
Lew
1/21/2015 5:00:01 PM
On Wednesday, January 21, 2015 at 6:00:04 PM UTC+1, Lew Pitcher wrote:
> On Wednesday January 21 2015 11:44, in comp.databases.mysql, "Kiuhnm Mnhuik"
> <gandalf23@mail.com> wrote:
> 
> > I don't understand why this works as expected:
> >   select *
> >   from games
> >   order by 2
> > but this doesn't:
> >   select *
> >   from games
> >   order by 1--1
> > The "order by" is completely ignored.
> 
> No, it isn't.
> 
> In the first example
> >   select *
> >   from games
> >   order by 2
> all rows are placed in order of "2". That is to say, each row is treated
> (for the purposes of ordering) as if the ordering key were the value
> of "2". Since /all/ rows have the same ordering key, there is nothing
> special about the resulting ordering; the rows come out in an order that is
> effectively random (possibly, the same order that they would come out with
> without the "order by" clause).

Not true. order by 2 means order by the second column. You can try it here:
http://sqlzoo.net/wiki/SELECT_.._WHERE

> 
> In the second example
> >   select *
> >   from games
> >   order by 1--1
> (which is treated as
> >   select *
> >   from games
> >   order by 1
> because a sequence of two dashes ("--") signifies the start of a comment),

Also not true. See here:
http://dev.mysql.com/doc/refman/5.1/en/comments.html

Try
  select 1--1
and you'll see.

0
Kiuhnm
1/21/2015 5:11:07 PM
On Wednesday January 21 2015 12:11, in comp.databases.mysql, "Kiuhnm Mnhuik"
<gandalf23@mail.com> wrote:
[snip]
> Not true. order by 2 means order by the second column. You can try it
> here: http://sqlzoo.net/wiki/SELECT_.._WHERE

Well, I be go-to-hell. I did not know that, and I've worked with SQL for
over 20 years.

Thanks for showing me something new.

[snip]


-- 
Lew Pitcher
"In Skills, We Trust"
PGP public key available upon request

0
Lew
1/21/2015 5:30:03 PM
In article <05863e1a-f5f9-49b5-8305-b1cd3ccbffbb@googlegroups.com>,
Kiuhnm Mnhuik  <gandalf23@mail.com> wrote:
> I don't understand why this works as expected:
>   select *
>   from games
>   order by 2
> but this doesn't:
>   select *
>   from games
>   order by 1--1
> The "order by" is completely ignored.

It depends on your value of "expected". It helps to mention that!

The first query orders by the value of the second selected column
(whatever that may be - rather opaque when you use "select *").

The second query evaluates the expression 1--1 to the constant 2,
and then as Lew said, uses that constant as the sort key for all
records, which gives no sorting at all. So it could appear as if
the ORDER BY is ignored, and in fact the optimiser might indeed
choose to ignore it if it is clever enough to notice.

Selection of columns by position can only be done using literal
column numbers, not expressions.

Cheers
Tony
-- 
Tony Mountifield
Work: tony@softins.co.uk - http://www.softins.co.uk
Play: tony@mountifield.org - http://tony.mountifield.org
0
tony
1/21/2015 6:01:08 PM
On Wednesday, January 21, 2015 at 7:02:31 PM UTC+1, Tony Mountifield wrote:
> In article <05863e1a-f5f9-49b5-8305-b1cd3ccbffbb@googlegroups.com>,
> Kiuhnm Mnhuik  <gandalf23@mail.com> wrote:
> > I don't understand why this works as expected:
> >   select *
> >   from games
> >   order by 2
> > but this doesn't:
> >   select *
> >   from games
> >   order by 1--1
> > The "order by" is completely ignored.
> 
> It depends on your value of "expected". It helps to mention that!
> 
> The first query orders by the value of the second selected column
> (whatever that may be - rather opaque when you use "select *").
> 
> The second query evaluates the expression 1--1 to the constant 2,
> and then as Lew said, uses that constant as the sort key for all
> records, which gives no sorting at all. So it could appear as if
> the ORDER BY is ignored, and in fact the optimiser might indeed
> choose to ignore it if it is clever enough to notice.
> 
> Selection of columns by position can only be done using literal
> column numbers, not expressions.

Got it. Thanks.
So one can shuffle rows by using rand():
  select *
  from games
  order by rand()
Cool!
0
Kiuhnm
1/21/2015 7:10:01 PM
On Wed, 21 Jan 2015 11:10:01 -0800, Kiuhnm Mnhuik wrote:

> So one can shuffle rows by using rand():
>   select *
>   from games order by rand()
> Cool!

It would appear so (having just tested), at least on my current version 
of mysql.

-- 
Denis McMahon, denismfmcmahon@gmail.com
0
Denis
1/21/2015 10:48:53 PM
tony@mountifield.org (Tony Mountifield) wrote in news:m9oph4$jdf$1@softins.softins.co.uk:

> 
> The second query evaluates the expression 1--1 to the constant 2,

It does *what*???

0
Doug
1/22/2015 2:16:44 AM
On 1/21/2015 9:16 PM, Doug Miller wrote:
> tony@mountifield.org (Tony Mountifield) wrote in news:m9oph4$jdf$1@softins.softins.co.uk:
> 
>>
>> The second query evaluates the expression 1--1 to the constant 2,
> 
> It does *what*???
> 
(1) - (-1) = 2

The syntax he used confused me for a while, also, Doug.

-- 
==================
Remove the "x" from my email address
Jerry Stuckle
jstucklex@attglobal.net
==================
0
Jerry
1/22/2015 3:50:20 AM
On Thursday, January 22, 2015 at 4:50:23 AM UTC+1, Jerry Stuckle wrote:
> On 1/21/2015 9:16 PM, Doug Miller wrote:
> > tony@mountifield.org (Tony Mountifield) wrote in news:m9oph4$jdf$1@softins.softins.co.uk:
> > 
> >>
> >> The second query evaluates the expression 1--1 to the constant 2,
> > 
> > It does *what*???
> > 
> (1) - (-1) = 2
> 
> The syntax he used confused me for a while, also, Doug.

This is also valid:
  select 1-----------------------------1
:)
0
Kiuhnm
1/23/2015 12:56:14 AM
On 1/22/2015 7:56 PM, Kiuhnm Mnhuik wrote:
> On Thursday, January 22, 2015 at 4:50:23 AM UTC+1, Jerry Stuckle wrote:
>> On 1/21/2015 9:16 PM, Doug Miller wrote:
>>> tony@mountifield.org (Tony Mountifield) wrote in news:m9oph4$jdf$1@softins.softins.co.uk:
>>>
>>>>
>>>> The second query evaluates the expression 1--1 to the constant 2,
>>>
>>> It does *what*???
>>>
>> (1) - (-1) = 2
>>
>> The syntax he used confused me for a while, also, Doug.
> 
> This is also valid:
>   select 1-----------------------------1
> :)
> 

Yes, but anyone who coded something like that on one of my projects
would find himself on the street before the last semicolon.

Your first version would not be allowed as coded, either (although you
wouldn't get fired for it).  I require clarity above all else.  And your
"problem" is a perfect reason why.


-- 
==================
Remove the "x" from my email address
Jerry Stuckle
jstucklex@attglobal.net
==================
0
Jerry
1/23/2015 1:47:12 AM
Kiuhnm Mnhuik <gandalf23@mail.com> wrote in news:b38cf7b5-2264-4a00-97fd-
00ca46657d52@googlegroups.com:

> On Thursday, January 22, 2015 at 4:50:23 AM UTC+1, Jerry Stuckle wrote:
>> On 1/21/2015 9:16 PM, Doug Miller wrote:
>> > tony@mountifield.org (Tony Mountifield) wrote in news:m9oph4$jdf$1
@softins.softins.co.uk:
>> > 
>> >>
>> >> The second query evaluates the expression 1--1 to the constant 2,
>> > 
>> > It does *what*???
>> > 
>> (1) - (-1) = 2
>> 
>> The syntax he used confused me for a while, also, Doug.

Well, I'll be darned. Learn something new every day. I had always believed the MySQL 
comment indicator was a double hyphen.

It's not.

It's hyphen, hyphen, SPACE.

So, yes, 1--1 *is* 1 minus negative 1 = 1 + 1 = 2, not a single 1 followed by a comment.
0
Doug
1/23/2015 3:21:09 AM
Reply:

Similar Artilces:

1 - 1, 1 -1, 1-1, 1
Hi, I have a question regarding lexical analysis. I recently came across a bug in our lexical analyser in phc (www.phpcompiler.org), that I am unsure how to solve. This is the problem: our current definition for integer constant looks something like INT ([1-9][0-9]*)|0 In particular, note that it does not allow for an (optional) "+" or "-" at the start of the integer. This means that the strings "1 - 1", "1 -1" and "1-1" all generate the same sequence of three tokens INT(1), OP(-), INT(1), for which the syntax analyser generates the subtr...

1.1.1.1 ?
hi my firewall logs dropped packets from an internal IP address trying to contact 1.1.1.1 through port 9999. Any ideas whether 1.1.1.1 is valid IP? and what is port 9999?? thanks mike wrote: > hi > > my firewall logs dropped packets from an internal IP address trying to > contact 1.1.1.1 through port 9999. Any ideas whether 1.1.1.1 is valid > IP? and what is port 9999?? > thanks These trojans *BlitzNet*, *Backdoor.Oracle*, *Backdoor.Spadeace* uses port 9999 -- S.S. "StarScripter" <Star@privacy.net> wrote in message news:<bv8ejj$p54t3$1@ID-185702.new...

plot 1, -1, 1, -1, -1
Hi, How to plot those numbers in a rectangle style? thanks x = linspace(-4*pi,4*pi,1000); y = sin(x); plot(x,sign(y)), axis([-12 12 -2 2]) PZ <patrick.zou@gmail.com> wrote in message <d64c545f-f2ac-4f87-af89-051bda896707@x6g2000vbg.googlegroups.com>... > Hi, > > How to plot those numbers in a rectangle style? > > thanks help stairs stairs([-1 1 -1 1 -1 1]) hth Jos ...

MYSQL V4-1-1 and PHP 1.2 and CSWS 1.3
Does someone have documentation on how to properly configure MYSQL and PHP on OpenVMS 7.3-1. My test script fails on the line with mysql_connect(). Mysql is running and works using the parameters in the connect statement at the command line and I can connect and look at the db. I have not been able to locate the doc that explains how all of the configuration files must be configured for proper operations for the 4.1.1 version -- it is quite different from the previous version... Until the STREAM_LF issue is resolved in CSWS 2.0, I cannot install it. Any help is appreciated. Michael...

Alignment of foo[1][1][1][1]
Suppose I have type 'foo' and: sizeof (foo) == 16 alignof (foo) == 2 Suppose I have type 'foo[1][1][1][1]' and: sizeof (foo[1][1][1][1]) == 16 Can: alignof (foo[1][1][1][1]) == 4 ? I'd like to think not, but is it prohibited? If I do: typedef foo bar[1][1][1][1]; bar * my_bar = malloc(sizeof *bar); foo * my_foo = (foo *) my_bar; certainly 'my_bar' points to an object whose alignment satisfies type 'foo'. But what about the other way around? typedef foo bar[1][1][1][1]; foo * my_foo = malloc(sizeof *foo); bar * my_ba...

ERROR IN EXPRESSION; -1 - (-1) not 1
The expression isn't a = 1 - (1-), but: a = -1 - (1) der <der@noemail.com> scribbled the following: > The expression isn't a = 1 - (1-), but: a = -1 - (1) Ah, that's different, then. Then the answer is: The result will be -2. You have *no* guarantees about which of -1 or (1) is evaluated first, but as they don't have side effects, you don't have to care. -- /-- Joona Palaste (palaste@cc.helsinki.fi) ---------------------------\ | Kingpriest of "The Flying Lemon Tree" G++ FR FW+ M- #108 D+ ADA N+++| | http://www.helsinki.fi/~palaste W++ B OP+ ...

Ip-Address 1.1.1.1
A traceroute to 213.176.224.4 showed following results: # traceroute -In -m10 213.176.224.4 traceroute to 213.176.224.4 (213.176.224.4), 10 hops max, 38 byte packets 1 212.152.136.1 103.658 ms 99.452 ms 109.767 ms 2 212.152.151.2 99.646 ms 99.680 ms 99.821 ms 3 62.218.1.93 99.689 ms 109.665 ms 99.751 ms 4 212.152.192.182 99.762 ms 1359.690 ms 99.718 ms 5 193.203.0.72 104.398 ms 105.014 ms 109.781 ms 6 146.188.2.229 109.696 ms 109.733 ms 99.771 ms 7 146.188.14.113 119.710 ms 199.720 ms 209.720 ms 8 146.188.49.194 119.695 ms 129.663 ms 119.808 ...

htmldata 1.1.1
Bug-fix release of htmldata, fixes error when parsing whitespace inside tags. Description of the htmldata module, from its PyPI site: http://cheeseshop.python.org/pypi/htmldata/ "Extract and modify HTML/CSS URLs, translate HTML documents <-> list data structures. The htmldata module allows one to translate HTML documents back and forth to list data structures. This allows for programmatic reading and writing of HTML documents, with much flexibility. Functions are also available for extracting and/or modifying all URLs present in the HTML or stylesheets of a docu...

DeepForm 1.1.1
DeepForm allows an analyst to create a document (named Model) that specifies the structure and the contents of the XML document containing the corporate data. DeepForm does not requires any knowledge of the XML format by the user. The data model creation The application allows the user to compone his own model using a quick and easy interface. The analyst can create his own model by simply combining the "basic" elements of DeepForm: * fields (customizables); * tables; * sections. DeepForm allows to create new models by using parts of other models pre...

RedNotebook 1.1.1
RedNotebook 1.1.1 has been released. You can get the tarball, the Windows installer and links to distribution packages at http://rednotebook.sourceforge.net/downloads.html What is RedNotebook? -------------------- RedNotebook is a **graphical journal** and diary helping you keep track of notes and thoughts. It includes a calendar navigation, customizable templates, export functionality and word clouds. You can also format, tag and search your entries. RedNotebook is available in the repositories of most common Linux distributions and a Windows installer is available. ...

slimKEYS 1.1.1
slimKEYS is a universal hotkey manager with a plug-in system that lets you perform an infinite number of actions simply by pressing system- wide hotkeys. It already comes with multiple built-in plug-ins: slimLAUNCH lets you open applications, documents or folders, or search for password entries stored securely, or open your del.icio.us posts simply by typing a few letters of their name or by directly assigning them specific hotkeys. slimSIZE lets you resize or move windows to any monitor and location just by pressing a hotkey. slimSEARCH helps you search on popular search engine...

CDSB 1.1.1
CDSB 1.1.1 is available. More tools can be installed on FreeBSD. For example, Calibre, HSPICE, SpiceExplorer, etc. Get more information from CDSB Wiki: http://code.google.com/p/cdsb/w/list Group: cdsbgroup@googlegroups.com ...

3DLDF Release 1.1.5 and 1.1.5.1
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Release of 3DLDF 1.1.5. 3DLDF is a GNU package for three-dimensional drawing with MetaPost output. It is available from http://ftp.gnu.org/gnu/3dldf and other ftp servers. Please see the author's website, http://wwwuser.gwdg.de/~lfinsto1 and http://www.gnu.org/directory/graphics/3D/3DLDF.html for more information. In release 1.1.5, I've tied up some loose ends. I wanted to do this before starting on the input routine. * Added `const real step' argument to the version of Ellipse::intersection_points()...

i++, ++i, i+=1 and i = i+1;
Hello, let say we have; 1) i++; /* use i and increment by one */ 2) ++i; /* increment i by one and use it */ 3) i += 1; 4) i = i+1; result (for value of i) of all 4 will be same; could anyone tell differences among them from any perspectives? I heard we'd better use 2) over 1). And 1) is faster than 3) or 4). Thanks in advance, jim said: > Hello, > let say we have; > > 1) i++; /* use i and increment by one */ > 2) ++i; /* increment i by one and use it */ > 3) i += 1; > 4) i = i+1; > > result (for value of i) of all 4 will be same; could anyone...

both 1 and not-1?
Okay, maybe I'm getting too tired for tonight, but ... how can add_to_array *possibly* die with a filter violation (which it does)? Certainly, is_filtered doesn't modify $visit -- or does it? sub add_to_array{ my $self = shift; my $visit = shift; if ( not $self->is_filtered($visit) ){ if ( $self->is_filtered($visit) ){die ("FILTER VIOLATION\n");} } } sub is_filtered{ my $self = shift; my $visit = shift; while ( my ($field, $pattern) = each %{ $self->{_excludepatterns} } ){ if ( $visit->{$field} =~ $pattern){ return 1;...

Web resources about - 1--1 - comp.databases.mysql

Long Hard Slog Ahead For Bernie After Iowa And New Hampshire
This morning, Morning Consult, published the results of some head-to-head match-ups that included Bloomberg. Bernie comes out on top in every ...

Peyton Manning's Son Marshall Stole the Show at His Championship Press Conference
Peyton Manning ‘s four-year-old son Marshall stole the show at his post AFC Championship game press conference held at Sports Authority Field ...

Will Twitter Shakeups Make It Relevant Again?
Twitter just fired four top executives and plans to continue its corporate shakeups even more in the coming weeks, but will the changes make ...

‪‪Mark Wahlberg‬ And Sean Combs Donate Water To Flint, Michigan – AQUAhydrate Pledges 1 Million Bottles ...
Sean “Diddy” Combs and Mark Wahlberg, who co-own California-based beverage company AQUAhydrate, have decided to donate 1 million bottles of water ...

Feds: City Officials Pledged Loyalty to Polygamous Leader Convicted of Sexually Assaulting Children
In a federal civil rights lawsuit against two towns in Arizona and Utah, prosecutors allege that local officials vowed loyalty to Warren Jeffs ...

Watch Liverpool Vs. Stoke City Football Live Online: Start Time, Streaming Video For 2016 Capital One ...
Fans can watch the Liverpool vs. Stoke City football match live online and see which team moves through to the 2016 Capital One Cup final. The ...

The aftermath: How the East Coast looked after this weekend's record-breaking storm
Residents surfaced from their snow covered homes on Sunday after the East Coast Blizzard lived up to — and even exceeded — the hype leading up ...

ISIS video shows attackers committing atrocities before Paris siege
PARIS — A new video released by the Islamic State group purports to show the extremists who carried out the Nov. 13 attacks in Paris committing ...

Batman v Superman trailer reminds us why they're fighting
The latest Batman v Superman: Dawn of Justice trailer reminds to get excited for two of our favorite heroes to take each other on. Previous ...

Apple car project rumors remain unconfirmed amid hiring freeze report
AppleInsider reports that Apple executives are unhappy with the alleged car project's direction, instituting a hiring freeze on the project's ...

Resources last updated: 1/26/2016 3:07:48 AM