ダミーテーブルの件数が正しく出力されない

Question

一定間隔のダミーテーブルを出力する SELECT文の出力件数が実行ユーザーによって異なります。

SELECT * FROM
( SELECT @dt := '2023-03-09 00:00:00' AS start, 
         @dt +interval (60 - 1) second AS end 
  UNION 
  SELECT @dt := @dt + interval 60 second, 
         @dt +interval (60 - 1) second AS start 
  FROM information_schema.COLUMNS 
  WHERE @dt <= '2023-03-10 00:00:00' 
) AS T 

mysql> show grants for user001@localhost;
+-----------------------------------------------------------------------------------------+
| Grants for user001@localhost                                                            |
+-----------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `user001`@`localhost`                                             |
| GRANT SELECT ON `table001`.* TO `user001`@`localhost`                                   |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `table001`.`user_list` TO `user001`@`localhost` |
+-----------------------------------------------------------------------------------------+

・ルートユーザーの場合：1442件（正解）
・user001の場合：801件（足らない）

■追加
その後、確認のためルートユーザーでも日時を広げると正しく出力されませんでした。
WHERE @dt <= '2023-03-20 00:00:00'
件数：3601件（15,842件くらいになるはず）

この時の「SHOW WARNINGS」は以下でした。

mysql> SHOW WARNINGS;
+---------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                                                                                                                              |
+---------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Warning | 1287 | Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'. |
| Warning | 1287 | Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'. |
+---------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

問題になるような警告は出て無いようです。
このSELECT文のダミーテーブルでは多くの件数を作成できないのでしょうか？？

Comments

[yambejp]

まずは「SHOW WARNINGS」で確認してみてください

[hoshi-takanori]

「ほとんどの INFORMATION_SCHEMA テーブルでは、各 MySQL ユーザーはそれらにアクセスする権限を持ちますが、ユーザーが適切なアクセス権限を持つオブジェクトに対応するテーブルの行のみを表示できます。」
https://dev.mysql.com/doc/refman/8.0/ja/information-schema-introduction.html#information-schema-privileges

[YakumoSaki]

hoshi-takanori さんの記述は完璧な回答だと思うので回答欄に書いたほうが良いと思います。

[cupnew1988]

返答が遅くなりました。

質問に追加で示したように、rootで実行しても一定数以上のカラムは出力されません。
変数だけで出力するダミーテーブルですが、
これも何らかのアクセス権が影響している、ということでしょうか。

[hoshi-takanori]

手許の mysql で select count(*) from information_schema.COLUMNS; したら 3575 だったので、そんなものでは。

追記。MySQL 8 ならこんな方法もあるらしい。https://stackoverflow.com/a/70231968/1019868

Answer 1

ご質問のSQLを見ると、FROM information_schema.COLUMNS を行っているのでDB上に存在しているカラム一覧の件数までしか行を出力できないように見えます。
rootユーザーとそれ以外で件数が異なってしまった件もこれで説明がつきます。

とりあえず以下のようなSQLで問題ないように見える出力が得られました。
ご参考になれば幸いです。

MySQL 5.7 で動作確認しました。

SELECT count(*) FROM
  ( SELECT @dt := '2023-03-09 00:00:00' AS start,
           @dt +interval (60 - 1) second AS end
    UNION
    SELECT @dt := @dt + interval 60 second,
           @dt +interval (60 - 1) second AS start
    FROM
      (
        SELECT @row := @row + 1 AS row FROM
        (select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t,
        (select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t2,
        (select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t3,
        (select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t4,
        (select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t5,
        (select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t6,
        (SELECT @row:=0) numbers
      ) NUMTBL
    WHERE @dt <= '2023-03-20 00:00:00'
  ) AS T
;

大量の行数の生成は以下を参考にしました。
https://stackoverflow.com/questions/304461/generate-an-integer-sequence-in-mysql

Comments

[cupnew1988]

回答、ありがとうございます！

早速そのままを試してみましたが（MySQL 8.0 です）
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'row FROM
(select 0 union all select 1 union all select 2 union all selec' at line 9
となります。
環境によってなにか差異は考えられますか？

[cupnew1988]

変数 "row" を "rowno" に変更すればできました。（rowは予約語？）

内部演算の桁数が増えると少し応答が遅いです。
(select 0 union all ... select 9) t, の繰り返し回数が10のn乗ですね。
出力カラム数は５桁：100,000行で十分なので、t1 ～ t5 で対応しました。
ありがとうございました！！

[YakumoSaki]

遅い件も本当にそのとおりの理屈です。8.0であればWITH句を使えるのでもう少しスマートにできるかもしれません。何にせよ、お役に立ててよかったです。

[cupnew1988]

他の方の情報を参考にWITH句でも試しました。
cte_max_recursion_depth値がデフォルトで1000なので、最大値を変更すると出力できました。

set @@session.cte_max_recursion_depth = 100000;
WITH RECURSIVE nrows(date) AS (
SELECT '2023-03-09 00:00:00' UNION ALL SELECT DATE_ADD(date,INTERVAL 60 SECOND)
FROM nrows WHERE date<='2023-03-10 00:00:00')
SELECT date AS start, DATE_ADD(date,INTERVAL 59 SECOND) AS end FROM nrows;

8.0ではこちらのほうが高速ですね。
以上、皆さんありがとうございました。