django-firebird test expressions

classic Classic list List threaded Threaded
7 messages Options
Reply | Threaded
Open this post in threaded view
|

django-firebird test expressions

maxi-2
Hi,

Testing expressions test app, the query generated into BasicExpressionsTests.test_annotate_values_filter method:

companies = Company.objects.annotate(
foo=RawSQL('%s', ['value']),
).filter(foo='value').order_by('name')

Generate:

'SELECT  "EXPRESSIONS_COMPANY"."ID", "EXPRESSIONS_COMPANY"."NAME", "EXPRESSIONS_COMPANY"."NUM_EMPLOYEES", "EXPRESSIONS_COMPANY"."NUM_CHAIRS", "EXPRESSIONS_COMPANY"."CEO_ID", "EXPRESSIONS_COMPANY"."POINT_OF_CONTACT_ID", ? AS "FOO" FROM "EXPRESSIONS_COMPANY" WHERE ? = ? ORDER BY "EXPRESSIONS_COMPANY"."NAME" ASC'

This sql command has 3 params (?), two of which are out of where clause.

? AS "FOO" 

WHERE ? = ?


So, the underlying database driver execute this, doing something like this

 cursor.execute(sql, ['value', 'value', 'value'])

Is correct that the 3 params are replaced into the entire sql and not just on where clause ?
It depend of the  implementation on each database driver?

Best regards
Maxi


--
You received this message because you are subscribed to the Google Groups "Django developers (Contributions to Django itself)" 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/django-developers.
To view this discussion on the web visit https://groups.google.com/d/msgid/django-developers/5570530d-696d-4f6e-b557-eb84be410661%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re: django-firebird test expressions

Josh Smeaton
It looks correct to me. RawSQL is defining a parameter to be included in the query. The RawSQL expression itself is used in the select and on the left hand side of the WHERE clause, so the parameter needs to be included there. The right hand side of the WHERE clause is a regular string (which just happens to also be named "value") which Django will always parameterise and has nothing to do with the RawSQL expression itself.

Is there some reason you think there's an issue with the SQL being constructed?

On Sunday, 15 April 2018 12:25:48 UTC+10, Maximiliano Robaina wrote:
Hi,

Testing expressions test app, the query generated into BasicExpressionsTests.test_annotate_values_filter method:

companies = Company.objects.annotate(
foo=RawSQL('%s', ['value']),
).filter(foo='value').order_by('name')

Generate:

'SELECT  "EXPRESSIONS_COMPANY"."ID", "EXPRESSIONS_COMPANY"."NAME", "EXPRESSIONS_COMPANY"."NUM_EMPLOYEES", "EXPRESSIONS_COMPANY"."NUM_CHAIRS", "EXPRESSIONS_COMPANY"."CEO_ID", "EXPRESSIONS_COMPANY"."POINT_OF_CONTACT_ID", ? AS "FOO" FROM "EXPRESSIONS_COMPANY" WHERE ? = ? ORDER BY "EXPRESSIONS_COMPANY"."NAME" ASC'

This sql command has 3 params (?), two of which are out of where clause.

? AS "FOO" 

WHERE ? = ?


So, the underlying database driver execute this, doing something like this

 cursor.execute(sql, ['value', 'value', 'value'])

Is correct that the 3 params are replaced into the entire sql and not just on where clause ?
It depend of the  implementation on each database driver?

Best regards
Maxi


--
You received this message because you are subscribed to the Google Groups "Django developers (Contributions to Django itself)" 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/django-developers.
To view this discussion on the web visit https://groups.google.com/d/msgid/django-developers/d63ac393-8e8f-46f6-93d9-d0d1373da0cd%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re: django-firebird test expressions

maxi-2
Hi Josh,

Thanks for response.

El domingo, 15 de abril de 2018, 20:35:54 (UTC-3), Josh Smeaton escribió:
It looks correct to me. RawSQL is defining a parameter to be included in the query. The RawSQL expression itself is used in the select and on the left hand side of the WHERE clause, so the parameter needs to be included there. The right hand side of the WHERE clause is a regular string (which just happens to also be named "value") which Django will always parameterise and has nothing to do with the RawSQL expression itself.

Is there some reason you think there's an issue with the SQL being constructed?

No, I don't think that the sql statement is wrong. I just traying to figured out how the sql is generated by Django and why it fails in Firebird.
I found the answere, of course, there is a Firebird limitation on this type of statement. Running that query raise a "Data type unknown" error.

https://stackoverflow.com/questions/37348807/data-type-unknown-in-case-expression-with-only-parameters-as-values

I will need to found other strategy to resolve this.

Regards.

 

On Sunday, 15 April 2018 12:25:48 UTC+10, Maximiliano Robaina wrote:
Hi,

Testing expressions test app, the query generated into BasicExpressionsTests.test_annotate_values_filter method:

companies = Company.objects.annotate(
foo=RawSQL('%s', ['value']),
).filter(foo='value').order_by('name')

Generate:

'SELECT  "EXPRESSIONS_COMPANY"."ID", "EXPRESSIONS_COMPANY"."NAME", "EXPRESSIONS_COMPANY"."NUM_EMPLOYEES", "EXPRESSIONS_COMPANY"."NUM_CHAIRS", "EXPRESSIONS_COMPANY"."CEO_ID", "EXPRESSIONS_COMPANY"."POINT_OF_CONTACT_ID", ? AS "FOO" FROM "EXPRESSIONS_COMPANY" WHERE ? = ? ORDER BY "EXPRESSIONS_COMPANY"."NAME" ASC'

This sql command has 3 params (?), two of which are out of where clause.

? AS "FOO" 

WHERE ? = ?


So, the underlying database driver execute this, doing something like this

 cursor.execute(sql, ['value', 'value', 'value'])

Is correct that the 3 params are replaced into the entire sql and not just on where clause ?
It depend of the  implementation on each database driver?

Best regards
Maxi


--
You received this message because you are subscribed to the Google Groups "Django developers (Contributions to Django itself)" 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/django-developers.
To view this discussion on the web visit https://groups.google.com/d/msgid/django-developers/45683c0b-6dcd-4207-a8e3-d4b578c42bd4%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re: django-firebird test expressions

Shai Berger
In reply to this post by maxi-2
Hi Maximiliano,

On Sat, 14 Apr 2018 19:25:48 -0700 (PDT)
Maximiliano Robaina <[hidden email]> wrote:

> Hi,
>
> Testing expressions test app, the query generated into
> BasicExpressionsTests.test_annotate_values_filter method:
>
> companies = Company.objects.annotate(
> foo=RawSQL('%s', ['value']),
> ).filter(foo='value').order_by('name')
>
> Generate:
>
> 'SELECT  "EXPRESSIONS_COMPANY"."ID", "EXPRESSIONS_COMPANY"."NAME",
> "EXPRESSIONS_COMPANY"."NUM_EMPLOYEES",
> "EXPRESSIONS_COMPANY"."NUM_CHAIRS", "EXPRESSIONS_COMPANY"."CEO_ID",
> "EXPRESSIONS_COMPANY"."POINT_OF_CONTACT_ID", ? AS "FOO" FROM
> "EXPRESSIONS_COMPANY" WHERE ? = ? ORDER BY
> "EXPRESSIONS_COMPANY"."NAME" ASC'
>
> This sql command has 3 params (?), two of which are out of where
> clause.
>
> ? AS "FOO"
>
> WHERE ? = ?
>

A few years ago something similar was causing issues with the Oracle
backend. To resolve it, Mariusz added a clevar hack, relying on named
parameters -- he made sure that if the same parameter value is used
more than once, then the statement re-uses the parameter name, passing
the value only once. So, in your case, the equivalent would be
something like

...
"EXPRESSIONS_COMPANY"."POINT_OF_CONTACT_ID", %arg1 AS "FOO" FROM
"EXPRESSIONS_COMPANY" WHERE %arg1 = %arg1 ORDER BY
"EXPRESSIONS_COMPANY"."NAME" ASC'

with

 cursor.execute(sql, [], arg1='value')

This was relatively easy to do in the Oracle backend, which has always
used named parameters under the hood (though the idea to do it was, in
my opinion, surprising and brilliant). If Firebird supports them, you
may be able to borrow this solution.

Shai

--
You received this message because you are subscribed to the Google Groups "Django developers  (Contributions to Django itself)" 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/django-developers.
To view this discussion on the web visit https://groups.google.com/d/msgid/django-developers/20180417090110.10bca728.shai%40platonix.com.
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re: django-firebird test expressions

maxi-2

Hi Shai,

Thank for the tip

El martes, 17 de abril de 2018, 3:01:30 (UTC-3), Shai Berger escribió:
Hi Maximiliano,

On Sat, 14 Apr 2018 19:25:48 -0700 (PDT)
Maximiliano Robaina <<a href="javascript:" target="_blank" gdf-obfuscated-mailto="a0FBVF70CAAJ" rel="nofollow" onmousedown="this.href=&#39;javascript:&#39;;return true;" onclick="this.href=&#39;javascript:&#39;;return true;">maxir...@...> wrote:

> Hi,
>
> Testing expressions test app, the query generated into
> BasicExpressionsTests.test_annotate_values_filter method:
>
> companies = Company.objects.annotate(
> foo=RawSQL('%s', ['value']),
> ).filter(foo='value').order_by('name')
>
> Generate:
>
> 'SELECT  "EXPRESSIONS_COMPANY"."ID", "EXPRESSIONS_COMPANY"."NAME",
> "EXPRESSIONS_COMPANY"."NUM_EMPLOYEES",
> "EXPRESSIONS_COMPANY"."NUM_CHAIRS", "EXPRESSIONS_COMPANY"."CEO_ID",
> "EXPRESSIONS_COMPANY"."POINT_OF_CONTACT_ID", ? AS "FOO" FROM
> "EXPRESSIONS_COMPANY" WHERE ? = ? ORDER BY
> "EXPRESSIONS_COMPANY"."NAME" ASC'
>
> This sql command has 3 params (?), two of which are out of where
> clause.
>
> ? AS "FOO"
>
> WHERE ? = ?
>

A few years ago something similar was causing issues with the Oracle
backend. To resolve it, Mariusz added a clevar hack, relying on named
parameters -- he made sure that if the same parameter value is used
more than once, then the statement re-uses the parameter name, passing
the value only once. So, in your case, the equivalent would be
something like

...
"EXPRESSIONS_COMPANY"."POINT_OF_CONTACT_ID", %arg1 AS "FOO" FROM
"EXPRESSIONS_COMPANY" WHERE %arg1 = %arg1 ORDER BY
"EXPRESSIONS_COMPANY"."NAME" ASC'

with

 cursor.execute(sql, [], arg1='value')

This was relatively easy to do in the Oracle backend, which has always
used named parameters under the hood (though the idea to do it was, in
my opinion, surprising and brilliant). If Firebird supports them, you
may be able to borrow this solution.

Shai

Ok, but it is a cx_Oracle implementation.
Unfortunately fdb (the firebird python driver) doesn't support this and, in any case, is a FirebirdSQL limitation. [1]

Regards.


[1]  https://stackoverflow.com/questions/37348807/data-type-unknown-in-case-expression-with-only-parameters-as-values

 

--
You received this message because you are subscribed to the Google Groups "Django developers (Contributions to Django itself)" 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/django-developers.
To view this discussion on the web visit https://groups.google.com/d/msgid/django-developers/306f8e3e-6ed2-4e7c-a8d2-b8d3798d463e%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re: django-firebird test expressions

Josh Smeaton
Have you looked into using Cast for this particular query to see if it passes?

https://docs.djangoproject.com/en/2.0/ref/models/database-functions/#cast

Cast(RawSQL('%s', ['value']), CharField(max_length=10) ?

If that worked, then perhaps you could find a clever way to wrap problem expressions, or at least document the workaround for your users.

On Tuesday, 17 April 2018 21:12:09 UTC+10, Maximiliano Robaina wrote:

Hi Shai,

Thank for the tip

El martes, 17 de abril de 2018, 3:01:30 (UTC-3), Shai Berger escribió:
Hi Maximiliano,

On Sat, 14 Apr 2018 19:25:48 -0700 (PDT)
Maximiliano Robaina <[hidden email]> wrote:

> Hi,
>
> Testing expressions test app, the query generated into
> BasicExpressionsTests.test_annotate_values_filter method:
>
> companies = Company.objects.annotate(
> foo=RawSQL('%s', ['value']),
> ).filter(foo='value').order_by('name')
>
> Generate:
>
> 'SELECT  "EXPRESSIONS_COMPANY"."ID", "EXPRESSIONS_COMPANY"."NAME",
> "EXPRESSIONS_COMPANY"."NUM_EMPLOYEES",
> "EXPRESSIONS_COMPANY"."NUM_CHAIRS", "EXPRESSIONS_COMPANY"."CEO_ID",
> "EXPRESSIONS_COMPANY"."POINT_OF_CONTACT_ID", ? AS "FOO" FROM
> "EXPRESSIONS_COMPANY" WHERE ? = ? ORDER BY
> "EXPRESSIONS_COMPANY"."NAME" ASC'
>
> This sql command has 3 params (?), two of which are out of where
> clause.
>
> ? AS "FOO"
>
> WHERE ? = ?
>

A few years ago something similar was causing issues with the Oracle
backend. To resolve it, Mariusz added a clevar hack, relying on named
parameters -- he made sure that if the same parameter value is used
more than once, then the statement re-uses the parameter name, passing
the value only once. So, in your case, the equivalent would be
something like

...
"EXPRESSIONS_COMPANY"."POINT_OF_CONTACT_ID", %arg1 AS "FOO" FROM
"EXPRESSIONS_COMPANY" WHERE %arg1 = %arg1 ORDER BY
"EXPRESSIONS_COMPANY"."NAME" ASC'

with

 cursor.execute(sql, [], arg1='value')

This was relatively easy to do in the Oracle backend, which has always
used named parameters under the hood (though the idea to do it was, in
my opinion, surprising and brilliant). If Firebird supports them, you
may be able to borrow this solution.

Shai

Ok, but it is a cx_Oracle implementation.
Unfortunately fdb (the firebird python driver) doesn't support this and, in any case, is a FirebirdSQL limitation. [1]

Regards.


[1]  <a href="https://stackoverflow.com/questions/37348807/data-type-unknown-in-case-expression-with-only-parameters-as-values" target="_blank" rel="nofollow" onmousedown="this.href=&#39;https://www.google.com/url?q\x3dhttps%3A%2F%2Fstackoverflow.com%2Fquestions%2F37348807%2Fdata-type-unknown-in-case-expression-with-only-parameters-as-values\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNEPPK42OFcLg9n9OKOOKAsH2dnTCg&#39;;return true;" onclick="this.href=&#39;https://www.google.com/url?q\x3dhttps%3A%2F%2Fstackoverflow.com%2Fquestions%2F37348807%2Fdata-type-unknown-in-case-expression-with-only-parameters-as-values\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNEPPK42OFcLg9n9OKOOKAsH2dnTCg&#39;;return true;">https://stackoverflow.com/questions/37348807/data-type-unknown-in-case-expression-with-only-parameters-as-values

 

--
You received this message because you are subscribed to the Google Groups "Django developers (Contributions to Django itself)" 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/django-developers.
To view this discussion on the web visit https://groups.google.com/d/msgid/django-developers/d2367698-dbf6-4750-88f7-3eba95ac9f2f%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re: django-firebird test expressions

maxi-2


El martes, 17 de abril de 2018, 9:11:23 (UTC-3), Josh Smeaton escribió:
Have you looked into using Cast for this particular query to see if it passes?

<a href="https://docs.djangoproject.com/en/2.0/ref/models/database-functions/#cast" target="_blank" rel="nofollow" onmousedown="this.href=&#39;https://www.google.com/url?q\x3dhttps%3A%2F%2Fdocs.djangoproject.com%2Fen%2F2.0%2Fref%2Fmodels%2Fdatabase-functions%2F%23cast\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNGZzOgiFbv1nq4EjM7r2TRMqj5G5g&#39;;return true;" onclick="this.href=&#39;https://www.google.com/url?q\x3dhttps%3A%2F%2Fdocs.djangoproject.com%2Fen%2F2.0%2Fref%2Fmodels%2Fdatabase-functions%2F%23cast\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNGZzOgiFbv1nq4EjM7r2TRMqj5G5g&#39;;return true;">https://docs.djangoproject.com/en/2.0/ref/models/database-functions/#cast

Cast(RawSQL('%s', ['value']), CharField(max_length=10) ?

If that worked, then perhaps you could find a clever way to wrap problem expressions, or at least document the workaround for your users.

Ok, it worked rigth. It is a good started point and solve the problem. 
I will need to dig a little more to found out a better solution.

Thanks.






 

On Tuesday, 17 April 2018 21:12:09 UTC+10, Maximiliano Robaina wrote:

Hi Shai,

Thank for the tip

El martes, 17 de abril de 2018, 3:01:30 (UTC-3), Shai Berger escribió:
Hi Maximiliano,

On Sat, 14 Apr 2018 19:25:48 -0700 (PDT)
Maximiliano Robaina <[hidden email]> wrote:

> Hi,
>
> Testing expressions test app, the query generated into
> BasicExpressionsTests.test_annotate_values_filter method:
>
> companies = Company.objects.annotate(
> foo=RawSQL('%s', ['value']),
> ).filter(foo='value').order_by('name')
>
> Generate:
>
> 'SELECT  "EXPRESSIONS_COMPANY"."ID", "EXPRESSIONS_COMPANY"."NAME",
> "EXPRESSIONS_COMPANY"."NUM_EMPLOYEES",
> "EXPRESSIONS_COMPANY"."NUM_CHAIRS", "EXPRESSIONS_COMPANY"."CEO_ID",
> "EXPRESSIONS_COMPANY"."POINT_OF_CONTACT_ID", ? AS "FOO" FROM
> "EXPRESSIONS_COMPANY" WHERE ? = ? ORDER BY
> "EXPRESSIONS_COMPANY"."NAME" ASC'
>
> This sql command has 3 params (?), two of which are out of where
> clause.
>
> ? AS "FOO"
>
> WHERE ? = ?
>

A few years ago something similar was causing issues with the Oracle
backend. To resolve it, Mariusz added a clevar hack, relying on named
parameters -- he made sure that if the same parameter value is used
more than once, then the statement re-uses the parameter name, passing
the value only once. So, in your case, the equivalent would be
something like

...
"EXPRESSIONS_COMPANY"."POINT_OF_CONTACT_ID", %arg1 AS "FOO" FROM
"EXPRESSIONS_COMPANY" WHERE %arg1 = %arg1 ORDER BY
"EXPRESSIONS_COMPANY"."NAME" ASC'

with

 cursor.execute(sql, [], arg1='value')

This was relatively easy to do in the Oracle backend, which has always
used named parameters under the hood (though the idea to do it was, in
my opinion, surprising and brilliant). If Firebird supports them, you
may be able to borrow this solution.

Shai

Ok, but it is a cx_Oracle implementation.
Unfortunately fdb (the firebird python driver) doesn't support this and, in any case, is a FirebirdSQL limitation. [1]

Regards.


[1]  <a href="https://stackoverflow.com/questions/37348807/data-type-unknown-in-case-expression-with-only-parameters-as-values" rel="nofollow" target="_blank" onmousedown="this.href=&#39;https://www.google.com/url?q\x3dhttps%3A%2F%2Fstackoverflow.com%2Fquestions%2F37348807%2Fdata-type-unknown-in-case-expression-with-only-parameters-as-values\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNEPPK42OFcLg9n9OKOOKAsH2dnTCg&#39;;return true;" onclick="this.href=&#39;https://www.google.com/url?q\x3dhttps%3A%2F%2Fstackoverflow.com%2Fquestions%2F37348807%2Fdata-type-unknown-in-case-expression-with-only-parameters-as-values\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNEPPK42OFcLg9n9OKOOKAsH2dnTCg&#39;;return true;">https://stackoverflow.com/questions/37348807/data-type-unknown-in-case-expression-with-only-parameters-as-values

 

--
You received this message because you are subscribed to the Google Groups "Django developers (Contributions to Django itself)" 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/django-developers.
To view this discussion on the web visit https://groups.google.com/d/msgid/django-developers/a378e035-d12e-473c-8da1-d6f1190ce3b3%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.