SQL execution returns different results than execution directly in MS SQL Server Management Studio

Avatar
  • updated
  • Not a bug

I have quite small experience with SQL, so maybe I am doing something wrong.

I was trying to build a query which will display results from several SELECT's. The code is quite large, but in general it goes like this:

SELECT
Obroty.ART_CODE AS [Kod Kategorii],
Obroty.COL_CODE AS [Kod Wariantu],
Obroty.Sum_QUANTITY,
(and so on.....)

FROM
(SELECT ROTAT.ART_CODE, ROTAT.COL_CODE, Sum(ROTAT.QUANTITY) AS Sum_QUANTITY
FROM ROTAT
GROUP BY ROTAT.ART_CODE, ROTAT.COL_CODE) Obroty

INNER JOIN CARDS_ITE ON CARDS_ITE.ART_CODE = Obroty.ART_CODE AND CARDS_ITE.COL_CODE = Obroty.COL_CODE
INNER JOIN STOCK_WHS ON STOCK_WHS.ART_CODE = Obroty.ART_CODE AND STOCK_WHS.COL_CODE = Obroty.COL_CODE
INNER JOIN
(SELECT STOCK_STO.ART_CODE,

and so on, more inner joins.

The problem is, that FlySpeed SQL Query returns different data than MS SQL SM Studio  (seems sub-queries are not joined correctly on two conditions) 

 

In general FlySpeed gives those results (dataset '990020' and '0' in two first columns does not even exist for sum of quantities = 204):

While MS SQL SM Studio returns correctly:

 

I don't have nearly enough experience to say what's wrong, but two programs giving different results on identical SQL query seems strange to me.

 

Other that this - I really appreciate Your work with FlySpeed, great program!

Avatar
Anonymous

Hello, Przemyslaw.

I agree that this is strange.

First, please check that you get connected to the same db and server, using the same user credentials (this can make sense).

Next, please click on the "SQL" button in the toolbar on the Result data tab. You will see the SQL query which is actually executed. Possibly the TOP clause has been added to this query. If you feel that your query is transformed incorrectly, you can send us the initial and the transformed query to support@activedbsoft.com for investigation.

The point is that MS SQL server requires having the ORDER BY clause in a query if it has the TOP or FETCH FIRST clause in it. We are adding TOP programmatically, so we have to add ORDER BY as well if it doesn't exist in the initial query. Thus, the order of records differs from the initial query. I am sure that if you add the ORDER BY to the initial query (executed in both programs), you will see the same result in both programs.

You can avoid this problem in two ways:

1. You can turn the "Fetch records on demand" property to True and set the "Initial limit of visible data records" to All. These options are available on the Advanced tab of the Connection Properties dialog.

2. You can turn the "Disable SQL sorting and filtration" property on, but I don't recommend you this, as you'll loose the ability to sort and filter data on the Result Data tab.

 

Avatar
Przemyslaw Rygol

Hello Sergey,

 

Thank You for very quick reply.

The database connection is exactly the same, that was my first though, but both uses same user, same privledges and same connection settings:

 

The TOP clausule has not been added, but indeed the ORDER BY clausule appeared. I can not copy whole SQL text to public server, but much smaller version generates the same error. Added text is marked in red:

Actually adding ORDER BY manually does not change the results. The problem is not the order, but amount of records returned:

FLySpeed returns I don't know how many records, as it hangs on export (timeout expired message, never happened before):

While MS SM Studio returns 168 records:

By doing manually simulation of queries I designed, sub-query after sub-query and then joining them as I did in main SQL I can confirm that 168 rows is correct result.

"Turing Fetch records on demand" = True did not change the situation, BUT "Disable SQL sorting and filtration" = True had solved the issue!

It seems there are two possibilities:

1) FlySpeed does not parse sub-queries first, so when it's doing main query it actually takes un-grupped results from sub-queries, or

2) FlySpeed does not join sub-queries on two 'equal' conditions merged by AND statement when it's parsing query with sorting/filtration enabled - and judging by results it is more probable.

I am ok with disabled SQL sorting/filtration, and I use FlySpeed as easy-to-use genius-level query builder and I then transfer those queries to SAP system / BI system, so for me the topic is solved, thank you very much! :)

If I can assist You with fixing this issue for other users, just let me know.

 

Avatar
Anonymous

Dear Przemyslaw,

We have tried to reproduce this problem but have not succeeded until now.

We would be grateful if you would send the text of both the original and transformed SQL queries as text, not as a screenshot. It is not necessary to send it to the public forum. Please submit a private request or send us an email to support@activedbsoft.com 

Thank you for helping us to make our software better.

Avatar
Andrey Zavyalov, PM
  • Not a bug