Main | My View | View Issues | Change Log | Roadmap
  

View Issue Details [ Jump to Notes ] [ Issue History ] [ Print ]
IDProjectCategoryView StatusDate SubmittedLast Update
0001150Sphinxgeneralpublic2012-04-06 19:092013-01-29 16:51
Reporterbtimby 
Assigned Toshodan 
PrioritynormalSeverityminorReproducibilityalways
StatusclosedResolutionfixed 
Platformx86_64OSLinuxOS VersionFedora 16
Product Version2.0.2-beta 
Target VersionFixed in Version2.0.7-release 
Summary0001150: SQL Syntax support need improvement.
DescriptionI am using SphinxQL and RT indexes with Django ORM. The Django ORM generates SQL for use with MySQL. There are many cases where the produced SQL is valid, but not accepted by Sphinx. Most of these are simple cases that could probably be handled in Sphinx quite easily. The goal would be to bring SphinxQL syntax more in line with that of MySQL.

There are two major shortcomings that I have observed.

1. Quoted identifiers.

SphinxQL does not support backticks for quoting identities. For example, the following query is accepted by Sphinx.

mysql> DELETE FROM ftp_pathindex WHERE id IN (1, 2, 2);
Query OK, 0 rows affected (0.00 sec)

But adding backticks causes it to fail.

mysql> DELETE FROM ftp_pathindex WHERE `id` IN (1, 2, 2);
ERROR 1064 (42000): sphinxql: syntax error, unexpected IDENT, expecting ID near '`id` IN (1, 2, 2)'

The support for backticks is inconsistent, they are allowed some places, but not others. In the next section, I am able to use backticks without issue.

2. Parenthesis.

This query is accepted:

mysql> SELECT * FROM `ftp_pathindex` WHERE `size` > 10;
+------+--------+---------+---------+------+
| id | weight | size | site_id | date |
+------+--------+---------+---------+------+
| 11 | 1 | 33850 | 1 | 2012 |
...
| 132 | 1 | 32731 | 1 | 2012 |
+------+--------+---------+---------+------+

And using parenthesis fails:

mysql> SELECT * FROM `ftp_pathindex` WHERE (`size` > 10);
ERROR 1064 (42000): sphinxql: syntax error, unexpected '(', expecting IDENT (or 5 other tokens) near '(`size` > 10)'

This might be a bit harder to support as parenthesis are often used to group clauses together with different operators (AND, OR, etc.) However, even when only one operator is in use, the Django ORM uses parenthesis, making the queries incompatible.
Steps To ReproduceThis behaviour should be easy to reproduce with any RT index.
Additional InformationI would be happy to provide any additional information deemed useful.
TagsNo tags attached.
Attached Files

- Relationships
parent of 0001305closedTomat ERROR 1064 (42000) 
parent of 0000520closedkevg SphinxQL fails to parse queries with parenthesis 
related to 0001153new SQL Syntax support need improvement. 

-  Notes
(0002733)
btimby (reporter)
2012-04-06 20:17

 I wanted to mention that it seems like backticks are allowed in the following circumstances:

1. index/table names in any query:

SELECT * FROM `index`;

2. columns names in SELECT WHERE clause.

SELECT * FROM `index` WHERE `id` = 1;

But NOT column names for DELETE queries. I will try to find other instances where they are unsupported.
(0003828)
Tomat (manager)
2013-01-28 08:15

 don't work either
select id from idx where match('the') and (properties.prop3086 >= 20);
select id, properties.prop3086 as cnd from idx where match('the') and (cnd >= 20);

with error message
ERROR 1064 (42000): sphinxql: only >=, <=,<,>, and BETWEEN floating-point filter types are supported in this version near '(properties.prop3086 >= 20)

however removing brackets fix that issue
(0003848)
shodan (administrator)
2013-01-29 15:52

 Fixed in trunk, r3636 (might utilize backporting to 2.0.x though).
(0003849)
shodan (administrator)
2013-01-29 16:51

 Fixed in rel20, r3638, too.

- Issue History
Date Modified Username Field Change
2012-04-06 19:09 btimby New Issue
2012-04-06 19:28 shodan Assigned To => shodan
2012-04-06 19:28 shodan Status new => assigned
2012-04-06 20:17 btimby Note Added: 0002733
2012-09-24 10:08 Tomat Relationship added parent of 0001305
2012-09-26 14:40 kevg Relationship added parent of 0000520
2012-10-15 05:01 kevg Relationship added related to 0001153
2013-01-28 08:15 Tomat Note Added: 0003828
2013-01-29 15:52 shodan Note Added: 0003848
2013-01-29 15:52 shodan Status assigned => resolved
2013-01-29 15:52 shodan Fixed in Version => 2.1.1-beta
2013-01-29 15:52 shodan Resolution open => fixed
2013-01-29 16:51 shodan Note Added: 0003849
2013-01-29 16:51 shodan Status resolved => closed
2013-01-29 16:51 shodan Fixed in Version 2.1.1-beta => 2.0.7-release

Copyright © 2000 - 2010 MantisBT Group
Powered by Mantis Bugtracker