Mailing List Archive


Back to the month index Back to the list index
Alex Tang (altitude@petrified.cic.net)
Wed, 8 Jan 1997 11:59:46 -0500 (EST) 

Message-Id: <199701081659.LAA09496@petrified.cic.net>
Subject: Re: [mSQL] ORDER BY + _rowid == bug?
Date: Wed, 8 Jan 1997 11:59:46 -0500 (EST)
From: Alex Tang <altitude@petrified.cic.net>

You scribbled...
> At 06:45 PM 1/7/97 -0800, you wrote:
> >How about keeping the original value for _rowid, and have another internal
> >column, instead, _tmprowid or something, or conversely, use _oldrowid for
> >the original table? This, of course, begs the question, which rowid do you
> >use for table joins output? Maintain _table1_rowid, _table2_rowid columns?
> >This could get messy.
> >
>
> I think all this discussion on _rowid is misleading. Virtually all
> databases contain a _rowid type -- but it is limited to the current dataset
> you are working with. You can't use it for table joins (and you shouldn't
> use _rowd anyway for joins).
>
> _rowid is a dynamic variable. It is NOT fixed per record!! So, let's say
> you want to do the following:
>
> CREATE TABLE employee (
> int employee_id,
> name char(50)
> )
>
> CREATE TABLE project (
> int project_id,
> name char(50)
> )
>
> CREATE TABLE jobassign (
> int employee_id,
> int project_id
> )
>
> The ONLY way to set this up is assign a unique value to employee_id and
> project_id using something like _seq (not _rowid). This way, you can
> export and re-import the table without any problem. Using _rowid to make
> the relation would not allow export/import of the tables (_rowid would be
> different on another database!).
>
> _rowid should only be used to refer to joined datasets -- in which case it
> is most assuredly different from any underlying tables.

The _rowid I was talking about in my original message was not being used
in a join though. I was saying that when ORDER BY is used on a query that
does not include all of the table's rows, the _rowid being returned was
not what you would expect. Whether or not msql uses an internal temporary
table (which it does, and is where the discrepancy is coming from) to
perform the ORDER BY is not something I would normally know in order to
use mSQL. So I thought that the response was misleading.

...alex...
--------------------------------------------------------------------------
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!