Aggregate functions are not allowed in this query

Written by on September 22, 2017, 11:19 am

Got burnt today by an odd error thrown by the Django ORM when writing some tests. It was an easy mistake to make and the exception "Aggregate functions are not allowed in this query" has lots of unrelated Google juice and was not helpful in getting to the bottom of the problem. Hence this post.

Start with a pretty simple model that we are going to be updating via an API.

1
2
3
class Foo(models.Model):
    name = models.CharField(max_length=512)
    value = models.CharField(max_length=512)

Then write some code to hit an API via requests and update the database with a new value.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
import requests
from .models import Foo


def make_request():

    # go get foo from your api
    resp = requests.get("http://your.thing/api/foo/name")

    # the name and value of the foo?
    value = resp.json()["value"]

    # Grab the instance of foo corresponding to name
    # and update its value
    foo = Foo.objects.get(name="name")
    foo.value = value
    foo.save()

Then a test using a patch on yourthing.helpers.requests so we don't hit the real API when we run our test. Let's pretend that make_requets has some side effect that we are testing for rather than the value stored in the Foo table. Given that assumption we might not mock the actual values returned by the API (see THIS --->).

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
from mock import Mock, patch

from django.test import TestCase

from .models import Foo
from .helpers import make_request


class MockTestCase(TestCase):

    @patch('yourthing.helpers.requests')
    def test_an_api(self, mock_requests):

        # Create the instance we are going to update
        Foo.objects.create(name="name", value=0)

        # THIS ---> mock_requests.get.return_value.json.return_value = { "value" : 1}
        make_request()

        # Test for something unrelated to the contents of the Foo table. Perhaps some other
        # side effect of the make_requests() call.

Run this test and you will get this error.

======================================================================
ERROR: test_an_api (yourthing.test_helpers.MockTestCase)
----------------------------------------------------------------------
Traceback (most recent call last):
File "/Users/tomhorn/environments/test_mock/lib/python3.6/site-packages/mock/mock.py", line 1305, in patched
return func(*args, **keywargs)
File "/Users/tomhorn/dev/sandbox/test_mock/yourthing/test_helpers.py", line 18, in test_an_api
make_request()
File "/Users/tomhorn/dev/sandbox/test_mock/yourthing/helpers.py", line 17, in make_request
foo.save()
File "/Users/tomhorn/environments/test_mock/lib/python3.6/site-packages/django/db/models/base.py", line 807, in save
force_update=force_update, update_fields=update_fields)
File "/Users/tomhorn/environments/test_mock/lib/python3.6/site-packages/django/db/models/base.py", line 837, in save_base
updated = self._save_table(raw, cls, force_insert, force_update, using, update_fields)
File "/Users/tomhorn/environments/test_mock/lib/python3.6/site-packages/django/db/models/base.py", line 904, in _save_table
forced_update)
File "/Users/tomhorn/environments/test_mock/lib/python3.6/site-packages/django/db/models/base.py", line 954, in _do_update
return filtered._update(values) > 0
File "/Users/tomhorn/environments/test_mock/lib/python3.6/site-packages/django/db/models/query.py", line 664, in _update
return query.get_compiler(self.db).execute_sql(CURSOR)
File "/Users/tomhorn/environments/test_mock/lib/python3.6/site-packages/django/db/models/sql/compiler.py", line 1191, in execute_sql
cursor = super(SQLUpdateCompiler, self).execute_sql(result_type)
File "/Users/tomhorn/environments/test_mock/lib/python3.6/site-packages/django/db/models/sql/compiler.py", line 863, in execute_sql
sql, params = self.as_sql()
File "/Users/tomhorn/environments/test_mock/lib/python3.6/site-packages/django/db/models/sql/compiler.py", line 1143, in as_sql
raise FieldError("Aggregate functions are not allowed in this query")
django.core.exceptions.FieldError: Aggregate functions are not allowed in this query
----------------------------------------------------------------------

This unhelpful error might lead us to examine queries & relationship looking for an aggregate function. In fact there is no aggregate function. Instead this is happening because without an explict mock the resp.json()["value"] call in make_request will return a MagicMock instead of a string. When the ORM tries to save the instance it gets into trouble dealing with a field that will mock any property it tries to access.

1
<MagicMock name='requests.get().json().__getitem__()' id='4385055128'>

So I guess the moral of the story is to make sure you mock every response that ends up being passed to a Django ORM model instance. If you don't you might spent a chunk of time investigating rabbit holes as I did.

Permalink - Comments - Tags: Development,Django

A Postal Plebiscite

Written by on September 5, 2017, 11:39 am

Tomorrow the high court of Australia will hear the government's arguments in favour of the constitutionality of the ABS run postal plebiscite. I need to write something down as I have feelings about this bullshit.

There is no "civilised debate" on marriage equality. There are bigots who are on the wrong side of history, willing to do real harm to delay the inevitable erosion of one more roadblock on the path from pre-scientific superstition to enlightened humanism.

They are wrong and they are disingenuous, but it's the 'real harm' that really gets my internal monologue extra sweary. There is a connection between this "civilised debate" and increased rates of suicide amongst young LGBT+ people. These fuckers know this and apparently they don't care.

If this plebiscite goes ahead innocent people will get hurt. A postal plebiscite will be gamed, it will be unrepresentative and even if it results in a resounding YES, this government can just ignore it.

So fuck those guys. Fuck the arseholes campaigning against marriage equality. Fuck their crappy, misdirected, straw man arguments and their dire predictions about the end of society as we know it if we let LGBT+ people get married.

Also fuck this spineless government who won't step up and take a free vote on same sex marriage. I hope this thing is killed tomorrow and our government is forced to do just that.

Permalink - Comments - Tags: misc

clojure.pprint/cl-format is slow

Written by on June 5, 2016, 12:51 pm

Had a serious performance problem last week. After some digging we narrowed it down to a call to cl-format.

Some test code to demonstrate the discrepancy:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
;; Format a string, an integer, a conditional and fancy plural thing
(defn cl-format-test [] (clojure.pprint/cl-format nil "
    [email protected][(Id: ~D)~] ~:[foo~;bar~] [~D second~:P ago]>"
                                      (java.util.UUID/randomUUID)
                                      (rand 1)
                                      (> (rand 2))
                                      (rand 100)))
;; Same inputs, just concat them and return the string
(defn str-test [] (str (java.util.UUID/randomUUID) " "
                            (rand 1) " "
                            (> (rand 2)) " "
                            (rand 100)))
;; Just format a string
(defn cl-format-simple-test [] (clojure.pprint/cl-format nil "~S" (java.util.UUID/randomUUID)))
;; Just return the string
(defn str-simple-test [] (str (java.util.UUID/randomUUID)))

(defn test-func [f tests] (time (doseq [_ (range 0 tests)] (f))))

(test-func cl-format-test 1000)
(test-func str-test 1000)
(test-func cl-format-simple-test 1000)
(test-func str-simple-test 1000)

With the following results. For the simple case, cl-format is 10 times slower than a simple string return. For more complicated format strings, closer to 100 times slower:

(test-func cl-format-test 1000)
"Elapsed time: 328.033183 msecs"
(test-func str-test 1000)
"Elapsed time: 5.939625 msecs"
(test-func cl-format-simple-test 1000)
"Elapsed time: 34.2998 msecs"
(test-func str-simple-test 1000)
"Elapsed time: 3.515013 msecs"

Having a quick look at the implementation of cl-format I immediately noticed a compile-format function which seems like something that might help out with performance for many calls to a cl-format with the same format string:

Compiles format-str into a compiled format which can be used as an argument to cl-format just like a plain format string. Use this function for improved performance when you're using the same format string repeatedly

It also seems like cl-format will check if it's format string is already compiled and skip compilation if that is the case:

(if (string? format-in) (compile-format format-in) format-in)

Oddly however, compile-format is not public, so I can't use it. So I am left a little confused. I am going to do some more digging tomorrow to solve this mystery as I am sure I am missing something here.

Not sure if pre-compilation will help solve the horrible performance you get from cl-format, but I am guessing it might help.

Update

After some discussion with my colleagues, it seems I haven't missed anything and we think this is a bug.

Permalink - Comments - Tags: Development,Clojure

Decoding Polylines from Google Maps Direction API with Clojure

Written by on January 17, 2016, 12:56 pm

My first crack at porting some imperative code to Clojure goodness.

I needed some code to turn the polyline points encoding you get back from Google Directions API. This stuff:

"overview_polyline" : {
            "points" : "[email protected]|FsBpG_C_BqA}@mGeEwH_FsDcCaGwDkNiJsJmG_SoMyByAzA}[email protected]{[email protected]@@[email protected]}@{[email protected][email protected]]^[email protected]|AcC|[email protected]@[email protected]`AU~A_ChH_D|[email protected]|@[email protected]@[email protected]@[email protected]@Z^X^[email protected][email protected]`@lA^`@[email protected]@[email protected]@`@[email protected]@[email protected]^[email protected]@[email protected]][email protected]@}@[email protected]@qC][email protected]@@eAT[L[[email protected]@SZQ^[email protected]`XoAbEmB~GqAvDkDzIsFvNcAvCcFrRkBnHcE`[email protected][email protected]@hA}@[email protected]@[email protected]|@[email protected]@[email protected]`@aB{[email protected]@yAmE`A}[email protected][email protected]{BhCoBtCuJrOkAxAkBzCeH~KkAlBeMrR{I|M}CtEyBjD}EpHmLbQ{[email protected]@[email protected]@HiABuKT_BEiBUaEkAcA][email protected]@][email protected]@[email protected][email protected]@^`@[email protected]@[email protected]@[email protected]@[email protected]@[email protected]@[email protected]@G^[email protected]@[email protected]@Z`[email protected]@Q"

I found Jeffrey Sambells' Java code here that does the job. I am just getting my teeth into writing Clojure for my day job at GoCatch, so I need a Clojure version. This is my first attempt. It's midnight and I haven't had a chance to check the line ends up on a map correctly, but it looks pretty good to me:

;; Port of the Java code to decode google polylines that I found here -> http://jeffreysambells.com/2010/05/27/decoding-polylines-from-google-maps-direction-api-with-java
(defn decode-next-result [encoded]
  ;; keep scanning through encoded till b>=0x20
  ;; returns the next latitude/longitude increment
  (loop [[current & rest] encoded shift 0 result 0]
    (let [b       (- (int current) 63)
          result  (bit-or result (bit-shift-left (bit-and b 0x1f) shift))
          shift   (+ shift 5)] 
      (if (>= b 0x20) 
        (do 
          ;; if we are encoding the next result then we 
          ;; must have more characters to scan
          (assert rest)
          ;; keep looking for our next result
          (recur rest shift result))
        ;; we found our next result 
        (let [return-value (if (not= (bit-and result 1) 0)
                             (bit-not (bit-shift-right result 1))
                             (bit-shift-right result 1))]
          [return-value rest])))))

(defn lat-lng-double [lat-lng-int]
  (/ lat-lng-int 1E5))

(defn path-for-encoded-polyline [encoded] 
  (loop [rest encoded lat 0 lng 0 results []]
    (if rest
      ;; if there is anthing in the encoded array
      ;; we should have two more results at least 
      (let [next-result       (decode-next-result rest)
            new-rest          (second next-result)
            next-lat-result   (+ lat (first next-result))
            next-result       (decode-next-result new-rest )
            new-rest          (second next-result)
            next-lng-result   (+ lng (first next-result))]
          ;; add our lat lng result to the results
          (recur new-rest next-lat-result next-lng-result (conj 
                                                            results 
                                                            {:latitude (lat-lng-double next-lat-result) 
                                                             :longitude (lat-lng-double next-lng-result)})))
      ;; we are done, return our results
      results)))

#_
(def example-polyline "pdymEssfy[rJhAlANNeALyBD{@XqFBW^[email protected]@[email protected]@[email protected]@[email protected]{@[email protected]@[email protected][email protected][email protected]@[email protected]`[email protected]@[email protected]@[[email protected]@[email protected]@][email protected]}DmBwD{[email protected]@}[email protected]@wDK}@@[[email protected]}[email protected]@[email protected]{[email protected]@[email protected]@[email protected]@{[email protected]{[email protected]^[email protected]}AdAcA|@[email protected]@[email protected]@Y|[email protected]@[email protected]@[email protected]@[email protected]}@[email protected]@eCVeDRiGF}FGiB[[email protected]{[email protected]@[email protected]@[email protected]@uByA}[email protected]{[email protected]@[email protected]@[email protected]@}[email protected]@[email protected][email protected][email protected]@kD^}A`@uA`CiHPcABYB[[email protected]@][email protected]@[email protected]@[email protected]@[email protected]][email protected][email protected]][email protected]@[email protected]@{@[email protected]@[email protected][email protected]@[email protected]@GK][email protected]@[email protected]}@[email protected]@[email protected][[email protected][UQ[[email protected]_DKsCCcBJcDcCIuBK")
#_
(path-for-encoded-polyline (seq (char-array example-polyline)))


Permalink - Comments - Tags: Development,Clojure

Hundreds Chart Dev Diary

Written by on June 28, 2015, 12:32 am

I've had this idea for a game to teach kids times tables since I finished Wordflight two years go. I got bogged down in my initial implementation using OpenGL ES and a few weeks ago I decided to re-boot the project with flat UI, vivid colours and UIDynamics to make things interesting.

This time the project took two solid weekends (and a few late nights), but I am pretty happy with how it turned out


If you are reading this and you live in Australia, make sure you check out goCatch next time you need to catch a taxi.

Permalink - Comments - Tags: Development,iPhone,App

Tags

Archive