ORDER BY support

[netfl0]

sphinx again has issues with the sql statement.

In [19]: EntryIndex.objects.all().order_by('-date')

errors:

DatabaseError: (1064, "sphinxql: syntax error, unexpected CONST_FLOAT, expecting $end near '.`date` A
SC LIMIT 21'")

i debugged the query sent to sphinx a bit:

In [27]: d.queries[0]['sql']

Out[27]: 'SELECT `id`, `date` FROM nfeeder_entryindex ORDER BY nfeeder_entryindex.`date` ASC LIMIT 21'

This doesn't work:

mysql> select `id`, `date` from app_modelindex order by app_MODELindex.date ASC LIMIT 2000;

These do however:

mysql> select `id`, `date` from app_modelindex order by date ASC LIMIT 2000;

mysql> select `id`, `date` from app_modelindex order `by date` ASC LIMIT 2000;

[netfl0]

updated above with more detail.

[btimby]

So it looks like the problem is the table name in the column definition i.e. 'app_MODELindex.date' vs. 'date'.

Again, this is valid syntax that Sphinx is choking on. I will file a bug with them, while also removing the table names from the generated SQL.

This approach will make things work, the need for the table name in the column definition is in the case of multi-table queries (JOINs), which SphinxQL has no occasion to do anyway. So, we should not really be missing anything with the absence of the table names.

[btimby]

Add-on to comment above:

However, it would be nice if Sphinx simply "ate" these table names and ignored them. That would make it more compatible with SQL syntax.

[btimby]

Opened issue upstream.

http://sphinxsearch.com/bugs/view.php?id=1153

[netfl0]

I have not found the place in the sql api to pluck out the table names for a temporary hack, do you have any insight on this?

[btimby]

I will take a look and get back to you. I have a couple items to tick off today, after that I can dig into it.