Weird behavior with MIN/MAX and ORDER BY returning different values

Uncategorized
1

I have a reflection on a MySQL view (essentially a table to Dremio) called dw_data

48%20PM
33%20PM

The raw reflection is made up many display fields, however the ones in the query are id, data, signature and data_type_id. Additionally, this reflection is sorted by the field id and the “id” refresh policy is set to incremental update on the “id” field. Finally, the reflection is partitioned by the data_type_id field as we will use this in subsequent reflections and in our where clause for most queries against this reflection.

18%20PM

OK… That is the reflection. Now here comes the weirdness.

Queries
The reflection is utilized and the query is accelerated for all of the below.

  1. SELECT MIN(id), MAX(id) FROM “production-data-analysis”.analysis.dw_data
    This query looks to be good to go. It returns a 1 for the MIN and 171878598 for the MAX. The id’s are accurate based on the source data id values.

  2. SELECT MIN(id), MAX(id) FROM “production-data-analysis”.analysis.dw_data WHERE data_type_id=6 OR data_type_id=7 OR data_type_id=8 OR data_type_id=32
    Adding data_type_id (the partitioned field in the reflection), returns us inaccurate MIN and MAX values for the “id” field. 1527187755000 MIN and 1528386742000 MAX

  3. SELECT * FROM “production-data-analysis”.analysis.dw_data WHERE data_type_id=6 OR data_type_id=7 OR data_type_id=8 OR data_type_id=32 LIMIT 1000
    Selecting a subset of 1000 records provides us with accurate “id” values when using the data_type_id in the WHERE clause

  4. SELECT * FROM “production-data-analysis”.analysis.dw_data WHERE data_type_id=6 OR data_type_id=7 OR data_type_id=8 OR data_type_id=32 ORDER BY id DESC LIMIT 1000
    Adding the ORDER BY id DESC produces values that are incorrect an inline with query number 2 from above where the values are somewhere between the MIN and MAX from query 2 and repetitive. The id field is a unique column, auto incremented column on the data source in MySQL.

Questions:
Why is query 2 and 4 returning unexpected results for a unique auto incremented “id” field that is sorted (by id) or grouped (to produce a min/max)?

Is the partition on the reflection the issue?
Is the sort on the reflection the issue?

Thank You for any insight into this. Let me know if you need query profiles to investigate the usage of the reflection.

2

Query profiles from 1 and 2 would be useful to figure out what is going on.

By unexpected results, what exactly do you mean?

3

Unexpected Results.

In the #2 query above. The MIN is resulting in 1527187755000 instead of 1.
In the #2 query above. The MAX is resulting in 1528386742000 instead of 171878598

In the #4 query above. The id field is resulting in a value between 1527187755000 and 1528386742000 often times repeating itself row by row. When the correct values should be a unique “id” value incremented from 1 to 171878598

#2 Query
35%20PM

d0c19879-8605-4ec5-bcd4-5d7c87949eb0.zip (7.3 KB)

#4 Query

29%20PM
Screen Shot 2018-06-14 at 9.01.29 PM.png572×539 37.1 KB

c47b975f-6797-4e81-9c21-4dcc9019de69.zip (8.9 KB)

#1 Query

24%20PM
Screen Shot 2018-06-14 at 9.10.24 PM.png575×549 34.3 KB

2d945d6d-b587-4109-a898-b23e1b0f91d1.zip (6.9 KB)

4

OK. That is weird.
This reflection was removed and then rebuilt again today. Same way as it was before.

#2 query is still the same as described above.

However…

#4 query is now giving me the correct values in the id column.

5

@doron any ideas with the Query Profiles provided?

6

Dremio community, any thoughts?