MySQL Bugs: #99795: shell.dump_rows()/shell.dumpRows() does ont work with SqlResult

Bug #99795	shell.dump_rows()/shell.dumpRows() does ont work with SqlResult
Submitted:	7 Jun 2020 12:06	Modified:	3 Aug 2020 10:26
Reporter:	Jesper Wisborg Krogh	Email Updates:
Status:	Verified	Impact on me:	None
Category:	Shell General / Core Client	Severity:	S2 (Serious)
Version:	8.0.20, 8.0.21	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
MySQL Shell has a very nice function to take a result and generate it in the format you like. However, it does not work with SqlResult results even though the documentation (http://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-shell-output-formats.html and inside MySQL Shell) suggest is works with any result type.

How to repeat:
py> result = session.run_sql('SELECT 1 AS Val')

py> result
Query OK, 0 rows affected (0.0006 sec)

py> print(result)
<SqlResult>

py> shell.dump_rows(result)
0

py> result = session.run_sql('SELECT 1 AS Val')

py> shell.dump_rows(result)
0

py> session.run_sql('SELECT 1 AS Val')
+-----+
| Val |
+-----+
|   1 |
+-----+
1 row in set (0.0003 sec)

Is it related that result.has_data() returns false even though there is a result set?

py> result = session.run_sql('SELECT * FROM world.city WHERE ID = 130')

py> result.has_data()
false

py> shell.dump_rows(result)
0

py> result = session.run_sql('SELECT * FROM world.city WHERE ID = 130')

py> print(result.fetch_all())
[[130,"Sydney","AUS","New South Wales",3276207]]

Dear Jesper,

Thank you for the report and feedback.

regards,
Umesh

Interestingly enough, it works as expected when using it in external modules.

Posted by developer:
 
I was unable to reproduce the bug on mysqlsh versions 8.0.20, 8.0.21, and 8.0.22 on 64bit Ubuntu 20.04.

I can still reproduce in 8.0.21 on Windows and Oracle Linux 7:

py> result = session.run_sql('SELECT 1 AS Val')

py> shell.dump_rows(result)
0

py> \s
MySQL Shell version 8.0.21
...

One observation: when I use dump_rows() in scripts, I have noticed that sometimes I need to insert a short sleep (0.1 second seems to be enough) or the same issue can happen.

Same here on Win10:

MySQL Shell 8.0.21

Copyright (c) 2016, 2020, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its affiliates.
Other names may be trademarks of their respective owners.

Type '\help' or '\?' for help; '\quit' to exit.
 MySQL  JS > \c root@localhost
Creating a session to 'root@localhost'
Please provide the password for 'root@localhost': *********
Save password for 'root@localhost'? [Y]es/[N]o/Ne[v]er (default No):
Fetching schema names for autocompletion... Press ^C to stop.
Your MySQL connection id is 11 (X protocol)
Server version: 8.0.21 MySQL Community Server - GPL
No default schema selected; type \use <schema> to set one.
 MySQL  localhost:33060+ ssl  JS >
 MySQL  localhost:33060+ ssl  world  JS > \py
Switching to Python mode...
 MySQL  localhost:33060+ ssl  world  Py > result = session.run_sql('SELECT 1 AS Val')
 MySQL  localhost:33060+ ssl  world  Py > result
+-----+
| Val |
+-----+
|   1 |
+-----+
1 row in set (0.0005 sec)
 MySQL  localhost:33060+ ssl  world  Py > print(result)
<SqlResult>
 MySQL  localhost:33060+ ssl  world  Py > shell.dump_rows(result)
0

Posted by developer:
 
In Umesh's output there is no bug as "Py > result" prints and consumes the result. The same with shell.sump_rows(), after the call the result is consumed. Whenever you access the result again it will tell you there are no more rows.

In Jesper's output it looks like there is a bug, but I tested again, this time on Windows 10 both current 8.0.22 and official 8.0.21 build and I cannot reproduce the problem by neither running the commands by hand nor using a script. What I am running always gives the same result:

 MySQL  localhost:33060+ ssl  Py > res = session.run_sql("select 1;")
 MySQL  localhost:33060+ ssl  Py > shell.dump_rows(res)
+---+
| 1 |
+---+
| 1 |
+---+
1

Am I missing something?