sammarshallou
commented
12 years ago This appears to be a limitation of SQL Server. Although the documentation does not say so, it appears you cannot use EXISTS in a SELECT field list. For example, this fails:
SELECT EXISTS (SELECT 1 FROM items)Most likely the solution is to change the SQL so that it does an explicit double-nested subquery, however this might cause problems for other databases that currently work, so I think (although it kind of sucks) we might have to code a check for SQL Server.
SELECT (SELECT 1 WHERE EXISTS(SELECT 1 FROM items));Not sure when I will have time to do this, I am about to go away for a week so not before then at least.
Hi
I created a forum and this database error occurred.
Debug info: SQLState: 42000
Error Code: 156
Message: [Microsoft][SQL Server Native Client 10.0][SQL Server]Incorrect syntax near the keyword 'EXISTS'.
SQLState: 42000
Error Code: 102
Message: [Microsoft][SQL Server Native Client 10.0][SQL Server]Incorrect syntax near ')'.
SQLState: 42000
Error Code: 156
Message: [Microsoft][SQL Server Native Client 10.0][SQL Server]Incorrect syntax near the keyword 'INNER'.
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 <= '1338455715') AND (cfd.timeend = 0 OR cfd.timeend > '1338455715')) OR (cfd.forumngid = '1') ) ) AS f_numdiscussions,
(EXISTS ( 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 = '3' WHERE fd.forumngid = f.id AND fplast.modified>'1333271715' AND ( (fd.groupid IS NULL) OR (fd.groupid IN ('1','10')) OR cm.groupmode = 2 OR (fd.forumngid = '1') ) AND fd.deleted = 0 AND ( ((fd.timestart = 0 OR fd.timestart <= '1338455715') AND (fd.timeend = 0 OR fd.timeend > '1338455715')) OR (fd.forumngid = '1') ) AND ((fplast.edituserid IS NOT NULL AND fplast.edituserid<>'3') OR fplast.userid<>'3') AND (fr.time IS NULL OR fplast.modified>fr.time)
) ) 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 f.course = '1' AND cm.id = '473' ORDER BY LOWER(f.name) [array ( 0 => 1338455715, 1 => 1338455715, 2 => '1', 3 => '3', 4 => 1333271715, 5 => '1', 6 => '10', 7 => '1', 8 => 1338455715, 9 => 1338455715, 10 => '1', 11 => '3', 12 => '3', 13 => '1', 14 => '473', )] Stack trace: o line 394 of \lib\dml\moodle_database.php: dml_read_exception thrown o line 256 of \lib\dml\sqlsrv_native_moodle_database.php: call to moodle_database->query_end() o line 372 of \lib\dml\sqlsrv_native_moodle_database.php: call to sqlsrv_native_moodle_database->query_end() o line 777 of \lib\dml\sqlsrv_native_moodle_database.php: call to sqlsrv_native_moodle_database->do_query() o line 811 of \lib\dml\sqlsrv_native_moodle_database.php: call to sqlsrv_native_moodle_database->get_recordset_sql() o line 3153 of \mod\forumng\mod_forumng.php: call to sqlsrv_native_moodle_database->get_records_sql() o line 2880 of \mod\forumng\mod_forumng.php: call to mod_forumng::query_forums() o line 425 of \mod\forumng\lib.php: call to mod_forumng::get_course_forums() o line 1026 of \lib\modinfolib.php: call to mod_forumng_cm_info_view() o line 1056 of \lib\modinfolib.php: call to cm_info->call_mod_function() o line 637 of \lib\modinfolib.php: call to cm_info->obtain_view_data() o line 1537 of \course\lib.php: call to cm_info->get_extra_classes() o line 141 of \index.php: call to print_section()