MySQL Bugs: #114452: Rows count difference when a SELECT query uses STRAIGHT

Bug #114452	Rows count difference when a SELECT query uses STRAIGHT_JOIN
Submitted:	22 Mar 2024 13:10	Modified:	16 May 10:13
Reporter:	Anderson Gregorio Gracher Anderson	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	Connector / NET	Severity:	S3 (Non-critical)
Version:	8.3.0	OS:	Windows (Windows Server 2022)
Assigned to:		CPU Architecture:	Any (Intel(R) Xeon(R) CPU E5-2650 v3 @ 2.30GHz 2.30 GHz (12 processors))
Tags:	percona mysql, windows server 2022

Description:
Different Rows Count, between a SELECT query using STRAIGHT_JOIN and the same query without Straight join

MySqlConnector version: 8.3.0
version: Percona server 8.0.33-25
.NET version: 4.6.2

We have differents queries in the same context:

Rows with STRAIGHT_JOIN: 1557
Rows without STRAIGHT_JOIN: 61979

When loading to the DataTable

How to repeat:
Code Example:

using System;
using System.Data;
using MySql.Data.MySqlClient;

namespace QueryMySQL
{
    class SelectMySQL
    {
        // Table
        private DataTable Table;

        bool using_data_table = true;

        private DataTable execDatatable(MySqlDataReader reader)
        {
            DataTable dt = new DataTable();
            try
            {
                dt.Load(reader);
            }
            catch (MySqlException ex)
            {
                Console.WriteLine("SelectMySQL - execDatatable - Exception : " + ex.Message.ToString());
            }

            return dt;
        }

        public SelectMySQL()
        {
            Int64 rows = 0;

            string connectionString = "Server=localhost;Database=mydatabase;Uid=username;Pwd=password;";

            using (MySqlConnection connection = new MySqlConnection(connectionString))
            {
                try
                {
                    connection.Open();

                    // Select Query  
                    string query = "SELECT STRAIGHT_JOIN FIELD_1, FIELD_2, FIELD_3 FROM TABLE_1, TABLE_2, TABLE_3 ....;";
                    // string query = "SELECT FIELD_1, FIELD_2, FIELD_3 FROM TABLE_1, TABLE_2, TABLE_3 ....;";

                    MySqlCommand command = new MySqlCommand(query, connection);

                    using (MySqlDataReader reader = command.ExecuteReader())
                    {
                        // Load the Data Reader in the DataTable and get rows count  
                        if (using_data_table)
                        {
                            Table = execDatatable(reader);
                            rows = Table.Rows.Count;
                        }
                        else
                        {
                            while (reader.Read())
                            {
                                rows++;
                            }
                        }

                        Console.WriteLine("SelectMySQL - SelectMySQL - Constructor - Rows Count: " + rows.ToString());
                        Console.ReadKey();
                    }
                }
                catch (Exception ex)
                {
                    {
                        Console.WriteLine("Error: " + ex.Message);
                    }
                }
            }
        }
    }
}

Suggested fix:
No Suggestion

Hi Mr. Anderson,

Thank you for your bug report.

Please, let us know whether you have located that a bug is in C/NET or in our server.

Thank you in advance .....

In the server it's ok

Probably is related to Mysql.data added to the project by Nugget Package.

Last version installed

A Sample code was sent, detailing the bug

Hello Anderson,

Thank you for the bug report and test case.
Please provide complete query along with create table and sample data used in the test case to reproduce this issue.

Regards,
Ashwini Patil

I`ve attached the script for testing.

In my opinion the bug is more related to "Table.Rows.Count", but affect Straight Join as Well

Try to Enable and Disable the variable using_data_table for see the difference.

Thx

Hello Anderson,

Thank you for the details.
Table data for 'tmp_TABLE2' is missing(wrong insert statements). Please share the correct insert statements.

Regards,
Ashwini Patil

Hello Anderson,

Thank you for the details.
I am not able to reproduce the issue with given code. Please provide repeatable test case(c# class).

Regards,
Ashwini Patil

I`ve attachet full example for the case.

Unfortunately i don't have Docker image ou something similar to this.

Please, if not working, send me examples with evidences like: database version, software version or anything that may help.

Thank you.

Hello Anderson,

Thank you for the details.
I once again tried to reproduce your issue with the given details but I am not seeing any issues at my end. Please upgrade to latest version and let us know if you are still facing any issues.

Regards,
Ashwini Patil