`SHOW VARIABLES` should render enum/set values

[fulghum]

SHOW VARIABLES seems to show the enum/set bitmask value, and not render the enum/set string values, which differs from MySQL's behavior.

In Dolt:

dolt> show variables like '%sql_mode%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sql_mode      | 256   |
+---------------+-------+

Versus in MySQL:

mysql> show variables like '%sql_mode%';
+---------------+-----------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value                                                                                                                 |
+---------------+-----------------------------------------------------------------------------------------------------------------------+
| sql_mode      | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
+---------------+-----------------------------------------------------------------------------------------------------------------------+

(Note that Dolt does render the enum/set strings when running SELECT @@sql_mode, just not with SHOW VARIABLES.)

[vavrajosef]

Hi, I just tried to reproduce it in "dolt sql" (I assume that's where the bug appeared), but I get the same response you are getting in mysql client. Which should be the correct behaviour.

Wasn't this resolved in some other PR or am I reproducing it incorrectly?

[timsehn]

I also can't repro using dolt sql or dolt sql-client. I don't have mysql on my windows box.

One shell:

PS C:\Users\timse\dolthub\dolt\test_dolt_sql> dolt sql -q "show variables like '%sql_mode%'"
+---------------+---------------------------------------------------------------+
| Variable_name | Value                                                         |
+---------------+---------------------------------------------------------------+
| sql_mode      | STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,ONLY_FULL_GROUP_BY |
+---------------+---------------------------------------------------------------+

PS C:\Users\timse\dolthub\dolt\test_dolt_sql> dolt sql-server
Starting server with Config HP="localhost:3306"|T="28800000"|R="false"|L="info"
2023-07-24T07:10:26-07:00 INFO [no conn] Server ready. Accepting connections. {}

Other shell after server started.

PS C:\Users\timse> dolt sql-client -u root
# Welcome to the Dolt MySQL client.
# Statements must be terminated with ';'.
# "exit" or "quit" (or Ctrl-D) to exit.
mysql> show variables like '%sql_mode%';
+---------------+---------------------------------------------------------------+
| Variable_name | Value                                                         |
+---------------+---------------------------------------------------------------+
| sql_mode      | STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,ONLY_FULL_GROUP_BY |
+---------------+---------------------------------------------------------------+
1 row in set (0.00 sec)

[fulghum]

Good catch. Looks like repro'ing this requires setting @@sql_mode first:

dolt> show variables like '%sql_mode%';
+---------------+---------------------------------------------------------------+
| Variable_name | Value                                                         |
+---------------+---------------------------------------------------------------+
| sql_mode      | STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,ONLY_FULL_GROUP_BY |
+---------------+---------------------------------------------------------------+

dolt> set @@sql_mode='ONLY_FULL_GROUP_BY';

dolt> show variables like '%sql_mode%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sql_mode      | 4096  |
+---------------+-------+

[vavrajosef]

If it showed anything else than bitmask, then it would behave differently from MySQL.
I just installed mariadb and got exatcly same result as is in dolt shell:

MySQL [(none)]> show variables like '%sql_mode%';
+---------------+---------------------------------------------------------------+
| Variable_name | Value                                                         |
+---------------+---------------------------------------------------------------+
| sql_mode      | STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,ONLY_FULL_GROUP_BY |
+---------------+---------------------------------------------------------------+
1 row in set (0.005 sec)

MySQL [(none)]> set @@sql_mode='ONLY_FULL_GROUP_BY';
Query OK, 1 row affected (0.005 sec)

MySQL [(none)]> show variables like '%sql_mode%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sql_mode      | 4096  |
+---------------+-------+
1 row in set (0.005 sec)

[fulghum]

Thanks for helping test this behavior with MariaDB. Very interesting that MariaDB also shows the bitmask for show variables, only after you set a value in @@sql_mode.

I'm testing with MySQL 8.0.33 and here's what I'm seeing:

mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.33    |
+-----------+

mysql> show variables like '%sql_mode%';
+---------------+-----------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value                                                                                                                 |
+---------------+-----------------------------------------------------------------------------------------------------------------------+
| sql_mode      | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
+---------------+-----------------------------------------------------------------------------------------------------------------------+

mysql> set @@sql_mode='ONLY_FULL_GROUP_BY';
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like '%sql_mode%';
+---------------+--------------------+
| Variable_name | Value              |
+---------------+--------------------+
| sql_mode      | ONLY_FULL_GROUP_BY |
+---------------+--------------------+

So, this behavior may actually differ between MariaDB and MySQL based on what you're seeing with MariaDB. It may have even changed or been fixed in a MySQL point release version. I think we want to match the behavior we're seeing above with MySQL 8.0.33.