No error shown when resizing STRING_LIST ColumnModel's maximimSize below size of list elements after MySQL version 8.0.18

Description

Test TableIndexDAOImplTest.testCreateAndPopulateListColumnIndexTables__StringListDataTooLarge is failing on MySQL 8.0.22
Does not fail on MYSQL 8.0.18

Environment

None

Activity

Show:
William Nguyen
April 23, 2021, 10:13 PM

Passes on MySQL 8.0.22

Ziming Dong
April 14, 2021, 12:50 AM
Edited

Caused by a bug in MySQL that we accidentally took advantage of.

I’m not sure exactly which version of MySQL after 8.0.18 decided to fix the bug(either not written in their release notes or I’m bad at reading).

Suppose we had a ColumnModel w/ maximumSize=30 and id=15

Original table with single row:

ROW_ID (BIGINT)

_C15_ (JSON)

0

[“tiny”, “longer than 5 characters”]

But then, a user wanted to change the column’s maximimSize to 5. We would first replace with a new ColumnModel definition w/ maximimSize=5 and id=16 since ColumnModels are immutable.

Original table with single row:

ROW_ID (BIGINT)

_C16_ (JSON)

0

[“tiny”, “longer than 5 characters”]

There would be no error in modifying the original table, since the data column is of type JSON and therefore does not have size limit.

When we populate a LIST column’s index table, we generate a select SQL statement to unnest the JSON Array in the original table:

The MySQL bug occurred on COLUMN_EXPAND VARCHAR(5). In older MySQL 8 versions, it would ignore the VARCHAR(5) restriction when inside of a JSON_TABLE definition.

However, when we populate the index table, something should go wrong because it will use a VARCHAR(5) column for the expanded list values:

TEMP_JSON_TABLE:

ROW_ID (BIGINT)

TEMP_JSON_TABLE.ORDINAL - 1 (BIGINT)

COLUMN_EXPAND (VARCHAR(5))

0

0

tiny

0

1

longer than 5 characters

When attempting to insert the unnested values into the temporary JSON_TABLE, we would expect a MySQL error since "longer than 5 characters" would not fit in a VARCHAR(5) column

 

However this will non longer occur in newer MySQL version, because "longer than 5 characters" will default to NULL!

 

In the newer MySQL versions the default behavior after their bug fix when any JSON Array values exceed the VARCHAR(5) limit is to set the value to NULL.

TEMP_JSON_TABLE:

ROW_ID (BIGINT)

TEMP_JSON_TABLE.ORDINAL - 1 (BIGINT)

COLUMN_EXPAND (VARCHAR(5))

0

0

tiny

0

1

NULL

Definitely not what we’d want since this makes the JSON column in the original table unsearchable! WE WANT THAT ERROR!

 

Luckily there’s a quick fix:

Change JSON_TABLE(_C16_, '$[*]' COLUMNS ( ORDINAL FOR ORDINALITY, COLUMN_EXPAND VARCHAR(5) PATH '$')) TEMP_JSON_TABLE

to JSON_TABLE(_C16_, '$[*]' COLUMNS ( ORDINAL FOR ORDINALITY, COLUMN_EXPAND VARCHAR(5) PATH '$' ERROR ON ERROR)) TEMP_JSON_TABLE

This will continue erroring on size differences as it did in PLFM-5999

Fixed
Your pinned fields
Click on the next to a field label to start pinning.

Assignee

Ziming Dong

Reporter

William Nguyen

Validator

William Nguyen

Priority

Major

Fix versions