results return trimmed content '...' suffix

[nbari]

Disabling pager, or using cat, vim -, more, less -RXF as a pager, always return the results with a ... suffix, the only way to remove it is by using \G, for example:

> select data from dashboard where id=1;
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| data                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| {"annotations":{"list":[{"builtIn":1,"datasource":"-- Grafana --","enable":true,"hide":true,"iconColor":"rgba(0, 211, 255, 1)","name":"Annotations \u0026 Alerts","type":"dashboard"}]},"editable":true,"gnetId":null,"graphTooltip":0,"id":1,"iteration":1568327616933,"links":[],"panels":[{"cacheTimeout":null,"colorBackground":true,"colorValue":false,"colors":["#d44a3a","rgba(237, 129, 40, 0.89)","#299c46"],"format":"none","gauge":{"maxValue":100,"minValue":0,"show":false,"thresholdLabels":false,"... |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

This behavior is ok, but in some cases, it may be good to have it work like the normal mysql client:

I am using:

 $ mycli --version
Version: 1.20.1

I am connection using mycli --ssl-ca /path/to/ca.crt with this .myclirc :

[main]
smart_completion = True
multi_line = False
destructive_warning = True
log_file = ~/.mycli.log
log_level = INFO
timing = True
table_format = ascii
syntax_style = default
key_bindings = vi
wider_completion_menu = False
prompt = "\d> "
prompt_continuation = "-> "
less_chatty = True
login_path_as_host = False
auto_vertical_output = False
keyword_casing = auto
[colors]
Token.Menu.Completions.Completion.Current = "bg:#00aaaa #000000"
Token.Menu.Completions.Completion = "bg:#008888 #ffffff"
Token.Menu.Completions.MultiColumnMeta = "bg:#aaffff #000000"
Token.Menu.Completions.ProgressButton = "bg:#003333"
Token.Menu.Completions.ProgressBar = "bg:#00aaaa"
Token.Output.Header = bold
Token.Output.OddRow = ""
Token.Output.EvenRow = ""
Token.SelectedText = "#ffffff bg:#6666aa"
Token.SearchMatch = "#ffffff bg:#4444aa"
Token.SearchMatch.Current = "#ffffff bg:#44aa44"
Token.Toolbar = "bg:#222222 #aaaaaa"
Token.Toolbar.Off = "bg:#222222 #888888"
Token.Toolbar.On = "bg:#222222 #ffffff"
Token.Toolbar.Search = noinherit bold
Token.Toolbar.Search.Text = nobold
Token.Toolbar.System = noinherit bold
Token.Toolbar.Arg = noinherit bold
Token.Toolbar.Arg.Text = nobold

Having the environment variable PAGER set to less -R

Any ideas?

[meeuw]

Hi @nbari the rationale is (as you might have guessed) to prevent totally unreadable tables, and you've already found the easiest workaround (use \G).

How would you like to see this solved? I can think of multiple solutions:

1. Add an command / configuration option to disable truncating (as a project value we'd like to keep the configuration to a minimum so this isn't the preferred option).
2. Use text wrapping (add newlines) to prevent unusable wide columns
3. Detect json/html and use a prettyfier
4. Add an output format that mimic the mysql client output (without truncating)

If you know a better solution, please share it! :-)

I also wonder why \G doesn't work for you, for the sample query you've posted I'd preferred the \G output format.

[nbari]

Hi @meeuw, option \G works fine for me, I was just wondering if there was an option to behave like the mysql client.

Probably adding an option in the configuration file, something like truncate: False could be an option. (not a must or priority I would say)

Regarding the current behavior, how internally mycli handles the data? If fetches the whole and later discards/truncates the results or if it detects that \G is not being used and based on that it will modify the queries by adding for example SUBSTRING to limit the amount of data the server will return?

[weirdan]

Can it be suppressed for file output, enabled with \o? I don't think display concerns should apply when I'm exporting the data as CSV or Markdown.

[j-bennet]

@weirdan it was not display concerns, but performance concerns:

https://github.com/dbcli/pgcli/issues/976

You have a good point with \o though, when you need to export data, truncation surely breaks things. If you set table format to CSV, however, the truncation is already turned off. We also switch it off if fields have newlines in them.

[weirdan]

If you set table format to CSV, however, the truncation is already turned off

Oh, didn't know that. I've tested it with Markdown and assumed other output formats behaved the same.

We also switch it off if fields have newlines in them.

Thanks for the hint, it actually provides a decent workaround I may use almost universally: appending a newline like this concat(field, "\n") as field disables the truncation without affecting the output much.

[hemna]

Using \G after running my query results in

(1065, 'Query was empty')

The query wasn't empty, just truncated. Is there a way to disable this truncating?

[j-bennet]

@hemna This is not how you use \G. It's a modifier that turns on expanded results mode, and you append it directly after the query:

Example without \G:

test> select * from j1
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    |
|----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
| {"query":{"function_score":{"boost_mode":"sum","score_mode":"sum","query":{"bool":{"must":[{"bool":{"should":[{"query_string":{"query":"\"quite happy\"","fields":["private.stem^0.95","title.stem^4.75","body.stem^1.9","type.stem^0.95","repository.stem^0.95","created_by.stem^0.95","url.stem^0.95","updated_by.stem^0.95","slug.stem^2.85","status.stem^0.95","assigned_to.stem^0.95","id^1.0"],"minimum_should_match":"1<-1 3<49%","phrase_slop":2}}]}}],"should":[{"multi_match":{"query":"\"quite happy\"... |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

Example with \G:

test> select * from j1 \G
-[ RECORD 1 ]-------------------------
t | {"query":{"function_score":{"boost_mode":"sum","score_mode":"sum","query":{"bool":{"must":[{"bool":{"should":[{"query_string":{"query":"\"quite happy\"","fields":["private.stem^0.95","title.stem^4.75","body.stem^1.9","type.stem^0.95","repository.stem^0.95","created_by.stem^0.95","url.stem^0.95","updated_by.stem^0.95","slug.stem^2.85","status.stem^0.95","assigned_to.stem^0.95","id^1.0"],"minimum_should_match":"1<-1 3<49%","phrase_slop":2}}]}}],"should":[{"multi_match":{"query":"\"quite happy\"","type":"phrase","boost":6,"slop":5,"fields":["private.stem^0.95","title.stem^4.75","body.stem^1.9","type.stem^0.95","repository.stem^0.95","created_by.stem^0.95","url.stem^0.95","updated_by.stem^0.95","slug.stem^2.85","status.stem^0.95","assigned_to.stem^0.95","id^1.0"]}}]}},"functions":[{"script_score":{"script":{"params":{"a":0.03,"b":0.02,"m":3.16E-11,"x":1623451658000},"source":"Math.max(_score * (((doc.containsKey(\"source.enum\") && !doc[\"source.enum\"].empty && ((doc[\"source.enum\"].length > 1) ? doc[\"source.enum\"].contains(\"github\") : doc[\"source.enum\"].value == \"github\") && doc.containsKey(\"status.enum\") && !doc[\"status.enum\"].empty && ((doc[\"status.enum\"].length > 1) ? doc[\"status.enum\"].contains(\"open\") : doc[\"status.enum\"].value == \"open\")) ? 2.0 : 1)) + ((_score * (0.1 * (params.a / (params.m * (params.x - (1 * (doc.containsKey(\"last_updated.date\") && !doc[\"last_updated.date\"].empty ? doc[\"last_updated.date\"].value.toInstant().toEpochMilli() : 0L))) + params.b))))) - _score, 0)"}}}]}},"sort":[{"_score":{"order":"desc"}}],"aggs":{"9b2ec59e-76bb-465c-9d4f-d2e451f85180":{"meta":{"filter_facet":true},"filter":{"bool":{"filter":{"bool":{"must":[{"terms":{"_index":[".ent-search-engine-documents-source-60c3da688742c861c1d330c4"]}}]}}}},"aggs":{"content_source_id.enum":{"terms":{"size":250,"field":"content_source_id.enum"}}}},"36c275ef-0c22-49ab-b5cb-d27d81329e03":{"meta":{"filter_facet":true},"filter":{"bool":{"filter":{"bool":{"must":[{"terms":{"_index":[".ent-search-engine-documents-source-60c3da688742c861c1d330c4"]}}]}}}},"aggs":{"type.enum":{"terms":{"size":250,"field":"type.enum"}}}},"d2aa28ed-f39c-4c29-9f65-a69e4d40d74c":{"meta":{"filter_facet":true},"filter":{"bool":{"filter":{"bool":{"must":[{"terms":{"_index":[".ent-search-engine-documents-source-60c3da688742c861c1d330c4"]}}]}}}},"aggs":{"status.enum":{"terms":{"size":250,"field":"status.enum"}}}},"e2b66682-2929-4c6a-94f3-a10ea5a2e83c":{"meta":{"filter_facet":true},"filter":{"bool":{"filter":{"bool":{"must":[{"terms":{"_index":[".ent-search-engine-documents-source-60c3da688742c861c1d330c4"]}}]}}}},"aggs":{"assigned_to.enum":{"terms":{"size":250,"field":"assigned_to.enum"}}}},"e0b622a5-c8ca-43e1-a18f-8aa90f6b0191":{"meta":{"filter_facet":true},"filter":{"bool":{"filter":{"bool":{"must":[{"terms":{"_index":[".ent-search-engine-documents-source-60c3da688742c861c1d330c4"]}}]}}}},"aggs":{"created_by.enum":{"terms":{"size":250,"field":"created_by.enum"}}}},"897bb705-2b83-44f2-82bc-d2d21df1ac9b":{"meta":{"filter_facet":true},"filter":{"bool":{"filter":{"bool":{"must":[{"terms":{"_index":[".ent-search-engine-documents-source-60c3da688742c861c1d330c4"]}}]}}}},"aggs":{"repository.enum":{"terms":{"size":250,"field":"repository.enum"}}}}},"highlight":{"fragment_size":300,"type":"plain","number_of_fragments":1,"order":"score","encoder":"html","require_field_match":false,"fields":{"private.stem":{"no_match_size":350},"private.prefix":{"no_match_size":350},"title.stem":{"no_match_size":350},"title.prefix":{"no_match_size":350},"body.stem":{"no_match_size":350},"body.prefix":{"no_match_size":350},"type.stem":{"no_match_size":350},"type.prefix":{"no_match_size":350},"repository.stem":{"no_match_size":350},"repository.prefix":{"no_match_size":350},"created_by.stem":{"no_match_size":350},"created_by.prefix":{"no_match_size":350},"url.stem":{"no_match_size":350},"url.prefix":{"no_match_size":350},"updated_by.stem":{"no_match_size":350},"updated_by.prefix":{"no_match_size":350},"slug.stem":{"no_match_size":350},"slug.prefix":{"no_match_size":350},"status.stem":{"no_match_size":350},"status.prefix":{"no_match_size":350},"assigned_to.stem":{"no_match_size":350},"assigned_to.prefix":{"no_match_size":350}},"highlight_query":{"multi_match":{"query":"\"quite happy\"","fields":["private.stem","private.prefix","title.stem","title.prefix","body.stem","body.prefix","type.stem","type.prefix","repository.stem","repository.prefix","created_by.stem","created_by.prefix","url.stem","url.prefix","updated_by.stem","updated_by.prefix","slug.stem","slug.prefix","status.stem","status.prefix","assigned_to.stem","assigned_to.prefix"]}}},"size":30,"from":0,"min_score":0.0,"_source":["private","title","body","type","repository","created_by","url","updated_by","slug","status","assigned_to","last_updated","_allow_permissions","_thumbnail_310x430","created_at","external_id","source","_subextracted_version","_thumbnail_80x100","updated_at","_subextracted_as_of","content_source_id","_deny_permissions"]}
SELECT 1
Time: 0.002s