It's the same on my production machine (Debian (Etch) 4.0, MySQL 5.0.32, Sphinx 0.9.8.1-release (r1533), thinking-sphinx (1.2.12)).
Luckily I was able to solve this on my development setup (MacBook). First I installed sphinx 0.99, then thinking-sphinx 1.3.8. But it was only after I updated MySQL to 5.1.41. I don't know what the problem with the old mysql version is, because the result set seems to be fine. And this solution isn't the best one, because I don't like it to use self compiled software in production when there are debian packages out there. Any way I'll able to find a solution for that problem on my own. Thank you very much Best regards Tobi On Dec 3, 3:21 am, Pat Allan <[email protected]> wrote: > Hmm, SQL seems fine... I was able to search for 1000 in my test setup > using both 0.9.8.1 and 0.9.9 though. I'm using MySQL 5.1.37. So I'm at > a loss on what the cause could be... maybe it's MySQL, but the data > seems correct. > > -- > Pat > > On 03/12/2009, at 1:05 PM, Tobias Schmidt wrote: > > > > > Hi Pat, > > > I'm using mysql on version 5.0.45. The sql_query is: > > > SELECT `users`.`id` * 1 + 0 AS `id` , CAST(`users`.`code` AS CHAR) AS > > `code`, `users`.`id` AS `sphinx_internal_id`, 765557111 AS > > `class_crc`, IFNULL('765557111', 0) AS `subclass_crcs`, 0 AS > > `sphinx_deleted`, GROUP_CONCAT(DISTINCT IFNULL(`addresses`.`id`, 0) > > SEPARATOR ',') AS `addresses_ids` FROM `users` LEFT OUTER JOIN > > `addresses` ON addresses.user_id = users.id WHERE `users`.`id` >= > > $start AND `users`.`id` <= $end GROUP BY `users`.`id` ORDER BY NULL > > > You can see the result for this query (with $start => 1 and $end => > > 1000) at my gist:http://gist.github.com/247213#file_sql_query_result.csv > > > thx, for your help. I'll try to check this problem on other machines > > with different mysql versions. > > Tobi > > > On Dec 3, 2:56 am, Pat Allan <[email protected]> wrote: > >> Hi Tobias > > >> I can't reproduce this on my machine, so that makes things a little > >> tricky... but first things first, what's the sql_query in your > >> user_core_0 source in the generated config file? What database are > >> you > >> using, and what version is it? > > >> -- > >> Pat > > >> On 03/12/2009, at 1:03 AM, Tobias Schmidt wrote: > > >>> Here is a more readable version:http://gist.github.com/247213 > > >>> Any help would be appreciated > > >>> On Dec 2, 1:08 am, Grobie <[email protected]> wrote: > >>>> Hi, > > >>>> I've encountered a problem with integer fields and associated > >>>> attributes. I can't search by the integer field for records with > >>>> more > >>>> than 3 digits as integer value. Here is a reduced example. > > >>>> script/generate model User code:integer > >>>> script/generate model Address user:references > > >>>> class User < ActiveRecord::Base > >>>> has_many :addresses > > >>>> define_index do > >>>> indexes :code > >>>> has addresses(:id), :as => :addresses_ids > >>>> end > >>>> end > > >>>> I create 3 records with [333, 999, 1000] as values for code. There > >>>> are > >>>> no associated addresses. After indexing the records and starting > >>>> the > >>>> searchd I open a console: > > >>>> script/console > >>>> >> User.all > >>>> User Load (0.4ms) SELECT * FROM `users` > >>>> => [#<User id: 1, code: 333, created_at: "2009-12-01 23:47:29", > >>>> updated_at: "2009-12-01 23:47:29">, > >>>> #<User id: 2, code: 999, created_at: "2009-12-01 23:47:34", > >>>> updated_at: "2009-12-01 23:47:34">, > >>>> #<User id: 3, code: 1000, created_at: "2009-12-01 23:47:36", > >>>> updated_at: "2009-12-01 23:47:36">] > >>>> >> User.search 333 > >>>> Querying Sphinx: 333 > >>>> User Load (0.3ms) SELECT * FROM `users` WHERE (`users`.`id` > >>>> IN > >>>> (1)) > >>>> => [#<User id: 1, code: 333, created_at: "2009-12-01 23:47:29", > >>>> updated_at: "2009-12-01 23:47:29">] > >>>> >> User.search 999 > >>>> Querying Sphinx: 999 > >>>> User Load (0.3ms) SELECT * FROM `users` WHERE (`users`.`id` > >>>> IN > >>>> (2)) > >>>> => [#<User id: 2, code: 999, created_at: "2009-12-01 23:47:34", > >>>> updated_at: "2009-12-01 23:47:34">] > >>>> >> User.search 1000 > >>>> Querying Sphinx: 1000 > >>>> => [] > > >>>> And here is my problem. I get no results for "1000". I can do a > >>>> search > >>>> for "100*" and get the one record. But this is not an option for my > >>>> production application. If there is no "has addresses(:id)" > >>>> attribute, > >>>> I can do search for 1000 and all is fine. > > >>>> I don't use any specific sphinx config. I use thinking-sphinx > >>>> (1.2.12), Sphinx 0.9.8.1-release (r1533) and Rails 2.3.4. I've > >>>> already > >>>> found a discussion for this problem, but there is no solution as > >>>> far I > >>>> can see. (http://groups.google.com/group/thinking-sphinx/browse_thread/ > >>>> thread/7c62eeb4944faef7/b2c1a13ea8bd95fb? > >>>> lnk=gst&q=123#b2c1a13ea8bd95fb). > > >>>> Can you reproduce the problem? Do you have any suggestions? > >>>> thanks > > >>> -- > > >>> You received this message because you are subscribed to the Google > >>> Groups "Thinking Sphinx" group. > >>> To post to this group, send email to [email protected] > >>> . > >>> To unsubscribe from this group, send email to > >>> [email protected] > >>> . > >>> For more options, visit this group > >>> athttp://groups.google.com/group/thinking-sphinx?hl=en > >>> . > > > -- > > > You received this message because you are subscribed to the Google > > Groups "Thinking Sphinx" group. > > To post to this group, send email to [email protected]. > > To unsubscribe from this group, send email to > > [email protected] > > . > > For more options, visit this group > > athttp://groups.google.com/group/thinking-sphinx?hl=en > > . -- You received this message because you are subscribed to the Google Groups "Thinking Sphinx" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/thinking-sphinx?hl=en.
