[MDEV-5800] indexes on virtual (not materialized) columns - Jira

Details

Type: Task
Status: Closed (View Workflow)
Priority: Blocker
Resolution: Fixed
Fix Version/s: 10.2.3
Component/s: Virtual Columns
Labels:

Epic Link:
Oracle Compatibility
Sprint:
10.2.0-1, 10.2.0-2, 10.2.0-4, 10.2.0-6, 10.2.0-7, 10.2.0-8, 10.2.0-9, 10.2.0-10, 10.2.0-11, 10.2.2-4, 10.2.3-2, 10.0.28, 10.2.4-1, 10.2.4-4

Description

Currently to have a index on a virtual column, one has to materialized it. To support indexes on fully virtual columns, a storage engine must call back into the server to calculate the value of the virtual column.

Attachments

Issue Links

blocks

MDEV-6017 Add support for Indexes on Expressions

Stalled

MDEV-9056 JSON support

Closed

causes

MDEV-11703 InnoDB background threads show up in the processlist

Stalled

MDEV-11704 InnoDB: Failing assertion: dfield_is_null(dfield2) || dfield2->data

Closed

MDEV-11710 thd_destructor_proxy() hangs on shutdown after aborted startup

Closed

MDEV-11742 [Draft] InnoDB: Failing assertion: mysql_table

Closed

MDEV-12057 Embedded server shutdown hangs in InnoDB

Closed

MDEV-13039 innodb_fast_shutdown=0 may fail to purge all undo logs

Closed

MDEV-13568 gcol.innodb_virtual_debug_purge failed in buildbot with wrong result

Closed

MDEV-16887 innobase/trx/trx0rec.cc .. Assertion `n_idx > 0' failed

Closed

MDEV-18084 Server crashes in row_upd_changes_some_index_ord_field_binary or Assertion `pos < index->n_def' failed in dict_index_get_nth_field upon UPDATE after upgrade from 10.1/10.0

Closed

MDEV-20618 Assertion `btr_validate_index(index, 0, false)' failed in row_upd_sec_index_entry

Closed

MDEV-23294 Segfault or assertion upon MyISAM repair

Closed

MDEV-29666 InnoDB fails to purge secondary index records when indexed virtual columns exist

Closed

MDEV-33329 ALTER TABLE...FORCE fails to recalculate generated stored columns

Open

is part of

MDEV-10137 Providing compatibility to other databases

Open

relates to

MDEV-11518 Assertion `!table || (!table->read_set || bitmap_is_set(table->read_set, field_index))' failed in Field_long::val_int()

Closed

MDEV-11526 Server crashes in in TABLE::init

Closed

MDEV-11540 Unexpected system threads in the process list

Closed

MDEV-11541 Wrong result (extra rows) with an index on a VIRTUAL column

Closed

MDEV-11582 InnoDB: Failing assertion: !((field)->vcol_info && !(field)->stored_in_db())

Closed

MDEV-11590 Server crashes in create_tmp_table / JOIN::create_postjoin_aggr_table

Closed

MDEV-11591 Error: Freeing overrun buffer, Freeing unallocated data or underrun buffer

Closed

MDEV-11598 Assertion `!table || (!table->read_set || bitmap_is_set(table->read_set, field_index))' failed in Field_double::val_real()

Closed

MDEV-11604 Assertion `!check_datetime_range(ltime)' failed in TIME_to_longlong_datetime_packed

Closed

MDEV-11605 Assertion `(longlong) thd->status_var.local_memory_used >= 0 || !debug_assert_on_not_freed_memory' failed in my_malloc_size_cb_func

Closed

MDEV-11606 Server crashes in mi_make_key / sort_key_read

Closed

MDEV-11607 Server crashes in __memcpy_sse2_unaligned / String::realloc_raw / Field_blob::store_field

Closed

MDEV-11639 Server crashes in update_virtual_field, gcol.innodb_virtual_basic fails in buildbot

Closed

MDEV-11704 InnoDB: Failing assertion: dfield_is_null(dfield2) || dfield2->data

Closed

MDEV-11736 InnoDB: Failing assertion: n < rec_offs_n_fields(offsets)

Closed

MDEV-11737 Failing assertion: block->magic_n == MEM_BLOCK_MAGIC_N

Closed

MDEV-11750 Assertion `vfield' failed in TABLE::update_virtual_fields after crash recovery on corrupted MyISAM table

Closed

MDEV-11754 Invalid read of size 8 in malloc_size_and_flag / ... Field_blob::free() or crash in

Closed

MDEV-13795 ALTER TABLE…DROP PRIMARY KEY, ADD PRIMARY KEY fails when VIRTUAL columns exist

Closed

MDEV-14046 Allow ALGORITHM=INPLACE for 10.1 tables that contain virtual columns

Closed

MDEV-16332 Allow ALGORITHM=NOCOPY or INSTANT for changes of virtual column type

Confirmed

MDEV-17466 Virtual column value not available during purge

Closed

MDEV-17468 Avoid table rebuild on operations on generated columns

Stalled

MDEV-17540 Server crashes in dict_table_get_first_index after TRUNCATE TABLE

Closed

MDEV-17598 InnoDB index option for per-record transaction ID

Open

MDEV-17834 Server crashes in row_upd_build_difference_binary on LOAD DATA into table with indexed virtual column

Closed

MDEV-18981 Possible corruption when using FOREIGN KEY with virtual columns

Closed

MDEV-23632 ALTER TABLE...ADD KEY creates corrupted index on virtual column

Closed

MDEV-25047 SIGSEGV in mach_read_from_n_little_endian

Closed

MDEV-32089 Assertion `!strcmp(&path[strlen(path) - strlen(dot_ext[IBD])], dot_ext[IBD])' failed in void mtr_t::log_file_op(mfile_type_t, uint32_t, const char*, const char*)

Closed

MDEV-11517 Confusing error message upon attempt to create an indexed virtual column with partially supported functions

Open

MDEV-11561 Different handling of out-of-range values for virtual columns with and without indexes

Open

MDEV-13472 rpl.rpl_semi_sync_wait_point crashes because of thd_destructor_proxy

Closed

MDEV-13671 InnoDB should use case-insensitive column name comparisons like the rest of the server

Closed

MDEV-14080 InnoDB shutdown sometimes hangs

Closed

MDEV-14332 Corruption during online table-rebuilding ALTER when VIRTUAL columns exist, causes assertion failure

Closed

MDEV-15114 ASAN heap-use-after-free in mem_heap_dup or dfield_data_is_binary_equal

Closed

MDEV-16222 Assertion `0' failed in row_purge_remove_sec_if_poss_leaf on table with virtual columns and indexes

Closed

MDEV-17005 ASAN heap-use-after-free in innobase_get_computed_value

Closed

MDEV-18812 Add support for generated indexed virtual columns to aria

Open

MDEV-20015 Assertion `!in_use->is_error()' failed in TABLE::update_virtual_field

Closed

MDEV-22363 Reimplement the InnoDB virtual column code

Open

MDEV-23547 InnoDB: Failing assertion: *len in row_upd_ext_fetch

Closed

MDEV-31066 Aria Engine Does Not Allow Index On Virtual Columns

Closed

links to

See also "JSON Labs Release: Effective Functional Indexes in InnoDB"

See also WL#8114: Don't store virtual generated columns in database

See also WL#8149: B-tree Index Support on non-materialized virtual columns

See also WL#8227: Support SEs to create index on virtual generated columns

(10 causes, 1 is part of, 44 relates to, 4 links to)

Activity

Descending order - Click to sort in ascending order

Axel Schwenke added a comment - 2015-10-19 11:56 - edited

From a user right now on #maria: an index on a virtual column should be used not only when the virtual column is referenced by name, but also when the expression defining that column is used. That would mean that an index on a virtual column is equivalent to a functional index (MDEV-6017) on the expression defining that column.

Example:

CREATE TABLE t1 (c1 INT, c2 INT, c3 INT AS (c1+c2) VIRTUAL, INDEX (c3));

SELECT * FROM t1 WHERE c3=42;

SELECT * FROM t1 WHERE (c1+c2)=42;

The expectation is that both queries use the index on the virtual column. The first because the virtual column is referenced by name, the second because the virtual column is referenced by the defining expression. The index on c3 would behave like a functional index on (c1+c2)

Axel Schwenke added a comment - 2015-10-19 11:56 - edited From a user right now on #maria: an index on a virtual column should be used not only when the virtual column is referenced by name, but also when the expression defining that column is used. That would mean that an index on a virtual column is equivalent to a functional index ( MDEV-6017 ) on the expression defining that column. Example: CREATE TABLE t1 (c1 INT, c2 INT, c3 INT AS (c1+c2) VIRTUAL, INDEX (c3)); SELECT * FROM t1 WHERE c3=42; SELECT * FROM t1 WHERE (c1+c2)=42; The expectation is that both queries use the index on the virtual column. The first because the virtual column is referenced by name, the second because the virtual column is referenced by the defining expression. The index on c3 would behave like a functional index on (c1+c2)

Sergei Golubchik added a comment - 2015-03-13 12:59

https://lists.launchpad.net/maria-developers/msg08303.html

Sergei Golubchik added a comment - 2015-03-13 12:59 https://lists.launchpad.net/maria-developers/msg08303.html

Richa Sehgal added a comment - 2015-03-13 10:40

Hi,

I am Richa Sehgal currently pursuing Master’s at University of Illinois Urbana Champaign, USA. I did my undergraduate from Indian Institute of Technology Delhi (IIT-Delhi). I would like to take this up as my GSoC project.

Materialization gives us two things:
1. A name to the column which we can use in queries
2. A formal "regular" column which is stored and indexed in the regular fashion - Disadvantage: Extra memory requirements for the materialized column.

My initial thoughts on this project are the following:
We do need the name of the column which can be used to query. So maybe we can expose a command such as:

create virtual_index <name> on <column_name> <expression>

What this would do would run a regular query which evaluates expressions (like in WHERE clause) and the feed the result into the indexer. This index can then be stored in the regular fashion.
Am I approaching this in the right direction? Can you please point me to the next steps?
Thanks
Richa

Richa Sehgal added a comment - 2015-03-13 10:40 Hi, I am Richa Sehgal currently pursuing Master’s at University of Illinois Urbana Champaign, USA. I did my undergraduate from Indian Institute of Technology Delhi (IIT-Delhi). I would like to take this up as my GSoC project. Materialization gives us two things: 1. A name to the column which we can use in queries 2. A formal "regular" column which is stored and indexed in the regular fashion - Disadvantage: Extra memory requirements for the materialized column. My initial thoughts on this project are the following: We do need the name of the column which can be used to query. So maybe we can expose a command such as: create virtual_index <name> on <column_name> <expression> What this would do would run a regular query which evaluates expressions (like in WHERE clause) and the feed the result into the indexer. This index can then be stored in the regular fashion. Am I approaching this in the right direction? Can you please point me to the next steps? Thanks Richa

People

Assignee:: Sergei Golubchik

Reporter:: Sergei Golubchik

Votes:: 7 Vote for this issue

Watchers:: 13 Start watching this issue

Dates

Created:: 2014-03-06 00:25

Updated:: 2024-01-30 10:55

Resolved:: 2016-12-13 07:45

Git Integration

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

MariaDB Server