in

System.Data.SQLite

An open source ADO.NET provider for the SQLite database engine

SELECT / GROUP BY / ORDER BY Issue.... ?

Last post 10-07-2009 5:59 PM by Fernando Pinguelo. 8 replies.
Page 1 of 1 (9 items)
Sort Posts: Previous Next
  • 10-10-2008 2:45 AM

    SELECT / GROUP BY / ORDER BY Issue.... ?

    I'm writing a Media Management application, and thought everything was working smoothly until I had to update my media player to make use of alternative data views, and it started breaking when the correct number of tracks for a particular playlist appeared to be incorrect.  I've attached the database in question for replication purposes.  I'm using an SQLCommand with a dataset, using the following VB.NET code:

    Public Function GetData(ByVal CommandText As String, ByRef ReturnResults As System.Data.DataSet) As Boolean

    Try

    Dim SQLData As New SQLite.SQLiteDataAdapter(CommandText, m_Connection)

    Dim DataSet As New System.Data.DataSet

    Call SQLData.Fill(DataSet)

    ReturnResults = DataSet

    Return True

    Catch ex As Exception RaiseEvent ErrorRaised(ex)

    End Try

    End Function

    Anyway, here is the query at the heart of my problem:

    SELECT playlists.name as Title, count(playlist_media.mediaid) AS Tracks, playlists.playlistid as TagID, (SELECT count(*) FROM selected_media,playlist_media WHERE selected_media.mediaid = playlist_media.mediaid AND playlists.playlistid = playlist_media.playlistid) AS Selected, case (SELECT count(*) FROM selected_media,playlist_media WHERE selected_media.mediaid = playlist_media.mediaid AND playlists.playlistid = playlist_media.playlistid) WHEN 0 THEN 0 ELSE 1 END AS IsSelected FROM playlists, playlist_media WHERE playlist_media.playlistid=playlists.playlistid GROUP BY playlists.name ORDER BY playlists.playlist_position;

    This is used to retrieve a list of 'playlists' to screen showing the associated track count for each playlist. If you run this query in something like SQLite Administrator (http://sqliteadmin.orbmu2k.de/) the data is returned in the correct order, with the correct values.

    Absolute Oldskool Classics Volume 4 (Disc 1)

    Absolute Oldskool Classics Volume 4 (Disc 2)

    Absolute Oldskool Classics Volume 7 (Disc 1)

    Absolute Oldskool Classics Volume 7 (Disc 2)

    If you run this query via ADO.NET 2.0 Data Provider, the 'Title' column appears in an incorrect order but all other columns contain the correct data.  The resulting incorrect column has the following order:

    Drum & Bass Arena (Disc 1)

    Drum & Bass Arena (Disc 2)

    Drum & Bass Arena (Disc 1)

    Prodigy Demo Tape

     If you run a simpler query:

    SELECT playlists.name as Title FROM playlists ORDER BY playlist_position;

    You get the following (correct) results:

    Absolute Oldskool Classics Volume 4 (Disc 1)

    Absolute Oldskool Classics Volume 4 (Disc 2)

    Absolute Oldskool Classics Volume 7 (Disc 1)

    Absolute Oldskool Classics Volume 7 (Disc 2)

    This issue is present in version 1.0.48, and also in the latest 1.0.60.  My guess is it could be something to do with the sub-selection.  Any idea on a way round this... or do I need a fix?

    **UPDATE**

     Modifying the query to use the outer tables, has no impact, so changing the query to something like this:

    SELECT playlists.name as Title, count(playlist_media.mediaid) AS Tracks, playlists.playlistid as TagID, (SELECT count(*) FROM selected_media WHERE selected_media.mediaid = playlist_media.mediaid AND playlists.playlistid = playlist_media.playlistid) AS Selected FROM playlists, playlist_media WHERE playlist_media.playlistid=playlists.playlistid GROUP BY playlists.name ORDER BY playlists.playlist_position;

    ...still returns the one column in the wrong order.

  • 10-10-2008 4:46 AM In reply to

    • masc
    • Top 50 Contributor
    • Joined on 10-26-2007
    • Posts 20

    Re: SELECT / GROUP BY / ORDER BY Issue.... ?

    I cannot replicate this.

    Using 1.0.50 running the following c# code while connected to your database, the output will still be the same as when firing against SQLite directly (using SqLiteSpy in my case):

    Absolute Oldskool Classics Volume 4 (Disc 1)

    Absolute Oldskool Classics Volume 4 (Disc 2)

    Absolute Oldskool Classics Volume 7 (Disc 1)

    Absolute Oldskool Classics Volume 7 (Disc 2)

    .....

    cn = new SQLiteConnection( string.Format( "Data Source={0};Synchronous=Normal;Version=3",
        Path.Combine(
            Environment.GetFolderPath( Environment.SpecialFolder.Desktop ),
            "PhatMM.db" ) ) );

    cn.Open();

    DbDataAdapter da = new SQLiteDataAdapter(
        "SELECT playlists.name as Title, count(playlist_media.mediaid) AS Tracks, playlists.playlistid as TagID, (SELECT count(*) FROM selected_media,playlist_media WHERE selected_media.mediaid = playlist_media.mediaid AND playlists.playlistid = playlist_media.playlistid) AS Selected, case (SELECT count(*) FROM selected_media,playlist_media WHERE selected_media.mediaid = playlist_media.mediaid AND playlists.playlistid = playlist_media.playlistid) WHEN 0 THEN 0 ELSE 1 END AS IsSelected FROM playlists, playlist_media WHERE playlist_media.playlistid=playlists.playlistid GROUP BY playlists.name ORDER BY playlists.playlist_position;",
        cn );

    DataSet ds = new DataSet();
    da.Fill( ds );

    foreach ( DataRow dr in ds.Tables[0].Rows )
    {
        Console.WriteLine( dr[0] );
    }

  • 10-10-2008 5:33 AM In reply to

    Re: SELECT / GROUP BY / ORDER BY Issue.... ?

    May be you are sorting the results inadvertly at the client side? Perhaps you use a dataview or a bindig source?

    Regards

    Jesús López

  • 10-10-2008 6:59 AM In reply to

    Re: SELECT / GROUP BY / ORDER BY Issue.... ?

    Nope I checked there was no client sorting, the ListView being populated has no custom sorter or any sorting methods applied to the control either at design time or run-time.

     I also added the console dump after getting the dataset back, and got the same incorrectly ordered results:

     Drum & Bass Arena (Disc 1)

    Drum & Bass Arena (Disc 2)

    Drum & Bass Arena (Disc 3)

    Prodigy Demo Tape

    Absolute Oldskool Classics Volume 4 (Disc 1)

    Absolute Oldskool Classics Volume 4 (Disc 2)

    Absolute Oldskool Classics Volume 7 (Disc 1)

    Absolute Oldskool Classics Volume 7 (Disc 2)

    DJ's Unite - Volume 1

    Essential Old Skool Hardcore (Disc 1)

    Essential Old Skool Hardcore (Disc 2)

    Hardcore U Know The Score (Disc 1)

    Hit The Decks Volume 2

    The Max Power Old Skool Collection

    Rave Anthems (Disc 1)

    Rave Anthems (Disc 2)

    Rave Anthems (Disc 3)

    Rave Anthems Volume 2 (Disc 1)

    Rave Anthems Volume 2 (Disc 2)

    Rave Anthems Volume 2 (Disc 3)

    The Oldskool Masters: Round 2 (Disc 1)

    The Oldskool Masters: Round 2 (Disc 2)

    Oldskool Classics - Phantasy

    The Prodigy Experience

    The Ultimate Rave Classics Volume 1 (Disc 1)

    The Ultimate Rave Classics Volume 1 (Disc 2)

    The Ultimate Rave Classics Volume 1 (Disc 3)

    Raveology (Disc 1)

    Raveology (Disc 2)

    The Ultimate Hardcore

    I Love 19 92

    The Best of LeetRadio.coA first chance exception of type 'System.Data.SQLite.SQLiteException' occurred in System.Data.SQLite.dll

    m

    Regression: Back To 89 to 92 (Disc 1)

    Regression: Back To 89 to 92 (Disc 2)

    Ravin - 40 Rave Anthems (Disc 1)

    Ravin - 40 Rave Anthems (Disc 2)

    Extreme Euphoria 4 (Disc 1)

    Extreme Euphoria 4 (Disc 2)

    Extreme Euphoria 4 (Disc 3)

    Hardhouse Anthems Volume 5 (Disc 1)

    Hardhouse Anthems Volume 5 (Disc 2)

    The Very Best Of Tried & Tested Euphoria (Disc 1)

    The Very Best Of Tried & Tested Euphoria (Disc 2)

    The Very Best Of Tried & Tested Euphoria (Disc 3)

    Hard House Nation [Disc 1]

    Hard House Nation [Disc 2]

    Keep It Tidy 4 - (Disc 1)

    Keep It Tidy 4 (Disc 2)

    Keep It Tidy 4 (Disc 3)

    Keep It Tidy 4 (Disc 4)

    Extreme Euphoria 3 (Disc 1)

    Extreme Euphoria 3 (Disc 2)

    BK - Extreme Euphoria 5 (Disc 1)

    BK - Extreme Euphoria 5 (Disc 2)

    Extreme Euphoria 2 (Disc 1)

    Extreme Euphoria 2 (Disc 2)

    Ministry Of Sound - Hardhouse Classics (Disc 1)

    Ministry Of Sound - Hardhouse Classics (Disc 2)

    Ministry Of Sound - Hardhouse Classics (Disc 2) 2

    Trance Nation America 3 (Disc 1)

    Trance Nation America 3 (Disc 2)

    Trance Nation Harder (Disc 1)

    Trance Nation Harder (Disc 2)

    Slinky - World Class (Disc 1)

    Slinky - World Class (Disc 2)

    Ministry of Sound - The Annual 2002 (Disc 1)

    Ministry of Sound - The Annual 2002 (Disc 2)

    Trance Nation 4

    Ibiza Mix

    Lashed Euphoria (Disc 1)

    Lashed Euphoria (Disc 2)

    Lashed Euphoria (Disc 3)

    Trance Nation Electric (Disc 1)

    Trance Nation Electric (Disc 2)

    Eminem - The Eminem Show

    Maximum Bass (Disc 1)

    Maximum Bass (Disc 2)

    Bass In Yer Face (Disc 1)

    Bass In Yer Face (Disc 2)

    Various Artists - Max Power Booty Bass (Disc 1)

    Various Artists - Max Power Booty Bass (Disc 2)

  • 10-10-2008 7:02 AM In reply to

    Re: SELECT / GROUP BY / ORDER BY Issue.... ?

    I also noticed, that if I change the ORDER BY to be use the same column as the GROUP BY, the Title column is in the correct order, but the other columns are in the wrong order!

    I've also tried simplifying the query to:

    select distinct playlists.name as Title, (select count(pm1.mediaid) from playlist_media pm1 where pm1.playlistid = playlists.playlistid) as Tracks, (select count(*) from selected_media, playlist_media pm2 where selected_media.mediaid = pm2.mediaid and pm2.playlistid=playlists.playlistid) as Selected from playlists;

    ...and the results are the same :(

  • 10-02-2009 5:18 PM In reply to

    Re: SELECT / GROUP BY / ORDER BY Issue.... ?

     I am having a similar issue. I have created the following table:

    CREATE TABLE EXPENSE_CATEGORIES ( 

    CATEGORY_ID INTEGER PRIMARY KEY AUTOINCREMENT,

    DESCRIPTION VARCHAR(255) NOT NULL, UNIQUE (DESCRIPTION))

    and entered the three records: 'atest1', 'btest2', and 'ctest3'.

    Then, in C# 2.0 I ran the query:

    SELECT CATEGORY_ID, DESCRIPTION FROM EXPENSE_CATEGORIES ORDER BY DESCRIPTION ASC

    I obtained the result:

    btest2

    atest1

    ctest3

    I then changed the query ORDER BY TO  ORDER BY DESCRIPTION DESC and got the result:

    ctest3

    atest1

    btest2

     

    I then changed the order by clause to  ORDER BY DESCRIPTION COLLATE NOCASE and obtained the result:

    btest2

    ctest3

    atest1

    I then changed the order by clause to  ORDER BY ORDER BY lower(DESCRIPTION) ASC and obtained the result:

    btest2

    ctest3

    atest1

     ORDER BY upper(DESCRIPTION) DESC

    atest1

    ctest3

    btest2

     ORDER BY lower(DESCRIPTION) DESC

    atest1

    ctest3

    btest2

    ORDER BY lower(DESCRIPTION) ASC

    btest2

    ctest3

    atest1

    ORDER BY lower(DESCRIPTION) COLLATE NOCASE

    btest2

    ctest3

    atest1

    So, I ask, what is the deal? It is a simple select with an order by. However, it does not work at all!!

     

     

     

     

  • 10-02-2009 8:23 PM In reply to

    Re: SELECT / GROUP BY / ORDER BY Issue.... ?

    I decided to continue trying other things and what I did was to re-define the table as:

    CREATE TABLE EXPENSE_CATEGORIES (
    CATEGORY_ID INTEGER PRIMARY KEY AUTOINCREMENT,
    DESCRIPTION VARCHAR(255) NOT NULL COLLATE NOCASE,
    UNIQUE (DESCRIPTION))

    The results were the same. The weird behavior I am reporting is very disturbing to me My confidence  in the product has been shaken and I can no longer rely on SQLITE at this point. I think it is time for me to stop developing with SQLITE and go back to MSDBE.

  • 10-03-2009 5:07 AM In reply to

    Re: SELECT / GROUP BY / ORDER BY Issue.... ?

    Try to create like vs says: -- Original table schema CREATE TABLE [Frases2] ( [AutoID] integer PRIMARY KEY AUTOINCREMENT NOT NULL, [Frase] nvarchar(400) NOT NULL COLLATE NOCASE ); CREATE UNIQUE INDEX [IX_Frase] ON [Frases2] ([Frase]); I haven't any problem ordering your 3 words -.- using (SQLiteCommand comm = conn.CreateCommand()) { comm.CommandText = "select frase,autoid from frases2 order by frase asc"; comm.CommandType = CommandType.Text; using (SQLiteDataReader data = comm.ExecuteReader()) { while (data.Read()) { for (int i = 0; i " + data[i]); } } } } Output: Frase -> atest1 AutoID -> 1003 Frase -> btest2 AutoID -> 1001 Frase -> ctest3 AutoID -> 1002
  • 10-07-2009 5:59 PM In reply to

    Re: SELECT / GROUP BY / ORDER BY Issue.... ?

     I replaced the database with MS SQL Express and realized, after the work was done, that I am encrypting the data before inserting into the table. Surely enough, sorting works. I just don't get what I would expect, since the data being sorted is encrypted.

    I decided to change the code to use SQLite again. I think I need to have my brain replaced instead of the DB.

     Thanks a bunch for the reply, anyway. In fact, your reply triggered the remaining good neurons in my brain and made me remember about the encryption.

    What I will do next is to make the encryption/decryption C# functions part of SQLite and then apply the decryption in the order by clause of the select statement. Hopefully it will work.

     Thanks again,

     

    Fernando

Page 1 of 1 (9 items)
Powered by Community Server (Commercial Edition), by Telligent Systems