SyntaxConformanceTest::testSqlOperators - Fix failure on MySQL 8
authorTim Otten <totten@civicrm.org>
Fri, 8 May 2020 03:53:21 +0000 (20:53 -0700)
committerTim Otten <totten@civicrm.org>
Fri, 8 May 2020 03:53:21 +0000 (20:53 -0700)
commit5d60cab9086bd105d85841dc9df29fb3a6fbd328
tree4931a50513a3d9384d11675ee90367f82041caa3
parent8b5f3b3ee65c5f64bb03af69e515e033ca5b9faf
SyntaxConformanceTest::testSqlOperators - Fix failure on MySQL 8

Before
------

`SyntaxConformanceTest::testSqlOperators` frequently fails on the `Dedupe` entity when running on MySQL 8 (`bknix-edge`).

After
------

`SyntaxConformanceTest::testSqlOperators` repeatedly passes for all entities on my copy of MySQL 8 (`bknix-edge`).

Technical Details
-----------------

The `testSqlOperators` creates a small pool of example records
(`$entities`), then it slices/dices that pool with a few SQL operators and
asserts the number of matches.

In particular:

```php
$this->callAPISuccessGetCount($entityName, ['id' => ['>' => $entities[0]]], $totalEntities - 1);
```

The problem is that `$entities[0]` does not necessarily have the lowest ID
-- because `$entities` was not fetched in any particular order.  The default
ordering is *often* the same as the `id`, but not always.

I suspect that this problem manifests most frequently with the `Dedupe`
entity because the underlying table (`civicrm_prevnext_cache`) is highly
volatile (many writes+deletes) -- thus it's more likely to reuse old storage
slots for new rows.
tests/phpunit/api/v3/SyntaxConformanceTest.php