Flex table interprets version string as a date

The string values '5.3.4' and '5.5.4' can be implicitly coerced to DATEs:

dbadmin=> SELECT '5.3.4'::DATE "Hmm..." UNION ALL SELECT '5.5.4'::DATE;
   Hmm...
------------
 2004-05-03
 2004-05-05
(2 rows)

Try temporily turing off the the EnableBetterFlexTypeGuessing parameter...

dbadmin=> SELECT current_value, default_value, description FROM configuration_parameters WHERE parameter_name = 'EnableBetterFlexTypeGuessing';
 current_value | default_value |                            description
---------------+---------------+-------------------------------------------------------------------
 0             | 1             | When computing flex table keys, attempt to guess non-string types
(1 row)

dbadmin=> SELECT set_config_parameter('EnableBetterFlexTypeGuessing', 0);
    set_config_parameter
----------------------------
 Parameter set successfully
(1 row)

dbadmin=> CREATE FLEX TABLE test();
CREATE TABLE

dbadmin=> \! cat /home/dbadmin/test.json
{"id":1, "version": "5.3.4"}
{"id":2, "version": "5.5.4"}

dbadmin=> COPY test FROM '/home/dbadmin/test.json' PARSER fjsonparser();
 Rows Loaded
-------------
           2
(1 row)

dbadmin=> SELECT compute_flextable_keys_and_build_view('test');
                            compute_flextable_keys_and_build_view
----------------------------------------------------------------------------------------------
 Please see public.test_keys for updated keys
The view public.test_view is ready for querying
(1 row)

dbadmin=> \dv public.test_view
                List of View Fields
 Schema |   View    | Column  |    Type     | Size
--------+-----------+---------+-------------+------
 public | test_view | id      | varchar(20) |   20
 public | test_view | version | varchar(20) |   20
(2 rows)

dbadmin=> SELECT set_config_parameter('EnableBetterFlexTypeGuessing', 1);
    set_config_parameter
----------------------------
 Parameter set successfully
(1 row)

See:
https://www.vertica.com/docs/9.3.x/HTML/Content/Authoring/FlexTables/SettingFlexTableParameters.htm

Or, you can also simply create your own view The actual data in the FLEX table is what you loaded. It's the view that's doing the data type coercion!

dbadmin=> DROP TABLE test;
DROP TABLE

dbadmin=> CREATE FLEX TABLE test();
CREATE TABLE

dbadmin=> COPY test FROM '/home/dbadmin/test.json' PARSER fjsonparser();
 Rows Loaded
-------------
           2
(1 row)

dbadmin=> SELECT compute_flextable_keys_and_build_view('test');
                            compute_flextable_keys_and_build_view
----------------------------------------------------------------------------------------------
 Please see public.test_keys for updated keys
The view public.test_view is ready for querying
(1 row)

dbadmin=> SELECT view_definition FROM views WHERE table_name = 'test_view';
                  view_definition
----------------------------------------------------
 SELECT id::!int8, version::!Date FROM public.test;
(1 row)

dbadmin=> CREATE OR REPLACE VIEW public.test_view AS SELECT id::!int8, version::!Varchar FROM public.test;
CREATE VIEW

dbadmin=> SELECT * FROM public.test_view;
 id | version
----+---------
  1 | 5.3.4
  2 | 5.5.4
(2 rows)

Thanks for the detailed response Jim.

It does not always 'guess' it as date, for the following JSON, it guesses 'version1' as Date but 'version2' as Varchar

{"version1":"6.1.4","version2":"7.0.2"}
{"version1" : "4.1.1","version2" : "7.0.1"}

I can disable 'EnableBetterFlexTypeGuessing' but dealing with boolean or numeric values requires some work.

I am not sure how 'data_type_guess' works but I am assuming it takes into account the top K occurrences of the values of the column, and determines which data type suits these top K values.
Anyways, it is an implementation detail which we do not have control over, so as a work around I was thinking if I update the '_keys' table with correct data type for this column and then build the view I should see correct results.

There is no month 0. They need to be 1 to 12. So a date parsing would fail.
In general , a one to two digit number, from 1 to 13, followed by a dot, followed by a one to two digit number, from one to twelve, followed by a dot, followed by either a number from 1 to 9999 , or a number from 1 to 99, is a date in European DMY format for Vertica.
So "6.1.4" is the sixth of January 2004, and "4.1.1" is the fourth of January 2001.

Not 1 to 13, of course, it's 1 to 31, my fingers were a bit un-synchronised ....

Thanks marcothesane, I didn't know it was a valid date format.

Hi sandeepdeva. It's been a few months since you asked this question, but I wanted to follow up on a related question you had.

What if you manually update the keys table to reflect the "right" data type? In a nutshell, the answer is yes you can safely do that because the contents of the keys table is derived from your flex table. This is a way to help the data type guessing algorithm, which is not always right as you saw. This algorithm does a best effort to guess the most suitable data type for a key given the values it sees in said key. Sometimes, it can get confused on values that can be coerced to multiple types, e.g., 1 is a integer but it can also be Boolean True, which one we choose? We won't get into the details of the algorithm here, but it suffices to say that build_flextable_view will take the updates in keys and come up with a new view object.

You may be wondering what if you manipulate the keys table too hard to the point that the resulting view isn't any good. In this case you can just truncate the keys table and re-run compute_flextable_keys to get you back to the initial state of the keys table.

I updated the keys table in your example and got the results you were looking for.

create flex table test();
CREATE TABLE
copy test from stdin parser fjsonparser(key_separator='_');
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
{"id":1, "version": "5.3.4"}
{"id":2, "version": "5.5.4"}>> 
\.
select compute_flextable_keys('test');
select * from test_keys;
-[ RECORD 1 ]---+--------
key_name        | id
frequency       | 2
data_type_guess | Integer
-[ RECORD 2 ]---+--------
key_name        | version
frequency       | 2
data_type_guess | Date
-- version column data type doesn't look right...

-- Let's create the view object and see what we get
select build_flextable_view('test');
select view_definition from views where table_name='test_view';
-[ RECORD 1 ]---+---------------------------------------------------
view_definition | SELECT id::!int8, version::!Date FROM public.test;

select * from test_view;
-[ RECORD 1 ]-------
id      | 1
version | 2004-05-03
-[ RECORD 2 ]-------
id      | 2
version | 2004-05-05
-- well, we suspected the version column wasn't going to look great

-- let's try to help the data type guesser by correcting the version data type
update test_keys set data_type_guess='Varchar(100)' where key_name='version';
commit;
-- now re-build the view
select build_flextable_view('test');
-- and check the new view definition
select view_definition from views where table_name='test_view';
-[ RECORD 1 ]---+--------------------------------------------------------------
view_definition | SELECT id::!int8, version::!"Varchar"(100)  FROM public.test;
-- now, query the view and see the results we expected
select * from test_view;
-[ RECORD 1 ]--
id      | 1
version | 5.3.4
-[ RECORD 2 ]--
id      | 2
version | 5.5.4