ORDER BY and LIMIT not working as expected

[iiegor]

Steps to reproduce

• Table structure

• Query
  SELECT message, uid FROM messages WHERE id = '#{roomId}' AND pool = '#{@chat.onPool}' ORDER BY time DESC limit 0,15
• Content
  

Issue

The expected result must be:

var result = [
  RowDataPacket {
    message: 'hello'
  },
  RowDataPacket {
    message: 'test'
  }
]

but I get:

var result = [
  RowDataPacket {
    message: 'test'
  },
  RowDataPacket {
    message: 'hello'
  }
]

[dougwilson]

Hi! that's weird, for sure. Can you tell me a couple things:

1. We don't return anything in the result format that you are giving there. Do you have code doing that and if so, can you provide it in case there is a bug there?
2. We want to see what your MySQL server is actually sending back. Can you add debug: true to your connection options (with host, and port, for example) and then run that query and paste all the debug output generated here?

[iiegor]

1. I'm using the generic-pool package (https://github.com/coopernurse/node-pool) (see my: database.coffee) and here is where I execute the query: chat.coffee#L64
2. I tried with debug: true but nothing was showing so I used log: true when creating the `genericPool`` and this was the output: (the first message in the output must be the last)

This looks like a node-pool issue 😕, anyway thanks for replying!

[iiegor]

@dougwilson I tried without node-pool and the same result. Here is the debug output:

--> ComQueryPacket
ComQueryPacket {
  command: 3,
  sql: 'SELECT message, uid FROM messages WHERE id = \'1\' AND pool = \'0\' ORDER BY time DESC limit 0,15' }

<-- ResultSetHeaderPacket
ResultSetHeaderPacket { fieldCount: 2, extra: undefined }

<-- FieldPacket
FieldPacket {
  catalog: 'def',
  db: 'xat',
  table: 'messages',
  orgTable: 'messages',
  name: 'message',
  orgName: 'message',
  charsetNr: 33,
  length: 765,
  type: 253,
  flags: 4097,
  decimals: 0,
  default: undefined,
  zeroFill: false,
  protocol41: true }

<-- FieldPacket
FieldPacket {
  catalog: 'def',
  db: 'xat',
  table: 'messages',
  orgTable: 'messages',
  name: 'uid',
  orgName: 'uid',
  charsetNr: 33,
  length: 765,
  type: 253,
  flags: 4097,
  decimals: 0,
  default: undefined,
  zeroFill: false,
  protocol41: true }

<-- EofPacket
EofPacket {
  fieldCount: 254,
  warningCount: 0,
  serverStatus: 34,
  protocol41: true }

<-- RowDataPacket
RowDataPacket { message: 'the most recent msg', uid: '265826731' }

<-- RowDataPacket
RowDataPacket { message: 'test', uid: '265826731' }

<-- RowDataPacket
RowDataPacket { message: 'hello world!', uid: '265826731' }

<-- RowDataPacket
RowDataPacket { message: 'example', uid: '265826731' }

<-- RowDataPacket
RowDataPacket { message: '(smile)', uid: '265826731' }

<-- RowDataPacket
RowDataPacket { message: ' (swt)  (swt)  (swt)  (swt) ', uid: '265826731' }

<-- RowDataPacket
RowDataPacket { message: ' (astonished) ', uid: '265826731' }

<-- RowDataPacket
RowDataPacket { message: 'helloooo', uid: '265826731' }

<-- RowDataPacket
RowDataPacket { message: 'blabla', uid: '265826731' }

<-- RowDataPacket
RowDataPacket { message: 'message', uid: '265826731' }

<-- RowDataPacket
RowDataPacket { message: 'another msg', uid: '265826731' }

<-- RowDataPacket
RowDataPacket { message: 'hello world', uid: '265826731' }

<-- RowDataPacket
RowDataPacket { message: 'my message', uid: '265826731' }

<-- RowDataPacket
RowDataPacket { message: 'example', uid: '265826731' }

<-- RowDataPacket
RowDataPacket { message: 'hello', uid: '265826731' }

<-- EofPacket
EofPacket {
  fieldCount: 254,
  warningCount: 0,
  serverStatus: 34,
  protocol41: true }

[dougwilson]

Hi, I don't know any more how to follow this conversation, because the only right/wrong things I have to go on is your original post, which is only about two rows, but suddenly you switched to a bunch of rows.

Are those data packets in the order you expected? Is the raw array you get from the results callback in the same order of those row data packets from the server or in a different order?

We should be presenting you the results in the same order as those row data packets, as that's the order MySQL is telling us for them to be in.

I'm not sure how to help further, because I'm no longer sure what the exact bug report is :/

[dougwilson]

Wait a minute, I just realized: your original post makes no sense. From your screenshot of your rows, you have basically the following:

message	time
hello	1445895122
test	1445895124

Then your query against that has ORDER BY time DESC. This means "give me the rows where time is sorted from the largest number first to the smallest number (the meaning of DESC)". I would expect that to go 1445895124 (test), then 1445895122 (hello), which is largest to smallest.

Your then show that this is how, indeed, you are getting the results. You went on to say you expected them in the reverse order. If this is the last, you want ascending order, or ORDER BY time ASC.