Subselects do not work with FilteredTypeResolver

[jtuchel]

It's me again. I am having trouble with a Subselect which doesn't work as I expected.

Let me start by explaining the model. This is about an event log in which I store a number of subclasses of Event, say LogonEvents, LogoffEvent etc. They are all stored in on table and distinguished by a field named type. Let's say in LogonEvents the type field contains 'LOGON' and in case of LogoutEvent it says 'LOGOUT'.  For this I use a FilteredTypeResolver, noithing special about it.

So far so good, all works fine, until I want to list the all users and their latest Logon. So I want to query the eventlog table only for LogonEvents.

What I tried is this:

q := Query read: LogonEvent where: [:ev| ev user id = 1000].
q where: [:lgon| logon timestamp = (

  (Query read: LogonEvent  where: [:e| e user id = 1000])
retrieveMax: #timestamp) 

   ].
dbSess execute: q

This query, however doesn't return any results, because the inner / sub query doesn't add a filter clause for the LogonEvent.

The generated SQL looks like this:

SELECT t1.timestamp, t1.type, t1.userid
  FROM EVENTLOG t1
  WHERE ((t1.typ = 'LOGON') AND (t1.timestamp = 

   (SELECT MAX(s1t1.timestamp)
       FROM db2inst1.EVENTLOG s1t1
       WHERE (s1t1.useridid = 1000))))

But it should look like this:

SELECT t1.timestamp, t1.type, t1.userid
  FROM EVENTLOG t1
  WHERE ((t1.type = 'LOGON') AND (t1.timestamp = 

   (SELECT MAX(s1t1.timestamp)
       FROM db2inst1.EVENTLOG s1t1
       WHERE (

           (s1t1.type = 'LOGON') AND  "This one is missing in Glorp"

           (s1t1.useridid = 1000)))))

...which actually returns the correct result when executed in an SQL client.

So what am I doing wrong here? Why does the subselect omit the type information?

Is there another approach to read what I need in one single SQL?

This is essential for mny use case, because there may be many other events of that user that happened after Logon, so it is important to only read LOGON events in this query.

I am not on VisualWorks, so once again I have no idea if this is a bug and if so if it has been fixed since 2016, the release date of my PUL from Cincom.But I guess it is much likely I am doing something wrong anyways.

Any hints? Any ideas?

Joachim

[jtuchel]

Hi there,

I can add a PseudoVariable mapping for the #type in the EventLog class's decsriptor and add an explicit

where: [:ev| ev type='LOGON' AND: (...)] 

to the subquery. It does work, but I think that is only a dirty workaround for a bug in Glorp...

Joachim

[Alan Knight]

Yeah, that seems like a bug. At a guess, the filtered inheritance tweaking is not being applied to the subquery. Unfortunately I don't have a VisualWorks version that will run on m current hardware either, to verify.

--
You received this message because you are subscribed to the Google Groups "glorp-group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to glorp-group...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/glorp-group/409d54be-1220-4bb4-af41-07cb6e852a80n%40googlegroups.com.

[jtuchel]

Hi Alan,

thanks for your confirmation that it seems to be a bug. Unfortunately, I don't really know hor to procede from here. Neither you nor I can tell if this is a bug that Cincom has fixed in one of the newer versions of Glorp (I am on VAST - Glorp Based on Glorp 9.0-11,nross - and don't have a 2020ies  version of VisualWorks, nor do I plan to buy one just to check).

 
I'll try to find the time to debug into this in VAST, maybe I find something. If I do, I'll probably just implement my own fix and post it here in the hopes somebody cares to take a look at it and/or integrate it.

Joachim

[Joachim Tuchel]

Dear all

I think I found a fix for this bug.

I would really appreciate if someone could take a look at it. I prepared a little test case for the bug and the fix for both VAST (Tonel format, so that Pharo users can read it) and VisualWorks (see attached .pcl).

Let me give a short explanation what it does:

We have a  table named LOG in which we write login and logout events for users. These are two subclasses of LogEvent which are mapped using a FilteredTypeResolver. The test inserts the following rows into the table:

2023-10-26-17.00.31.000000 | Mary | Mary: logged in
2023-10-26-17.00.51.000000 | Paul | Paul: logged in
2023-10-26-17.01.11.000000 | Mary | Mary: logged out
2023-10-26-17.01.31.000000 | Paul | Paul: logged out
2023-10-26-17.01.51.000000 | Mary | Mary: logged in
2023-10-26-17.02.11.000000 | Mary | Mary: logged out

In order to demonstrate the bug, I prepared a test case that wants to find the last LOGIN of Mary (note that there is a logout in the table after the login, so the login is not the latest entry).

The query to find mary's latest login is prepared like this:

        Query
            read: LogEntryLogin
            where: [:entry |
                entry user userId = 1 AND:
                    entry timestamp = (
                        (Query read: LogEntryLogin where: [:e | e user userId = 1])
                            retrieveMax: #timestamp)].

So you see we ask fo a LOGIN Event sepcifically, so the subquery should explicitly limit the search by asking for the TYPE of a record. But it doesn't, so it returns a wrong result.

The fix I suggest for this problem is a change to the follwing methods in SubSelectBaseExpression (see attached SubSelectBaseExpressionChanges.st):

additionalExpressions

    ^self additionalExpressionsOn: self

and:

additionalExpressionsOn: anExpression


    | expressions |

    expressions := OrderedCollection new.

    self descriptor typeMapping addTypeMappingCriteriaTo: expressions in: anExpression.

    expressions addAll: (trueBase additionalExpressionsOn: anExpression).

    ^expressions

In my image, these changes lead to the test turning green and also my existing subqueries in the application are still working nicely (I am using a few notExists: and other subqueries, but this is of course not a prrof that my change doesn't break things in some exotic places...).

So it would be great if somebody could take a look and check whether the change looks good for them.
Another thing I cannot verify is whether this bug is still around in current VisualWorks versions, because I have no current VisualWorks Version around. I know the bug exists in VAST, VisualWorks 8.2 PUL and Pharo up unitl 11. I could verify my fix works in VAST and VW 8.2 with the attached test case.

Please find attached my VAST export and a VisualWorks port of the test case. It uses a SQLite3 in-Memory DB, so needs zero configuration in VAST (V12.x). In VisualWorks you need to either change the TestResource to use your favorite DB or configure the SQLite connection.

Since there is no commonplace to manage bugs for Glorp, I ask you to please respond here with the following info:

• I ran the tests on (Smalltalk dialect and version, Database used)
• I can confirm the error (test runs red before fix) and agree it is a bug
  
• I could successfully integrate the fix suggested on my dialect
• The test turned green after the fix on my Smalltalk dialect
• I integrated the fix in a new Glorp version for my dialect. it can be found here:...
  

Feel free to ask questions or take the code and integrate it in your fork of Glorp (I don't think there is any such thing as a common base for Glorp at this time).

And, of course, if anybody has an idea on how to cooperate on such topics in the future, I am eager to hear your suggestions.

Joachim

Am 06.10.23 um 20:40 schrieb Alan Knight:

You received this message because you are subscribed to a topic in the Google Groups "glorp-group" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/glorp-group/k5JEpMw8W_4/unsubscribe.
To unsubscribe from this group and all its topics, send an email to glorp-group...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/glorp-group/CAGWHZ994LunkSmkXw4XM136-yhjrxvS2dU8KGaY-BnqSrZY%3DjQ%40mail.gmail.com.

-- 

----------------------------------------------------------------------- 
Objektfabrik Joachim Tuchel              mailto:jtu...@objektfabrik.de 
Fliederweg 1                                 http://www.objektfabrik.de
D-71640 Ludwigsburg                  http://joachimtuchel.wordpress.com
Telefon: +49 7141 56 10 86 0                    Fax: +49 7141 56 10 86 1