Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-34880

Incorrect result for query with derived table having TEXT field

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Critical
    • Resolution: Fixed
    • 11.4.3, 11.0(EOL), 11.2, 11.4, 11.5(EOL)
    • 11.2.6, 11.4.4
    • Optimizer
    • Production

    Description

      The issue can be reproduced by creating the following table:

      CREATE TABLE `__testIssue` (
      		 
      	`ID` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
      		 
      	`name` VARCHAR(255) NOT NULL COLLATE 'utf8mb3_unicode_ci',
      		 
      	`notes` LONGTEXT NOT NULL COLLATE 'utf8mb3_unicode_ci',
      		 
      	PRIMARY KEY (`ID`) USING BTREE
      		 
      )
      		 
      COLLATE='utf8mb3_unicode_ci'
      		 
      ENGINE=InnoDB

      And then insert one test record:

      INSERT INTO __testIssue VALUES (NULL, 'test', 'test')

      And then this query gives 0 results in 11.4.3 while it did return results in 10.11.9:

      select test.ID, test.notes from (select ID, notes from __testIssue
      		 
      union
      		 
      select ID, notes from __testIssue) test
      		 
      WHERE ID = 1

      Some findings:

      • changing "WHERE ID = 1" into "WHERE ID > 0" does give results
      • changing the colum notes from LONGTEXT into varchar does also give results
      • removing the column notes from the select's does give results

      Attachments

        Issue Links

          causes

          Bug - A problem which impairs or prevents the functions of the product. MDEV-35270 Debug build Assertion from test: DBUG_ASSERT(!(table->key_info[active_index].flags & HA_UNIQUE_HASH)); connect.general

          • Blocker - Blocks development and/or testing work, production could not run.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-35342 Server crashes when creating index on a rocksdb table

          • Blocker - Blocks development and/or testing work, production could not run.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-35362 Server crashes in handler::ha_index_init upon adding index to RocksDB table

          • Blocker - Blocks development and/or testing work, production could not run.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-35399 Assertion `!(table->key_info[active_index].flags & 262144U)' failed in handler::ha_index_init

          • Major - Major loss of function.
          • Confirmed

          Activity

            People

              oleg.smirnov Oleg Smirnov
              Harold Harold
              Votes:
              0 Vote for this issue
              Watchers:
              6 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.