0

I tried 

SELECT count(*) as count FROM rt_item WHERE MATCH('') AND sale between 1 and 2;

and got many rows. But sphinx complains

ERROR 1064 (42000): sphinxql: syntax error, unexpected BETWEEN, expecting IN near 'between 1 and 2'

when I tried

SELECT count(*) as count FROM rt_item WHERE MATCH('') AND sale not between 1 and 2;

I searched Sphinx official document, which says :

WHERE clause. This clause will map both to fulltext query and filters. Comparison operators (=, !=, <, >, <=, >=), IN, AND, NOT, and BETWEEN are all supported and map directly to filters. OR is not supported yet but will be in the future. MATCH('query') is supported and maps to fulltext query.

But it has no example about NOT BETWEEN. Can anybody tell me whether sphinxql support NOT BETWEEN? If if supports, what's the correct grammar?

chariothy
  • 320
  • 2
  • 8

1 Answers1

1

No, no NOT BETWEEN. There is a NOT IN() operator, which might help in this specific case 

SELECT count(*) as count FROM rt_item WHERE MATCH('') AND sale not in (1,2);

(gets cumbersome with long ranges)

It's a bit convoluted but can do 

SELECT count(*) as count,sale>=1 AND sale<=2 AS f FROM rt_item WHERE f=0

Create a virtual attribute to mimic a 'between', then just check for false.

Might also find 

SELECT count(*) as count,sale < 1 OR sale > 2 AS f FROM rt_item WHERE f=1

clearer. Inverts the logic.

(empty match() does nothing, can be omitted)

barryhunter
  • 20,886
  • 3
  • 30
  • 43
  • Thanks! Which confused me is that some operators are supported in mysql but not in sphinx, such as NOT BETWEEN and IN with float column. So it's hard to write a general query method. – chariothy Jun 14 '17 at 00:34
  • Well yes, mysql and sphinx are very differnt things. They have very different capabilities. Generally would need seperate functions. – barryhunter Jun 14 '17 at 11:57