`SHOW TABLE CREATE` `PRIMARY KEY` ordering

[max-hoffman]

SHOW CREATE TABLE prints a schema with PRIMARY KEY columns listed in column definition order, when they should be in primary key order:

dolt sql
>  create table e (pk int, x int, primary key (pk, x));

dolt commit -am "cm"
dolt checkout -b test
dolt sql
> alter table e add y int;
> alter table e drop primary key;
> alter table e add primary key (y, pk);

dolt checkout main
dolt diff test
diff --dolt a/e b/e
--- a/e @ 8ulk6of8cbm5g81fhg1psvrpslv1qh57
+++ b/e @ vf35a5qij9rab1c6l462cgnk2cn9dhtf
 CREATE TABLE `e` (
   `pk` int NOT NULL,
   `x` int NOT NULL,
-  PRIMARY KEY (`pk`,`x`)
+  `y` int NOT NULL,
+  PRIMARY KEY (`pk`,`y`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
warning: skipping data diff due to primary key set change

The correct output would be:

 CREATE TABLE `e` (
   `pk` int NOT NULL,
   `x` int NOT NULL,
-  PRIMARY KEY (`pk`,`x`)
+  `y` int NOT NULL,
+  PRIMARY KEY (`y`,`pk`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

plan/show_create_table.go -> produceCreateTableStatement needs to access the PK ordinals.

Implementation considerations:

• sqle/show_create_table.go shells out a SHOW CREATE TABLE, which could be replaced with a direct row iter?
• sql.Table interface could have PrimaryKeySchema() method
• or the ShowCreateTable node could have a PrimaryKeySchema populated by the optimizer

[max-hoffman]

simpler ex:

dolt sql
tmp> create table a (x int, y int, primary key (y,x));
tmp> show create table a;
+-------+-----------------------------------------------------+
| Table | Create Table                                        |
+-------+-----------------------------------------------------+
| a     | CREATE TABLE `a` (
  `x` int NOT NULL,
  `y` int NOT NULL,
  PRIMARY KEY (`x`,`y`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
+-------+-----------------------------------------------------+

desired output:

tmp> show create table a;
+-------+-----------------------------------------------------+
| Table | Create Table                                        |
+-------+-----------------------------------------------------+
| a     | CREATE TABLE `a` (
  `x` int NOT NULL,
  `y` int NOT NULL,
  PRIMARY KEY (`y`,`x`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
+-------+-----------------------------------------------------+

[zachmu]

This was fixed long ago.