Problems with exists: subselect

28 views
Skip to first unread message

jtuchel

unread,
May 14, 2019, 6:05:58 AM5/14/19
to glorp-group
Hi,

I am not sure if I am encountering a Glorp bug or if my code is incorrect. Glorp creates a subselect referencing a table as s1t2 which is not used anywhere in the query.

Here's my Smalltalk code:

    query := SimpleQuery read: KontolinoPurchase.
    query where: [:k |
        (k package id = 1)
            AND:
                (k dateOfPurchase between: (Date today subtractDays: 2) and: (Date today subtractDays: 1))
            AND: (k dateWelcomeMail = nil)].
    query where: [:k |
        k exists: [Query read: User where: [:b | b company = k company AND: (b isActive)]]].

And the resulting SQL code:

SELECT t1.id, "long list of columns prefixed with t1"
 FROM DB2INST1.PURCHASE t1
 WHERE  EXISTS (SELECT s1t1.id, "long list of attributes prefixed with s1t1"
 FROM DB2INST1.USER s1t1
 WHERE ((s1t1.company_id = s1t2.id) AND s1t1.active))

And this is what DB2 shouts at me:

[SQLSTATE=42S22 - [IBM][CLI Driver][DB2/LINUXX8664] SQL0206N  "S1T2.ID" is not valid in this context.  SQLSTATE=42703 [Native Error=-206]]

I tend to absolutely agree with DB2 here. But what am I doing wrong in my subselect? There is a 1:n relationship between Purchase and User, and the purpose of the query is to find package purchases that were bought yesterday and the day before and which have not received a welcome mail yet. At least one of the users in that company have to be activated (otherwise we do not want to send a mail message to the company).


Why does Glorp use a s1t2 prefix here?
Any ideas are welcome

Joachim



Wallen, David

unread,
May 14, 2019, 11:42:18 PM5/14/19
to glorp...@googlegroups.com

Hi Jocheim,

 

Sorry I don’t have time to look at this in detail, but you might want to examine the glorp clauses and the SQL produced in GlorpDBTests. For example,

                GlorpAnySatisfyTest>>testAnySatisfyTwiceNested

There is at least some overlap with your description.

Hth,

Dave

 

Dave Wallen 
Software Engineer
 

https://no-cache.hubspot.com/cta/default/431576/c36613c8-e7ed-43e4-96aa-21da1a227630.png

blank
office:
513-612-2003
website: www.cincomsmalltalk.com
email: dwa...@cincom.com
 

 
 
 https://no-cache.hubspot.com/cta/default/431576/0488e96d-1f19-4f15-9229-6366998fd492.png   https://no-cache.hubspot.com/cta/default/431576/98535c75-36a7-4ad2-886f-59aed10b7d2d.png   https://no-cache.hubspot.com/cta/default/431576/1b963ee4-96da-4a8d-92b5-dc1c5570e9ad.png   https://no-cache.hubspot.com/cta/default/431576/dc3c920a-1832-4080-afb7-2e78ad67d354.png   https://no-cache.hubspot.com/cta/default/431576/df552d48-897f-4524-babf-2e9a83340091.png

 

--- CONFIDENTIALITY STATEMENT ---

This e-mail transmission contains information that is intended to be privileged and confidential. It is intended only for the addressee named above. If you receive this e-mail in error, please do not read, copy or disseminate it in any manner. If you are not the intended recipient, any disclosure, copying, distribution or use of the contents of this information is prohibited, please reply to the message immediately by informing the sender that the message was misdirected. After replying, please erase it from your computer system. Your assistance in correcting this error is appreciated.

--
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 post to this group, send email to glorp...@googlegroups.com.
Visit this group at https://groups.google.com/group/glorp-group.
To view this discussion on the web visit https://groups.google.com/d/msgid/glorp-group/43abbfcc-13f1-41f4-91b0-5056d601f564%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

Wallen, David

unread,
May 14, 2019, 11:44:02 PM5/14/19
to Wallen, David, glorp...@googlegroups.com

Sorry, Joachim, I’m the world’s absolute worst speller!

Dave

jtu...@objektfabrik.de

unread,
May 15, 2019, 5:41:51 AM5/15/19
to Thomas Brodt, glorp...@googlegroups.com
Hi Thomas,

thanks for taking such a sharp look at my code. Indeed, I had a "second" where: in my code. *facepalm*
So I replaced the second where: with AND:

Unfortunately, it doesn't solve the problem, however. Instead I now have two ;-)

The first one is that the prefix s1t2 ist still there. The second is that I get another Error which says there is no decscrptor for User. I seem to remember this is a bug which has been discussed here and needs some extra care with #asGlorpExpressionOn: and such. I need to investigate further.

I also tried expressing this with only a single block (you are of course right, the code I wrote here is a simple extract from the original) and still I get the s1t2 problem:

    query := SimpleQuery read: KontolinoPurchase.
    query where: [:k |
        (k package id = 1)
            AND:
                (k dateOfPurchase between: (Date today subtractDays: 2) and: (Date today subtractDays: 1))
            AND: (k dateWelcomeMail = nil)
            AND:
        (k exists: [Query read: User where: [:b | b company = k company AND: (b isActive)]])].


Joachim

Am 15.05.19 um 10:58 schrieb Thomas Brodt:

Hi Joachim,

maybe it is a side effect of twice setting the where-block for the query? At least I cannot see anything wrong, and we also have exists-queries written that way that work.

I assume you simplified the code for testing the exists only and modified the where: assignment. Or did you want to add the exists with AND: instead of where:?

Thomas

--
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 post to this group, send email to glorp...@googlegroups.com.
Visit this group at https://groups.google.com/group/glorp-group.
To view this discussion on the web visit https://groups.google.com/d/msgid/glorp-group/43abbfcc-13f1-41f4-91b0-5056d601f564%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.


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


Alan Knight

unread,
May 15, 2019, 9:27:33 AM5/15/19
to glorp...@googlegroups.com, Thomas Brodt
I'm guessing here, but the problem you refer to is probably that when building complex queries, a block can either get built without a proper base expression, or you can get it built with two different base expressions. So if there are two different ones, you could get the problem that they don't know about the joined tables that each other has created, so you can get an orphan table like s1t2. And if a base expression doesn't have a descriptor associated with it then you could get the second error. The usual fix is to explicitly do an asGlorpExpression on the baseExpression of the other part.

Thomas Brodt

unread,
May 15, 2019, 9:31:44 AM5/15/19
to glorp...@googlegroups.com, Joachim Tuchel

Hi Joachim,

maybe it is a side effect of twice setting the where-block for the query? At least I cannot see anything wrong, and we also have exists-queries written that way that work.

I assume you simplified the code for testing the exists only and modified the where: assignment. Or did you want to add the exists with AND: instead of where:?

Thomas

Am 14.05.2019 um 12:05 schrieb jtuchel:

jtu...@objektfabrik.de

unread,
May 15, 2019, 9:39:15 AM5/15/19
to glorp...@googlegroups.com
Alan,

this sounds reasonable. I just don't understand it ;-)

Let me try to rephrase:

I'd ask the query after it has been created with #where: for its #baseExpression and then send asGlorpExpressionOn: baseExpressionFromFirstPart to the Block that's handed over to AND: ?


Joachim



Am 15.05.19 um 15:26 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/_HPQ7nYR4_k/unsubscribe.
To unsubscribe from this group and all its topics, send an email to glorp-group...@googlegroups.com.

To post to this group, send email to glorp...@googlegroups.com.
Visit this group at https://groups.google.com/group/glorp-group.

For more options, visit https://groups.google.com/d/optout.

Thomas Brodt

unread,
May 15, 2019, 9:40:19 AM5/15/19
to jtu...@objektfabrik.de, glorp...@googlegroups.com

Hi Joachim,

have you tried

  (k exists: (Query read: User where: [:b | b company = k company AND: (b isActive)]))

instead of

  (k exists: [Query read: User where: [:b | b company = k company AND: (b isActive)]])

giving the exists: a query in parentheses instead of a BlockClosure in square brackets?

Thomas

jtu...@objektfabrik.de

unread,
May 15, 2019, 10:16:01 AM5/15/19
to glorp...@googlegroups.com

Alan,


I tried this:


    query := SimpleQuery read: KontolinoPaketKauf.
    query where: [:k |
        (k paket id = 1) AND: (k kaufDatum < Date today) AND: (k datumFrischAktiviertMail = nil)].

    "Es muss auch aktivierte benutzer geben, sonst darf das nicht versandt werden"
    bExp := query baseExpression.
    query AND: (
        [:k | k exists: (Query read: Benutzer where: [:b | b mandant = k kunde AND: (b aktiv)])]
            asGlorpExpressionOn: bExp).


But still get the same error....


Jaochim


Thomas Brodt

unread,
May 15, 2019, 10:26:35 AM5/15/19
to glorp...@googlegroups.com

We had something like this:

Query
        read: WBPPersonal
        where: [:each |
            | subQuery |
            subQuery := Query
                read: WBPLeistung
                where: [:lei | lei datumVon = Date today AND: [lei personal = each]].
            subQuery retrieve: [:x | 'x'].
            each exists: subQuery].

Maybe reducing the retrieve block to only one column and some constant for each rows helps?

Thomas

--
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 post to this group, send email to glorp...@googlegroups.com.
Visit this group at https://groups.google.com/group/glorp-group.

Alan Knight

unread,
May 15, 2019, 11:14:04 AM5/15/19
to glorp...@googlegroups.com
Yes, exactly. Although now that I think about it I'd expect the and: operation to do that anyway. So there might be something more subtle going on, perhaps with a nested block.

Tom Robinson

unread,
May 15, 2019, 1:34:58 PM5/15/19
to glorp...@googlegroups.com, Thomas Brodt
In general, reducing the fields that subqueries retrieve to only the ones you need to satisfy their conditional function is likely to simplify the SQL generated and improve performance.

jtuchel

unread,
May 17, 2019, 2:28:07 AM5/17/19
to glorp-group
Thomas,


thanks again for taking time to look at my problem.

Unfortunately, it seems this still doesn't work. I now tried this:

    query := SimpleQuery read: KontolinoPaketKauf.
    query where: [:k | |sub|
        sub := Query read: Benutzer where: [:b | b mandant = k kunde AND: (b aktiv)].
        sub retrieve: [:x| 'hit'].   
        (k paket id = 1)
            AND: (k kaufDatum < Date today)
            AND: (k datumFrischAktiviertMail = nil)
            AND: (k exists: sub)].



and still got this SQl statement:

SELECT t1.id, "long list of all mapped attributes in that table, not relevant here"
 FROM DB2INST1.PAKETKAUF t1
 WHERE (((
   (t1.paket_id = 1)
   AND (t1.DATUM < '2019-05-17'))
   AND (t1.DAT_AKTIVIERT_MAIL IS NULL))
  AND  EXISTS (
   SELECT 'hit' FROM DB2INST1.BENUTZER s1t1
   WHERE ((s1t1.firma_id = s1t2.id)
  AND s1t1.aktiv)))


So the table pefix s1t2 is still occuring in this SQL and leads to an SQL error, the JOIN is not correctly determined. I'm giving up on this. I have also tried a few variations of sending several asGlorpExpressionOn: variants and stuff and never get any useful SQL.

Joachim
To unsubscribe from this group and stop receiving emails from it, send an email to glorp...@googlegroups.com.

jtuchel

unread,
May 17, 2019, 2:29:36 AM5/17/19
to glorp-group
Yeah, I'm a big fan of good performance, once things work ;-) I admit the SQL in my last attempt using the retrieve: trick makes the SQL much more readable as well. If only it was correct as well...

jtuchel

unread,
May 20, 2019, 8:36:08 AM5/20/19
to glorp-group
Hi again,


I've spent a little time with this, although I had decided to give up ;-)
And I am not sure I am getting better in understanding all this, but I thought I'd share a little bit of what I found so far..

So I went and loaded GlorpTests into my VAST image and browse arund a little. And it seems there is a much simpler way to achieve what I want by using anySatisfy: in a Glorp Query.

So I tried this:

    query := SimpleQuery read: KontolinoPurchase. 
    query where: [:k |
        (k package id = 1) AND: (k dateOfPurchase < Date today) AND: (k dateWelcomeMail = nil) AND: (k company users anySatisfy: [::usr| usr isActive])].

And this will also transform to an EXISTS subselect where the join is correct:

SELECT t1.id, "many more"
 FROM (DB2INST1.PURCHASE t1 INNER JOIN DB2INST1.COMPANY t2 ON (t1.company_id = t2.id))
 WHERE (
(((t1.package_id = 1)
AND (t1.date < '2019-05-20'))
AND (t1.date_welcome_mail IS NULL))
AND  EXISTS (
SELECT t3.id
 FROM DB2INST1.USER t3
 WHERE ((t3.active = 1) AND (t2.id = t3.company_id))))


I wasn't aware of the fact that you can use anySatisfy: in query blocks, but this is much more convenient and makes the ST and SQL code much nicer.

This brings me to a new conclusion: since several people told me here and privately that they didn't encounter this problem (most of them using VisualWorks and/or another Database), there might be something wrong with the definitition of the EXIST and NOT EXISTS function in either DB2Platform on all Glorp Dialects or in the DB2Platform in the Glorp port on VAST.

DB2Platform>>#initializeFunctions overrides the defintion of EXISTS and NOT EXISTS from DatabasePlatform. Unfortunately it does so in write-only manner that I am unsable to understand....


    "Like Oracle, SQL Server doesn't allow functions like EXISTS in a field list,
    but will allow a select there as an argument to a function, such as CASE.
    So print it that way if we're in the select list."

    baseExists := functions at: #exists:.
    dbSpecificExists := DualRoleFunction new.
    dbSpecificExists function: '#exists:'.
    dbSpecificExists whereClauseVersion: baseExists.
    selectList :=
        Case new argumentTypes: (Array with: (self varchar: 1) with: self integer with: self integer).
    selectList argumentModifierBlock: [:function :args |
        function base: args first.
        Array with: 'x' asGlorpExpression with: 1 asGlorpExpression with: 0 asGlorpExpression].
    dbSpecificExists selectListVersion: selectList.
    dbSpecificExists type: self integer.
    functions at: #exists: put: dbSpecificExists.

Please note: the comment mentions SQL server, but this snippet is taken from DB2Platform. Maybe that is a hint?


I will look a bit further, but for now I'll see if I can express some of my EXISTS subqueries using anySatisfy: and thus make our Application a bit faster in some places...

This leads me to a very interesting question. If EXIST can be done with anySatisfy: in a query Block, chances are I can also do a NOT EXIST using noneSatisfy:, right?


As always, thanks for listening and giving me hooks for looking at the right places. Even if exists: and notExists: don't work for me on VAST/DB2, I have the feeling I can express most of what I need using #anySatisfy: and #noneSatisfy: (or with anySatisfy: not).


Joachim





Wallen, David

unread,
May 20, 2019, 3:17:12 PM5/20/19
to glorp...@googlegroups.com

Hi Joachim,

 

It looks like your version of GlorpDatabase may be from 2012. The #initialize function and many other things have been updated since then, all available in the Cincom public repository.

 

Dave

 

Dave Wallen 
Software Engineer
 

https://no-cache.hubspot.com/cta/default/431576/c36613c8-e7ed-43e4-96aa-21da1a227630.png

blank
office:
513-612-2003
website: www.cincomsmalltalk.com
email: dwa...@cincom.com
 

 
 
 https://no-cache.hubspot.com/cta/default/431576/0488e96d-1f19-4f15-9229-6366998fd492.png   https://no-cache.hubspot.com/cta/default/431576/98535c75-36a7-4ad2-886f-59aed10b7d2d.png   https://no-cache.hubspot.com/cta/default/431576/1b963ee4-96da-4a8d-92b5-dc1c5570e9ad.png   https://no-cache.hubspot.com/cta/default/431576/dc3c920a-1832-4080-afb7-2e78ad67d354.png   https://no-cache.hubspot.com/cta/default/431576/df552d48-897f-4524-babf-2e9a83340091.png

 

--- CONFIDENTIALITY STATEMENT ---

This e-mail transmission contains information that is intended to be privileged and confidential. It is intended only for the addressee named above. If you receive this e-mail in error, please do not read, copy or disseminate it in any manner. If you are not the intended recipient, any disclosure, copying, distribution or use of the contents of this information is prohibited, please reply to the message immediately by informing the sender that the message was misdirected. After replying, please erase it from your computer system. Your assistance in correcting this error is appreciated.

 

 

--

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 post to this group, send email to glorp...@googlegroups.com.
Visit this group at https://groups.google.com/group/glorp-group.

jtuchel

unread,
May 21, 2019, 3:49:41 AM5/21/19
to glorp-group
To make things a little more interesting: I forgot to mention that we actually do use exists: and notExists: for some queries and have been doing so for years. So it is not a general problem with these queries. This also means my last "suspect" about #initializeFunctions is probably not very accurate ;-)

I guess it's more likely there are some differences between the ones that work and ones that don't in the information provided in the mappings or such.

But I've heard from several sources in the meantime that this problem once occured also in VisualWorks and on Postgres and has been fixed in Glorp on VW a while ago now... so chances are this is a VAST only problem these days...

Joachim
Reply all
Reply to author
Forward
0 new messages