IS NULL WHERE clause fails with ATTR_EMULATE_PREPARES set to 0

[JasonBarnabe]

With 2.1.6 and master setting $PDO->setAttribute(PDO::ATTR_EMULATE_PREPARES, 0);, the following sample plug-in fails. Commenting out that line or using 2.1.5, the plugin works as intended.

<?php if (!defined('APPLICATION')) exit();

// Define the plugin:
$PluginInfo['PDOTestPlugin'] = array(
    'Name' => 'PDO Test Plugin',
    'Description' => 'Tests PDO',
    'Version' => '1.0',
    'Author' => "Jason Barnabe",
    'RequiredApplications' => array('Vanilla' => '2.1'),
    'AuthorEmail' => 'jason.barnabe@gmail.com',
    'AuthorUrl' => 'https://github.com/JasonBarnabe',
    'MobileFriendly' => TRUE
);

class PDOTestPlugin extends Gdn_Plugin {

    public function DiscussionModel_BeforeGet_Handler($Sender) {
        $Sender->SQL->Where('d.DiscussionID IS NULL');
    }
}

Load Recent Discussions to trigger the plug-in. Result:

Fatal Error in Gdn_Database.Query();
PDO Statement failed to prepare
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '? order by d.DateLastComment desc limit 30' at line 5
The error occurred on or near: /www/vanilla/library/database/class.database.php
337:       if (!is_null($InputParameters) && count($InputParameters) > 0) {
338:          $PDOStatement = $PDO->prepare($Sql);
339: 
340:          if (!is_object($PDOStatement)) {
341:             trigger_error(ErrorMessage('PDO Statement failed to prepare', $this->ClassName, 'Query', $this->GetPDOErrorMessage($PDO->errorInfo())), E_USER_ERROR);
342:          } else if ($PDOStatement->execute($InputParameters) === FALSE) {
343:             trigger_error(ErrorMessage($this->GetPDOErrorMessage($PDOStatement->errorInfo()), $this->ClassName, 'Query', $Sql), E_USER_ERROR);
344:          }
345:       } else {

Backtrace:
/www/vanilla/library/database/class.database.phpPHP::Gdn_ErrorHandler();
[/www/vanilla/library/database/class.database.php:341] PHP::trigger_error();
[/www/vanilla/library/database/class.sqldriver.php:1671] Gdn_Database->Query();
[/www/vanilla/library/database/class.sqldriver.php:677] Gdn_SQLDriver->Query();
[/www/vanilla/applications/vanilla/models/class.discussionmodel.php:379] Gdn_SQLDriver->Get();
[/www/vanilla/applications/vanilla/controllers/class.discussionscontroller.php:145] DiscussionModel->GetWhere();
[/www/vanilla/applications/vanilla/controllers/class.discussionscontroller.php:145] DiscussionsController->Index();
[/www/vanilla/library/core/class.dispatcher.php:356] PHP::call_user_func_array();
[/www/vanilla/index.php:46] Gdn_Dispatcher->Dispatch();

Variables in local scope:

[Sql] 'select d2.*, w.UserID as `WatchUserID`, w.DateLastViewed as `DateLastViewed`, w.Dismissed as `Dismissed`, w.Bookmarked as `Bookmarked`, w.CountComments as `CountCommentWatch`, w.Participated as `Participated`
from GDN_Discussion d
join GDN_Discussion d2 on d.DiscussionID = d2.DiscussionID
left join GDN_UserDiscussion w on w.DiscussionID = d2.DiscussionID and w.UserID = 7
where d.DiscussionID IS :dDiscussionID
order by d.DateLastComment desc
limit 30'

[InputParameters] array (
  ':dDiscussionID' => NULL,
)

[Options] array (
  'Type' => 'select',
  'Slave' => NULL,
  'ReturnType' => 'DataSet',
)

[ReturnType] 'DataSet'

[PDO] array (
)

[PDOStatement] false

Need Help?

If you are a user of this website, you can report this message to a website administrator.

If you are an administrator of this website, you can get help at the Vanilla Community Forums.
Additional information for support personnel:

    Application: Vanilla
    Application Version: 2.2.16.8
    PHP Version: 5.5.18
    Operating System: Linux
    Server Software: nginx/1.6.1
    User Agent: Mozilla/5.0 (X11; Ubuntu; Linux x86_64; rv:33.0) Gecko/20100101 Firefox/33.0
    Request Uri: /
    Controller: Gdn_Database
    Method: Query

[imnotjames]

If you change the d.DiscussionID IS NULL to d.DiscussionID is NULL you'll find that it fixes it, since the SQL driver only seems to work with lowercase is. I've opened a PR to fix that though.

[JasonBarnabe]

This is fixed in 2.1.7.