3

I'm using MariaDB v5.x and want to return a long database's name with the query. I can successfully do this with a simple query, but with a more involved query the database name comes back truncated [34 characters]. Why does this happen and is there a way to retrieve the full, un-truncated name?

This example query works:

SELECT DATABASE() as 'database', hour as 'hour'
FROM time
WHERE hour = 1;

However, this type of query returns the truncated database name:

SELECT DATABASE() as 'database',
       t.hour as 'hour',
       d.day as 'day'
FROM   time t,
       day d
WHERE  t.day_pk = day.pk AND
       t.hour   = 1      AND
       d.day    = 22
UNION ALL
SELECT DATABASE() as 'database',
       t.hour as 'hour',
       d.day as 'day'
FROM   time t,
       day d
WHERE  t.day_pk = day.pk AND
       t.hour   = 1      AND
       d.day    = 24;

Thanks for any /all help.

D.

dave
  • 45
  • 3
  • "MariaDB 5.x"? The unsupported one? And what is "x" in this case? They tend to introduce some major changes even in minor releases. – mustaccio Jan 17 '22 at 17:59
  • Please show us the actual input and output. The characters involved may be a clue as to what went wrong. – Rick James Jan 17 '22 at 19:55
  • It may help to use a CAST(...) on at least the first Select. – Rick James Jan 17 '22 at 20:11
  • I assume you are using UNION to demonstrate the problem, if not you can use d.day in (22,24). FWIW, I would avoid identifiers like time and day since they are reserved words (and no I would not use quotes to get around that problem). You may also want to have a look at ANSI joins instead of ",". In the long run I find it easier to maintain my code with these. – Lennart - Slava Ukraini Jan 18 '22 at 06:04
  • For @Lennart, the SQL is representative of the actual query, but good points regarding using time and day. As for using ASNI joins ... this format is, for me, the easiest to read and use when trying to tie a dozen tables together. Finally, since I'm not an SQL guru, any example of the d.day in (22,24) would be most helpful. – dave Jan 19 '22 at 01:24

2 Answers2

2

I can confirm that the same behaviour is present even in MariaDB 10.7.1.

The truncation appears to happen when UNION is used.

Here is a workaround - use the substring function to indicate you want the full length:

SELECT substring(database() FROM 1 FOR length(database())) AS "database"
UNION ALL
SELECT substring(database() FROM 1 FOR length(database()));
dbdemon
  • 6,351
  • 4
  • 19
  • 38
2

Confirming what @dbdemon posted this is a bug that is also present in all versions. Using --column-type-info shows a little more insight as to what's happening.

$ db=$(printf 'a%.0s' {1..40})
$ echo $db
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
$ podman run -d --rm -e MYSQL_ALLOW_EMPTY_PASSWORD=1 -e MYSQL_DATABASE=$db -e MYSQL_USER=u -e MYSQL_PASSWORD=p --name m55 mariadb:5.5
1b2c0740bf664b8a3da8071bb96234a86fe8dd5f3eb6348598f5d0c2b59ede56

$ podman exec -ti m55 mysql -u u -pp  --column-type-info -e 'select database() as "database" union all select database()' $db
Field   1:  database
Catalog:    def
Database:   Table:
Org_table:  ``
Type:       VAR_STRING
Collation:  latin1_swedish_ci (8)
Length:     34
Max_length: 34
Decimals:   0
Flags:


+------------------------------------+
| database                           |
+------------------------------------+
| aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa |
| aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa |
+------------------------------------+


$ podman exec -ti m55 mysql -u u -pp  --column-type-info -e 'select database()' $db
Field   1:  database()
Catalog:    def
Database:   Table:
Org_table:  ``
Type:       VAR_STRING
Collation:  latin1_swedish_ci (8)
Length:     34
Max_length: 40
Decimals:   31
Flags:


+------------------------------------------+
| database()                               |
+------------------------------------------+
| aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa |
+------------------------------------------+

From the code we can see the 34 comes from the MAX_FIELD_NAME rather than the 64 NAME_CHAR_LEN from various definitions of Db in system tables.

These sorts of bugs are easy to fix. Please report them (whether easy or not).

Update: Bug MDEV-27544 created

Update: Fixed in 10.2.42+, 10.3.33+, 10.4.23+, 10.5.14+, 10.6.6+, 10.7.2+ and 10.8.0+

danblack
  • 7,719
  • 2
  • 10
  • 27
  • 1
    While a bit above my database knowledge, this information is quite interesting, thank you. – dave Jan 19 '22 at 01:37
  • It was a fun investigation, and just showing that MariaDB is just code and things happen for a reason. I've lodged MDEV-27544 as a bug report but the level of detail in your original question was enough to do a bug report. The currently minimum developed version is 10.2, so while you are on 5.5 you'll have to survive with @dbdemon's workarounds. By the time you upgrade it should be fixed. – danblack Jan 19 '22 at 02:45
  • For @danblack, the bug report is most awesome, thank you. Unfortunately, my legacy system will be stuck on v5.5 for quite some time, but when we eventually get to v10 there will lots of new tricks to take advantage of, this being one. – dave Jan 20 '22 at 00:33
  • Well its fixed now. Not really a trick, just something that should of been fixed a while ago (if anyone noticed). Happy to have any feedback of what would make an upgrade easier (probably off the channel, should be able to find my email easy enough). – danblack Jan 20 '22 at 09:56