[Gambas-bugtracker] Bug #2780: "Type mismatch" error with PostgreSQL

Fri Aug 25 14:00:41 CEST 2023

http://gambaswiki.org/bugtracker/edit?object=BUG.2780&from=L21haW4-

Gianluigi GRADASCHI reported a new bug.

Summary
-------

"Type mismatch" error with PostgreSQL

Type             : Bug
Priority         : Medium
Gambas version   : Master
Product          : Database component

Description
-----------

Hi Benoit,

If I save both data, name and date together, the code works:

2023-08-25 13:18:11.099 gb.db.postgresql: 0x56122605cb20: select pg_attribute.attname, pg_attribute.atttypid::int, pg_attribute.atttypmod, pg_attribute.attnotnull, pg_get_expr(adbin, adrelid) AS adsrc, pg_attribute.atthasdef, pg_collation.collname from pg_class, pg_attribute LEFT JOIN pg_catalog.pg_attrdef  ON (pg_attrdef.adnum = pg_attribute.attnum AND pg_attrdef.adrelid = pg_attribute.attrelid) LEFT JOIN pg_collation ON (pg_collation.oid = pg_attribute.attcollation) where pg_class.relname = 't_flowers' and (pg_class.relnamespace in (select oid from pg_namespace where nspname = 'public')) and pg_attribute.attnum > 0 and not pg_attribute.attisdropped and pg_attribute.attrelid = pg_class.oid 
2023-08-25 13:18:11.101 gb.db.postgresql: 0x56122605cb20: select pg_att1.attname, pg_att1.atttypid::int, pg_cl.relname from pg_attribute pg_att1, pg_attribute pg_att2, pg_class pg_cl, pg_index pg_ind, pg_class pg_table where pg_table.relname = 't_flowers' AND pg_table.oid = pg_att1.attrelid AND pg_cl.oid = pg_ind.indexrelid and (pg_cl.relnamespace in (select oid from pg_namespace where nspname = 'public')) and pg_ind.indisprimary and pg_att2.attrelid = pg_ind.indexrelid and pg_att1.attrelid = pg_ind.indrelid and pg_att1.attnum = pg_ind.indkey[pg_att2.attnum-1] order by pg_att2.attnum
2023-08-25 13:18:11.105 gb.db.postgresql: 0x56122605cb20: INSERT INTO "t_flowers" ( "date", "name" ) VALUES ( '2023-08-25 00:00:00', E'Roses' ) RETURNING "id_flower"
2023-08-25 13:18:11.128 gb.db.postgresql: 0x56122605cb20: SELECT * FROM (SELECT *,ROW_NUMBER() OVER (ORDER BY "name","id_flower") AS __index FROM "t_flowers") AS __pg_sucks WHERE "id_flower" = 7 LIMIT 1
2023-08-25 13:18:11.130 gb.db.postgresql: 0x56122605cb20: SELECT COUNT(*) AS nRecord FROM "t_flowers"
2023-08-25 13:18:11.131 gb.db.postgresql: 0x56122605cb20: SELECT "name","id_flower" FROM "t_flowers" ORDER BY "name","id_flower" LIMIT 256
2023-08-25 13:18:11.131 gb.db.postgresql: 0x56122605cb20: SELECT * FROM "t_flowers" ORDER BY "name","id_flower" LIMIT 256

On the contrary, if I first save the name and only then save the date, it doesn't work and the error window appears:

Name works well:================>>> 2023-08-25 13:24:46.664 gb.db.postgresql: 0x556eea398a80: select pg_attribute.attname, pg_attribute.atttypid::int, pg_attribute.atttypmod, pg_attribute.attnotnull, pg_get_expr(adbin, adrelid) AS adsrc, pg_attribute.atthasdef, pg_collation.collname from pg_class, pg_attribute LEFT JOIN pg_catalog.pg_attrdef  ON (pg_attrdef.adnum = pg_attribute.attnum AND pg_attrdef.adrelid = pg_attribute.attrelid) LEFT JOIN pg_collation ON (pg_collation.oid = pg_attribute.attcollation) where pg_class.relname = 't_flowers' and (pg_class.relnamespace in (select oid from pg_namespace where nspname = 'public')) and pg_attribute.attnum > 0 and not pg_attribute.attisdropped and pg_attribute.attrelid = pg_class.oid 
2023-08-25 13:24:46.666 gb.db.postgresql: 0x556eea398a80: select pg_att1.attname, pg_att1.atttypid::int, pg_cl.relname from pg_attribute pg_att1, pg_attribute pg_att2, pg_class pg_cl, pg_index pg_ind, pg_class pg_table where pg_table.relname = 't_flowers' AND pg_table.oid = pg_att1.attrelid AND pg_cl.oid = pg_ind.indexrelid and (pg_cl.relnamespace in (select oid from pg_namespace where nspname = 'public')) and pg_ind.indisprimary and pg_att2.attrelid = pg_ind.indexrelid and pg_att1.attrelid = pg_ind.indrelid and pg_att1.attnum = pg_ind.indkey[pg_att2.attnum-1] order by pg_att2.attnum
2023-08-25 13:24:46.669 gb.db.postgresql: 0x556eea398a80: INSERT INTO "t_flowers" ( "date", "name" ) VALUES ( NULL, E'Roses' ) RETURNING "id_flower"
2023-08-25 13:24:46.691 gb.db.postgresql: 0x556eea398a80: SELECT * FROM (SELECT *,ROW_NUMBER() OVER (ORDER BY "name","id_flower") AS __index FROM "t_flowers") AS __pg_sucks WHERE "id_flower" = 7 LIMIT 1
2023-08-25 13:24:46.692 gb.db.postgresql: 0x556eea398a80: SELECT COUNT(*) AS nRecord FROM "t_flowers"
2023-08-25 13:24:46.693 gb.db.postgresql: 0x556eea398a80: SELECT "name","id_flower" FROM "t_flowers" ORDER BY "name","id_flower" LIMIT 256
2023-08-25 13:24:46.693 gb.db.postgresql: 0x556eea398a80: SELECT * FROM "t_flowers" ORDER BY "name","id_flower" LIMIT 256
Date not works:=================>>> 2023-08-25 13:25:29.960 gb.db.postgresql: 0x556eea398a80: SELECT * FROM "t_flowers" WHERE "id_flower" = 7
2023-08-25 13:25:29.961 gb.db.postgresql: 0x556eea398a80: select pg_attribute.attname, pg_attribute.atttypid::int, pg_attribute.atttypmod, pg_attribute.attnotnull, pg_get_expr(adbin, adrelid) AS adsrc, pg_attribute.atthasdef, pg_collation.collname from pg_class, pg_attribute LEFT JOIN pg_catalog.pg_attrdef  ON (pg_attrdef.adnum = pg_attribute.attnum AND pg_attrdef.adrelid = pg_attribute.attrelid) LEFT JOIN pg_collation ON (pg_collation.oid = pg_attribute.attcollation) where pg_class.relname = 't_flowers' and (pg_class.relnamespace in (select oid from pg_namespace where nspname = 'public')) and pg_attribute.attnum > 0 and not pg_attribute.attisdropped and pg_attribute.attrelid = pg_class.oid 
2023-08-25 13:25:29.962 gb.db.postgresql: 0x556eea398a80: select pg_att1.attname, pg_att1.atttypid::int, pg_cl.relname from pg_attribute pg_att1, pg_attribute pg_att2, pg_class pg_cl, pg_index pg_ind, pg_class pg_table where pg_table.relname = 't_flowers' AND pg_table.oid = pg_att1.attrelid AND pg_cl.oid = pg_ind.indexrelid and (pg_cl.relnamespace in (select oid from pg_namespace where nspname = 'public')) and pg_ind.indisprimary and pg_att2.attrelid = pg_ind.indexrelid and pg_att1.attrelid = pg_ind.indrelid and pg_att1.attnum = pg_ind.indkey[pg_att2.attnum-1] order by pg_att2.attnum

I enclose the test project and the image with the error window

Regards

Gianluigi

System information
------------------

[System]
Gambas=3.18.90 a840f49 (master)
OperatingSystem=Linux
Distribution=Linux Mint 21.2 Victoria
Kernel=5.15.0-79-generic
Architecture=x86_64
Cores=1
Memory=6930M
Language=it_IT.UTF-8
Desktop=CINNAMON
DesktopResolution=96
DesktopScale=7
WidgetTheme=mint-y-aqua
Font=Ubuntu,10

[Programs]
gcc=gcc (Ubuntu 11.4.0-1ubuntu1~22.04) 11.4.0
git=git version 2.34.1

[Libraries]
Cairo=libcairo.so.2.11600.0
Curl=libcurl.so.4.7.0
DBus=libdbus-1.so.3.19.13
GDK2=libgdk-x11-2.0.so.0.2400.33
GDK3=libgdk-3.so.0.2404.29
GStreamer=libgstreamer-1.0.so.0.2003.0
GTK+2=libgtk-x11-2.0.so.0.2400.33
GTK+3=libgtk-3.so.0.2404.29
OpenGL=libGL.so.1.7.0
Poppler=libpoppler.so.118.0.0
QT5=libQt5Core.so.5.15.3
RSvg=librsvg-2.so.2.48.0
SDL=libSDL2-2.0.so.0.18.2
SQLite 3=libsqlite3.so.0.8.6

[Environment]
CINNAMON_VERSION=5.8.4
DBUS_SESSION_BUS_ADDRESS=unix:path=/run/user/1000/bus
DESKTOP_SESSION=cinnamon
DISPLAY=:0
GB_GUI=gb.gtk3
GDMSESSION=cinnamon
GDM_LANG=it
GIO_LAUNCHED_DESKTOP_FILE=/usr/share/applications/gambas3.desktop
GIO_LAUNCHED_DESKTOP_FILE_PID=2167
GJS_DEBUG_OUTPUT=stderr
GJS_DEBUG_TOPICS=JS ERROR;JS LOG
GNOME_DESKTOP_SESSION_ID=this-is-deprecated
GPG_AGENT_INFO=/run/user/1000/gnupg/S.gpg-agent:0:1
GTK3_MODULES=xapp-gtk3-module
GTK_MODULES=gail:atk-bridge
HOME=<home>
LANG=it_IT.UTF-8
LANGUAGE=it_IT.UTF-8
LC_ALL=it_IT.UTF-8
LOGNAME=<user>
PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/usr/games:/usr/local/games:/snap/bin
PWD=<home>
QT_ACCESSIBILITY=1
QT_LOGGING_RULES=*.debug=false
QT_QPA_PLATFORMTHEME=qt5ct
SESSION_MANAGER=local/<hostname>:@/tmp/.ICE-unix/1317,unix/<hostname>:/tmp/.ICE-unix/1317
SHELL=/bin/bash
SHLVL=0
SSH_AUTH_SOCK=/run/user/1000/keyring/ssh
TZ=:/etc/localtime
USER=<user>
XAUTHORITY=<home>/.Xauthority
XDG_CONFIG_DIRS=/etc/xdg/xdg-cinnamon:/etc/xdg
XDG_CURRENT_DESKTOP=X-Cinnamon
XDG_DATA_DIRS=/usr/share/cinnamon:/usr/share/gnome:<home>/.local/share/flatpak/exports/share:/var/lib/flatpak/exports/share:/usr/local/share:/usr/share
XDG_GREETER_DATA_DIR=/var/lib/lightdm-data/<user>
XDG_RUNTIME_DIR=/run/user/1000
XDG_SEAT=seat0
XDG_SEAT_PATH=/org/freedesktop/DisplayManager/Seat0
XDG_SESSION_CLASS=user
XDG_SESSION_DESKTOP=cinnamon
XDG_SESSION_ID=c2
XDG_SESSION_PATH=/org/freedesktop/DisplayManager/Session0
XDG_SESSION_TYPE=x11
XDG_VTNR=7