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 13:10	Modified:	19 Jul 4:09
Reporter:	Anderson Gregorio Gracher Anderson	Email Updates:
Status:	Analyzing	Impact on me:	None
Category:	Connector / NET	Severity:	S3 (Non-critical)
Version:	8.3.0	OS:	Windows (Windows Server 2022)
Assigned to:	MySQL Verification Team	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.