It is so in all active versions of MariaDB and MySQL. I expect that bar will be able to explain why it is so.

A shorter script demonstrating the same problem:

CREATE OR REPLACE VIEW v1 AS SELECT 2019 as v1, 1 AS v2;

CREATE OR REPLACE VIEW v2 AS SELECT CONCAT(RIGHT(v1,2),'BARRQ',v2) AS v3 FROM v1;

DESC v2;

+-------+-------------+------+-----+---------+-------+

| Field | Type        | Null | Key | Default | Extra |

+-------+-------------+------+-----+---------+-------+

| v3    | varchar(18) | YES  |     | NULL    |       |

+-------+-------------+------+-----+---------+-------+

This is an intentional legacy behavior. The column "v2" has a data type of int(1).
However, the length in combination with an integer data type in MySQL (and hence in MariaDB) does not really restrict the value range. It stands for a "display length". It's explained here:

https://dev.mysql.com/doc/refman/8.0/en/numeric-type-attributes.html

So a column of an int(1) data type can still store all values in the range -2147483648..2147483647, which requires 11 characters.

You can use something like this as a workaround:

CREATE OR REPLACE VIEW v1 AS SELECT 2019 as v1, 1 AS v2;

CREATE OR REPLACE VIEW v2 AS SELECT CONCAT(RIGHT(v1,2),'BARRQ',CAST(v2 AS CHAR(1))) AS v3 FROM v1;

Ah, that makes sense. Unfortunately I was using the length reported by SHOW FIELDS to calculate an appropriate index prefix for a temporary table to load a subset of the VIEW into, e.g.:

CREATE TEMPORARY TABLE `data_view_temporary`

            (INDEX index_on_value_v3 (v3(10)))

            ENGINE=Innodb

            SELECT * FROM `data_view` WHERE ...

The first 10 characters for VARCHAR and TEXT columns is sufficient to make a reasonable index, and for all other data types I can just index the whole column. Of course I must not exceed the 1000 characters limit on all indexes on the temporary table, and I must not specify an index prefix that is longer than the width of a VARCHAR. TEXT columns are always longer than 10 characters. Hence, I have logic like this (in Ruby):

if (column_definition.type == :string && column_definition.limit > 10) || column_definition.type == :text

    index_prefix = 10

end

In the example above, the column_definition.limit (from SHOW FIELDS) is 18, and so my code applies an index prefix of 10 characters, but that fails:

Incorrect prefix key; the used key part isn't a string, the used length is longer than the key part, or the storage engine doesn't support unique prefix keys

So, if I cannot rely on the width of a column from SHOW FIELDS, how should I discover the maximum width of a column in a VIEW that is triggering the error message?

I cannot reproduce this.

This script works fine for me:

DROP VIEW IF EXISTS v1, v2, v3;

CREATE VIEW v1 AS SELECT 2019 as v1, 1 AS v2;

CREATE VIEW v2 AS SELECT CONCAT(RIGHT(v1,2),'BARRQ',v2) AS v3 FROM v1;

CREATE VIEW v3 AS SELECT v3 FROM v2;

DROP TEMPORARY  TABLE IF EXISTS t3;

CREATE TEMPORARY TABLE t3

            (INDEX index_on_value_v3 (v3(10)))

            ENGINE=Innodb

            SELECT * FROM v3;

Can you please paste the full SQL script that generates this error message?

This is odd. I have expanded your script in various ways in trying to reproduce the problem again, but it works on MariaDb 5.5 and MariaDb 10.2. I have created a source table that the VIEWs pull data from, added multiple values including NULL values in case that is confusing things, and added a final VIEW that simply copies through the calculated values which is similar to our production scenario:

MariaDB [global_production]> DROP TABLE IF EXISTS t1;

Query OK, 0 rows affected, 1 warning (0.00 sec)

MariaDB [global_production]> CREATE TABLE t1 (c1 INT(4), c2 INT(1));

Query OK, 0 rows affected (0.07 sec)

MariaDB [global_production]> INSERT INTO t1 VALUES (2019,1),(NULL,1),(2019,NULL);

Query OK, 3 rows affected (0.00 sec)

Records: 3  Duplicates: 0  Warnings: 0

MariaDB [global_production]> 

MariaDB [global_production]> CREATE VIEW v1 AS SELECT c1, c2 FROM t1;

Query OK, 0 rows affected (0.01 sec)

MariaDB [global_production]> SHOW FIELDS FROM v1;

+-------+--------+------+-----+---------+-------+

| Field | Type   | Null | Key | Default | Extra |

+-------+--------+------+-----+---------+-------+

| c1    | int(4) | YES  |     | NULL    |       |

| c2    | int(1) | YES  |     | NULL    |       |

+-------+--------+------+-----+---------+-------+

2 rows in set (0.00 sec)

MariaDB [global_production]> CREATE VIEW v2 AS SELECT c1 AS d1, c2 AS d2, CONCAT(RIGHT(c1,2),'BARRQ',c2) AS d3 FROM v1;

Query OK, 0 rows affected (0.00 sec)

MariaDB [global_production]> CREATE VIEW v3 AS SELECT d1 AS e1, d2 AS e2, d3 AS e3 FROM v2;

Query OK, 0 rows affected (0.00 sec)

MariaDB [global_production]> SHOW FIELDS FROM v3;

+-------+-------------+------+-----+---------+-------+

| Field | Type        | Null | Key | Default | Extra |

+-------+-------------+------+-----+---------+-------+

| e1    | int(4)      | YES  |     | NULL    |       |

| e2    | int(1)      | YES  |     | NULL    |       |

| e3    | varchar(18) | YES  |     | NULL    |       |

+-------+-------------+------+-----+---------+-------+

3 rows in set (0.00 sec)

MariaDB [global_production]>  

MariaDB [global_production]> DROP TEMPORARY TABLE IF EXISTS t2;

Query OK, 0 rows affected, 1 warning (0.01 sec)

MariaDB [global_production]> CREATE TEMPORARY TABLE t2 (

    ->     INDEX index_on_value_e1 (e1),

    ->     INDEX index_on_value_e2 (e2),

    ->     INDEX index_on_value_e3 (e3(10))

    ->   )

    ->   ENGINE=Innodb

    ->   SELECT * FROM v3;

Query OK, 3 rows affected (0.01 sec)

Records: 3  Duplicates: 0  Warnings: 0

MariaDB [global_production]> 

MariaDB [global_production]> SELECT * FROM t2;

+------+------+----------+

| e1   | e2   | e3       |

+------+------+----------+

| 2019 |    1 | 19BARRQ1 |

| NULL |    1 | NULL     |

| 2019 | NULL | NULL     |

+------+------+----------+

3 rows in set (0.00 sec)

Note that:

• the schema for v1 clearly shows that the narrow integer types have been inherited from the source table
• the schema for v3 shows that the narrow integer types have been inherited there too
• the type for the e3 column has been calculated as VARCHAR(18) as you explained above
• but the CREATE TEMPORARY TABLE does not fail when the index prefix is 10.

In my (much more complex) scenario, the CREATE TEMPORARY TABLE statement fails with "Incorrect prefix key; the used key part isn't a string, the used length is longer than the key part, or the storage engine doesn't support unique prefix keys", and I thought that was because the actual values were all less than 10 characters.

Clearly your script, and my expanded script show that the index prefix does not have to be less than the length of the values, but that contradicts my experience

https://dev.mysql.com/doc/refman/5.5/en/create-index.html#create-index-column-prefixes states

but I don't think that is relevant.

Perhaps I am misunderstanding the error message: the used key part is a string (generated by CONCAT), and the storage engine is InnoDb which does support unique prefix keys, so I am assuming that the error is referring to "the used length is longer than the key part". Can you provide an example in which that part of the error is triggered?

The "the used length is longer than the key part" is triggered in cases like this:

DROP TABLE IF EXISTS t1;

CREATE TABLE t1 (a VARCHAR(10), KEY(a(20)));

ERROR 1089 (HY000): Incorrect prefix key; the used key part isn't a string, the used length is longer than the key part, or the storage engine doesn't support unique prefix keys

Notice, the column size is 10, and the prefix size if 20.

This is not allowed. The prefix size must be less than (or equal to) the column size.

Yes, I understand that simple scenario. We have a much more complex scenario that caused the error, with calculated values, multiple levels of VIEWs and then creating a temporary table from the final VIEW.

I tried to cut the problem down to raise this issue, but clearly over-simplified it. In my previous comment you can see that the t2 temporary table has a 10 character index prefix on the e3 column, which seems to be allowed because the schema for the e3 column in the v3 VIEW is VARCHAR(18), but the values in the VIEW and the temporary table are all less than 10 characters. In our much more complex scenario we got the error, and the only explanation for it is the "the used length is longer than the key part", i.e. the index prefix of 10 characters is longer than the 8 character actual values.

I will try to reproduce our complex scenario again, and come up with a better reproducible script.

This error is raised at the table creation time (not at the table populating time).
The actual value size does not matter. It can be shorter than the prefix key length.