Polls > in an Oracle environment, poll options are not displayed in the order they're created

This bug only affects those who are running with an Oracle database. When creating options for a poll the display of these options (for both instructors and students) may appear in a "random" order, rather than the order in which they were created.

The cause of this bug is that the code is doing an ORDER BY clause on the optionId column, which is the primary key of the table:

    public List<Option> getOptionsForPoll(Long pollId) {
        Poll poll;
		try {
			poll = getPollById(pollId, false);
		} catch (SecurityException e) {
			throw new SecurityException(e);
		}
        if (poll == null) {
            throw new IllegalArgumentException("Cannot get options for a poll ("+pollId+") that does not exist");
        }
        Search search = new Search();
        search.addRestriction(new Restriction("pollId", pollId));
        search.addOrder(new Order("optionId"));
        List<Option> optionList = dao.findBySearch(Option.class, search);
        return optionList;
    }

In MySQL, this field is defined as an auto_increment field, which guarantees that the IDs are unique and ascending:

mysql> describe poll_option;
+----------------+--------------+------+-----+---------+----------------+
| Field          | Type         | Null | Key | Default | Extra          |
+----------------+--------------+------+-----+---------+----------------+
| OPTION_ID      | bigint(20)   | NO   | PRI | NULL    | auto_increment |
| OPTION_POLL_ID | bigint(20)   | YES  |     | NULL    |                |
| OPTION_TEXT    | longtext     | YES  |     | NULL    |                |
| OPTION_UUID    | varchar(255) | YES  |     | NULL    |                |
| DELETED        | bit(1)       | YES  |     | NULL    |                |
| OPTION_ORDER   | int(11)      | YES  |     | NULL    |                |
+----------------+--------------+------+-----+---------+----------------+

So for those on MySQL, this bug never presents itself because the IDs of the options are always in the order in which they were created. However, in Oracle this field uses a sequence to generate the ID, and sequences do not behave in the same way an auto_increment field in MySQL does:

do not rely on sequences being

a) gap free
b) sequential
c) always increasing

Only think of them as "unique - nothing more, nothing less"

Source: https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:369390500346406705

Given these known limitations of Oracle's sequence, you can see how a situation can arise where the IDs of the options created by the user may or may not be in direct ascending order. The result is that the options may present themselves in a differing order than the user expects (a different order than they were created in).

The use of the primary key determining the "order" of the option also has a severe limitation: introducing a "re-order" functionality can become tricky if you have to manipulate primary keys.

The linked PR introduces a new, dedicated OPTION_ORDER column in the POLL_OPTION table to store the option's visual order. By doing so, it not only eliminates the Oracle bug, but it also lays the groundwork for introducing a "re-order" function for the end users (which will be coming in the future).

It also introduces a new Quartz job (named "Backfill all existing poll options' order based on their original ID"), which is a run-only-once task to back-fill the OPTION_ORDER column for all existing poll options (maybe not all, but any Polls that are attached to an existing site). The job outputs logger statements to track the process. For example:

28-Feb-2019 11:43:21.196 INFO [QuartzScheduler_Worker-3] org.sakaiproject.component.app.scheduler.jobs.PollOrderOptionBackFillJob.execute Attempting to back-fill all existing Poll option orders...
...
28-Feb-2019 11:43:22.106 INFO [QuartzScheduler_Worker-3] org.sakaiproject.component.app.scheduler.jobs.PollOrderOptionBackFillJob.execute Poll ID 4 has options with null order, processing...
...
28-Feb-2019 11:43:22.116 INFO [QuartzScheduler_Worker-3] org.sakaiproject.component.app.scheduler.jobs.PollOrderOptionBackFillJob.execute Option 7 ---> new order == 1
...
28-Feb-2019 11:44:04.234 INFO [QuartzScheduler_Worker-3] org.sakaiproject.component.app.scheduler.jobs.PollOrderOptionBackFillJob.execute Processing finished, modified 27 poll options