Clever Geek Handbook

Informix SQL query: two similar queries returning different results

I have an Informix SQL query that returns a set of rows. It was slightly modified for the new version of the site we were working on, and our QA noticed that the new version was returning different results. After researching, we found that the only difference between the two queries was the number of fields returned.

FROM, WHERE, and ORDER BY clauses are identical, and the column names in the SELECT element do not affect the results. It only caused the number of fields.

Any ideas?

+1


source to share


4 answers


The Informix SQL engine uses indexes on the tables based on the columns we want to retrieve. When fetching different columns, we used different indices and therefore got results in a different order.



+1


source


Adding --+ ORDERED

a join-order directive fixes the problem, allowing you to get results in a predictable order every time.

Links are provided in the description of how the directive works http://publib.boulder.ibm.com/infocenter/idshelp/v10/index.jsp?topic=/com.ibm.sqls.doc/sqls1144.htm



Use the ORDERED join-order directive to force the optimizer to join tables or views in the order in which they appear in the FROM clause of the query.

SELECT --+ ORDERED
   name, title, salary, dname
FROM dept, job, emp WHERE title = 'clerk' AND loc = 'Palo Alto' 
   AND emp.dno = dept.dno 
   AND emp.job= job.job;
+2


source


I assume that by "fields" you mean the number of lines of output? In my experience, people use "fields" and "columns" interchangeably. Given that the names in the select list have not changed, you apparently only got differences in the number of rows returned.

Given the same tables, input and query, the size and content of the result set should be the same regardless of the query plan or server version. The sequence of the result set can be different if you don't place an order on the results, but this is legal in any DBMS.

If you receive different sizes of result sets, you should probably contact IBM Technical Services. At least one of the result sets is wrong, and wrong results are always serious.

While hints can help with performance, and the standard advice "run UPDATE STATS (with appropriate parameter sets)" usually helps, neither the presence or absence of indexes should change the result set when the underlying data is stable. (If the data changes, there are many problems and complications to worry about.)

0


source


I can only think of two explanations:

  • An aggregate function such as COUNT (DISTINCT column) or
  • The additional columns that are selected refer to the table that OUTER joined

I understand that you don't want to post SQL and table definitions, but that makes it difficult to diagnose.

0


source






More articles:


All Articles