Bug #77240 [Opn]: Incorrect binding near constraint errors.

Edit report at https://bugs.php.net/bug.php?id=77240&edit=1 ID: 77240 User updated by: dave at mausner dot us Reported by: dave at mausner dot us Summary: Incorrect binding near constraint errors. Status: Open Type: Bug Package: SQLite related Operating System: win 10 -PHP Version: 7.0.32 +PHP Version: 7.2.12 Block user comment: N Private report: N New Comment: Also occurs in: PHP 7.2.12 (cli) (built: Nov 8 2018 05:47:24) (NTS MSVC15 (Visual C++ 2017) x64) Zend Engine v3.2.0, Copyright (c) 1998-2018 Zend Technologies Previous Comments: ------------------------------------------------------------------------ [2018-12-04 20:19:08] dave at mausner dot us It seems to me that if only one constraint error 19 occurs amid good inserts, the error is not harmful. However, if two or more constraint errors occur consecutively, the NEXT good insert will contain junky data values. The test data demonstrates this. As the occurrence of non-unique keys increases, the amount of junk increases, too. Yet the internal echo shows that the intended data was not junk. ------------------------------------------------------------------------ [2018-12-04 19:59:33] dave at mausner dot us Description: ------------ Insertions into a table with UNIQUE keys having a high occurrence of error 19 (constraint violation due to duplicate key) causes junk to be inserted. The test case reproduces the erroneous environment. The test keys are bound such that the column values MUST be "FIRSTx", "MIDx", and "LASTx". But the bad data shows inexplicable mix-ups in the order and sometimes junky contents. The test script is completely self-contained. Tested with stock Win10 PHP build: PHP 7.0.32 (cli) (built: Sep 12 2018 15:54:04) ( NTS ) Copyright (c) 1997-2017 The PHP Group Zend Engine v3.0.0, Copyright (c) 1998-2017 Zend Technologies SQLite 3.22.0 2018-01-22 18:45:57 zlib version 1.2.11 Test script: --------------- <?php define("SQLITE_CONSTRAINT", 19); /* Abort due to constraint violation */ // connect and configure session. $data = new SQLite3("buggy"); $data->exec("pragma foreign_keys=on"); $data->exec("pragma recursive_triggers=on"); $data->exec("pragma journal_mode=off"); $data->exec("pragma synchronous=off"); $data->exec("pragma locking_mode=exclusive"); $data->exec("drop table if exists buggy"); $data->exec(<<<EOT create table buggy ( bug integer primary key autoincrement, bugfirst text, bugmid text, buglast text, unique (bugfirst, bugmid, buglast) ) EOT ); $data->exec("begin"); // prepare sql. $bugsql = $data->prepare("insert into buggy (bugfirst, bugmid, buglast) values (:bugfirst, :bugmid, :buglast)"); for ($i = $j = $k = 0; $i < 1000; $i += 1) { $bugfirst = "FIRST" . rand(1, 3); $bugmid = "MID" . rand(1, 5); $buglast = "LAST" . rand(1, 7); $bugsql->bindValue(":bugfirst", $bugfirst); $bugsql->bindValue(":bugmid", $bugmid); $bugsql->bindValue(":buglast", $buglast); @$insert = $bugsql->execute(); $error = $data->lastErrorCode(); echo "$bugfirst, $bugmid, $buglast, $error\n"; if ($insert === false) { if ($error != SQLITE_CONSTRAINT) echo "Code $error.\n"; $k += 1; continue; } $bug = $data->lastInsertRowID(); $j += 1; } $data->exec("commit"); $data->close(); echo "looped $i, inserted $j, clashed $k.\n"; exit; ?> Expected result: ---------------- All inserted column values must be like this: "FIRSTx", "MIDx", and "LASTx". And all triplet values are to be unique. Actual result: -------------- But when unique key violations occur, rows are inserted with key values out-of-order, or just junk. Example: PK key1 key2 key3 129 FIRST3 MID2 LAST4 130 MID5 LAST LAST3 131 MID2 LAST LAST5 132 MID1 LAST LAST3 133 LAST4 LAST MID1 134 FIRST3 MID2 LAST2 135 FIRST2 MID5 LAST3 136 MID2 LAST LAST1 137 LAST4 MID5 :bugm 138 MID1 LAST LAST7 139 MID1 LAST LAST1 140 FIRST2 MID5 LAST2 141 LAST7 LAST MID1 142 LAST1 LAST MID4 ------------------------------------------------------------------------ -- Edit this bug report at https://bugs.php.net/bug.php?id=77240&edit=1