firebird-qa/tests/bugs/gh_6545_test.py

#coding:utf-8

"""
ID:          issue-6545
ISSUE:       https://github.com/FirebirdSQL/firebird/issues/6545
TITLE:       Error writing to TIMESTAMP/TIME WITH TIME ZONE array
DESCRIPTION:
    Test generates values which will be inserted into ARRAY columns defined as 'time with time zone' and 'timestamp with time zone'.
    We process time zones defined in the FB_HOME/include/firebird/TimeZones.h (except several, see notes below),
    and use every time zone value as argument to 'get_timezone()' in datetime.dsatetime / datetime.time calls.
    Then we run DML which tries to insert tuple of generated data into appropriate ARRAY columns. This must not raise error.
    Finally, we run query to get just inserted data and compare its result with input argument that was used in previous step.
NOTES:
    [15.08.2024] pzotov
    1. ### ACHTUNG ### TEST REQUIRES FIREBIRD-DRIVER VERSION 1.10.6+ (date: 15-aug-2024).
       See also addition in firebird-driver doc:
       https://firebird-driver.readthedocs.io/en/latest/usage-guide.html#working-with-time-timestamp-with-timezone

    2. Following timezones present in $FB_HOME/include/firebird/TimeZones.h
       and in https://en.wikipedia.org/wiki/List_of_tz_database_time_zones
       but are absent in pytz.all_timezones list:
         America/Ciudad_Juarez
         Europe/Kyiv
         Pacific/Kanton
       We have to SKIP them from handling.

    Checked on 4.0.0.436, 5.0.2.1478, 4.0.6.3142
"""
import datetime
from firebird.driver import get_timezone
import random

import pytest
from firebird.qa import *

init_script = """
    create table test (
        arr_tmtz time with time zone [0:2]
       ,arr_tstz timestamp with time zone [0:2]
    );
"""
db = db_factory(init = init_script)

act = python_act('db')

@pytest.mark.version('>=4.0')
def test_1(act: Action, capsys):
    fb_time_zones = [
         'Africa/Abidjan'
        ,'Africa/Accra'
        ,'Africa/Addis_Ababa'
        ,'Africa/Algiers'
        ,'Africa/Asmara'
        ,'Africa/Asmera'
        ,'Africa/Bamako'
        ,'Africa/Bangui'
        ,'Africa/Banjul'
        ,'Africa/Bissau'
        ,'Africa/Blantyre'
        ,'Africa/Brazzaville'
        ,'Africa/Bujumbura'
        ,'Africa/Cairo'
        ,'Africa/Casablanca'
        ,'Africa/Ceuta'
        ,'Africa/Conakry'
        ,'Africa/Dakar'
        ,'Africa/Dar_es_Salaam'
        ,'Africa/Djibouti'
        ,'Africa/Douala'
        ,'Africa/El_Aaiun'
        ,'Africa/Freetown'
        ,'Africa/Gaborone'
        ,'Africa/Harare'
        ,'Africa/Johannesburg'
        ,'Africa/Juba'
        ,'Africa/Kampala'
        ,'Africa/Khartoum'
        ,'Africa/Kigali'
        ,'Africa/Kinshasa'
        ,'Africa/Lagos'
        ,'Africa/Libreville'
        ,'Africa/Lome'
        ,'Africa/Luanda'
        ,'Africa/Lubumbashi'
        ,'Africa/Lusaka'
        ,'Africa/Malabo'
        ,'Africa/Maputo'
        ,'Africa/Maseru'
        ,'Africa/Mbabane'
        ,'Africa/Mogadishu'
        ,'Africa/Monrovia'
        ,'Africa/Nairobi'
        ,'Africa/Ndjamena'
        ,'Africa/Niamey'
        ,'Africa/Nouakchott'
        ,'Africa/Ouagadougou'
        ,'Africa/Porto-Novo'
        ,'Africa/Sao_Tome'
        ,'Africa/Timbuktu'
        ,'Africa/Tripoli'
        ,'Africa/Tunis'
        ,'Africa/Windhoek'
        ,'America/Adak'
        ,'America/Anchorage'
        ,'America/Anguilla'
        ,'America/Antigua'
        ,'America/Araguaina'
        ,'America/Argentina/Buenos_Aires'
        ,'America/Argentina/Catamarca'
        ,'America/Argentina/ComodRivadavia'
        ,'America/Argentina/Cordoba'
        ,'America/Argentina/Jujuy'
        ,'America/Argentina/La_Rioja'
        ,'America/Argentina/Mendoza'
        ,'America/Argentina/Rio_Gallegos'
        ,'America/Argentina/Salta'
        ,'America/Argentina/San_Juan'
        ,'America/Argentina/San_Luis'
        ,'America/Argentina/Tucuman'
        ,'America/Argentina/Ushuaia'
        ,'America/Aruba'
        ,'America/Asuncion'
        ,'America/Atikokan'
        ,'America/Atka'
        ,'America/Bahia'
        ,'America/Bahia_Banderas'
        ,'America/Barbados'
        ,'America/Belem'
        ,'America/Belize'
        ,'America/Blanc-Sablon'
        ,'America/Boa_Vista'
        ,'America/Bogota'
        ,'America/Boise'
        ,'America/Buenos_Aires'
        ,'America/Cambridge_Bay'
        ,'America/Campo_Grande'
        ,'America/Cancun'
        ,'America/Caracas'
        ,'America/Catamarca'
        ,'America/Cayenne'
        ,'America/Cayman'
        ,'America/Chicago'
        ,'America/Chihuahua'
        # -- !! -- ,'America/Ciudad_Juarez'
        ,'America/Coral_Harbour'
        ,'America/Cordoba'
        ,'America/Costa_Rica'
        ,'America/Creston'
        ,'America/Cuiaba'
        ,'America/Curacao'
        ,'America/Danmarkshavn'
        ,'America/Dawson'
        ,'America/Dawson_Creek'
        ,'America/Denver'
        ,'America/Detroit'
        ,'America/Dominica'
        ,'America/Edmonton'
        ,'America/Eirunepe'
        ,'America/El_Salvador'
        ,'America/Ensenada'
        ,'America/Fort_Nelson'
        ,'America/Fort_Wayne'
        ,'America/Fortaleza'
        ,'America/Glace_Bay'
        ,'America/Godthab'
        ,'America/Goose_Bay'
        ,'America/Grand_Turk'
        ,'America/Grenada'
        ,'America/Guadeloupe'
        ,'America/Guatemala'
        ,'America/Guayaquil'
        ,'America/Guyana'
        ,'America/Halifax'
        ,'America/Havana'
        ,'America/Hermosillo'
        ,'America/Indiana/Indianapolis'
        ,'America/Indiana/Knox'
        ,'America/Indiana/Marengo'
        ,'America/Indiana/Petersburg'
        ,'America/Indiana/Tell_City'
        ,'America/Indiana/Vevay'
        ,'America/Indiana/Vincennes'
        ,'America/Indiana/Winamac'
        ,'America/Indianapolis'
        ,'America/Inuvik'
        ,'America/Iqaluit'
        ,'America/Jamaica'
        ,'America/Jujuy'
        ,'America/Juneau'
        ,'America/Kentucky/Louisville'
        ,'America/Kentucky/Monticello'
        ,'America/Knox_IN'
        ,'America/Kralendijk'
        ,'America/La_Paz'
        ,'America/Lima'
        ,'America/Los_Angeles'
        ,'America/Louisville'
        ,'America/Lower_Princes'
        ,'America/Maceio'
        ,'America/Managua'
        ,'America/Manaus'
        ,'America/Marigot'
        ,'America/Martinique'
        ,'America/Matamoros'
        ,'America/Mazatlan'
        ,'America/Mendoza'
        ,'America/Menominee'
        ,'America/Merida'
        ,'America/Metlakatla'
        ,'America/Mexico_City'
        ,'America/Miquelon'
        ,'America/Moncton'
        ,'America/Monterrey'
        ,'America/Montevideo'
        ,'America/Montreal'
        ,'America/Montserrat'
        ,'America/Nassau'
        ,'America/New_York'
        ,'America/Nipigon'
        ,'America/Nome'
        ,'America/Noronha'
        ,'America/North_Dakota/Beulah'
        ,'America/North_Dakota/Center'
        ,'America/North_Dakota/New_Salem'
        ,'America/Nuuk'
        ,'America/Ojinaga'
        ,'America/Panama'
        ,'America/Pangnirtung'
        ,'America/Paramaribo'
        ,'America/Phoenix'
        ,'America/Port-au-Prince'
        ,'America/Port_of_Spain'
        ,'America/Porto_Acre'
        ,'America/Porto_Velho'
        ,'America/Puerto_Rico'
        ,'America/Punta_Arenas'
        ,'America/Rainy_River'
        ,'America/Rankin_Inlet'
        ,'America/Recife'
        ,'America/Regina'
        ,'America/Resolute'
        ,'America/Rio_Branco'
        ,'America/Rosario'
        ,'America/Santa_Isabel'
        ,'America/Santarem'
        ,'America/Santiago'
        ,'America/Santo_Domingo'
        ,'America/Sao_Paulo'
        ,'America/Scoresbysund'
        ,'America/Shiprock'
        ,'America/Sitka'
        ,'America/St_Barthelemy'
        ,'America/St_Johns'
        ,'America/St_Kitts'
        ,'America/St_Lucia'
        ,'America/St_Thomas'
        ,'America/St_Vincent'
        ,'America/Swift_Current'
        ,'America/Tegucigalpa'
        ,'America/Thule'
        ,'America/Thunder_Bay'
        ,'America/Tijuana'
        ,'America/Toronto'
        ,'America/Tortola'
        ,'America/Vancouver'
        ,'America/Virgin'
        ,'America/Whitehorse'
        ,'America/Winnipeg'
        ,'America/Yakutat'
        ,'America/Yellowknife'
        ,'Antarctica/Casey'
        ,'Antarctica/Davis'
        ,'Antarctica/DumontDUrville'
        ,'Antarctica/Macquarie'
        ,'Antarctica/Mawson'
        ,'Antarctica/McMurdo'
        ,'Antarctica/Palmer'
        ,'Antarctica/Rothera'
        ,'Antarctica/South_Pole'
        ,'Antarctica/Syowa'
        ,'Antarctica/Troll'
        ,'Antarctica/Vostok'
        ,'Arctic/Longyearbyen'
        ,'Asia/Aden'
        ,'Asia/Almaty'
        ,'Asia/Amman'
        ,'Asia/Anadyr'
        ,'Asia/Aqtau'
        ,'Asia/Aqtobe'
        ,'Asia/Ashgabat'
        ,'Asia/Ashkhabad'
        ,'Asia/Atyrau'
        ,'Asia/Baghdad'
        ,'Asia/Bahrain'
        ,'Asia/Baku'
        ,'Asia/Bangkok'
        ,'Asia/Barnaul'
        ,'Asia/Beirut'
        ,'Asia/Bishkek'
        ,'Asia/Brunei'
        ,'Asia/Calcutta'
        ,'Asia/Chita'
        ,'Asia/Choibalsan'
        ,'Asia/Chongqing'
        ,'Asia/Chungking'
        ,'Asia/Colombo'
        ,'Asia/Dacca'
        ,'Asia/Damascus'
        ,'Asia/Dhaka'
        ,'Asia/Dili'
        ,'Asia/Dubai'
        ,'Asia/Dushanbe'
        ,'Asia/Famagusta'
        ,'Asia/Gaza'
        ,'Asia/Harbin'
        ,'Asia/Hebron'
        ,'Asia/Ho_Chi_Minh'
        ,'Asia/Hong_Kong'
        ,'Asia/Hovd'
        ,'Asia/Irkutsk'
        ,'Asia/Istanbul'
        ,'Asia/Jakarta'
        ,'Asia/Jayapura'
        ,'Asia/Jerusalem'
        ,'Asia/Kabul'
        ,'Asia/Kamchatka'
        ,'Asia/Karachi'
        ,'Asia/Kashgar'
        ,'Asia/Kathmandu'
        ,'Asia/Katmandu'
        ,'Asia/Khandyga'
        ,'Asia/Kolkata'
        ,'Asia/Krasnoyarsk'
        ,'Asia/Kuala_Lumpur'
        ,'Asia/Kuching'
        ,'Asia/Kuwait'
        ,'Asia/Macao'
        ,'Asia/Macau'
        ,'Asia/Magadan'
        ,'Asia/Makassar'
        ,'Asia/Manila'
        ,'Asia/Muscat'
        ,'Asia/Nicosia'
        ,'Asia/Novokuznetsk'
        ,'Asia/Novosibirsk'
        ,'Asia/Omsk'
        ,'Asia/Oral'
        ,'Asia/Phnom_Penh'
        ,'Asia/Pontianak'
        ,'Asia/Pyongyang'
        ,'Asia/Qatar'
        ,'Asia/Qostanay'
        ,'Asia/Qyzylorda'
        ,'Asia/Rangoon'
        ,'Asia/Riyadh'
        ,'Asia/Saigon'
        ,'Asia/Sakhalin'
        ,'Asia/Samarkand'
        ,'Asia/Seoul'
        ,'Asia/Shanghai'
        ,'Asia/Singapore'
        ,'Asia/Srednekolymsk'
        ,'Asia/Taipei'
        ,'Asia/Tashkent'
        ,'Asia/Tbilisi'
        ,'Asia/Tehran'
        ,'Asia/Tel_Aviv'
        ,'Asia/Thimbu'
        ,'Asia/Thimphu'
        ,'Asia/Tokyo'
        ,'Asia/Tomsk'
        ,'Asia/Ujung_Pandang'
        ,'Asia/Ulaanbaatar'
        ,'Asia/Ulan_Bator'
        ,'Asia/Urumqi'
        ,'Asia/Ust-Nera'
        ,'Asia/Vientiane'
        ,'Asia/Vladivostok'
        ,'Asia/Yakutsk'
        ,'Asia/Yangon'
        ,'Asia/Yekaterinburg'
        ,'Asia/Yerevan'
        ,'Atlantic/Azores'
        ,'Atlantic/Bermuda'
        ,'Atlantic/Canary'
        ,'Atlantic/Cape_Verde'
        ,'Atlantic/Faeroe'
        ,'Atlantic/Faroe'
        ,'Atlantic/Jan_Mayen'
        ,'Atlantic/Madeira'
        ,'Atlantic/Reykjavik'
        ,'Atlantic/South_Georgia'
        ,'Atlantic/St_Helena'
        ,'Atlantic/Stanley'
        ,'Australia/ACT'
        ,'Australia/Adelaide'
        ,'Australia/Brisbane'
        ,'Australia/Broken_Hill'
        ,'Australia/Canberra'
        ,'Australia/Currie'
        ,'Australia/Darwin'
        ,'Australia/Eucla'
        ,'Australia/Hobart'
        ,'Australia/LHI'
        ,'Australia/Lindeman'
        ,'Australia/Lord_Howe'
        ,'Australia/Melbourne'
        ,'Australia/NSW'
        ,'Australia/North'
        ,'Australia/Perth'
        ,'Australia/Queensland'
        ,'Australia/South'
        ,'Australia/Sydney'
        ,'Australia/Tasmania'
        ,'Australia/Victoria'
        ,'Australia/West'
        ,'Australia/Yancowinna'
        ,'Brazil/Acre'
        ,'Brazil/DeNoronha'
        ,'Brazil/East'
        ,'Brazil/West'
        ,'CET'
        ,'CST6CDT'
        ,'Canada/Atlantic'
        ,'Canada/Central'
        ,'Canada/Eastern'
        ,'Canada/Mountain'
        ,'Canada/Newfoundland'
        ,'Canada/Pacific'
        ,'Canada/Saskatchewan'
        ,'Canada/Yukon'
        ,'Chile/Continental'
        ,'Chile/EasterIsland'
        ,'Cuba'
        ,'EET'
        ,'EST'
        ,'EST5EDT'
        ,'Egypt'
        ,'Eire'
        ,'Etc/GMT'
        ,'Etc/GMT+0'
        ,'Etc/GMT+1'
        ,'Etc/GMT+10'
        ,'Etc/GMT+11'
        ,'Etc/GMT+12'
        ,'Etc/GMT+2'
        ,'Etc/GMT+3'
        ,'Etc/GMT+4'
        ,'Etc/GMT+5'
        ,'Etc/GMT+6'
        ,'Etc/GMT+7'
        ,'Etc/GMT+8'
        ,'Etc/GMT+9'
        ,'Etc/GMT-0'
        ,'Etc/GMT-1'
        ,'Etc/GMT-10'
        ,'Etc/GMT-11'
        ,'Etc/GMT-12'
        ,'Etc/GMT-13'
        ,'Etc/GMT-14'
        ,'Etc/GMT-2'
        ,'Etc/GMT-3'
        ,'Etc/GMT-4'
        ,'Etc/GMT-5'
        ,'Etc/GMT-6'
        ,'Etc/GMT-7'
        ,'Etc/GMT-8'
        ,'Etc/GMT-9'
        ,'Etc/GMT0'
        ,'Etc/Greenwich'
        ,'Etc/UCT'
        ,'Etc/UTC'
        ,'Etc/Universal'
        ,'Etc/Zulu'
        ,'Europe/Amsterdam'
        ,'Europe/Andorra'
        ,'Europe/Astrakhan'
        ,'Europe/Athens'
        ,'Europe/Belfast'
        ,'Europe/Belgrade'
        ,'Europe/Berlin'
        ,'Europe/Bratislava'
        ,'Europe/Brussels'
        ,'Europe/Bucharest'
        ,'Europe/Budapest'
        ,'Europe/Busingen'
        ,'Europe/Chisinau'
        ,'Europe/Copenhagen'
        ,'Europe/Dublin'
        ,'Europe/Gibraltar'
        ,'Europe/Guernsey'
        ,'Europe/Helsinki'
        ,'Europe/Isle_of_Man'
        ,'Europe/Istanbul'
        ,'Europe/Jersey'
        ,'Europe/Kaliningrad'
        ,'Europe/Kiev'
        ,'Europe/Kirov'
        # -- !! -- ,'Europe/Kyiv'
        ,'Europe/Lisbon'
        ,'Europe/Ljubljana'
        ,'Europe/London'
        ,'Europe/Luxembourg'
        ,'Europe/Madrid'
        ,'Europe/Malta'
        ,'Europe/Mariehamn'
        ,'Europe/Minsk'
        ,'Europe/Monaco'
        ,'Europe/Moscow'
        ,'Europe/Nicosia'
        ,'Europe/Oslo'
        ,'Europe/Paris'
        ,'Europe/Podgorica'
        ,'Europe/Prague'
        ,'Europe/Riga'
        ,'Europe/Rome'
        ,'Europe/Samara'
        ,'Europe/San_Marino'
        ,'Europe/Sarajevo'
        ,'Europe/Saratov'
        ,'Europe/Simferopol'
        ,'Europe/Skopje'
        ,'Europe/Sofia'
        ,'Europe/Stockholm'
        ,'Europe/Tallinn'
        ,'Europe/Tirane'
        ,'Europe/Tiraspol'
        ,'Europe/Ulyanovsk'
        ,'Europe/Uzhgorod'
        ,'Europe/Vaduz'
        ,'Europe/Vatican'
        ,'Europe/Vienna'
        ,'Europe/Vilnius'
        ,'Europe/Volgograd'
        ,'Europe/Warsaw'
        ,'Europe/Zagreb'
        ,'Europe/Zaporozhye'
        ,'Europe/Zurich'
        ,'Factory'
        ,'GB'
        ,'GB-Eire'
        ,'GMT+0'
        ,'GMT-0'
        ,'GMT0'
        ,'Greenwich'
        ,'HST'
        ,'Hongkong'
        ,'Iceland'
        ,'Indian/Antananarivo'
        ,'Indian/Chagos'
        ,'Indian/Christmas'
        ,'Indian/Cocos'
        ,'Indian/Comoro'
        ,'Indian/Kerguelen'
        ,'Indian/Mahe'
        ,'Indian/Maldives'
        ,'Indian/Mauritius'
        ,'Indian/Mayotte'
        ,'Indian/Reunion'
        ,'Iran'
        ,'Israel'
        ,'Jamaica'
        ,'Japan'
        ,'Kwajalein'
        ,'Libya'
        ,'MET'
        ,'MST'
        ,'MST7MDT'
        ,'Mexico/BajaNorte'
        ,'Mexico/BajaSur'
        ,'Mexico/General'
        ,'NZ'
        ,'NZ-CHAT'
        ,'Navajo'
        ,'PRC'
        ,'PST8PDT'
        ,'Pacific/Apia'
        ,'Pacific/Auckland'
        ,'Pacific/Bougainville'
        ,'Pacific/Chatham'
        ,'Pacific/Chuuk'
        ,'Pacific/Easter'
        ,'Pacific/Efate'
        ,'Pacific/Enderbury'
        ,'Pacific/Fakaofo'
        ,'Pacific/Fiji'
        ,'Pacific/Funafuti'
        ,'Pacific/Galapagos'
        ,'Pacific/Gambier'
        ,'Pacific/Guadalcanal'
        ,'Pacific/Guam'
        ,'Pacific/Honolulu'
        ,'Pacific/Johnston'
        # -- !! -- ,'Pacific/Kanton'
        ,'Pacific/Kiritimati'
        ,'Pacific/Kosrae'
        ,'Pacific/Kwajalein'
        ,'Pacific/Majuro'
        ,'Pacific/Marquesas'
        ,'Pacific/Midway'
        ,'Pacific/Nauru'
        ,'Pacific/Niue'
        ,'Pacific/Norfolk'
        ,'Pacific/Noumea'
        ,'Pacific/Pago_Pago'
        ,'Pacific/Palau'
        ,'Pacific/Pitcairn'
        ,'Pacific/Pohnpei'
        ,'Pacific/Ponape'
        ,'Pacific/Port_Moresby'
        ,'Pacific/Rarotonga'
        ,'Pacific/Saipan'
        ,'Pacific/Samoa'
        ,'Pacific/Tahiti'
        ,'Pacific/Tarawa'
        ,'Pacific/Tongatapu'
        ,'Pacific/Truk'
        ,'Pacific/Wake'
        ,'Pacific/Wallis'
        ,'Pacific/Yap'
        ,'Poland'
        ,'Portugal'
        ,'ROC'
        ,'ROK'
        ,'Singapore'
        ,'Turkey'
        ,'UCT'
        ,'US/Alaska'
        ,'US/Aleutian'
        ,'US/Arizona'
        ,'US/Central'
        ,'US/East-Indiana'
        ,'US/Eastern'
        ,'US/Hawaii'
        ,'US/Indiana-Starke'
        ,'US/Michigan'
        ,'US/Mountain'
        ,'US/Pacific'
        ,'US/Samoa'
        ,'UTC'
        ,'Universal'
        ,'W-SU'
        ,'WET'
        ,'Zulu'
    ]

    problematic_timezones_map = {}
    with act.db.connect() as con:
        cur = con.cursor()
        # random.choice(fb_time_zones)
        for tz_name in fb_time_zones:
            try:
                tz_info = get_timezone(tz_name)
                # print(tz_name)
                tm_region = (
                     datetime.time(11, 22, 33, 561400, get_timezone(tz_name))
                    ,datetime.time(12, 23, 34, 672400, get_timezone(tz_name))
                    ,datetime.time(13, 24, 35, 783400, get_timezone(tz_name))
                )
                ts_region = (
                     datetime.datetime(2020, 10, 20, 11, 22, 33, 561400, get_timezone(tz_name))
                    ,datetime.datetime(2021, 11, 21, 12, 23, 34, 672400, get_timezone(tz_name))
                    ,datetime.datetime(2022, 12, 22, 13, 24, 35, 783400, get_timezone(tz_name))
                )
                
                #------------------------------------------------
                cur.execute("insert into test(arr_tmtz, arr_tstz) values (?, ?) returning arr_tmtz,arr_tstz", ( tm_region, ts_region ) )
                inserted_tmtz_array, inserted_tstz_array = cur.fetchone()[:2]
                if set(inserted_tmtz_array) == set(tm_region) and set(inserted_tstz_array) == set(ts_region):
                    pass
                else:
                    print('MISMATCH detected between input data and stored result:')
                    for i,x in enumerate(inserted_tmtz_array):
                        print(i, f'Input element (TIME WITH TIME ZONE): {tm_region[i]}', f'; stored data: {x}')
                    for i,x in enumerate(inserted_tstz_array):
                        print(i, f'Input element (TIMESTAMP WITH TIME ZONE): {ts_region[i]}', f'; stored data: {x}')

                cur.execute("delete from test")
            except Exception as e:
                problematic_timezones_map[tz_name] = e.__str__()

        if problematic_timezones_map:
            print('Problems detected with time zone(s):')
        for k,v in problematic_timezones_map.items():
            print(k,v)

        act.stdout = capsys.readouterr().out
        assert act.clean_stdout == ''
        act.reset()