select from a subquery with information_schema: command denied to user 'restadmin'@'%'

[muescha]

Setup

dolt server created database restdb created user restadmin connected to database restdb as restadmin

Command denied

There is some other odd functionality with dolt:

SELECT SUM(found)
FROM (
    (SELECT 1 as found FROM information_schema.tables)
    UNION
    (SELECT 1 as found FROM information_schema.events)
) as all_found;

Result in Error:

[HY000][1105] command denied to user 'restadmin'@'%'

Commands working

But this SQL works:

(SELECT 1 as found FROM information_schema.tables)
    UNION
    (SELECT 1 as found FROM information_schema.events)

this also works:

 SELECT SUM(found)
FROM (
    (SELECT 1 as found FROM dual)
    UNION
    (SELECT 1 as found FROM dual)
) as all_found;

and this also works:

SELECT SUM(found)
FROM (
    (SELECT 1 as found FROM restdb.beer)
    UNION
    (SELECT 1 as found FROM restdb.beer)
) as all_found;

[muescha]

FYI: the full sql statement from flyway is:

SELECT SUM(found)
FROM ((SELECT 1 as found FROM information_schema.tables WHERE table_schema = 'restdb')
      UNION ALL
      (SELECT 1 as found FROM information_schema.views WHERE table_schema = 'restdb' LIMIT 1)
      UNION ALL
      (SELECT 1 as found FROM information_schema.table_constraints WHERE table_schema = 'restdb' LIMIT 1)
      UNION ALL
      (SELECT 1 as found FROM information_schema.triggers WHERE event_object_schema = 'restdb' LIMIT 1)
      UNION ALL
      (SELECT 1 as found FROM information_schema.routines WHERE routine_schema = 'restdb' LIMIT 1)
      UNION ALL
      (SELECT 1 as found FROM information_schema.events WHERE event_schema = 'restdb' LIMIT 1)) as all_found

[muescha]

changed the subject from UNION to subquery because removing theUNION result in same error:

SELECT SUM(found)
FROM (
         SELECT 1 as found FROM information_schema.tables
) as all_found;

restdb> SELECT SUM(found)
        FROM (
                 SELECT 1 as found FROM information_schema.tables
        ) as all_found
[2024-06-24 19:49:19] [HY000][1105] command denied to user 'restadmin'@'%'

[bpf120]

@muescha , Thank you for all of these bug reports. We'd love to learn about your use case too. Feel free to email me or swing by our Discord if you'd like to share

[muescha]

FYI: no error anymore with 1.41.1