[SOLVED] SQLAlchemy: Invalid SQL with load_only, order

SQLAlchemy: Invalid SQL with load_only, order_by and limit

When running the example below, the following, invalid SQL query is generated:

SELECT anon_1.venue_id              AS anon_1_venue_id, 
    St_asbinary(anon_1.venue_location)  AS anon_1_venue_location, 
    St_asbinary(anon_1.anon_2)      AS anon_1_anon_2, 
    label_1.id              AS label_1_id 
FROM    (
    SELECT venue.id AS venue_id, 
               venue.location AS venue_location, 
               venue.location <-> St_geomfromtext(:ST_GeomFromText_1, 
                  :ST_GeomFromText_2) AS anon_2 
    FROM   venue 
    ORDER  BY venue.location <-> St_geomfromtext(:ST_GeomFromText_1, 
                     :ST_GeomFromText_2) 
    LIMIT  :param_1
    ) AS anon_1 
LEFT OUTER JOIN (
    venue_to_label AS venue_to_label_1 
    JOIN label AS label_1 
    ON label_1.id = venue_to_label_1.label_id) 
ON anon_1.venue_id = venue_to_label_1.venue_id 
ORDER  BY anon_1.anon_2

The problem is that St_asbinary is applied to anon_1.anon_2. I'd expect the line either not to be generated or at least without the "St_asbinary". I'm pretty sure that this is the fault of GeoAlchemy2. Can anyone comment on that assumption?

Any idea how to best resolve this issue? It seems pretty fundamental unfortunately. We're trying to use the code in a big project and any help is welcome!

The (minimal) example below assumes a local PostgreSQL database "tmp" with GIS extension installed.

import unittest
from geoalchemy2 import WKTElement, Geometry
from sqlalchemy import Table, Column, Integer, ForeignKey
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import joinedload, relationship, load_only
from flask import Flask
from flask.ext.sqlalchemy import SQLAlchemy

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = (
    'postgres://postgres:password@localhost:5432/tmp')
db = SQLAlchemy(app)

Base = declarative_base()

# many (venue) <-> many (label) mapping table
venue_to_label = Table(
    'venue_to_label', db.metadata,
    Column('venue_id', Integer, ForeignKey('venue.id'), primary_key=True),
    Column('label_id', Integer, ForeignKey('label.id'), primary_key=True)
)


class Label(db.Model):
    __tablename__ = 'label'
    id = Column(Integer, primary_key=True, nullable=False)


class Venue(db.Model):
    id = Column(Integer, primary_key=True, nullable=False)
    labels = relationship(Label, secondary=venue_to_label)
    location = Column(Geometry(geometry_type="POINT"), nullable=False)

db.create_all()


class TestGeoAlchemy2Bug(unittest.TestCase):

    def test_geo_alchemy2_bug(self):
        point = WKTElement("POINT(0 0)")

        query = Venue.query
        query = query.options(joinedload(*['labels']).load_only(*['id']))
        query = query.order_by(Venue.location.distance_centroid(point))
        query = query.limit(10)

        print query
        print query.all()

Disclaimer: I've already posted the problem as an issue on the GeoAlchemy2 github page, but haven't gotten any reply there yet (https://github.com/geoalchemy/geoalchemy2/issues/93).

Even some general advice which direction I should be looking is very much appreciated!

Thank you for your help!

Update

Resolved this for now by creating the "missing" function when the database is created with:

CREATE FUNCTION St_asbinary(double precision)
    RETURNS double precision
AS 'select $1;'
LANGUAGE SQL
IMMUTABLE
RETURNS NULL ON NULL INPUT;

Still very interested in a proper solution!

Solution