B4J Question jSQL ... need to upgrade ?

pierrem · Oct 14, 2022

Hi,
I have a SQLite problem with a simple query ...
When I run this query from windows, I get correct results.
When I run it from B4J, I get wrong results.

I just upgrade to B4J version 9.80, the jSQL.jar date is 2022-07-05 (version 1.61 in B4J IDE)
Should I upgrade to a more recent jSQL.jar, and how to to it ? Simply copy a fresher jar file to internal library folder ?

thanks for help

Table (extract):

id    ticket    heure            article        qte        prix  
1    1    2021-07-20 10:14:44    Coca            1    2
2    3    2021-07-20 10:15:10    Coca            1    2
2    3    2021-07-20 10:15:10    Heineken        1    3,2
2    4    2021-07-20 10:15:21    Coca            1    2
2    4    2021-07-20 10:15:21    Heineken        1    3,2
2    5    2021-07-20 10:16:07    Coca            1    2
2    5    2021-07-20 10:16:07    Heineken        1    3,2
2    5    2021-07-20 10:16:07    Jambon-beure    1    2,2
2    6    2021-07-20 11:17:52    Quatre-quarts    1    1,5
2    7    2021-07-23 11:23:56    Pâté            1    2,3
2    8    2021-07-23 14:41:51    Heineken        1    3,2

the query

B4X:

select
          theticket,
          max(montant) as montantmax,
          nombre,
          heure
    from
        (
              Select
                ticket as theticket,
                Sum(qte) As nombre,
                Sum(qte * prix) As montant,
                heure
              From
                tickets
              group by ticket
        )
    where
          heure >= '2021-01-01' and heure < '2021-07-22'

As expected, on windows I get

B4X:

ticket montantmax nombre heure
5        7.4                 3           2021-07-20

but under B4J, i get

B4X:

ticket montantmax nombre heure
6        7.4                 1           2021-07-20

Note 2nd column is correct ... ?

jahswant · Oct 14, 2022

What do you mean by on windows ?

pierrem · Oct 15, 2022

@jahswant : I mean an .exe running under windows (flyspeed sql query, may be not the best but i'm a long time user ...)

OliverA · Oct 16, 2022

You do not need to update any internal libraries (not really advised unless recommended by @Erel or you know/understand what you are getting yourself into when you do so). It looks like the JDBC driver for SQLite that is included in the latest B4J (9.80) is sqlite-jdbc-3.7.2.jar. The latest JDBC driver from xerial is sqlite-jdbc-3.39.3.0.jar. Download the newest version from https://search.maven.org/artifact/org.xerial/sqlite-jdbc or https://repo1.maven.org/maven2/org/xerial/sqlite-jdbc/. Place the jar file in the Additional Libraries folder for B4J and/or B4A and then reference the library in Main via the #AdditionalJar attribute

B4X:

#AdditionalJar: sqlite-jdbc-3.39.3.0

Note: Not tested, but this should work. Nothing in jSQL modifies the behavior of the SQL call. If you want to see what jSQL does, you can look at the source for jSQL here: https://github.com/AnywhereSoftware...SQL/src/anywheresoftware/b4j/objects/SQL.java

Update:
Even though the above shows how to update the JDBC driver, it technically has nothing to do with the issue the author experiences. See post below (#5)

OliverA · Oct 16, 2022

After reviewing the SQL, I guess I can see where you can get different results. You are using a MAX without grouping the additional columns that are not included in the MAX expression and some DBMS's will complain about using a MAX without the use of GROUP BY in these situations. If not, then I guess unexpected results (such as in your case) are to be expected. Updating the JDBC driver does not really correct the issue that you are having (using MAX without GROUP BY as in your example).

I'm incorrect, SQLite has special behavior for using aggregate functions w/o grouping: https://www.sqlite.org/quirks.html#...t_columns_that_are_not_in_the_group_by_clause

OliverA · Oct 16, 2022

The only issue you may run into is:

With SQLite (but not any other SQL implementation that we know of) if an aggregate query contains a single min() or max() function, then the values of columns used in the output are taken from the row where the min() or max() value was achieved. If two or more rows have the same min() or max() value, then the columns' values will be chosen arbitrarily from one of those rows.

Source:

Are there in guarantees about the non-aggregate...anycodings

Are there in guarantees about the non-aggregated columns in a GROUP BY query? Let's assume we have the following tabl ...

webcache.googleusercontent.com

Note:
I had to use Google's cache to get to the site. Going directly to the site seems to trip some sort of hack on the site. I've noticed this hack on quite a few sites lately, but luckily I was able to use Google's cache to get to the information.

pierrem · Oct 16, 2022

@OliverA :
thanks for the link + additional jar directive in post #4, it will help

updated, but still the same error ...
the query should give records with ticketID #5 but I get ticketID #6 .... but the max() value is correct. (2 + 3.2 +2.2 = 7.4 .... I get 7.4 from the query)
I'm not an SQL king ... maybe the error is in the query itself , not in jSQL. I'll check that again.

table (extract)
2 5 2021-07-20 10:16:07 Coca 1 2
2 5 2021-07-20 10:16:07 Heineken 1 3,2
2 5 2021-07-20 10:16:07 Jambon-beure 1 2,2

for other post, I seems logical to me :"If two or more rows have the same min() or max() value, then the columns' values will be chosen arbitrarily from one of those rows."

thanks for help

OliverA · Oct 17, 2022

Just out of curiosity, I've built a small test app that uses the limited data set from post #1, and with that limited data set, I get the result that you are looking for.

theticket montantmax nombre heure
5 7.4 3 2021-07-20 10:16:07

So the only conclusion that I can come to, is that you have a larger data set that you are working with and it encounters the issue I've pointed out in post #6. But that is only a guess, and next time, please provide the data set you are working with and a small sample application so that people here on the forum can help you better.

I'm attaching my small sample app. I'm using B4J 9.80 and the JDK 11 provided here: https://www.b4x.com/b4j.html. I'm also using sqlite-jdbc-3.39.3.0.jar that can be downloaded by the links provided in post #4.

pierrem · Oct 17, 2022

@OliverA,

Thanks a lot for the time you spent on this ...

I tested your projet SQLiteMaxTest ... it works perfectly !
I'll try to understand where is MY mistake ... i'll let you know

pierrem · Oct 21, 2022

@OliverA,

I tryied again ... with allways wrong result.
I delete functions, modules, every thing until just having minimal code ...

And then I discover that the results are wrong when I add/check 'B4Xtable' in the library pane in the IDE ...
If 'B4XTable' is unchecked ... results are correct.

So, I would say the problem is here ...

Any idea ?

PS : 2 ZIPs, 1 for project, 1 for Sqllite DB

MicroDrie · Oct 21, 2022

The problem is that you have opened a database file without added any information to it (empty tables). So there is no information.

Delete the empty database into the Objects directory
Add the following database copy routine

Add table rows count routine:

'    --- Retrieve how many rows a table have
Public Sub getRowCount(TableName As String, Field As String) As Int
    Dim Qstr As String = $"SELECT count(${Field}) FROM ${TableName}"$
    Log(Qstr)
    Dim RowCount As Int = SQL1.ExecQuerySingleResult(Qstr)
    Log($"Rowcount for ${TableName} = ${RowCount}"$)
    Return RowCount
End Sub

Replace routine Button1_Click with the following code
(tip use principle to improve the detection of wrong table content to prevent the error)

Add line 5 - 16:

Private Sub Button1_Click
    'xui.MsgboxAsync("Hello world!", "B4X")
    Dim sb As StringBuilder
    SQL1.InitializeSQLite(File.DirApp, "caisse.db", True)
    
'    --- Check for empty table
    Dim RowsCount As Int = getRowCount("tickets", "id")
    Log(RowsCount)
    
    If RowsCount = 0 Then
'        --- Delete whole database
        File.Delete(File.DirApp, "caisse.db")
'        --- Copy default database
        File.Copy(File.DirAssets, "caisse.db",File.DirApp, "caisse.db")
    End If
    
    Dim res As ResultSet = SQL1.ExecQuery($"
select
          theticket,
          max(montant) as montantmax,
          nombre,
          heure
    from (
 Select ticket as theticket,
    Sum(qte) As nombre,
    Sum(qte * prix) As montant,
    heure
        From tickets
        group by ticket)
    where
          heure >= '2021-01-01' and heure < '2021-07-22'   
"$)
    If res.ColumnCount > 0 Then
        sb.Initialize
        For x = 0 To res.ColumnCount - 1
            sb.Append($"${res.GetColumnName(x)}        "$)
        Next
        Log(sb.ToString)
    End If
    Do While res.NextRow
        sb.Initialize
        For x = 0 To res.ColumnCount - 1
            sb.Append($"${res.GetString2(x)}        "$)
        Next
        Log(sb.ToString)
    Loop
    res.Close
    SQL1.Close
End Sub

OliverA · Oct 22, 2022

pierrem said:
And then I discover that the results are wrong when I add/check 'B4Xtable' in the library pane in the IDE ...
If 'B4XTable' is unchecked ... results are correct.

You've exposed an interesting bug/gotcha. The manifest file for B4XTable is

Version=1.22
B4J.DependsOn=B4XFormatter.b4xlib, jSQL, sqlite-jdbc-3.7.2, XUI Views
B4A.DependsOn=B4XFormatter.b4xlib, SQL, XUI Views
B4i.DependsOn=B4XFormatter.b4xlib, iSQL, XUI Views

If you notice, for B4J, besides other internal/extra B4J libraries, it also specifies the .jar file to use for SQLite. It looks like specifying a .jar file that you usually would include with the #AdditionalJar takes priority over the #AdditionalJar directive if both reference the same library with different versions! What a sneaky issue you have uncovered here. I've modified the manifest file to this

Version=1.23
B4J.DependsOn=B4XFormatter.b4xlib, jSQL, XUI Views
B4A.DependsOn=B4XFormatter.b4xlib, SQL, XUI Views
B4i.DependsOn=B4XFormatter.b4xlib, iSQL, XUI Views

and bundled it as B4XTable_1_23_mod.b4xlib, placed it into the appropriate sub-directory in the Additional Libraries path and now I get the correct results that you are looking for. If one does use this modified version of the library, one has to make sure to include the SQLite .jar file via #AdditionalJar.

Conclusion:
As I first guessed, the issue had to do with an older version of SQLite producing a different result than a newer version of SQLite. What was unexpected is that a B4XLib can overwrite a Java library that is included via a #AdditionalJar directive if the libraries are the same (Java namespace), but have different version numbers. That is a sneaky issue to be aware of. I also learned that the manifest file of a B4XLib can be used to reference Java libraries. I just naturally assumed it could not and would only include B4J/B4A/B4i/B4X libraries, no external libraries. I learned something new. I also learned that this can be a source of unexpected behavior. I don't know if this would be considered a bug or a gotcha one has to be aware of. Only @Erel can make a determination on this.

I'm attaching a modified version of B4XTable that, if used, will resolve your encountered issue for the moment. Any updates to the internal library by @Erel may need to be re-modified to exclude the SQLite jar file reference in the manifest file of the B4XLib.

pierrem · Oct 23, 2022

@OliverA,

Thank for this detailled, credible and really interresting post.
For now, I understand where and what was the problem (even if I not confident enough to find it !).

thanks a lot for all your work

B4J Question jSQL ... need to upgrade ?

pierrem

Member

jahswant

Well-Known Member

pierrem

Member

OliverA

Expert

OliverA

Expert

OliverA

Expert

Are there in guarantees about the non-aggregate...anycodings

pierrem

Member

OliverA

Expert

Attachments

pierrem

Member

pierrem

Member

Attachments

MicroDrie

Well-Known Member

OliverA

Expert

Attachments

pierrem

Member

Similar Threads

B4J Question jSQL ... need to upgrade ?

Member

Well-Known Member

Member

Expert

Expert

Expert

Member

Expert

Attachments

Member

Member

Attachments

Well-Known Member

Expert

Attachments

Member

Similar Threads

Privacy & Transparency

Privacy & Transparency