# Different Query Output for Same Data in Percona vs MariaDB

**URL:** <https://forums.percona.com/t/different-query-output-for-same-data-in-percona-vs-mariadb/28359>\
**Category:** Percona Server for MySQL 5.7\
**Created:** [February 9, 2024, 12:35pm UTC](https://forums.percona.com/t/different-query-output-for-same-data-in-percona-vs-mariadb/28359 "2024-02-09T12:35:22Z")\
**Posts on this page:** 8\
**Page:** 1

<div class="post-metadata">

**Author:** ![Ankur](https://avatars.discourse-cdn.com/v4/letter/a/91b2a8/32.png) [@Ankur](https://forums.percona.com/u/Ankur)\
**Post date:** [February 9, 2024, 12:35pm UTC](https://forums.percona.com/t/different-query-output-for-same-data-in-percona-vs-mariadb/28359/1 "2024-02-09T12:35:23Z")

</div>

Dear All

Copied database from Percona 5.5.57-38.9 to MariaDB 10.11.2. But on Mariadb when running following query its returning two rows whereas on Percona same query was returning one row, so need help to investigate this change.

Thanks in advance,

Details -

Original Data on Source Percona DB Server -

mysql\> SELECT f.id,f.leg\_id,f.leg\_srcid FROM Table1 f WHERE f.leg\_id=2 AND f.id IN (1,2);  
±----------±-------±----------+  
| id| leg\_id | leg\_srcid |  
±----------±-------±----------+  
| 1 | 2 | 37214|  
| 2 | 2 | 37214|  
±----------±-------±----------+

* * *

When running following Query ‘A’ on Percona -

SELECT f.id,f.leg\_id,f.leg\_srcid FROM Table1 f WHERE f.leg\_srcid=37214 and leg\_id = 2;

Output on Percona is -

mysql\> SELECT f.id,f.leg\_id,f.leg\_srcid FROM Table1 f WHERE f.leg\_srcid=37214 and leg\_id = 2;  
±----------±-------±----------+  
| id | leg\_id | leg\_srcid |  
±----------±-------±----------+  
| 2 | 2 | 37214|  
±----------±-------±----------+  
1 row in set (0.20 sec)

But when same Query ‘A’ run on MariaDB, output is -

MariaDB [DB1]\> SELECT f.id,f.leg\_id,f.leg\_srcid FROM Table1 f WHERE f.leg\_srcid=37214 and leg\_id = 2;  
±----------±-------±----------+  
| id | leg\_id | leg\_srcid |  
±----------±-------±----------+  
| 1 | 2 | 37214|  
| 2 | 2 | 37214|  
±----------±-------±----------+  
2 rows in set (1.138 sec)

Kindly check and suggest.

---

<div class="post-metadata">

**Author:** ![matthewb](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/matthewb/32/34_2.png) [@matthewb](https://forums.percona.com/u/matthewb)\
**Post date:** [February 9, 2024, 3:35pm UTC](https://forums.percona.com/t/different-query-output-for-same-data-in-percona-vs-mariadb/28359/2 "2024-02-09T15:35:07Z")

</div>

Percona 5.5 is incredibly old. Can you please test your comparison against Percona 8.0? Many changes were made to the query optimizer since 5.5 for both Percona and MariaDB.

---

<div class="post-metadata">

**Author:** ![Ankur](https://avatars.discourse-cdn.com/v4/letter/a/91b2a8/32.png) [@Ankur](https://forums.percona.com/u/Ankur)\
**Post date:** [February 9, 2024, 4:57pm UTC](https://forums.percona.com/t/different-query-output-for-same-data-in-percona-vs-mariadb/28359/3 "2024-02-09T16:57:02Z")

</div>

Thanks Matthewbs for swift reply.

However there are no plans to perform upgrade soon, so do we have any other way to cross check and find any solution to it?

Any suggestions.

---

<div class="post-metadata">

**Author:** ![matthewb](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/matthewb/32/34_2.png) [@matthewb](https://forums.percona.com/u/matthewb)\
**Post date:** [February 9, 2024, 5:39pm UTC](https://forums.percona.com/t/different-query-output-for-same-data-in-percona-vs-mariadb/28359/4 "2024-02-09T17:39:22Z")

</div>

Check the value of `optimizer_switch` on both flavors.

> [@Ankur](#):
>
> there are no plans to perform upgrade soon

I hope you realize that 5.5 is around 10+ years dead. 5.7 died in Oct’23 and 8.0.\* is entering LTS with 8.3 the current GA. You have no security patches, no bug fixes, no improvements in performance, etc.

Again, by comparing an _extremely_ out of date MySQL to modern MariaDB, that’s apples to hamburgers. (Remember that MariaDB isn’t MySQL. If you were comparing Community MySQL to Percona, that’d be plain ice cream to ice cream with fudge on top. Whereas MariaDB isn’t even ice cream.)

---

<div class="post-metadata">

**Author:** ![Ankur](https://avatars.discourse-cdn.com/v4/letter/a/91b2a8/32.png) [@Ankur](https://forums.percona.com/u/Ankur)\
**Post date:** [February 12, 2024, 2:17pm UTC](https://forums.percona.com/t/different-query-output-for-same-data-in-percona-vs-mariadb/28359/5 "2024-02-12T14:17:00Z")

</div>

Thanks, yeah makes sense and perfectly understand your point of view.

Actually DBA team has been asked to perform a mock test to perform migration from Percona to MariaDB. But had some issues and were forced to do rollback, but strange part is after performing (taking backup of database from MariaDB and restored it to same old Percona server), this issue start coming. So raised this issue to check if something missing here.

As of now…we already checked Table structure, Indexes, keys on both database and all seems same on both databases.

Out of your extensive experience, Is there anything which you suggest to check which could help us to fix it.

---

<div class="post-metadata">

**Author:** ![matthewb](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/matthewb/32/34_2.png) [@matthewb](https://forums.percona.com/u/matthewb)\
**Post date:** [February 12, 2024, 7:14pm UTC](https://forums.percona.com/t/different-query-output-for-same-data-in-percona-vs-mariadb/28359/6 "2024-02-12T19:14:32Z")

</div>

> [@Ankur](#):
>
> Out of your extensive experience, Is there anything which you suggest to check which could help us to fix it.

There isn’t anything to “fix” because nothing is “broken.” Again, you are comparing the results of extremely-out-of-date-MySQL to modern-mariadb. If you want to accurately test, you need to upgrade your 5.5 to 8.0. _Then_, you can do a much closer comparison of "modern MySQL to “modern MariaDB”. But again, that’s mysql/maria, apples/oranges. MariaDB is not MySQL. MariaDB has made changes to the query optimizer that do not align with the rest of the community.

---

<div class="post-metadata">

**Author:** ![Ankur](https://avatars.discourse-cdn.com/v4/letter/a/91b2a8/32.png) [@Ankur](https://forums.percona.com/u/Ankur)\
**Post date:** [February 13, 2024, 1:05pm UTC](https://forums.percona.com/t/different-query-output-for-same-data-in-percona-vs-mariadb/28359/7 "2024-02-13T13:05:27Z")

</div>

Thanks again for quick response, indeed agreed.

---

<div class="post-metadata">

**Author:** ![matthewb](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/matthewb/32/34_2.png) [@matthewb](https://forums.percona.com/u/matthewb)\
**Post date:** [February 13, 2024, 3:03pm UTC](https://forums.percona.com/t/different-query-output-for-same-data-in-percona-vs-mariadb/28359/8 "2024-02-13T15:03:49Z")

</div>

> [@Ankur](#):
>
> Actually DBA team has been asked to perform a mock test to perform migration from Percona to MariaDB.

Why not just upgrade Percona to 8.0? Why swap out to MariaDB which is not MySQL? You said above “there are no plans to perform upgrade” but yet you are trying to move to a different database? That is very confusing.