Add-ons
  1. Add-ons
  2. CONTRIB-3480

Unknown column 'f.id' in 'where clause'

    Details

    • Type: Bug Bug
    • Status: Resolved
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: 2.1.1
    • Fix Version/s: None
    • Component/s: None
    • Labels:
    • Database:
      MySQL
    • Affected Branches:
      MOODLE_21_STABLE
    • Rank:
      38312

      Description

      Debug info: Unknown column 'f.id' in 'where clause'

      SELECT
      f.id as f_id,f.course as f_course,f.name as f_name,f.type as f_type,f.intro as f_intro,f.ratingscale as f_ratingscale,f.ratingfrom as f_ratingfrom,f.ratinguntil as f_ratinguntil,f.grading as f_grading,f.attachmentmaxbytes as f_attachmentmaxbytes,f.reportingemail as f_reportingemail,f.subscription as f_subscription,f.feedtype as f_feedtype,f.feeditems as f_feeditems,f.maxpostsperiod as f_maxpostsperiod,f.maxpostsblock as f_maxpostsblock,f.postingfrom as f_postingfrom,f.postinguntil as f_postinguntil,f.typedata as f_typedata,f.magicnumber as f_magicnumber,f.originalcmid as f_originalcmid,f.shared as f_shared,
      cm.id as cm_id,cm.course as cm_course,cm.module as cm_module,cm.instance as cm_instance,cm.section as cm_section,cm.added as cm_added,cm.score as cm_score,cm.indent as cm_indent,cm.visible as cm_visible,cm.visibleold as cm_visibleold,cm.groupmode as cm_groupmode,cm.groupingid as cm_groupingid,cm.idnumber as cm_idnumber,cm.groupmembersonly as cm_groupmembersonly,cm.completion as cm_completion,cm.completiongradeitemnumber as cm_completiongradeitemnumber,cm.completionview as cm_completionview,cm.completionexpected as cm_completionexpected,cm.availablefrom as cm_availablefrom,cm.availableuntil as cm_availableuntil,cm.showavailability as cm_showavailability,
      c.id as c_id,c.shortname as c_shortname,c.fullname as c_fullname,
      (SELECT COUNT(1)
      FROM mdl_forumng_discussions cfd
      WHERE cfd.forumngid = f.id AND cfd.deleted = 0
      AND (
      ((cfd.timestart = 0 OR cfd.timestart <= ?)
      AND (cfd.timeend = 0 OR cfd.timeend > ?))
      OR (cfd.forumngid = ?)
      )
      ) AS f_numdiscussions,

      (SELECT
      COUNT(1)
      FROM (
      SELECT
      1

      FROM
      mdl_forumng_discussions fd
      INNER JOIN mdl_forumng_posts fplast ON fd.lastpostid = fplast.id
      INNER JOIN mdl_forumng_posts fpfirst ON fd.postid = fpfirst.id
      LEFT JOIN mdl_forumng_read fr ON fd.id = fr.discussionid AND fr.userid = ?
      WHERE
      fd.forumngid = f.id AND fplast.modified>?
      AND (
      (fd.groupid IS NULL)
      OR ((1=0))
      OR cm.groupmode = 2
      OR (fd.forumngid = ?)
      )
      AND fd.deleted = 0
      AND (
      ((fd.timestart = 0 OR fd.timestart <= ?)
      AND (fd.timeend = 0 OR fd.timeend > ?))
      OR (fd.forumngid = ?)
      )
      AND ((fplast.edituserid IS NOT NULL AND fplast.edituserid<>?)
      OR fplast.userid<>?)
      AND (fr.time IS NULL OR fplast.modified>fr.time)

      LIMIT 1) innerquery
      ) AS f_hasunreaddiscussions
      FROM
      mdl_forumng f
      INNER JOIN mdl_course_modules cm ON cm.instance = f.id
      AND cm.module = (SELECT id from mdl_modules WHERE name = 'forumng')
      INNER JOIN mdl_course c ON c.id = f.course
      WHERE
      cm.id = ?
      ORDER BY
      LOWER(f.name)
      [array (
      0 => 1329827034,
      1 => 1329827034,
      2 => '1',
      3 => '2',
      4 => 1324643034,
      5 => '1',
      6 => 1329827034,
      7 => 1329827034,
      8 => '1',
      9 => '2',
      10 => '2',
      11 => '2',
      )]
      Stack trace:
      line 391 of \lib\dml\moodle_database.php: dml_read_exception thrown
      line 790 of \lib\dml\mysqli_native_moodle_database.php: call to moodle_database->query_end()
      line 3167 of \mod\forumng\mod_forumng.php: call to mysqli_native_moodle_database->get_records_sql()
      line 3046 of \mod\forumng\mod_forumng.php: call to mod_forumng::query_forums()
      line 2895 of \mod\forumng\mod_forumng.php: call to mod_forumng::query_forums()
      line 370 of \mod\forumng\lib.php: call to mod_forumng::get_course_forums()
      line 930 of \lib\modinfolib.php: call to mod_forumng_cm_info_view()
      line 960 of \lib\modinfolib.php: call to cm_info->call_mod_function()
      line 569 of \lib\modinfolib.php: call to cm_info->obtain_view_data()
      line 1484 of \course\lib.php: call to cm_info->get_extra_classes()
      line 110 of \course\format\weeks\format.php: call to print_section()
      line 240 of \course\view.php: call to require()

        Activity

        Hide
        sumit negi added a comment -

        I got this error when I created an instance of Fourmng

        Show
        sumit negi added a comment - I got this error when I created an instance of Fourmng
        Hide
        Michael de Raadt added a comment -

        I've moved this from the Moodle project to the ForumNG component under Contrib.

        However, this report would be more useful if you could explain the circumstances that led to this error. How could someone reproduce it?

        I'll leave this in the hands of Sam.

        Show
        Michael de Raadt added a comment - I've moved this from the Moodle project to the ForumNG component under Contrib. However, this report would be more useful if you could explain the circumstances that led to this error. How could someone reproduce it? I'll leave this in the hands of Sam.
        Hide
        Sam Marshall added a comment -

        thanks Michael.

        I believe this is probably a MySQL problem. I don't currently have a MySQL install, so I'll probably see if I can get some MySQL user to tell me what's wrong with the query... (Other than that it is crazy long, which I can already see.)

        Show
        Sam Marshall added a comment - thanks Michael. I believe this is probably a MySQL problem. I don't currently have a MySQL install, so I'll probably see if I can get some MySQL user to tell me what's wrong with the query... (Other than that it is crazy long, which I can already see.)
        Hide
        Sam Marshall added a comment -
        Show
        Sam Marshall added a comment - Blog post requesting community help: http://learn.open.ac.uk/mod/oublog/viewpost.php?post=104925
        Hide
        gavin henrick added a comment -

        Hi,

        To confirm:

        Add forumng to site - works
        Add forumng to a course, and when you next try to view the course page, that error kills it.

        Show
        gavin henrick added a comment - Hi, To confirm: Add forumng to site - works Add forumng to a course, and when you next try to view the course page, that error kills it.
        Hide
        Sam Marshall added a comment -

        Thanks Gavin - just to confirm, this is on MySQL 5.5?

        Show
        Sam Marshall added a comment - Thanks Gavin - just to confirm, this is on MySQL 5.5?
        Hide
        gavin henrick added a comment -

        nope, was on 5.1.41-3

        Show
        gavin henrick added a comment - nope, was on 5.1.41-3
        Hide
        Brian King added a comment - - edited

        This simpler scenario demonstrates the same problem. This will work on postgres, but fail on mysql (tested on 5.1.49-3 (Debian)):

        create table foo (id int);
        create table bar(id int, fooid int);
        
        insert into foo values(1);
        insert into foo values(2);
        insert into bar values(1,2);
        insert into bar values(2,2);
        
        select f.id,
               (select count(1) 
                from (select b.id from bar b where b.fooid = f.id) foobarids
               ) foobarcount 
            from foo f;
        

        MySQL doesn't grok that that innermost f.id refers to the the f.id two nested levels up. I guess this is a mysql limitation for column alias subqueries.

        For this simple scenario, the following query does work for mysql (here, the inner f.id only has to look up one level):

        select f.id, (select count(1) from bar b where b.fooid = f.id) foobarcount from foo f;
        

        Based on the above, I believe the following would fix your problem (but I have neither forumNG nor Moodle installed on the computer I'm on now):

        Replace:

               (SELECT COUNT(1) 
                FROM   (SELECT 1
                        FROM   mdl_forumng_discussions fd 
                               INNER JOIN mdl_forumng_posts fplast 
                                 ON fd.lastpostid = fplast.id 
                               INNER JOIN mdl_forumng_posts fpfirst 
                                 ON fd.postid = fpfirst.id
                               LEFT JOIN mdl_forumng_read fr 
                                 ON fd.id = fr.discussionid 
                                    AND fr.userid = ? 
                        WHERE  fd.forumngid = f.id 
                               AND fplast.modified >?
                               AND ( ( fd.groupid IS NULL ) 
                                      OR (( 1 = 0 )) 
                                      OR cm.groupmode = 2 
                                      OR ( fd.forumngid = ? ) ) 
                               AND fd.deleted = 0
                               AND ( ( ( fd.timestart = 0 
                                          OR fd.timestart <= ? ) 
                                       AND ( fd.timeend = 0 
                                              OR fd.timeend > ? ) ) 
                                      OR ( fd.forumngid = ? ) )
                               AND ( ( fplast.edituserid IS NOT NULL 
                                       AND fplast.edituserid <>? )
                                      OR fplast.userid <>? )
                               AND ( fr.TIME IS NULL
                                      OR fplast.modified > fr.TIME )
                        LIMIT  1) innerquery)             AS f_hasunreaddiscussions
        

        with:

               (SELECT COUNT(1) > 0
                        FROM   mdl_forumng_discussions fd
                               INNER JOIN mdl_forumng_posts fplast
                                 ON fd.lastpostid = fplast.id
                               INNER JOIN mdl_forumng_posts fpfirst
                                 ON fd.postid = fpfirst.id
                               LEFT JOIN mdl_forumng_read fr
                                 ON fd.id = fr.discussionid
                                    AND fr.userid = ?
                        WHERE  fd.forumngid = f.id
                               AND fplast.modified >?
                               AND ( ( fd.groupid IS NULL )
                                      OR (( 1 = 0 ))
                                      OR cm.groupmode = 2
                                      OR ( fd.forumngid = ? ) )
                               AND fd.deleted = 0
                               AND ( ( ( fd.timestart = 0
                                          OR fd.timestart <= ? )
                                       AND ( fd.timeend = 0
                                              OR fd.timeend > ? ) )
                                      OR ( fd.forumngid = ? ) )
                               AND ( ( fplast.edituserid IS NOT NULL
                                       AND fplast.edituserid <>? )
                                      OR fplast.userid <>? )
                               AND ( fr.TIME IS NULL
                                      OR fplast.modified > fr.TIME )
                        )             AS f_hasunreaddiscussions
        
        Show
        Brian King added a comment - - edited This simpler scenario demonstrates the same problem. This will work on postgres, but fail on mysql (tested on 5.1.49-3 (Debian)): create table foo (id int ); create table bar(id int , fooid int ); insert into foo values(1); insert into foo values(2); insert into bar values(1,2); insert into bar values(2,2); select f.id, (select count(1) from (select b.id from bar b where b.fooid = f.id) foobarids ) foobarcount from foo f; MySQL doesn't grok that that innermost f.id refers to the the f.id two nested levels up. I guess this is a mysql limitation for column alias subqueries. For this simple scenario, the following query does work for mysql (here, the inner f.id only has to look up one level): select f.id, (select count(1) from bar b where b.fooid = f.id) foobarcount from foo f; Based on the above, I believe the following would fix your problem (but I have neither forumNG nor Moodle installed on the computer I'm on now): Replace: (SELECT COUNT(1) FROM (SELECT 1 FROM mdl_forumng_discussions fd INNER JOIN mdl_forumng_posts fplast ON fd.lastpostid = fplast.id INNER JOIN mdl_forumng_posts fpfirst ON fd.postid = fpfirst.id LEFT JOIN mdl_forumng_read fr ON fd.id = fr.discussionid AND fr.userid = ? WHERE fd.forumngid = f.id AND fplast.modified >? AND ( ( fd.groupid IS NULL ) OR (( 1 = 0 )) OR cm.groupmode = 2 OR ( fd.forumngid = ? ) ) AND fd.deleted = 0 AND ( ( ( fd.timestart = 0 OR fd.timestart <= ? ) AND ( fd.timeend = 0 OR fd.timeend > ? ) ) OR ( fd.forumngid = ? ) ) AND ( ( fplast.edituserid IS NOT NULL AND fplast.edituserid <>? ) OR fplast.userid <>? ) AND ( fr.TIME IS NULL OR fplast.modified > fr.TIME ) LIMIT 1) innerquery) AS f_hasunreaddiscussions with: (SELECT COUNT(1) > 0 FROM mdl_forumng_discussions fd INNER JOIN mdl_forumng_posts fplast ON fd.lastpostid = fplast.id INNER JOIN mdl_forumng_posts fpfirst ON fd.postid = fpfirst.id LEFT JOIN mdl_forumng_read fr ON fd.id = fr.discussionid AND fr.userid = ? WHERE fd.forumngid = f.id AND fplast.modified >? AND ( ( fd.groupid IS NULL ) OR (( 1 = 0 )) OR cm.groupmode = 2 OR ( fd.forumngid = ? ) ) AND fd.deleted = 0 AND ( ( ( fd.timestart = 0 OR fd.timestart <= ? ) AND ( fd.timeend = 0 OR fd.timeend > ? ) ) OR ( fd.forumngid = ? ) ) AND ( ( fplast.edituserid IS NOT NULL AND fplast.edituserid <>? ) OR fplast.userid <>? ) AND ( fr.TIME IS NULL OR fplast.modified > fr.TIME ) ) AS f_hasunreaddiscussions
        Hide
        Brian King added a comment -

        Interestingly,

        select f.id, (select exists( select 1  from bar b where b.fooid = f.id)) foobarcount from foo f;
        

        also works fine on mysql and postgres. It could be more efficient than count(1) > 0. Just beware: postgres returns a boolean value, and mysql an int (0 or 1).

        Show
        Brian King added a comment - Interestingly, select f.id, (select exists( select 1 from bar b where b.fooid = f.id)) foobarcount from foo f; also works fine on mysql and postgres. It could be more efficient than count(1) > 0. Just beware: postgres returns a boolean value, and mysql an int (0 or 1).
        Hide
        Sam Marshall added a comment -

        Brian, thanks very much for your help! I'll try to do a version with this soon.

        As you can probably tell, the select with limit 1 was intended to achieve the same performance improvement that the EXISTS does, but EXISTS is a clearer way to do it, so if that works (albeit for inexplicable reasons) it is definitely the way to go.

        Show
        Sam Marshall added a comment - Brian, thanks very much for your help! I'll try to do a version with this soon. As you can probably tell, the select with limit 1 was intended to achieve the same performance improvement that the EXISTS does, but EXISTS is a clearer way to do it, so if that works (albeit for inexplicable reasons) it is definitely the way to go.
        Hide
        sumit negi added a comment -

        Sql Query is generated dynamically ,so please tell me how can I made changes in it as you suggested me Brian King.

        Show
        sumit negi added a comment - Sql Query is generated dynamically ,so please tell me how can I made changes in it as you suggested me Brian King.
        Hide
        Sam Marshall added a comment -

        Sumit: You can't easily make those changes. I am going to produce a new version of ForumNG hopefully on Monday or Tuesday with this fix incorporated. (Busy with another project/problem at the minute.)

        Show
        Sam Marshall added a comment - Sumit: You can't easily make those changes. I am going to produce a new version of ForumNG hopefully on Monday or Tuesday with this fix incorporated. (Busy with another project/problem at the minute.)
        Hide
        Sam Marshall added a comment -

        Optimistically, I'm now resolving this as 'fixed', although I haven't tested the new code on MySQL.

        I did test it still works on Postgres, including the code to make sure that it accepts 'f' as false (0).

        The new version uses EXISTS (which means the subquery is only 'one deep' so it hopefully should work in MySQL) as per above discussion.

        It is now available on our GitHub site:

        https://github.com/moodleou/moodle-mod_forumng

        Obviously this is based on Brian telling me how to do it Thanks. Hope this works.

        Show
        Sam Marshall added a comment - Optimistically, I'm now resolving this as 'fixed', although I haven't tested the new code on MySQL. I did test it still works on Postgres, including the code to make sure that it accepts 'f' as false (0). The new version uses EXISTS (which means the subquery is only 'one deep' so it hopefully should work in MySQL) as per above discussion. It is now available on our GitHub site: https://github.com/moodleou/moodle-mod_forumng Obviously this is based on Brian telling me how to do it Thanks. Hope this works.
        Hide
        sumit negi added a comment -

        Thanks sam I got updated version of Fourmng.I did not get Sql error and Forumng instance is created successfully but I want to know the workflow of Fourmng. can you provide me user guide to grasp functionality of Forumng.

        Show
        sumit negi added a comment - Thanks sam I got updated version of Fourmng.I did not get Sql error and Forumng instance is created successfully but I want to know the workflow of Fourmng. can you provide me user guide to grasp functionality of Forumng.
        Hide
        Sam Marshall added a comment -

        Great, thanks for confirming it works.

        There isn't any documentation. Sorry.

        Show
        Sam Marshall added a comment - Great, thanks for confirming it works. There isn't any documentation. Sorry.
        Hide
        Nadav Kavalerchik added a comment -

        Works great! super thanks, Sam

        Show
        Nadav Kavalerchik added a comment - Works great! super thanks, Sam

          People

          • Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved:

              Development