We have recently had to do some work with an OpenEdge database that a third party product uses, and today (after much hair-pulling), we finally identified why a view was returning no results. This view in question combines about 100 separate tables, and is then queried against (we have limited rights to this database). One of the fields returned by this view is a hard-coded string literal, along the lines of
'John Smith' AS TheName
We were having difficulty running queries that included this string, which we were trying to RTrim (the view returned a lot of trailing spaces) and then concatenate with another field. However, if we used RTrim on this field then, instead of returning an error message, or a null or something like that, the row simply wasn't returned. We weren't trying to use it in a WHERE clause or JOIN, this was simply part of the SELECT ... FROM VIEWNAME. After reviewing the view, it seemed that the view had erroneously detected the length of the string as 9 characters (no length was specified in the definition), and RTrim just didn't work. Now, I could understand why this might lead to an error message, or a NULL value in the SELECT, but why would the row simply not be returned at all? This doesn't seem like good SQL behaviour and I've never seen it happen with any other RDBMS.
Other info : we're test querying via ODBC and WinSQL, with a view to this being included in an existing ASP.NET app. We don't have access to the backend except via this, although we do have rights to create views.
Update : As a freaky follow-up, we have now discovered that if we attempt to query this view without any WHERE clause, no records are returned. This may have the same cause.