import java.sql.*;


class ColumnIssueDemo {
    public static void main(String[] args) {
        /**
         * Examples below were generated against MariaDB server 10.5.3 using MariaDB Connector/J 2.6.1
         * Problem also was present using MariaDB server 10.4.12, and with Connector/J 2.4.2
         * Problem did *not* manifest when run against MariaDB server 10.3.22
         */

        try {
            String jdbcUrl = "jdbc:mysql://localhost:3307/test";
            String jdbcUser = "application";
            String jdbcPassword = "wxdb2019";

            Class.forName("org.mariadb.jdbc.MariaDbDataSource");
            Connection  connection = DriverManager.getConnection(
                jdbcUrl,
                jdbcUser,
                jdbcPassword
            );
            // Prepare all the tables and data
            Statement stmt = connection.createStatement();
            stmt.execute("CREATE TABLE IF NOT EXISTS foo_outer (original_id varchar(100), key_p2 DATETIME, some_value text, PRIMARY KEY (original_id, key_p2))");
            stmt.execute("INSERT IGNORE INTO foo_outer (original_id, key_p2, some_value) VALUES ('oid_a', '2020-06-01 00:00', 'some_value_a')");
            stmt.execute("CREATE TABLE IF NOT EXISTS foo_inner (original_id varchar(100), other_id varchar(100), PRIMARY KEY (original_id, other_id))");
            stmt.execute("INSERT IGNORE INTO foo_inner (original_id, other_id) VALUES ('oid_a', 'otherid_1')");
            stmt.execute("INSERT IGNORE INTO foo_inner (original_id, other_id) VALUES ('oid_b', 'otherid_1')");

            // Example 1
            System.out.println("Trying to access shadowed field with an order by...this should fail");
            try {
                ResultSet r = stmt.executeQuery("SELECT original_id AS alt_id, key_p2, some_value FROM foo_outer WHERE original_id IN (SELECT DISTINCT(inner1.original_id) FROM foo_inner AS inner1 RIGHT JOIN foo_inner AS inner2 ON inner1.other_id = inner2.other_id WHERE inner1.original_id = 'oid_a') ORDER BY key_p2");
                while (r.next()) {
                    System.out.println("value of original_id is " + r.getString("original_id"));
                }
                System.out.println("The demo is broken!");
            } catch (SQLException ex) {
                ex.printStackTrace();
                System.out.println("The demo is working as intended");
            }

            // Example 2
            System.out.println("Trying to access shadowed field without an order by...this should work");
            try {
                ResultSet r = stmt.executeQuery("SELECT original_id AS alt_id, key_p2, some_value FROM foo_outer WHERE original_id IN (SELECT DISTINCT(inner1.original_id) FROM foo_inner AS inner1 RIGHT JOIN foo_inner AS inner2 ON inner1.other_id = inner2.other_id WHERE inner1.original_id = 'oid_a')");
                while (r.next()) {
                    System.out.println("value of original_id is " + r.getString("original_id"));
                }
                System.out.println("The demo is working as intended");
            } catch (SQLException ex) {
                ex.printStackTrace();
                System.out.println("The demo is broken!");
            }

            // Example 3
            System.out.println("Trying to access alias field with an order by...this should work");
            try {
                ResultSet r = stmt.executeQuery("SELECT original_id AS alt_id, key_p2, some_value FROM foo_outer WHERE original_id IN (SELECT DISTINCT(inner1.original_id) FROM foo_inner AS inner1 RIGHT JOIN foo_inner AS inner2 ON inner1.other_id = inner2.other_id WHERE inner1.original_id = 'oid_a') ORDER BY key_p2");
                while (r.next()) {
                    System.out.println("value of alt_id is " + r.getString("alt_id"));
                }
                System.out.println("The demo is working as intended");
            } catch (SQLException ex) {
                ex.printStackTrace();
                System.out.println("The demo is broken!");
            }

            stmt.close();
            connection.close();
        } catch (Exception ex) {
            ex.printStackTrace();
        }
    }
}