Eager loading a many-to-many join table relationship breaks when columns in eager-loaded table are not in alphabetical order

[cpickett-ml]

What version of SQLBoiler are you using (sqlboiler --version)?

SQLBoiler v3.0.1

If this happened at generation time what was the full SQLBoiler command you used to generate your models? (if not applicable leave blank)

If this happened at runtime what code produced the issue? (if not applicable leave blank)

package main

import (
    "database/sql"
    "fmt"

    "github.com/volatiletech/sqlboiler/boil"
    _ "github.com/volatiletech/sqlboiler/drivers/sqlboiler-mysql/driver" // mysql connection
    "github.com/volatiletech/sqlboiler/queries/qm"
    "pernicious.games/test/app/models"
)

//go:generate sqlboiler --wipe --add-global-variants --no-context --no-tests --no-hooks -o app\models mysql

func main() {
    db, _ := sql.Open("mysql", "root:3.asi79>%swB@tcp/sqlboil_test?charset=utf8&parseTime=True")

    player := &models.Player{Name: "Test player"}
    player.Insert(db, boil.Infer())
    team := &models.Team{Name: "Test team"}
    team.Insert(db, boil.Infer())
    player.SetTeams(db, false, team)

    foundPlayer, err := models.Players(qm.Load("Teams"), qm.Where("id = ?", player.ID)).One(db)
    fmt.Println(foundPlayer, err)
}

What is the output of the command above with the -d flag added to it? (Provided you are comfortable sharing this, it contains a blueprint of your schema)

{"config":{"driver_name":"mysql","driver_config":{"blacklist":null,"dbname":"sqlboil_test","host":"localhost","pass":"3.asi79\u003e%swB","sslmode":"false","user":"root","whitelist":null},"pkg_name":"models","out_folder":"app\\models","debug":true,"add_global":true,"no_context":true,"no_tests":true,"no_hooks":true,"wipe":true,"struct_tag_casing":"snake","imports":{"all":{"Standard":["\"database/sql\"","\"fmt\"","\"reflect\"","\"strconv\"","\"strings\"","\"sync\"","\"time\""],"ThirdParty":["\"github.com/pkg/errors\"","\"github.com/volatiletech/sqlboiler/boil\"","\"github.com/volatiletech/sqlboiler/queries\"","\"github.com/volatiletech/sqlboiler/queries/qm\"","\"github.com/volatiletech/sqlboiler/strmangle\""]},"test":{"Standard":["\"bytes\"","\"reflect\"","\"testing\""],"ThirdParty":["\"github.com/volatiletech/sqlboiler/boil\"","\"github.com/volatiletech/sqlboiler/queries\"","\"github.com/volatiletech/sqlboiler/randomize\"","\"github.com/volatiletech/sqlboiler/strmangle\""]},"singleton":{"boil_queries":{"Standard":null,"ThirdParty":["\"github.com/volatiletech/sqlboiler/drivers\"","\"github.com/volatiletech/sqlboiler/queries\"","\"github.com/volatiletech/sqlboiler/queries/qm\""]},"boil_types":{"Standard":["\"strconv\""],"ThirdParty":["\"github.com/pkg/errors\"","\"github.com/volatiletech/sqlboiler/boil\"","\"github.com/volatiletech/sqlboiler/strmangle\""]},"mysql_upsert":{"Standard":["\"fmt\"","\"strings\""],"ThirdParty":["\"github.com/volatiletech/sqlboiler/drivers\"","\"github.com/volatiletech/sqlboiler/strmangle\""]}},"test_singleton":{"boil_main_test":{"Standard":["\"database/sql\"","\"flag\"","\"fmt\"","\"math/rand\"","\"os\"","\"path/filepath\"","\"strings\"","\"testing\"","\"time\""],"ThirdParty":["\"github.com/spf13/viper\"","\"github.com/volatiletech/sqlboiler/boil\""]},"boil_queries_test":{"Standard":["\"bytes\"","\"fmt\"","\"io\"","\"io/ioutil\"","\"math/rand\"","\"regexp\""],"ThirdParty":["\"github.com/volatiletech/sqlboiler/boil\""]},"boil_suites_test":{"Standard":["\"testing\""],"ThirdParty":null},"mysql_main_test":{"Standard":["\"bytes\"","\"database/sql\"","\"fmt\"","\"io\"","\"io/ioutil\"","\"os\"","\"os/exec\"","\"regexp\"","\"strings\""],"ThirdParty":["_ \"github.com/go-sql-driver/mysql\"","\"github.com/kat-co/vala\"","\"github.com/pkg/errors\"","\"github.com/spf13/viper\"","\"github.com/volatiletech/sqlboiler/drivers/sqlboiler-mysql/driver\"","\"github.com/volatiletech/sqlboiler/randomize\""]},"mysql_suites_test":{"Standard":["\"testing\""],"ThirdParty":[]}},"based_on_type":{"null.Bool":{"Standard":[],"ThirdParty":["\"github.com/volatiletech/null\""]},"null.Bytes":{"Standard":[],"ThirdParty":["\"github.com/volatiletech/null\""]},"null.Float32":{"Standard":[],"ThirdParty":["\"github.com/volatiletech/null\""]},"null.Float64":{"Standard":[],"ThirdParty":["\"github.com/volatiletech/null\""]},"null.Int":{"Standard":[],"ThirdParty":["\"github.com/volatiletech/null\""]},"null.Int16":{"Standard":[],"ThirdParty":["\"github.com/volatiletech/null\""]},"null.Int32":{"Standard":[],"ThirdParty":["\"github.com/volatiletech/null\""]},"null.Int64":{"Standard":[],"ThirdParty":["\"github.com/volatiletech/null\""]},"null.Int8":{"Standard":[],"ThirdParty":["\"github.com/volatiletech/null\""]},"null.JSON":{"Standard":[],"ThirdParty":["\"github.com/volatiletech/null\""]},"null.String":{"Standard":[],"ThirdParty":["\"github.com/volatiletech/null\""]},"null.Time":{"Standard":[],"ThirdParty":["\"github.com/volatiletech/null\""]},"null.Uint":{"Standard":[],"ThirdParty":["\"github.com/volatiletech/null\""]},"null.Uint16":{"Standard":[],"ThirdParty":["\"github.com/volatiletech/null\""]},"null.Uint32":{"Standard":[],"ThirdParty":["\"github.com/volatiletech/null\""]},"null.Uint64":{"Standard":[],"ThirdParty":["\"github.com/volatiletech/null\""]},"null.Uint8":{"Standard":[],"ThirdParty":["\"github.com/volatiletech/null\""]},"time.Time":{"Standard":["\"time\""],"ThirdParty":[]},"types.Decimal":{"Standard":[],"ThirdParty":["\"github.com/volatiletech/sqlboiler/types\""]},"types.JSON":{"Standard":[],"ThirdParty":["\"github.com/volatiletech/sqlboiler/types\""]},"types.NullDecimal":{"Standard":[],"ThirdParty":["\"github.com/volatiletech/sqlboiler/types\""]}}},"aliases":{"tables":{"players":{"up_plural":"Players","up_singular":"Player","down_plural":"players","down_singular":"player","columns":{"created_at":"CreatedAt","id":"ID","name":"Name","updated_at":"UpdatedAt"}},"team_players":{"relationships":{"FK_team_players_players":{"local":"Teams","foreign":"Players"},"FK_team_players_teams":{"local":"Players","foreign":"Teams"}}},"teams":{"up_plural":"Teams","up_singular":"Team","down_plural":"teams","down_singular":"team","columns":{"created_at":"CreatedAt","id":"ID","name":"Name","updated_at":"UpdatedAt"}}}}},"driver_config":{"blacklist":null,"dbname":"sqlboil_test","host":"localhost","pass":"3.asi79\u003e%swB","sslmode":"false","user":"root","whitelist":null},"schema":"","dialect":{"lq":96,"rq":96,"use_index_placeholders":false,"use_last_insert_id":true,"use_schema":false,"use_default_keyword":false,"use_auto_columns":false,"use_top_clause":false,"use_output_clause":false,"use_case_when_exists_clause":false},"tables":[{"name":"players","schema_name":"","columns":[{"name":"created_at","type":"time.Time","db_type":"timestamp","default":"","nullable":false,"unique":false,"validated":false,"arr_type":null,"udt_name":"","full_db_type":"timestamp","auto_generated":false},{"name":"id","type":"uint","db_type":"int","default":"auto_increment","nullable":false,"unique":true,"validated":false,"arr_type":null,"udt_name":"","full_db_type":"int(10) unsigned","auto_generated":false},{"name":"name","type":"string","db_type":"text","default":"","nullable":false,"unique":false,"validated":false,"arr_type":null,"udt_name":"","full_db_type":"text","auto_generated":false},{"name":"updated_at","type":"time.Time","db_type":"timestamp","default":"","nullable":false,"unique":false,"validated":false,"arr_type":null,"udt_name":"","full_db_type":"timestamp","auto_generated":false}],"p_key":{"name":"PRIMARY","columns":["id"]},"f_keys":null,"is_join_table":false,"to_one_relationships":null,"to_many_relationships":[{"name":"","table":"players","column":"id","nullable":false,"unique":true,"foreign_table":"teams","foreign_column":"id","foreign_column_nullable":false,"foreign_column_unique":true,"to_join_table":true,"join_table":"team_players","join_local_fkey_name":"FK_team_players_players","join_local_column":"player_id","join_local_column_nullable":false,"join_local_column_unique":false,"join_foreign_fkey_name":"FK_team_players_teams","join_foreign_column":"team_id","join_foreign_column_nullable":false,"join_foreign_column_unique":false}]},{"name":"team_players","schema_name":"","columns":[{"name":"player_id","type":"uint","db_type":"int","default":"","nullable":false,"unique":false,"validated":false,"arr_type":null,"udt_name":"","full_db_type":"int(10) unsigned","auto_generated":false},{"name":"team_id","type":"uint","db_type":"int","default":"","nullable":false,"unique":false,"validated":false,"arr_type":null,"udt_name":"","full_db_type":"int(10) unsigned","auto_generated":false}],"p_key":{"name":"PRIMARY","columns":["team_id","player_id"]},"f_keys":[{"table":"team_players","name":"FK_team_players_players","column":"player_id","nullable":false,"unique":false,"foreign_table":"players","foreign_column":"id","foreign_column_nullable":false,"foreign_column_unique":true},{"table":"team_players","name":"FK_team_players_teams","column":"team_id","nullable":false,"unique":false,"foreign_table":"teams","foreign_column":"id","foreign_column_nullable":false,"foreign_column_unique":true}],"is_join_table":true,"to_one_relationships":null,"to_many_relationships":null},{"name":"teams","schema_name":"","columns":[{"name":"created_at","type":"time.Time","db_type":"timestamp","default":"","nullable":false,"unique":false,"validated":false,"arr_type":null,"udt_name":"","full_db_type":"timestamp","auto_generated":false},{"name":"id","type":"uint","db_type":"int","default":"auto_increment","nullable":false,"unique":true,"validated":false,"arr_type":null,"udt_name":"","full_db_type":"int(10) unsigned","auto_generated":false},{"name":"name","type":"string","db_type":"text","default":"","nullable":false,"unique":false,"validated":false,"arr_type":null,"udt_name":"","full_db_type":"text","auto_generated":false},{"name":"updated_at","type":"time.Time","db_type":"timestamp","default":"","nullable":false,"unique":false,"validated":false,"arr_type":null,"udt_name":"","full_db_type":"timestamp","auto_generated":false}],"p_key":{"name":"PRIMARY","columns":["id"]},"f_keys":null,"is_join_table":false,"to_one_relationships":null,"to_many_relationships":[{"name":"","table":"teams","column":"id","nullable":false,"unique":true,"foreign_table":"players","foreign_column":"id","foreign_column_nullable":false,"foreign_column_unique":true,"to_join_table":true,"join_table":"team_players","join_local_fkey_name":"FK_team_players_teams","join_local_column":"team_id","join_local_column_nullable":false,"join_local_column_unique":false,"join_foreign_fkey_name":"FK_team_players_players","join_foreign_column":"player_id","join_foreign_column_nullable":false,"join_foreign_column_unique":false}]}],"templates":[{"name":"templates\\00_struct.go.tpl","loader":"asset:templates/00_struct.go.tpl"},{"name":"templates\\01_types.go.tpl","loader":"asset:templates/01_types.go.tpl"},{"name":"templates\\02_hooks.go.tpl","loader":"asset:templates/02_hooks.go.tpl"},{"name":"templates\\03_finishers.go.tpl","loader":"asset:templates/03_finishers.go.tpl"},{"name":"templates\\04_relationship_to_one.go.tpl","loader":"asset:templates/04_relationship_to_one.go.tpl"},{"name":"templates\\05_relationship_one_to_one.go.tpl","loader":"asset:templates/05_relationship_one_to_one.go.tpl"},{"name":"templates\\06_relationship_to_many.go.tpl","loader":"asset:templates/06_relationship_to_many.go.tpl"},{"name":"templates\\07_relationship_to_one_eager.go.tpl","loader":"asset:templates/07_relationship_to_one_eager.go.tpl"},{"name":"templates\\08_relationship_one_to_one_eager.go.tpl","loader":"asset:templates/08_relationship_one_to_one_eager.go.tpl"},{"name":"templates\\09_relationship_to_many_eager.go.tpl","loader":"asset:templates/09_relationship_to_many_eager.go.tpl"},{"name":"templates\\10_relationship_to_one_setops.go.tpl","loader":"asset:templates/10_relationship_to_one_setops.go.tpl"},{"name":"templates\\11_relationship_one_to_one_setops.go.tpl","loader":"asset:templates/11_relationship_one_to_one_setops.go.tpl"},{"name":"templates\\12_relationship_to_many_setops.go.tpl","loader":"asset:templates/12_relationship_to_many_setops.go.tpl"},{"name":"templates\\13_all.go.tpl","loader":"asset:templates/13_all.go.tpl"},{"name":"templates\\14_find.go.tpl","loader":"asset:templates/14_find.go.tpl"},{"name":"templates\\15_insert.go.tpl","loader":"asset:templates/15_insert.go.tpl"},{"name":"templates\\16_update.go.tpl","loader":"asset:templates/16_update.go.tpl"},{"name":"templates\\17_upsert.go.tpl","loader":"base64:(sha256 of content): 69aa632a19e1481b3f19bbebb432fcc3be871cd8b3e680b9f5ffa604152b1027"},{"name":"templates\\18_delete.go.tpl","loader":"asset:templates/18_delete.go.tpl"},{"name":"templates\\19_reload.go.tpl","loader":"asset:templates/19_reload.go.tpl"},{"name":"templates\\20_exists.go.tpl","loader":"asset:templates/20_exists.go.tpl"},{"name":"templates\\21_auto_timestamps.go.tpl","loader":"asset:templates/21_auto_timestamps.go.tpl"},{"name":"templates\\singleton\\boil_queries.go.tpl","loader":"asset:templates/singleton/boil_queries.go.tpl"},{"name":"templates\\singleton\\boil_table_names.go.tpl","loader":"asset:templates/singleton/boil_table_names.go.tpl"},{"name":"templates\\singleton\\boil_types.go.tpl","loader":"asset:templates/singleton/boil_types.go.tpl"},{"name":"templates\\singleton\\mysql_upsert.go.tpl","loader":"base64:(sha256 of content): cf5a76ec698efe64aa43537b3f2398b34d0d370f1b164ad6d801ebf7295b07d1"},{"name":"templates_test\\00_types.go.tpl","loader":"asset:templates_test/00_types.go.tpl"},{"name":"templates_test\\all.go.tpl","loader":"asset:templates_test/all.go.tpl"},{"name":"templates_test\\delete.go.tpl","loader":"asset:templates_test/delete.go.tpl"},{"name":"templates_test\\exists.go.tpl","loader":"asset:templates_test/exists.go.tpl"},{"name":"templates_test\\find.go.tpl","loader":"asset:templates_test/find.go.tpl"},{"name":"templates_test\\finishers.go.tpl","loader":"asset:templates_test/finishers.go.tpl"},{"name":"templates_test\\hooks.go.tpl","loader":"asset:templates_test/hooks.go.tpl"},{"name":"templates_test\\insert.go.tpl","loader":"asset:templates_test/insert.go.tpl"},{"name":"templates_test\\relationship_one_to_one.go.tpl","loader":"asset:templates_test/relationship_one_to_one.go.tpl"},{"name":"templates_test\\relationship_one_to_one_setops.go.tpl","loader":"asset:templates_test/relationship_one_to_one_setops.go.tpl"},{"name":"templates_test\\relationship_to_many.go.tpl","loader":"asset:templates_test/relationship_to_many.go.tpl"},{"name":"templates_test\\relationship_to_many_setops.go.tpl","loader":"asset:templates_test/relationship_to_many_setops.go.tpl"},{"name":"templates_test\\relationship_to_one.go.tpl","loader":"asset:templates_test/relationship_to_one.go.tpl"},{"name":"templates_test\\relationship_to_one_setops.go.tpl","loader":"asset:templates_test/relationship_to_one_setops.go.tpl"},{"name":"templates_test\\reload.go.tpl","loader":"asset:templates_test/reload.go.tpl"},{"name":"templates_test\\select.go.tpl","loader":"asset:templates_test/select.go.tpl"},{"name":"templates_test\\singleton\\boil_main_test.go.tpl","loader":"asset:templates_test/singleton/boil_main_test.go.tpl"},{"name":"templates_test\\singleton\\boil_queries_test.go.tpl","loader":"asset:templates_test/singleton/boil_queries_test.go.tpl"},{"name":"templates_test\\singleton\\boil_suites_test.go.tpl","loader":"asset:templates_test/singleton/boil_suites_test.go.tpl"},{"name":"templates_test\\singleton\\mysql_main_test.go.tpl","loader":"base64:(sha256 of content): ba7534dde32335bc1cad9bc8ea838f8dcdac2eb4bbcbf1aa0ee93e451a251126"},{"name":"templates_test\\singleton\\mysql_suites_test.go.tpl","loader":"base64:(sha256 of content): 10c471afd916418b4bfce8bafdfa4d2c01d100e1b078ee7fd065f3a143ba3ce7"},{"name":"templates_test\\types.go.tpl","loader":"asset:templates_test/types.go.tpl"},{"name":"templates_test\\update.go.tpl","loader":"asset:templates_test/update.go.tpl"},{"name":"templates_test\\upsert.go.tpl","loader":"base64:(sha256 of content): db92f57f80cc1faf82b2121906d469c357e45313494c87e7d9e0e1e6f56c4020"}]}

Please provide a relevant database schema so we can replicate your issue (Provided you are comfortable sharing this)

-- --------------------------------------------------------
-- Host:                         127.0.0.1
-- Server version:               8.0.12 - MySQL Community Server - GPL
-- Server OS:                    Win64
-- HeidiSQL Version:             9.5.0.5196
-- --------------------------------------------------------

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET NAMES utf8 */;
/*!50503 SET NAMES utf8mb4 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;

-- Dumping database structure for sqlboil_test
CREATE DATABASE IF NOT EXISTS `sqlboil_test` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */;
USE `sqlboil_test`;

-- Dumping structure for table sqlboil_test.players
CREATE TABLE IF NOT EXISTS `players` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `created_at` timestamp NOT NULL,
  `updated_at` timestamp NOT NULL,
  `name` text NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- Data exporting was unselected.
-- Dumping structure for table sqlboil_test.teams
CREATE TABLE IF NOT EXISTS `teams` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `created_at` timestamp NOT NULL,
  `updated_at` timestamp NOT NULL,
  `name` text NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- Data exporting was unselected.
-- Dumping structure for table sqlboil_test.team_players
CREATE TABLE IF NOT EXISTS `team_players` (
  `team_id` int(10) unsigned NOT NULL,
  `player_id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`team_id`,`player_id`),
  KEY `FK_team_players_players` (`player_id`),
  CONSTRAINT `FK_team_players_players` FOREIGN KEY (`player_id`) REFERENCES `players` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `FK_team_players_teams` FOREIGN KEY (`team_id`) REFERENCES `teams` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- Data exporting was unselected.
/*!40101 SET SQL_MODE=IFNULL(@OLD_SQL_MODE, '') */;
/*!40014 SET FOREIGN_KEY_CHECKS=IF(@OLD_FOREIGN_KEY_CHECKS IS NULL, 1, @OLD_FOREIGN_KEY_CHECKS) */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;

Further information. What did you do, what did you expect?

Attempting to eager load Teams when querying for Players causes an error:

models: failed to execute a one query for players: failed to eager load Teams: failed to scan eager loaded results for teams: sql: Scan error on column index 0, name "id": unsupported Scan, storing driver.Value type int64 into type *time.Time

This seems to be due to the generated code attempting to scan in columns in alphabetical order instead of the order they appear in the database. The generated code (app/models/players.go, LoadTeams in this example) contains:

err = results.Scan(&one.CreatedAt, &one.ID, &one.Name, &one.UpdatedAt, &localJoinCol)

when it should read

err = results.Scan(&one.ID, &one.CreatedAt, &one.UpdatedAt, &one.Name, &localJoinCol)

[aarondl]

This is pretty interesting. In my generated code it comes out exactly as you specified. Copy pasted from generated code:

        err = results.Scan(&one.ID, &one.CreatedAt, &one.UpdatedAt, &one.Name, &localJoinCol)

There's two differences between my test and yours:

• I didn't have whatever CHARSET and COLLATE you have going on, so I used CHARSET=utf8 COLLATE=utf8_general_ci. Could that be messing with something? I really doubt it.
• I'm using the latest dev branch of sqlboiler (though I tried on master and v3.0.1 and still could not recreate this issue).

Trying to understand how this could have happened...

[cpickett-ml]

I sat down this morning and ran go generate as well as the raw sqlboiler command and the Scan line was generated in the correct order. I got very confused, continued messing with things, and it continued to generate Scan in the correct order. Eventually my generates started putting the fields back in alphabetical order again, but I haven't been able to connect what is triggering the switch.

I'm going to continue trying for a consistent repro on my end as well.

[cpickett-ml]

Okay, so if I manually run the query that the sqlboiler code seems to be running to get column names against my "players" table as a test:

select
    c.column_name,
    c.column_type,
    if(c.data_type = 'enum', c.column_type, c.data_type),
    if(extra = 'auto_increment','auto_increment', c.column_default),
    c.is_nullable = 'YES',
        exists (
            select c.column_name
            from information_schema.table_constraints tc
            inner join information_schema.key_column_usage kcu
                on tc.constraint_name = kcu.constraint_name and tc.table_name = kcu.table_name and tc.table_schema = kcu.table_schema
            where c.column_name = kcu.column_name and tc.table_name = c.table_name and
                (tc.constraint_type = 'PRIMARY KEY' or tc.constraint_type = 'UNIQUE') and
                (select count(*) from information_schema.key_column_usage where table_schema = kcu.table_schema and table_name = tc.table_name and constraint_name = tc.constraint_name) = 1
        ) as is_unique
    from information_schema.columns as c
    where table_name = 'players' and table_schema='sqlboil_test' and c.extra not like '%VIRTUAL%'

I (currently) get the columns back in alphabetical order. I'm not familiar enough with this part of MySQL to understand what might cause this order to change.

I was expecting this to use "show create table" to figure out the columns instead of this query, but I guess that would turn the problem into a text-parsing problem instead of just having the db provide the info in a result set for you.

[cpickett-ml]

Selecting ordinal_position and ordering by it causes the columns to come back in the expected order 100% of the time:

select
    c.column_name,
    c.column_type,
    c.ordinal_position,
    if(c.data_type = 'enum', c.column_type, c.data_type),
    if(extra = 'auto_increment','auto_increment', c.column_default),
    c.is_nullable = 'YES',
        exists (
            select c.column_name
            from information_schema.table_constraints tc
            inner join information_schema.key_column_usage kcu
                on tc.constraint_name = kcu.constraint_name and tc.table_name = kcu.table_name and tc.table_schema = kcu.table_schema
            where c.column_name = kcu.column_name and tc.table_name = c.table_name and
                (tc.constraint_type = 'PRIMARY KEY' or tc.constraint_type = 'UNIQUE') and
                (select count(*) from information_schema.key_column_usage where table_schema = kcu.table_schema and table_name = tc.table_name and constraint_name = tc.constraint_name) = 1
        ) as is_unique
    from information_schema.columns as c
    where table_name = 'players' and table_schema='sqlboil_test' and c.extra not like '%VIRTUAL%' order by `ordinal_position`

Pulled from https://dev.mysql.com/doc/refman/8.0/en/columns-table.html which states

Unlike SHOW COLUMNS, SELECT from the COLUMNS table does not have automatic ordering.