Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.3.23, 10.4.13, 10.6.7, 10.8.3
-
None
-
Linux
Description
This issue is a little bit similar to MDEV-17226
When selecting a large number of string characters witih an UNION the data gets truncated.
I did not found any variable which can change this behaviour and I did not get any answers on my stackoverflow question regarding this:
https://stackoverflow.com/questions/62404168/mariadb-cuts-of-text-when-select-with-union
Example is attached.
Simple example:
SELECT 'LARGETEXT' works fine.
SELECT 'SHORTTEXT' UNION SELECT 'LARGETEXT' truncated the text after about 34797 Bytes.
The order of the columns does not make any difference. So also
SELECT 'LARGETEXT' UNION SELECT 'SHORTTEXT'
does fail.
This seems to be critical for me because the user is not noticed about loss of data.
Attachments
Issue Links
- relates to
-
-
- Closed
-
Activity
| Field | Original Value | New Value |
|---|---|---|
| Comment |
[ It seems to be that
mysqld Ver 10.2.32-MariaDB-1:10.2.32+maria~bionic for debian-linux-gnu on x86_64 (mariadb.org binary distribution) is unaffected. Also mysql does not seem to have the same problem. ] |
| Link |
This issue relates to |
| Assignee | Alexander Barkov [ bar ] |
| Description |
This issue is a little bit similar to https://jira.mariadb.org/browse/MDEV-17226
When selecting a large number of string characters witih an UNION the data gets truncated. I did not found any variable which can change this behaviour and I did not get any answers on my stackoverflow question regarding this: https://stackoverflow.com/questions/62404168/mariadb-cuts-of-text-when-select-with-union Example is attached. Simple example: SELECT 'LARGETEXT' works fine. SELECT 'SHORTTEXT' UNION SELECT 'LARGETEXT' truncated the text after about 34797 Bytes. The order of the columns does not make any difference. So also SELECT 'LARGETEXT' UNION SELECT 'SHORTTEXT' does fail. This seems to be critical for me because the user is not noticed about loss of data. |
This issue is a little bit similar to When selecting a large number of string characters witih an UNION the data gets truncated. I did not found any variable which can change this behaviour and I did not get any answers on my stackoverflow question regarding this: https://stackoverflow.com/questions/62404168/mariadb-cuts-of-text-when-select-with-union Example is attached. Simple example: SELECT 'LARGETEXT' works fine. SELECT 'SHORTTEXT' UNION SELECT 'LARGETEXT' truncated the text after about 34797 Bytes. The order of the columns does not make any difference. So also SELECT 'LARGETEXT' UNION SELECT 'SHORTTEXT' does fail. This seems to be critical for me because the user is not noticed about loss of data. |
| Fix Version/s | 10.3 [ 22126 ] | |
| Fix Version/s | 10.4 [ 22408 ] | |
| Fix Version/s | 10.5 [ 23123 ] |
| Priority | Major [ 3 ] | Critical [ 2 ] |
So this is total half-knowledge, but maybe it helps.
All of the UNION Functions seem to use use VARCHAR Types. When loading the Data they correctly load all of the data and set the str_length correctly to 100328. BUT the varchars Types only have two Bytes to calculate the length (the first two bytes in front of the text in the data ptr). So when saving the length the calculation gives us 0x187E8 - make that little endian and cut of the last byte (we only have space for two!) we get 0xE887.
Back from Little Endian that gives us 34792. And that's exactly the amount of text we're getting!
I have absolutely no clue where the heck the UNION Functions decide which type to use for the answer data, but Varchar just isn't enough.
Hopefully that helps a bit.
| Assignee | Alexander Barkov [ bar ] | Oleksandr Byelkin [ sanja ] |
I tried some git bisect, but I failed to pin down the exact commit which is responsible for the bug.
(many commits can't be build and so I can't test them!)
# bad: [0025eb3f963fdca88028ff14a27d9b9638079337] Merge mariadb-10.3.24
|
# good: [dd33a70dad2b32aa6fcdbd1f8161d5351f6dbebd] Merge mariadb-10.2.36 into 10.2
|
# bad: [2232784f0094ae7e81f5033fe8f3499e15224fed] Fix inadverent change in Sql_alloc
|
# bad: [e01d788428270d2039f1e9adf637fbd185ee91a0] Optimized away excessive condition
|
# bad: [46cf221815e4d0ddc708270a99c1b9e952016011] Merge remote-tracking branch 'origin/bb-10.2-ext' into 10.3
|
# bad: [474f51711b1abbba799d93a0223ef38316b78f6c] Merge bb-10.2-ext into 10.3
|
# bad: [25c06f5282513e15c25c54364ef886310126e870] Optimize LEX_STRING comparisons
|
# bad: [a70809c0fcd5eca38c5f6bafa92270f1704a0597] MDEV-13555 Assertion `!item->null_value' failed in Type_handler::Item_send_str
|
# bad: [0f554dd0fdc54a07e38d6fd28b2f79a3dfc08713] Adjusted result files for tokudb tests.
|
# good: [749bbb3d7b213b5044d97006259f013d70007d60] MDEV-11357 Split Item_cache::get_cache() into virtual methods in Type_handler
|
# good: [ec8c38a82ec1bb00f681a3f6662bd5c76ebb7c75] Merge remote-tracking branch 'origin/10.2' into bb-10.2-ext
|
# bad: [cf9e0bf3e6d266f9b56fd13ceef82d66b1d92a46] Fixed compiler warning
|
# good: [e43156e1a679c95413aab2c4bf9523da7947fc18] Removing the duplicate copy of char_to_byte_length_safe().
|
# good: [30c36b2c150d6bf52e56abfbef755119fbc773f9] Make rocksdb.rocksdb_icp test stable
|
# bad: [0f348bcd965cd47784634508da2202aba2dc0547] MDEV-13302 Avoid using LEX::spname during CREATE PROCEDURE and CREATE FUNCTION
|
# bad: [0f348bcd965cd47784634508da2202aba2dc0547] MDEV-13302 Avoid using LEX::spname during CREATE PROCEDURE and CREATE FUNCTION
|
# good: [42f657cd2fab9acb00b15e00c7cc630f64ecc6ec] MDEV-13267 At startup with crash recovery: mtr_t::commit_checkpoint(lsn_t, bool): Assertion `!recv_no_log_write' failed
|
# good: [42f657cd2fab9acb00b15e00c7cc630f64ecc6ec] MDEV-13267 At startup with crash recovery: mtr_t::commit_checkpoint(lsn_t, bool): Assertion `!recv_no_log_write' failed
|
# bad: [9bc32256427373e5bfa78f17c5854ac4e6a2fc31] Merge tag 'mariadb-10.2.6' into bb-10.2-ext
|
# good: [02ada41744382fea11621a5a8094cbbea06c9487] MDEV-12721 Wrong execution plan for WHERE (date_field <=> timestamp_expr AND TRUE)
|
# good: [276b0c8ef03046cc210e4eeab7231cb8d9f16bac] Fixed crash with SEQUENCE when using REPAIR
|
# possible first bad commit: [9bc32256427373e5bfa78f17c5854ac4e6a2fc31] Merge tag 'mariadb-10.2.6' into bb-10.2-ext
|
# possible first bad commit: [77b2f55f61a4e3ba642049a01651c92bfb0a7d9d] Follow-up fixes for MDEV-10139 Support for InnoDB SEQUENCE objects
|
# possible first bad commit: [e1f81822c399e07d771ce6b8504da084765e430b] A cleanup for MDEV-11514, MDEV-11497, MDEV-11554, MDEV-11555 - IN and CASE type aggregation problems
|
# possible first bad commit: [109bc47084c72164130fc2e735ffe371713ede43] Fixing a few data type related problems: MDEV-12875, MDEV-12886, MDEV-12916
|
# possible first bad commit: [54e29712a39b1ec53663d483003a1b5c14ff5766] Adding tests for MDEV-12917 Wrong data type for CREATE..SELECT year_sp_variable
|
# possible first bad commit: [a4789f52d8f7437fab1bf156f7e190b6e025122b] More tests for SEQUENCE's
|
# possible first bad commit: [8acf4d6f783e5a5f736624064989b88867143685] Follow-up fixes for MDEV-10139 Support for InnoDB SEQUENCE objects
|
# possible first bad commit: [9497a646791fd350e3e96611e73f69d10d24bca8] Fixed failing test sql_sequence.replication
|
# possible first bad commit: [d60e5fe391702de722cdff2624cc46c8c0ed1d84] Simple replication test for sequences
|
# possible first bad commit: [e5145a5ac3e52b697b095c5338f7244c7e7a5119] MDEV-12546 Wrong metadata or data type for string user variables
|
# possible first bad commit: [90f06818b3250fd44aff4235875c7b6dd6ba8041] MDEV-12876 Wrong data type for CREATE..SELECT sp_var
|
# possible first bad commit: [62b62319bf92e9c00da8498fe6e9944a4e43ed7d] A cleanup for the patch for MDEV-12852, MDEV-12853, MDEV-12869
|
# possible first bad commit: [6a779a6d28fa69be4c89b72205a11398859f07ca] Make SEQUENCE working with replication
|
# possible first bad commit: [d9304914bed2d6d45dbc9f43aa1e2f7ea3bbeb13] Fixing a few problems with data type and metadata for INT result functions (MDEV-12852, MDEV-12853, MDEV-12869)
|
# possible first bad commit: [9b79888df82259743284501af5b156a18edc427f] MDEV-12866 Out-of-range error with CREATE..SELECT..TO_SECONDS(NOW())
|
# possible first bad commit: [c84bbeda7f42e940553aee5377f64d2452872138] MDEV-12858 + MDEV+12859 + MDEV-12862 - a join patch fixing a few data type problems with CREATE..SELECT
|
# possible first bad commit: [feb15f4e45788550eb20292f22ab74d5fd7242c7] MDEV-12860 Out-of-range error on CREATE..SELECT with a view using MAX and EXTRACT(MINUTE_MICROSECOND..)
|
# possible first bad commit: [8c479820da2ef10438485864a65ceb3ac750c228] MDEV-12856 Wrong .. metadata for DIV + MDEV-12857..errors on CREATE..SELECT..DIV
|
# possible first bad commit: [a8caa8e04a27f7463abd4fd588a4ce1799e600c7] MDEV-12854 Synchronize CREATE..SELECT data type and result set metadata data type for INT functions
|
# possible first bad commit: [d2fec340d25c24c8862f461b1f3272c098e67d54] An after-fix for MDEV-12849 Out-of-range errors when casting hex-hybrid to SIGNED and UNSIGNED
|
# possible first bad commit: [ac4ce47b09f8869431dfc7a71a250f38ca84b406] MDEV-12849 Out-of-range errors when casting hex-hybrid to SIGNED and UNSIGNED
|
# possible first bad commit: [732cfabafdddd8f3c3abc1f097a971924f2f9e13] Merge branch 'halfspawn-bb-10.2-ext' into bb-10.2-ext
|
# possible first bad commit: [87d952746f46679128ad5d73fb690ddfce325ace] MDEV-12783 : sql_mode=ORACLE: Functions LENGTH() and LENGTHB()
|
# possible first bad commit: [5b034f1cf86b91a463878ce7e63018aa5e4ab072] MDEV-12833 Split Column_definition::create_length_to_internal_length() to virtual methods in Type_handler
|
# possible first bad commit: [278c3ea756d0ad0ad66f56651a2c6facdf4168d1] MDEV-12826 Add Type_handler::val_int_signed_typecast() and Type_handler::val_int_unsigned_typecast()
|
# possible first bad commit: [fba7fbbc5c7bb1d05488108a29b854ee8ef0066a] MDEV-9397 Split field.cc:calc_pack_length() into virtual methods in Type_handler
|
# possible first bad commit: [f1b729d35270383048842f2023a5c42769d6637f] MDEV-9188 Split Column_definition::check() into virtual methods in Type_handler
|
# possible first bad commit: [705fc43eaafccd7a41e541f3149a917850f4e2fb] MDEV-12775 Reuse data type aggregation code for hybrid functions and UNION
|
# possible first bad commit: [7c44b8afb781c533898dfb7321dd5cbb88c32ce0] MDEV-12798 Item_param does not preserve exact field type in EXECUTE IMMEDIATE 'CREATE TABLE AS SELECT ?' USING POINT(1,1)
|
# possible first bad commit: [38acc29ccba3911318477baf6ddff79a9f8697a2] Fixed buildbot failures with --embedded-server
|
# possible first bad commit: [238eb410053a2691f397933dd190238119cb0c97] MDEV-12784 Change Item_func_length::print() to display octet_length() rather than length()
|
# possible first bad commit: [7beb8ff27498c4b2955c4908f16ec9b9321b1bee] A --ps cleanup for MDEV-12658 Make the third parameter to LPAD and RPAD optional
|
# possible first bad commit: [533506b4edb5873dc25ae335dbd018da37235f2b] MDEV-12777 Change Lex_field_type_st::m_type from enum_field_types to Type_handler pointer
|
# possible first bad commit: [191638416b3dbc206b4955639533e87cd488550c] MDEV-12772 Add Field::get_typelib() and Item::get_typelib()
|
# possible first bad commit: [0f642188ccdd309056f3a2a29be6cf3cc83dc256] MDEV-12771 Remove Item_func_xxx::decimal_precision() for case and abbreviations
|
# possible first bad commit: [cd32f842148d57c33e13a22a663278b7faa4ed24] MDEV-12770 Add Type_handler::decimal_precision() + MDEV-12769
|
# possible first bad commit: [18ad176809999275ac7e86a586d41c8c9141f4a4] MDEV-12685 Oracle-compatible function CHR()
|
# possible first bad commit: [71fa413c165e644f8f1433356f95fed12579fe3e] MDEV-10139 Support for SEQUENCE objects
|
# possible first bad commit: [1e04ad284c6ac0a9ce433f827bc6dbfbd6029007] Fixed compiler warnings and warnings from build.tags
|
#
|
Commit https://github.com/MariaDB/server/commit/705fc43eaafccd7a41e541f3149a917850f4e2fb could be the problem: MDEV-12775 Reuse data type aggregation code for hybrid functions and UNION
I again followed the rabbit down the hole...
In the working version in st_select_lex_unit::prepare after throwing the error 'ER_WRONG_NUMBER_OF_COLUMNS_IN_SELECT' we are calling Item_type_holder::join_types().
Thats exactly the function which finds out that our first column in the select (the 'a') has a datatype of varchar, and our second column (the very long text) has a type of medium_blob.
After finding that out, it switches the column for the result from varchar to mediumblob. It also writes that clearly in the debug log:
<st_select_lex_unit::prepare_join
|
>Item_type_holder::join_types
|
info:: was type varchar len 3, dec 39 name a |
info:: in type varchar len 300984, dec 39 |
info: become type: mediumblob len: 300984 dec: 39 |
In newer version the code to call join_types() and also the function Item_type_holder::join_types() is completely gone. And so we don't fix up the field and let the result be a varchar. Which is too short for our second Union row which is a mediumblob.
Granted, I have no idea if that missing fixup is made at some other point in the source, but in the actual mysql source it's still using join_types()
This also points to this Case where join_types() has been removed: https://jira.mariadb.org/browse/MDEV-12775
And this commit: https://github.com/MariaDB/server/commit/705fc43eaafccd7a41e541f3149a917850f4e2fb
To me it seems that sq_union.cc, these lines just don't work:
// In case of a non-recursive UNION, join data types for all UNION parts. |
if (!is_recursive && join_union_item_types(thd, types, union_part_count)) |
goto err; |
In st_select_lex_unit::join_union_type_handlers(), item->real_type_handler() we get the Type Handler 'Type_handler_varchar' for both columns. Thats wrong. The second column should get the Type Handler 'Type_handler_mediumblob' and also this type handler should be used for the result column, which it isn't.
| Workflow | MariaDB v3 [ 110139 ] | MariaDB v4 [ 142023 ] |
Also happens in:
10.6.7-MariaDB-2ubuntu1
10.8.3-MariaDB-1:10.8.3+maria~jammy
| Affects Version/s | 10.8.3 [ 27502 ] | |
| Affects Version/s | 10.6.7 [ 26812 ] |
| Priority | Critical [ 2 ] | Major [ 3 ] |
| Fix Version/s | 10.3 [ 22126 ] |
| Fix Version/s | 10.4 [ 22408 ] |
Problem maybe started with Version 10.3 ->
Works on:
10.2.32-MariaDB-1:10.2.32+maria~bionic for debian-linux-gnu on x86_64 (mariadb.org binary distribution)
Problems on:
10.3.23-MariaDB-1:10.3.23+maria~focal mariadb.org binary distribution
10.4.13-MariaDB-1:10.4.13+maria~focal for debian-linux-gnu on x86_64 (mariadb.org binary distribution)
10.4.13-MariaDB-1:10.4.13+maria~bionic mariadb.org binary distribution