Mailing List Archive


Back to the month index Back to the list index
Alex Tang (altitude@petrified.cic.net)
Fri, 3 Jan 1997 23:48:46 -0500 (EST) 

Message-Id: <199701040448.XAA17239@petrified.cic.net>
Subject: [mSQL] ORDER BY + _rowid == bug?
Date: Fri, 3 Jan 1997 23:48:46 -0500 (EST)
From: Alex Tang <altitude@petrified.cic.net>

Hi folks. Hope everyone had a good holiday. :)

But now for the question...

I'm having problems using ORDER BY and _rowid in a select query. It seems
to munge the data pretty bad...Has anyone else seen this? Are there any
fixes? (The raw data is included at the bottom of the message).

I'm assuming it's in the library because this happens in the "msql" unix
program and also when using MsqlPerl-1.14.

For example:

This seems to work fine...

    mSQL > select _rowid, user, start_time from data \g
    Query OK. 9 row(s) modified or retrieved.
     
     +----------+----------+------------+
     | _rowid | user | start_time |
     +----------+----------+------------+
     | 0 | user0001 | 1288 |
     | 1 | user0001 | 1288 |
     | 2 | user0001 | 1289 |
     | 3 | user0001 | 1290 |
     | 4 | user0001 | 12090 |
     | 5 | user0002 | 1702 |
     | 6 | user0002 | 1702 |
     | 7 | user3 | 2375 |
     | 8 | user3 | 2376 |
     +----------+----------+------------+

but when I use order by, it get's messed up.

      mSQL > select _rowid, user, start_time from data
          -> order by start_time \g
       
      Query OK. 9 row(s) modified or retrieved.
       
       +----------+----------+------------+
       | _rowid | user | start_time |
       +----------+----------+------------+
       | 0 | NULL | 1667786607 |
       | 1 | NULL | 1667786607 |
       | 2 | NULL | 1667786607 |
       | 3 | NULL | 1667786607 |
       | 4 | NULL | 1667786607 |
       | 5 | NULL | 1798136181 |
       | 6 | NULL | 1798136181 |
       | 7 | NULL | 1835101796 |
       | 8 | NULL | 1835101796 |
       +----------+----------+------------+

How about this:

      mSQL > select start_time, user, uid from data \g

      Query OK. 9 row(s) modified or retrieved.
       
       +------------+----------+----------+
       | start_time | user | uid |
       +------------+----------+----------+
       | 1288 | user0001 | 1450 |
       | 1288 | user0001 | 1450 |
       | 1289 | user0001 | 1450 |
       | 1290 | user0001 | 1450 |
       | 12090 | user0001 | 1450 |
       | 1702 | user0002 | 1248 |
       | 1702 | user0002 | 1248 |
       | 2375 | user3 | 1515 |
       | 2376 | user3 | 1515 |
       +------------+----------+----------+
 
 
 
This works too. If I don't use "_rowid" in the query, it seems ok.

      mSQL > select start_time, user, uid from data
          -> order by start_time desc \g
       
      Query OK. 9 row(s) modified or retrieved.
       
       +------------+----------+----------+
       | start_time | user | uid |
       +------------+----------+----------+
       | 12090 | user0001 | 1450 |
       | 2376 | user3 | 1515 |
       | 2375 | user3 | 1515 |
       | 1702 | user0002 | 1248 |
       | 1702 | user0002 | 1248 |
       | 1290 | user0001 | 1450 |
       | 1289 | user0001 | 1450 |
       | 1288 | user0001 | 1450 |
       | 1288 | user0001 | 1450 |
       +------------+----------+----------+

Thanks very much.

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

 
If you'd like to try this, I've got the following output from msqldump: 

CREATE TABLE data (
  user CHAR(8) NOT NULL,
  uid INT NOT NULL,
  gid INT,
  start_time INT NOT NULL,
  end_time INT NOT NULL,
  line_num CHAR(18) NOT NULL,
  trans INT NOT NULL,
  conn_type CHAR(1) NOT NULL,
  finished CHAR(1) NOT NULL
) \g
 
CREATE   INDEX idx1 ON data (
        uid,
        line_num,
        conn_type,
        finished
) \g
 
CREATE   INDEX idx2 ON data (
        user,
        line_num,
        conn_type,
        finished
) \g
 
 
#
# Dumping data for table 'data'
#
 
INSERT INTO data  VALUES ('user0001',1450,40,1288,-1,'192.217.64.253:2',34152,'t','n')\g
INSERT INTO data  VALUES ('user0001',1450,40,1288,-1,'192.217.64.253:2',34152,'t','n')\g
INSERT INTO data  VALUES ('user0001',1450,40,1289,-1,'192.217.64.253:2',34152,'t','n')\g
INSERT INTO data  VALUES ('user0001',1450,40,1290,-1,'192.217.64.253:2',34152,'t','n')\g
INSERT INTO data  VALUES ('user0001',1450,40,12090,-1,'192.217.64.253:2',34152,'t','n')\g
INSERT INTO data  VALUES ('user0002',1248,99,1702,-1,'192.217.64.253:1',52056,'t','n')\g
INSERT INTO data  VALUES ('user0002',1248,-1,1702,-1,'192.217.64.253:1',52436,'s','n')\g
INSERT INTO data  VALUES ('user3',1515,40,2375,-1,'192.217.65.200:6',6337,'t','n')\g
INSERT INTO data  VALUES ('user3',1515,-1,2376,-1,'192.217.65.200:6',9505,'s','n')\g

Here's what msqladmin version says:

Version Details :-
 
        msqladmin version       2.0 Beta 1
        mSQL server version     2.0 Beta 1
        mSQL protocol version   22
        mSQL connection         Localhost via UNIX socket
        Target platform         SunOS-4.1.4-Sparc
 
Configuration Details :-
 
        Default config file     /usr/local/Hughes/msql.conf
        TCP socket              1114
        UNIX socket             /usr/local/Hughes/msql2.sock
        mSQL user               root
        Admin user              root
        Install directory       /usr/local/Hughes
        PID file location       /usr/local/Hughes/msql2d.pid

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