In Between causes my query to return "wrong" values
Your query:

SELECT resources.*, chunks.x, chunks.y 
FROM resources, chunks 
where chunk_ID in (SELECT ID 
                   FROM chunks 
                   where x between 17185-1 and 17185+1 
                     and y between 10846-1 and 10846+1) 
group by ID


I see several issues:


The two tables in the FROM clause are not joined (that's the reason for the irrelevant results you get, the two tables are correlated through the subquery* but the FROM clause introduces an additional non-correlated chunks reference which produces all these unrelated results):

FROM resources, chunks 


Join them:

FROM resources JOIN chunks ON resources.chunk_id = chunks.id 

Since chunks is in the FROM clause, we don't need* the subquery no more, to check x and y:

where chunk_ID in (SELECT ID 
                   FROM chunks 
                   where x between 17185-1 and 17185+1 
                     and y between 10846-1 and 10846+1) 


Simplify WHERE:

WHERE chunks.x BETWEEN 17185-1 AND 17185+1 
  AND chunks.y BETWEEN 10846-1 AND 10846+1

You have SELECT * combined with GROUP BY. This is a recipe for spurious, wrong results.

Just remove the GROUP BY.


The query becomes after using all the above comments (and some polishing with table aliases):

-- Suggested query
SELECT r.*, c.x, c.y 
FROM resources AS r
     JOIN chunks AS c
     ON r.chunk_id = c.id  
WHERE c.x BETWEEN 17185-1 AND 17185+1 
  AND c.y BETWEEN 10846-1 AND 10846+1 ;




* Just for completeness, we could have kept the IN subquery and remove chunks from the FROM clause.

This would have the restriction that we couldn't have any column from chunks in the SELECT list - and more likely worse performance than the JOIN query:

-- Don't use this one
SELECT r.* 
FROM resources AS r
WHERE r.chunk_d IN 
      ( SELECT c.id
        FROM chunks AS c
        WHERE c.x BETWEEN 17185-1 AND 17185+1
          AND c.y BETWEEN 10846-1 AND 10846+1
      ) ;