Mailing List Archive


Back to the month index Back to the list index
Alex Tang (altitude@petrified.cic.net)
Sat, 4 Jan 1997 18:17:10 -0500 (EST) 

Message-Id: <199701042317.SAA02902@petrified.cic.net>
Subject: Re: [mSQL] ORDER BY + _rowid == bug?
Date: Sat, 4 Jan 1997 18:17:10 -0500 (EST)
From: Alex Tang <altitude@petrified.cic.net>

Hi folks.

I asked yesterdy about problems with using _rowid and ORDER BY in the same
select statement. After crawling through the code for a couple of hours
today, I think I figured out a little more.

It seems that when the temporary table is created (and sent to the user),
the offset pointer (which is used to match and retrieve data) which points
into the row structure is set improperly. I tried fixing it, but I wasn't
able to figure it out. (And I have a load of core files to prove it. :)

However, I've hit upon another (design type) question...Perhaps someone
who's more familiar with the internals can help me out.

When an ORDER BY or a SELECT DISTINCT is used in a select statement, a
temporary table is created. It looks like the process for creating this
table is:

  - the select is completed, finding all of the matching rows.
  - the temporary table is created with ony the fields and rows that are
    needed.
  - the temporary table is sorted
  - the results are passed back (from the temporary table) to the client.

The _rowid variable is an "internal" variable (as per the documentation),
and is retreived off of the table data structure. The problem is that
when a temporary table is created, the _rowid returned in the select query
is the _rowid of the >temporary table<, not of the original table, so the
_rowid may not correspond with the original table.

For example:

if you have the table "table" which has the following records:

        id name _rowid
        ----------------------
        1 foo 0
        59 bar 1
        349 ack 2
        301 yikes 3
        23 ackack 4
        355 argh 5

If you perform the following query:

        SELECT _rowid, id, name
        FROM table
        WITH id > 300
        ORDER BY id

you would get the following response (if the offset bug were fixed).

        _rowid id name
        --------------------
        0 301 yikes
        1 349 ack
        2 355 argh

Is this the expected result? I was assuming I'd get back:

        _rowid id name
        --------------------
        3 301 yikes
        2 349 ack
        5 355 argh

Thanks a bunch. Good Luck! Thanks David for mSQL, it's very excellent.

...alex... 

--
    Alex Tang   altitude@cic.net   http://petrified.cic.net/~altitude
CICNet:  Programmer/Webmaster, Network Services Group  http://www.cic.net
   Hey, it's one of my super powers...I'm super loyal.   -Road Rovers...
--------------------------------------------------------------------------
To remove yourself from the Mini SQL mailing list send a message containing
"unsubscribe" to "unsubscribe" to msql-list-request@bunyip.com.  Send a message containing
"info msql-list" to majordomo@bunyip.com for info on monthly archives of
the list. For more help, mail owner-msql-list@bunyip.com NOT the msql-list!