Hello to anyone reading this thread.
I found the problem after quite a bit of experimentation. What got me to a point of illumination was my attempt to backup our current database and restore it to a new empty database for testing purposes. I ran into many instances where the restore process returned an error that existed in a backup file that was over 20,000 lines of SQL text and the total size of the backup files around 2 gigabytes. There are no text editors that I know of that allow you to open a text file that large, so I had to rely on a program that I wrote to segment very large files and return a list of segmentations of 100 megabytes each. Then I used Notepad++ to find the line number referenced in the restore process's error by starting from the first file, finding how many lines were in it, then adding the line numbers of subsequent files until I got to the file that would contain the error.
Ultimately I found that that there was an issue with many fields in the database where a VARCHAR(255) field was used as a KEY, either UNIQUE index or a KEY index and the database was converted to support the UTF8MB4 character type to support smilies in the last major transition.
It took me 4 days of 12+ hours to discover and manually fix every instance of this issue since I only had knowledge of each error after re-backing up and re-restoring, then having to re-segment and the parse the line numbers, leaving me rather frustrated to say the least with whomever decided to key such a huge VARCHAR field in the first place. It's poor database design to make a field of that size be a key of any kind. So I resized all those fields down to 191 characters max except for one instance that that size still did not work out, and in that instance I resized that particular field to 150 characters and finally the backup script would rebuild properly.
This leaves a myriad of unanswered questions for me, and a great deal of frustration that I was not able to find this information on the Moodle forums. I had to rely on genericized information from Stack Overflow and other troubleshooting sites to understand why these fields resulted in this error.
The biggest question I have is, how do I get our current database up to the same specs as say a newly built Moodle database (version 3.8.x)? My initial thoughts are that I should download and install a new Moodle of the latest build, set it up on another subdomain, have it build the new database then add all my other necessary modules I'm using in our current system, then backup the data only from our current database and then restore only the data into the new system. Hopefully then I won't have hundreds possibly thousands of entries where custom collations were declared.
A word on the collations, it is extremely frustrating to join Moodle tables with external tables that reside in the same database unless every table has the same character set and every field used in joining uses the same collation.
I'm seeking for the most elegant manner here. Hoping that someone with some experience here can provide some caveats or point me in the right direction.
So, the takeaway I can highlight in my experience here will be of great value to someone else who experiences this problem of a view not returning rows when there are rows that should be returned. If that happens, check your database for wrongly sized keys when using UTF8MB4 character type. The problem seems to be focused on smilies which requires 4 bytes (not 3) per character.
Why are smilies so important? Did I miss the front page news reports? Hehe. This was a huge effort on my part to fix this predicament. Seems to me time could be spent in much more fruitful ways than extending a poor database design due to index field size constraints of 767 bytes.
I hope this information benefits another confused developer or Moodle administrator in the future.