Why does Synapse On-Demand return same row counts when running queries with UNION ALL?

Question

Why does Synapse On-Demand return same row counts when running queries with UNION ALL?

Valter Prykäri 126

In the second query, FIRSTROW is set to 2. Running these queries separately will return different counts, but running them in an UNION ALL query, the both values will be the same.

SELECT
    count(*)
FROM
    OPENROWSET(
        BULK 'https://teststorage.dfs.core.windows.net/data/test.csv',
        FORMAT = 'CSV',
        PARSER_VERSION='2.0',
        FIRSTROW = 1,
        FIELDTERMINATOR = ';' ) as res

UNION ALL 

SELECT
    count(*)
FROM
    OPENROWSET(
        BULK 'https://teststorage.dfs.core.windows.net/data/test.csv',
        FORMAT = 'CSV',
        PARSER_VERSION='2.0',
        FIRSTROW = 2,
        FIELDTERMINATOR = ';') as res

Samara Soucy - MSFT 5,141 Reputation points

2021-05-24T03:17:56.02+00:00

Is there anything in particular about the test file that could be tripping up the parser? I haven't been able to duplicate your issue:
Valter Prykäri 126 Reputation points

2021-05-24T07:18:15.817+00:00

Not sure, happens with every file I have:
Samara Soucy - MSFT 5,141 Reputation points

2021-05-24T16:07:06.007+00:00

Okay, I'm going to reach out to the product team to see if they have any suggestions on what could be wrong. It can take a couple days to get a response- I will let you know if they know, or if we need to escalate to a support case so that we can look into your specific instance.
Samara Soucy - MSFT 5,141 Reputation points

2021-05-28T04:30:10.117+00:00

Hi Valter!

I wanted to check in with you since you haven't replied to the private message I sent for more info about your instance. If you don't want to post there you can also send an email my attention at azcommunity@microsoft.com with one or more of the Request IDs for the incorrect results and a link to this thread.
Samara Soucy - MSFT 21 Reputation points

2021-06-01T15:53:12.2+00:00

Hi @Valter Prykäri

I haven't heard back on the private message requesting Request IDs so the product team can look into why your queries are having unexpected results. Are you still seeing this issue or has it resolved?
Valter Prykäri 126 Reputation points

2021-06-02T05:19:11.25+00:00

Sorry, I haven't had time to look in to this earlier. I sent you a request ID that should have the issue. However, I cannot replicate the issue anymore. All queries that were previously having the issue are working properly now. Could it have been a temporary issue or something that got fixed?
Samara Soucy - MSFT 5,141 Reputation points

2021-06-11T17:28:39.967+00:00

I apologize for the delayed response- since it resolved on its own it is likely that it was temporarily or a fix has since been released. I'm still going to send the ID on to the product team so they can ensure it's not something that will reoccur- thank you for sharing that info..