That just looks like a bug. Yes you should add those two lines. Maybe you are in a better position to then debug the syntax error, if you have MySQL warmed up: does it work if you surround both sub-SELECTs with parentheses?
On Sat, Sep 17, 2011 at 12:08 AM, orbz <orb...@gmail.com> wrote: > Java and Mahout newbie, so if this is incorrect you'll know why > > I have the following setup: > > AbstractJDBCDataModel dataModel = new MySQLJDBCDataModel(dataSource); > DiffStorage diffStorage = new MySQLDiffStorage(dataModel); > Recommender recommender = new SlopeOneRecommender(dataModel, > Weighting.UNWEIGHTED, Weighting.UNWEIGHTED, diffStorage); > Recommender cachingRecommender = new CachingRecommender(recommender); > List<RecommendedItem> l = cachingRecommender.recommend(userId, 2); > > Everything works when using a MemoryDiffStorage, but when I exchange it for > the MySQLJDBCDiffStorage it complains about lacking the correct number of > parameters. > > Which makes sense (I think) as the "getDiffsSQL" generates SQL like this: > > SELECT count, average_diff, standard_deviation, item_id_a > FROM taste_slopeone_diffs, taste_preferences > WHERE item_id_b=? > AND item_id_a = item_id > AND user_id=? > ORDER BY item_id_a > UNION > SELECT count, -average_diff, standard_deviation, item_id_b > FROM taste_slopeone_diffs, taste_preferences > WHERE item_id_a=? > AND item_id_b = item_id > AND user_id=? > ORDER BY item_id_b > > But in AbstractJDBCDiffStorage in getDiffs only two parameters are > specified > > stmt = conn.prepareStatement(getDiffsSQL, ResultSet.TYPE_FORWARD_ONLY, > ResultSet.CONCUR_READ_ONLY); > stmt.setFetchDirection(ResultSet.FETCH_FORWARD); > stmt.setFetchSize(getFetchSize()); > stmt.setLong(1, itemID); > stmt.setLong(2, userID); > > Which results in an error `No value specified for parameter 3` > > But say we pass in two random numbers > > stmt.setLong(3, itemID); > stmt.setLong(4, userID); > > We'll now get an error `Incorrect usage of UNION and ORDER BY` > > Is this a bug or am I doing something wrong? >