Problems with exists: subselect

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

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

--
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 [hidden email].
To post to this group, send email to [hidden email].
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.

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:

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

--
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 [hidden email].
To post to this group, send email to [hidden email].
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          [hidden email]
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

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.

On Wed, May 15, 2019, 02:41 [hidden email] <[hidden email]> wrote:

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

Am 14.05.2019 um 12:05 schrieb jtuchel:

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

--
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 [hidden email].
To post to this group, send email to [hidden email].
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          [hidden email]
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

--
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 [hidden email].
To post to this group, send email to [hidden email].
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/a1510648-d301-0ce1-86f8-f985cca2fe7c%40objektfabrik.de.
For more options, visit https://groups.google.com/d/optout.

--
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 [hidden email].
To post to this group, send email to [hidden email].
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/CAGWHZ9-GmFDLAJ1uGCg3mMvMSjjCux9RXCD%2BC9LCAOo7pW0VGg%40mail.gmail.com.
For more options, visit https://groups.google.com/d/optout.

-- 
-----------------------------------------------------------------------
Objektfabrik Joachim Tuchel          [hidden email]
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

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

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

Am 14.05.2019 um 12:05 schrieb jtuchel:

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

--
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 [hidden email].
To post to this group, send email to [hidden email].
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          [hidden email]
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,

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

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

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

--
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 [hidden email].
To post to this group, send email to [hidden email].
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/6e0c6868-6b44-a53a-0ad4-8b713edc5f8b%40objektfabrik.de.
For more options, visit https://groups.google.com/d/optout.

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

Am 15.05.2019 um 16:15 schrieb [hidden email]:

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

--
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 [hidden email].
To post to this group, send email to [hidden email].
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/6e0c6868-6b44-a53a-0ad4-8b713edc5f8b%40objektfabrik.de.
For more options, visit https://groups.google.com/d/optout.

--
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 [hidden email].
To post to this group, send email to [hidden email].
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/bc667e31-d216-73aa-2983-a9f9b54f9865%40porabo.ch.
For more options, visit https://groups.google.com/d/optout.