Uploaded image for project: 'Plugins'
  1. Plugins
  2. CONTRIB-3480

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

    Details

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

      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()

        Gliffy Diagrams

          Activity

          Hide
          sumit.negi sumit negi added a comment -

          I got this error when I created an instance of Fourmng

          Show
          sumit.negi sumit negi added a comment - I got this error when I created an instance of Fourmng
          Hide
          salvetore 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
          salvetore 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
          quen 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
          quen 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
          quen Sam Marshall added a comment -
          Show
          quen Sam Marshall added a comment - Blog post requesting community help: http://learn.open.ac.uk/mod/oublog/viewpost.php?post=104925
          Hide
          ghenrick 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
          ghenrick 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
          quen Sam Marshall added a comment -

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

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

          nope, was on 5.1.41-3

          Show
          ghenrick gavin henrick added a comment - nope, was on 5.1.41-3
          Hide
          brianking 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
          brianking 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
          brianking 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
          brianking 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
          quen 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
          quen 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 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 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
          quen 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
          quen 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
          quen 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
          quen 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 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 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
          quen Sam Marshall added a comment -

          Great, thanks for confirming it works.

          There isn't any documentation. Sorry.

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

          Works great! super thanks, Sam

          Show
          nadavkav 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