[Django] #34671: Inspectdb collation issue with oracle views
Django
-------------------------------------+-------------------------------------
Reporter: Philipp | Owner: (none)
Maino |
Type: Bug | Status: new
Component: Error | Version: 4.2
reporting | Keywords: oracle, inspectdb,
Severity: Normal | collation
Triage Stage: | Has patch: 0
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
Given a simple oracle table with a primary id column and a VARCHAR column
as well as an oracle view that simply selects all column from that table
without any manipulation.
When using inspectdb to introspect the **table** we get a
{{{models.CharField}}} for the VARCHAR column **without** a
{{{db_collation}}} argument.
When using inspectdb to introspect the **view** we get a
{{{models.CharField}}} for the VARCHAR column **with** a
{{{db_collation}}} argument.
I believe this to be a bug that's being caused by
{{{DatabaseIntrospection.get_table_description}}} in
''django/db/backends/oracle/introspection.py''.
This will cause an issue when {{{DiscoverRunner.run_checks}}} triggers
{{{CharField._check_db_collation()}}} in
''django/db/models/fields/__init__.py'' as this section will return an
error if {{{db_collation is not None}}}.
**Recreate Issue**
{{{
CREATE TABLE COLLATION_TEST
(
ID NUMBER,
TEST VARCHAR2(10),
TEST2 NVARCHAR2(10),
TEST3 VARCHAR(10)
);
CREATE VIEW COLLATION_TEST_VIEW AS
SELECT *
FROM COLLATION_TEST;
SELECT *
FROM COLLATION_TEST_VW;
}}}
Run {{{manage.py inspectdb COLLATION_TEST}}}:
{{{
class CollationTest(models.Model):
id = models.FloatField(blank=True, null=True)
test = models.CharField(max_length=10, blank=True, null=True)
test2 = models.CharField(max_length=10, blank=True, null=True)
test3 = models.CharField(max_length=10, blank=True, null=True)
class Meta:
managed = False
db_table = 'collation_test'
}}}
Run {{{manage.py inspectdb COLLATION_TEST_VIEW}}}.
{{{
class CollationTestView(models.Model):
id = models.FloatField(blank=True, null=True)
test = models.CharField(max_length=10, db_collation='USING_NLS_COMP',
blank=True, null=True)
test2 = models.CharField(max_length=10, db_collation='USING_NLS_COMP',
blank=True, null=True)
test3 = models.CharField(max_length=10, db_collation='USING_NLS_COMP',
blank=True, null=True)
class Meta:
managed = False
db_table = 'collation_test_view'
}}}
If you consider this as a bug as well then this could be fixed for example
by adjusting {{{get_table_description}}}.
You could check first whether {{{table_name}}} is a table or a view by
querying {{{user_object}}} as column ''OBJECT_TYPE'' identifies an obhect
as ''TABLE' or "VIEW'.
[[Image(collation.png)]]
If it's a view you have to adjust the introspection query to use
{{{user_views}}} instead of {{{user_tables}}}. This will yield the correct
CharField definition without db_collation.
{{{
SELECT user_tab_cols.column_name,
user_tab_cols.data_default,
CASE
WHEN user_tab_cols.collation = user_views.default_collation
THEN NULL
ELSE user_tab_cols.collation
END collation,
CASE
WHEN user_tab_cols.char_used IS NULL
THEN user_tab_cols.data_length
ELSE user_tab_cols.char_length
END as display_size,
CASE
WHEN user_tab_cols.identity_column = 'YES' THEN 1
ELSE 0
END as is_autofield,
CASE
WHEN EXISTS (SELECT 1
FROM user_json_columns
WHERE user_json_columns.table_name =
user_tab_cols.table_name
AND user_json_columns.column_name =
user_tab_cols.column_name)
THEN 1
ELSE 0
END as is_json,
user_col_comments.comments as col_comment
FROM user_tab_cols
LEFT OUTER JOIN
user_views ON user_views.view_name = user_tab_cols.table_name
LEFT OUTER JOIN
user_col_comments ON
user_col_comments.column_name = user_tab_cols.column_name
AND
user_col_comments.table_name = user_tab_cols.table_name
WHERE user_tab_cols.table_name = 'COLLATION_TEST_VIEW'
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/34671>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
Django
-------------------------------------+-------------------------------------
Type: Bug | Status: new
Component: Error reporting | Version: 4.2
Severity: Normal | Resolution:
Keywords: oracle, inspectdb, | Triage Stage:
collation | Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* Attachment "collation.png" added.
Django
-------------------------------------+-------------------------------------
Reporter: Philipp Maino | Owner: (none)
Type: Bug | Status: new
Component: Error reporting | Version: 4.2
Severity: Normal | Resolution:
Keywords: oracle, inspectdb, | Triage Stage:
collation | Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Old description:
New description:
If it's a view you have to adjust the introspection query in
{{{get_table_description}}} to use {{{user_views}}} instead of
--
--
Ticket URL: <https://code.djangoproject.com/ticket/34671#comment:1>
Django
Old description:
New description:
Run {{{manage.py inspectdb COLLATION_TEST}}}:
--
--
Ticket URL: <https://code.djangoproject.com/ticket/34671#comment:2>
Django
-------------------------------------+-------------------------------------
Type: Bug | Status: new
Component: Core (Management | Version: 4.2
commands) |
Severity: Normal | Resolution:
Keywords: oracle, inspectdb, | Triage Stage: Accepted
collation |
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* owner: (none) => nobody
* component: Error reporting => Core (Management commands)
* stage: Unreviewed => Accepted
Comment:
Thanks for the report! Materialized views are also affected.
Unfortunately, data dictionary views are extremely slow on Oracle, we must
be carefully with introducing any additional complexity here.
--
Ticket URL: <https://code.djangoproject.com/ticket/34671#comment:3>
Django
-------------------------------------+-------------------------------------
Reporter: Philipp Maino | Owner: nobody
Type: Bug | Status: new
Component: Core (Management | Version: 4.2
commands) |
Severity: Normal | Resolution:
Keywords: oracle, inspectdb, | Triage Stage: Accepted
collation |
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by SecondPort):
Has patch : https://github.com/django/django/pull/17002
--
Ticket URL: <https://code.djangoproject.com/ticket/34671#comment:4>
Django
-------------------------------------+-------------------------------------
Reporter: Philipp Maino | Owner:
Type: Bug | Status: assigned
Component: Core (Management | Version: 4.2
commands) |
Severity: Normal | Resolution:
Keywords: oracle, inspectdb, | Triage Stage: Accepted
collation |
Needs tests: 1 | Patch needs improvement: 1
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* owner: nobody => SecondPort
* needs_better_patch: 0 => 1
* has_patch: 0 => 1
* status: new => assigned
* needs_tests: 0 => 1
--
Ticket URL: <https://code.djangoproject.com/ticket/34671#comment:5>
Django
-------------------------------------+-------------------------------------
| Felisiak
Type: Bug | Status: assigned
Component: Core (Management | Version: 4.2
commands) |
Severity: Normal | Resolution:
Keywords: oracle, inspectdb, | Triage Stage: Accepted
collation |
Has patch: 1 | Needs documentation: 0
-------------------------------------+-------------------------------------
Changes (by Mariusz Felisiak):
* owner: SecondPort => Mariusz Felisiak
* needs_better_patch: 1 => 0
* needs_tests: 1 => 0
Comment:
[https://github.com/django/django/pull/17012 New PR]
--
Ticket URL: <https://code.djangoproject.com/ticket/34671#comment:6>
Django
-------------------------------------+-------------------------------------
Reporter: Philipp Maino | Owner: Mariusz
| Felisiak
Component: Core (Management | Version: 4.2
commands) |
Keywords: oracle, inspectdb, | Triage Stage: Accepted
collation |
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* status: assigned => closed
* resolution: => fixed
Comment:
In [changeset:"a6d30f50125782db6643d4b24ff30d88adf13cbe" a6d30f5]:
{{{
#!CommitTicketReference repository=""
revision="a6d30f50125782db6643d4b24ff30d88adf13cbe"
Fixed #34671 -- Fixed collation introspection for views and materialized
views on Oracle.
Thanks Philipp Maino for the report.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/34671#comment:7>