SQLite Forum

pragma table_info Bug
Login

pragma table_info Bug

(1) By Patrick Ottavi (Farscape06) on 2022-01-10 08:04:22 [link] [source]

Hello ,
since version 3.37.0 the pragma table info command gives incorrect information on unsigned word fields.

example: pragma table_info(database)

cid	name	type	notnull	dflt_value	pk
0	CleArt	VARCHAR(10)	0		1
1	bNoWeb	UNSIGNED WORD](1	0	'0'	0
2	RefFrn	VARCHAR(13)	0	''	0
3	NomArt	VARCHAR(40)	0	''	0
4	OldLib	VARCHAR(10)	0	''	0
5	Fou	VARCHAR(6)	0	''	0
6	CleFam	VARCHAR(3)	0	''	0
7	CleSFam	VARCHAR(3)	0	''	0
8	CTva	UNSIGNED WORD](1	0	'0'	0
9	CTpf	UNSIGNED WORD](1	0	'0'	0
10	UVente	VARCHAR(2)	0	''	0

there are brackets instead of parentheses : bNoWeb UNSIGNED WORD(1)

(2) By Richard Hipp (drh) on 2022-01-10 11:41:00 in reply to 1 [link] [source]

Unable to repro. Please launch the CLI against your database and then run the following commands:

.quote
SELECT * FROM sqlite_schema;

Post the output here. Thanks.

(3) By Dan Kennedy (dan) on 2022-01-10 11:42:53 in reply to 1 [link] [source]

Can you post the output of the following?

    SELECT sql FROM sqlite_schema WHERE name='database'

It's working in at least some cases here:

$ cat ./s.sql

CREATE TABLE database(
    CleArt      VARCHAR(10),
    bNoWeb      UNSIGNED WORD(1),
    RefFrn      VARCHAR(13),
    NomArt      VARCHAR(40),
    OldLib      VARCHAR(10),
    Fou VARCHAR(6),
    CleFam      VARCHAR(3),
    CleSFam     VARCHAR(3),
    CTva        UNSIGNED WORD(1),
    CTpf        UNSIGNED WORD(1),
    UVente      VARCHAR(2)
);
.mode box
SELECT sqlite_version();
PRAGMA table_info(database);

$ ./sqlite3 < ./s.sql
┌──────────────────┐
│ sqlite_version() │
├──────────────────┤
│ 3.37.0           │
└──────────────────┘
┌─────┬─────────┬──────────────────┬─────────┬────────────┬────┐
│ cid │  name   │       type       │ notnull │ dflt_value │ pk │
├─────┼─────────┼──────────────────┼─────────┼────────────┼────┤
│ 0   │ CleArt  │ VARCHAR(10)      │ 0       │            │ 0  │
│ 1   │ bNoWeb  │ UNSIGNED WORD(1) │ 0       │            │ 0  │
│ 2   │ RefFrn  │ VARCHAR(13)      │ 0       │            │ 0  │
│ 3   │ NomArt  │ VARCHAR(40)      │ 0       │            │ 0  │
│ 4   │ OldLib  │ VARCHAR(10)      │ 0       │            │ 0  │
│ 5   │ Fou     │ VARCHAR(6)       │ 0       │            │ 0  │
│ 6   │ CleFam  │ VARCHAR(3)       │ 0       │            │ 0  │
│ 7   │ CleSFam │ VARCHAR(3)       │ 0       │            │ 0  │
│ 8   │ CTva    │ UNSIGNED WORD(1) │ 0       │            │ 0  │
│ 9   │ CTpf    │ UNSIGNED WORD(1) │ 0       │            │ 0  │
│ 10  │ UVente  │ VARCHAR(2)       │ 0       │            │ 0  │
└─────┴─────────┴──────────────────┴─────────┴────────────┴────┘

(4) By ET (EricTsau) on 2022-01-11 12:36:08 in reply to 1 [link] [source]

The brackets must be from your SQL schema.

sqlite> create table [h](i[unsigned word](3));
sqlite> pragma table_info(h);
0|i|unsigned word](3|0||0

(5) By ET (EricTsau) on 2022-01-11 12:48:10 in reply to 4 [link] [source]

Use double quotes or backticks instead. (But then field size won't be shown in table_info).

sqlite> create table h(i "unsigned word"(3));
sqlite> pragma table_info(h);
cid|name|type|notnull|dflt_value|pk
0|i|unsigned word|0||0

(6) By Patrick Ottavi (Farscape06) on 2022-01-11 14:16:07 in reply to 5 [source]

hello,
I have these problems on bases which are more than 10 years old.
I have never created a table using square brackets, my creation process is automated, however I see that in the schema of some of my tables there are square brackets and the problem must be there.
In my programs I have a check of the zones of the tables using table_info until version 3.36.0 I had no problem. as of 3.37 I have seen it still for the same databases.
example I found this table where I have a description with and without  brackets on the same table, which I don't understand how it is possible by my creation process.

sqlite> .schema cbreleve
CREATE TABLE [CBRELEVE] (
  [Clt] VARCHAR(6) NOT NULL,
  [Typ] [UNSIGNED WORD](1) NOT NULL,
  [Date] DATETEXT NOT NULL,
  [nSeq] [INTEGER UNSIGNED] NOT NULL,
  [Nf] [DOUBLE PRECISION],
  [Nom] VARCHAR(35),
  [RegClt] VARCHAR(4),
  [Tra] [UNSIGNED WORD](1),
  [PiedHtMon0] [DOUBLE PRECISION],
  [PiedHtInd0] [UNSIGNED WORD](1),
  [PiedHtMon1] [DOUBLE PRECISION],
  [PiedHtInd1] [UNSIGNED WORD](1),
  [PiedHtMon2] [DOUBLE PRECISION],
  [PiedHtInd2] [UNSIGNED WORD](1),
  [PiedHtMon3] [DOUBLE PRECISION],
  [PiedHtInd3] [UNSIGNED WORD](1),
  [PiedHtMon4] [DOUBLE PRECISION],
  [PiedHtInd4] [UNSIGNED WORD](1),
  [PiedHtMon5] [DOUBLE PRECISION],
  [PiedHtInd5] [UNSIGNED WORD](1),
  [PiedHtMon6] [DOUBLE PRECISION],
  [PiedHtInd6] [UNSIGNED WORD](1),
  [PiedHtMon7] [DOUBLE PRECISION],
  [PiedHtInd7] [UNSIGNED WORD](1),
  [PiedHtMon8] [DOUBLE PRECISION],
  [PiedHtInd8] [UNSIGNED WORD](1),
  [PiedHtMon9] [DOUBLE PRECISION],
  [PiedHtInd9] [UNSIGNED WORD](1),
  [PiedHtMon10] [DOUBLE PRECISION],
  [PiedHtInd10] [UNSIGNED WORD](1),
  [PiedHtMon11] [DOUBLE PRECISION],
  [PiedHtInd11] [UNSIGNED WORD](1),
  [dPiedTpf] [DOUBLE PRECISION],
  [dPiedMht] [DOUBLE PRECISION],
  [dPiedRem0] [DOUBLE PRECISION],
  [dPiedRem1] [DOUBLE PRECISION],
  [dPiedTx0] [DOUBLE PRECISION],
  [dPiedTx1] [DOUBLE PRECISION],
  [dPiedPnt] [DOUBLE PRECISION],
  [dPiedTva] [DOUBLE PRECISION],
  [dPiedTTC] [DOUBLE PRECISION], dPiedTaxe0 DOUBLE PRECISION DEFAULT '0', dPiedT
axe1 DOUBLE PRECISION DEFAULT '0', dPiedTaxe2 DOUBLE PRECISION DEFAULT '0', dPie
dTaxe3 DOUBLE PRECISION DEFAULT '0', dPiedTaxe4 DOUBLE PRECISION DEFAULT '0', dP
iedTaxe5 DOUBLE PRECISION DEFAULT '0', dPiedTaxe6 DOUBLE PRECISION DEFAULT '0',
dPiedTaxe7 DOUBLE PRECISION DEFAULT '0', PiedTaxeInd0 UNSIGNED WORD(1) DEFAULT '
 ', PiedTaxeInd1 UNSIGNED WORD(1) DEFAULT ' ', PiedTaxeInd2 UNSIGNED WORD(1) DEF
AULT ' ', PiedTaxeInd3 UNSIGNED WORD(1) DEFAULT ' ', PiedTaxeInd4 UNSIGNED WORD(
1) DEFAULT ' ', PiedTaxeInd5 UNSIGNED WORD(1) DEFAULT ' ', PiedTaxeInd6 UNSIGNED
 WORD(1) DEFAULT ' ', PiedTaxeInd7 UNSIGNED WORD(1) DEFAULT ' ',
  CONSTRAINT [sqlite_autoindex_CBRELEVE_1] PRIMARY KEY ([Clt], [Typ], [Date], [n
Seq]));

and if I ask for a table_info on this table I get:

sqlite> pragma table_info(cbreleve);
┌─────┬──────────────┬──────────────────┬─────────┬────────────┬────┐
│ cid │     name     │       type       │ notnull │ dflt_value │ pk │
├─────┼──────────────┼──────────────────┼─────────┼────────────┼────┤
│ 0   │ Clt          │ VARCHAR(6)       │ 1       │            │ 1  │
│ 1   │ Typ          │ UNSIGNED WORD](1 │ 1       │            │ 2  │
│ 2   │ Date         │ DATETEXT         │ 1       │            │ 3  │
│ 3   │ nSeq         │ INTEGER UNSIGNED │ 1       │            │ 4  │
│ 4   │ Nf           │ DOUBLE PRECISION │ 0       │            │ 0  │
│ 5   │ Nom          │ VARCHAR(35)      │ 0       │            │ 0  │
│ 6   │ RegClt       │ VARCHAR(4)       │ 0       │            │ 0  │
│ 7   │ Tra          │ UNSIGNED WORD](1 │ 0       │            │ 0  │
│ 8   │ PiedHtMon0   │ DOUBLE PRECISION │ 0       │            │ 0  │
│ 9   │ PiedHtInd0   │ UNSIGNED WORD](1 │ 0       │            │ 0  │
│ 10  │ PiedHtMon1   │ DOUBLE PRECISION │ 0       │            │ 0  │
│ 11  │ PiedHtInd1   │ UNSIGNED WORD](1 │ 0       │            │ 0  │
│ 12  │ PiedHtMon2   │ DOUBLE PRECISION │ 0       │            │ 0  │
│ 13  │ PiedHtInd2   │ UNSIGNED WORD](1 │ 0       │            │ 0  │
│ 14  │ PiedHtMon3   │ DOUBLE PRECISION │ 0       │            │ 0  │
│ 15  │ PiedHtInd3   │ UNSIGNED WORD](1 │ 0       │            │ 0  │
│ 16  │ PiedHtMon4   │ DOUBLE PRECISION │ 0       │            │ 0  │
│ 17  │ PiedHtInd4   │ UNSIGNED WORD](1 │ 0       │            │ 0  │
│ 18  │ PiedHtMon5   │ DOUBLE PRECISION │ 0       │            │ 0  │
│ 19  │ PiedHtInd5   │ UNSIGNED WORD](1 │ 0       │            │ 0  │
│ 20  │ PiedHtMon6   │ DOUBLE PRECISION │ 0       │            │ 0  │
│ 21  │ PiedHtInd6   │ UNSIGNED WORD](1 │ 0       │            │ 0  │
│ 22  │ PiedHtMon7   │ DOUBLE PRECISION │ 0       │            │ 0  │
│ 23  │ PiedHtInd7   │ UNSIGNED WORD](1 │ 0       │            │ 0  │
│ 24  │ PiedHtMon8   │ DOUBLE PRECISION │ 0       │            │ 0  │
│ 25  │ PiedHtInd8   │ UNSIGNED WORD](1 │ 0       │            │ 0  │
│ 26  │ PiedHtMon9   │ DOUBLE PRECISION │ 0       │            │ 0  │
│ 27  │ PiedHtInd9   │ UNSIGNED WORD](1 │ 0       │            │ 0  │
│ 28  │ PiedHtMon10  │ DOUBLE PRECISION │ 0       │            │ 0  │
│ 29  │ PiedHtInd10  │ UNSIGNED WORD](1 │ 0       │            │ 0  │
│ 30  │ PiedHtMon11  │ DOUBLE PRECISION │ 0       │            │ 0  │
│ 31  │ PiedHtInd11  │ UNSIGNED WORD](1 │ 0       │            │ 0  │
│ 32  │ dPiedTpf     │ DOUBLE PRECISION │ 0       │            │ 0  │
│ 33  │ dPiedMht     │ DOUBLE PRECISION │ 0       │            │ 0  │
│ 34  │ dPiedRem0    │ DOUBLE PRECISION │ 0       │            │ 0  │
│ 35  │ dPiedRem1    │ DOUBLE PRECISION │ 0       │            │ 0  │
│ 36  │ dPiedTx0     │ DOUBLE PRECISION │ 0       │            │ 0  │
│ 37  │ dPiedTx1     │ DOUBLE PRECISION │ 0       │            │ 0  │
│ 38  │ dPiedPnt     │ DOUBLE PRECISION │ 0       │            │ 0  │
│ 39  │ dPiedTva     │ DOUBLE PRECISION │ 0       │            │ 0  │
│ 40  │ dPiedTTC     │ DOUBLE PRECISION │ 0       │            │ 0  │
│ 41  │ dPiedTaxe0   │ DOUBLE PRECISION │ 0       │ '0'        │ 0  │
│ 42  │ dPiedTaxe1   │ DOUBLE PRECISION │ 0       │ '0'        │ 0  │
│ 43  │ dPiedTaxe2   │ DOUBLE PRECISION │ 0       │ '0'        │ 0  │
│ 44  │ dPiedTaxe3   │ DOUBLE PRECISION │ 0       │ '0'        │ 0  │
│ 45  │ dPiedTaxe4   │ DOUBLE PRECISION │ 0       │ '0'        │ 0  │
│ 46  │ dPiedTaxe5   │ DOUBLE PRECISION │ 0       │ '0'        │ 0  │
│ 47  │ dPiedTaxe6   │ DOUBLE PRECISION │ 0       │ '0'        │ 0  │
│ 48  │ dPiedTaxe7   │ DOUBLE PRECISION │ 0       │ '0'        │ 0  │
│ 49  │ PiedTaxeInd0 │ UNSIGNED WORD(1) │ 0       │ ' '        │ 0  │
│ 50  │ PiedTaxeInd1 │ UNSIGNED WORD(1) │ 0       │ ' '        │ 0  │
│ 51  │ PiedTaxeInd2 │ UNSIGNED WORD(1) │ 0       │ ' '        │ 0  │
│ 52  │ PiedTaxeInd3 │ UNSIGNED WORD(1) │ 0       │ ' '        │ 0  │
│ 53  │ PiedTaxeInd4 │ UNSIGNED WORD(1) │ 0       │ ' '        │ 0  │
│ 54  │ PiedTaxeInd5 │ UNSIGNED WORD(1) │ 0       │ ' '        │ 0  │
│ 55  │ PiedTaxeInd6 │ UNSIGNED WORD(1) │ 0       │ ' '        │ 0  │
│ 56  │ PiedTaxeInd7 │ UNSIGNED WORD(1) │ 0       │ ' '        │ 0  │
└─────┴──────────────┴──────────────────┴─────────┴────────────┴────┘
I can't explain where the brackets come from in my table schema, I would like to do a table_info with a version of sqlite3.exe older than version 3.37.0 to see the difference. in the end, I managed to adapt my code to check the areas of tables.

(7) By Gunter Hick (gunter_hick) on 2022-01-11 15:31:00 in reply to 6 [link] [source]

Perhaps you should check your schema creation code. You have square brackets in all type names with embedded space and none in type names without embedded space.

Maybe you would like to explain why you choose to copy type declarations verbatim from your calling programming language over to automatically generated schema definitions. SQlite only rudimentarily parses them to determine affinity.

INTEGER UNSIGNED translates into INTEGER affinity, whereas UNSIGNED WORD(1) translates to NUMERIC affinity. Both may hold negative values. If you really want to limit UNSIGNED WORD to 0..65535 then you should generate an appropriate check constraint too.

BTW: Your schema contains multiple fields whose names differ only by a numeric suffix. This begs the question as to why the schema is not normalized.

(8) By Kees Nuyt (knu) on 2022-01-11 19:13:17 in reply to 6 [link] [source]

If you decide to use square brackets around type names, why don't you include the parenthesized length part of the types in the square brackets?

In other words, I would expect

  [RegClt] VARCHAR(4),
  [Tra] [UNSIGNED WORD(1)],
  [PiedHtMon0] [DOUBLE PRECISION],

instead of

  [RegClt] VARCHAR(4),
  [Tra] [UNSIGNED WORD](1),
  [PiedHtMon0] [DOUBLE PRECISION],

I'll leave the testing to you...

-- 
Regards,
Kees NUyt

(9) By Patrick Ottavi (Farscape06) on 2022-01-12 07:43:58 in reply to 8 [link] [source]

Hello,
Except that as I specified I never declared the table with brackets ..,
my creation process is automated by program in c ++ and I have tables on bases which have brackets and others not .(my databases are not recent, some are over 10 years old)
Then I may have declared a zone incorrectly, but in no case did I put square brackets in my declarations. :)

Regards,
Patrick

(10) By Kees Nuyt (knu) on 2022-01-12 14:27:02 in reply to 9 [link] [source]

If the generator can't be improved, you will have to improve the schema in the database.

For your convenience, here is a Proof of Concept bash script to repair the definition of CBRELEVE.

#!/bin/bash
# PoC for patching a malformed schema
# Use at your own risk.

db=tmp/test.db
tee=tmp/test.tee
mkdir -p tmp
rm -f $db*

# generate a test database
sqlite3 $db  <<'EOF'
CREATE TABLE [CBRELEVE] (
  [Clt] VARCHAR(6) NOT NULL,
  [Typ] [UNSIGNED WORD](1) NOT NULL,
  [Date] DATETEXT NOT NULL,
  [nSeq] [INTEGER UNSIGNED] NOT NULL,
  [Nf] [DOUBLE PRECISION],
  [Nom] VARCHAR(35),
  [RegClt] VARCHAR(4),
  [Tra] [UNSIGNED WORD](1),
  [PiedHtMon0] [DOUBLE PRECISION],
  [PiedHtInd0] [UNSIGNED WORD](1),
  [PiedHtMon1] [DOUBLE PRECISION],
  [PiedHtInd1] [UNSIGNED WORD](1),
  [PiedHtMon2] [DOUBLE PRECISION],
  [PiedHtInd2] [UNSIGNED WORD](1),
  [PiedHtMon3] [DOUBLE PRECISION],
  [PiedHtInd3] [UNSIGNED WORD](1),
  [PiedHtMon4] [DOUBLE PRECISION],
  [PiedHtInd4] [UNSIGNED WORD](1),
  [PiedHtMon5] [DOUBLE PRECISION],
  [PiedHtInd5] [UNSIGNED WORD](1),
  [PiedHtMon6] [DOUBLE PRECISION],
  [PiedHtInd6] [UNSIGNED WORD](1),
  [PiedHtMon7] [DOUBLE PRECISION],
  [PiedHtInd7] [UNSIGNED WORD](1),
  [PiedHtMon8] [DOUBLE PRECISION],
  [PiedHtInd8] [UNSIGNED WORD](1),
  [PiedHtMon9] [DOUBLE PRECISION],
  [PiedHtInd9] [UNSIGNED WORD](1),
  [PiedHtMon10] [DOUBLE PRECISION],
  [PiedHtInd10] [UNSIGNED WORD](1),
  [PiedHtMon11] [DOUBLE PRECISION],
  [PiedHtInd11] [UNSIGNED WORD](1),
  [dPiedTpf] [DOUBLE PRECISION],
  [dPiedMht] [DOUBLE PRECISION],
  [dPiedRem0] [DOUBLE PRECISION],
  [dPiedRem1] [DOUBLE PRECISION],
  [dPiedTx0] [DOUBLE PRECISION],
  [dPiedTx1] [DOUBLE PRECISION],
  [dPiedPnt] [DOUBLE PRECISION],
  [dPiedTva] [DOUBLE PRECISION],
  [dPiedTTC] [DOUBLE PRECISION], dPiedTaxe0 DOUBLE PRECISION DEFAULT '0', dPiedTaxe1 DOUBLE PRECISION DEFAULT '0', dPiedTaxe2 DOUBLE PRECISION DEFAULT '0', dPiedTaxe3 DOUBLE PRECISION DEFAULT '0', dPiedTaxe4 DOUBLE PRECISION DEFAULT '0', dPiedTaxe5 DOUBLE PRECISION DEFAULT '0', dPiedTaxe6 DOUBLE PRECISION DEFAULT '0', dPiedTaxe7 DOUBLE PRECISION DEFAULT '0', PiedTaxeInd0 UNSIGNED WORD(1) DEFAULT ' ', PiedTaxeInd1 UNSIGNED WORD(1) DEFAULT ' ', PiedTaxeInd2 UNSIGNED WORD(1) DEFAULT ' ', PiedTaxeInd3 UNSIGNED WORD(1) DEFAULT ' ', PiedTaxeInd4 UNSIGNED WORD(1) DEFAULT ' ', PiedTaxeInd5 UNSIGNED WORD(1) DEFAULT ' ', PiedTaxeInd6 UNSIGNED WORD(1) DEFAULT ' ', PiedTaxeInd7 UNSIGNED WORD(1) DEFAULT ' ', 
  CONSTRAINT [sqlite_autoindex_CBRELEVE_1] PRIMARY KEY ([Clt], [Typ], [Date], [nSeq]));
EOF

# Generate correctly spelled DDL
patch=$( sqlite3 $db \
	"SELECT quote(sql) FROM sqlite_schema \
	WHERE type=='table' AND name=='CBRELEVE'" \
| gawk '{
	t1 = gensub(/\](\([[:digit:]]+\))/,"\\1]","g",$0)
	t2 = gensub(/, ([^\n[])/,",\n\\1","g",t1)
	print t2
}' | tee $tee )

# apply the patch to  the database
sqlite3 $db \
	"PRAGMA writable_schema=on" \
	"UPDATE sqlite_schema SET sql=$patch \
	WHERE type=='table' AND name=='CBRELEVE'"

# check the schema
sqlite3 $db ".schema" | less -NS
# printf "%s\n" "$patch"
# less -NS $tee

Enjoy!

-- 
Regards,
Kees Nuyt

(11) By Patrick Ottavi (Farscape06) on 2022-01-13 07:33:32 in reply to 10 [link] [source]

Hello,
Thank you for your reply. :)

Regards,
Patrick.